Enable nolock hint for the background non-editable dataflow query that is used by the Power BI service to retrieve SQL server metadata. Having an option to enable this hint would avoid load failures/timeouts for dataflows

Dataflows execute the query below in the background to get metadata related to the SQL server content. This query is not editable to Power BI administrators or pro users. The statement is executed without a nolock hint and this can cause dataflows to fail if there is a header blocker on the metadata data on the sql server side.

The idea is to introduce a simple configuration parameter or toggle that would allow this background process to leverage a nolock hint for the query below. Making it an optional toggle would give administrators an option to reduce the likelihood of dataflow failures. The background query executed by the Power BI service returns the database name, schema, table name, table type, description, created and modified dates (see query below). These details don't change often and a hint would resolve the current dataflow load failures. As we don't have any control on the background Power BI service query we are unable to edit or provide the necessary nolock hint.

background non-editable query is below:

SELECT

t.TABLE_CATALOG

  ,t.TABLE_SCHEMA

  ,t.TABLE_NAME

  ,t.TABLE_TYPE

  ,tv.create_date AS CREATED_DATE

  ,tv.modify_date AS MODIFIED_DATE

  ,CAST(e.value AS NVARCHAR(MAX)) AS DESCRIPTION

FROM

INFORMATION_SCHEMA.TABLES t

JOIN sys.schemas s ON

s.name = t.TABLE_SCHEMA

JOIN sys.objects tv ON

tv.name = t.TABLE_NAME

AND tv.schema_id = s.schema_id

AND tv.parent_object_id = 0

LEFT OUTER JOIN sys.extended_properties e ON

tv.object_id = e.major_id

AND e.minor_id = 0

AND e.class = 1

AND e.name = 'MS_Description'

WHERE

1 = 1

AND 1 = 1;es e on tv.object_id = e.major_id and e.minor_id = 0 and e.class = 1 and e.name = 'MS_Description'

where 1=1 and 1=1