Power BI DirectQuery vs Import Mode: What Every SQL Server DBA Needs to Know

---

Your organization deploys Power BI reports connected to SQL Server. Users start complaining that dashboards are slow. The Power BI developer says the data model is fine. You look at SQL Server and see queries you have never seen before hammering the production server every time someone interacts with a report. Or the opposite: the developer chose Import mode and now nobody trusts the data because it was last refreshed six hours ago and the numbers do not match what the application shows.

Both problems come from the same root cause: the connection mode decision was made without understanding what it means for the SQL Server side. Most Power BI articles explain DirectQuery and Import mode from the Power BI developer perspective. This article explains it from the SQL Server DBA perspective, because that is where the performance impact actually lands.

1 How Import Mode Works: Power BI as a Copy of Your Data Beginner

In Import mode Power BI connects to SQL Server, runs queries to extract data, and stores a compressed copy of that data inside the Power BI dataset using its in-memory engine called VertiPaq. From that point forward all report interactions run against the in-memory copy, not against SQL Server. The connection to SQL Server is only used during scheduled refresh operations.

From the SQL Server DBA’s perspective, Import mode looks like this: once or several times per day, Power BI connects and runs large extraction queries that read significant volumes of data from your tables. These queries can be heavy and should be expected during the refresh window. Between refreshes, SQL Server receives no queries from Power BI at all. Users interacting with reports do not touch SQL Server.

The data in the Power BI dataset is a snapshot. If an order is placed at 9 AM and the next scheduled refresh is at 6 PM, that order will not appear in any Power BI report until the 6 PM refresh completes. For many reporting use cases this is completely acceptable. Monthly sales dashboards, weekly operational reports, and historical trend analysis do not need data that is current to the minute.

2 How DirectQuery Works: Power BI as a Query Generator Beginner

In DirectQuery mode Power BI stores no data locally. Every time a user interacts with a report, which means every filter change, every slicer selection, every cross-highlight click, Power BI generates one or more SQL queries and sends them to SQL Server in real time. The results come back, Power BI renders the visual, and the next interaction triggers the next set of queries.

A single report page with 10 visuals can send 10 or more queries to SQL Server every time a user changes a filter. If 50 users are viewing the same report and all actively interacting with it simultaneously, SQL Server receives hundreds of queries per minute that did not exist before the report was published.

The critical point for DBAs is that these queries are generated by Power BI’s query engine, not written by a developer. You cannot see them in advance. They follow patterns based on the visuals and DAX measures in the report, but their exact form depends on the report design and the specific user interactions. The only way to see what DirectQuery is sending to SQL Server is to monitor it after the fact using DMVs or Query Store.

3 The Third Option: Direct Lake (Microsoft Fabric) Beginner

Direct Lake is a connection mode available only in Microsoft Fabric, not in traditional Power BI against an on-premises or Azure SQL Server. It reads data directly from Parquet files in OneLake without copying data the way Import mode does. Query performance is close to Import mode speed without the refresh overhead.

For organizations running SQL Server on-premises or in Azure SQL Database, Direct Lake is not an available option. It is worth knowing it exists because it will come up in conversations about modern data architecture, but it does not change the Import vs DirectQuery decision for traditional SQL Server workloads. If your organization is evaluating Microsoft Fabric, Direct Lake becomes part of the conversation at that point.

4 Side by Side: What Each Mode Means for SQL Server Beginner

Factor	Import Mode	DirectQuery
SQL Server query load	Predictable, scheduled	Continuous, scales with users
Data freshness	Snapshot at last refresh	Real time
Report speed	Fast (in-memory)	Depends on SQL Server
SQL Server indexing importance	Moderate	Critical
Concurrent user impact on SQL Server	None	Linear scaling
DAX function availability	Full	Restricted subset
Data volume	Limited by dataset size	No dataset size limit
Row-level security	Power BI managed	SQL Server enforced via SSO
DBA visibility before go-live	Can review refresh queries	Queries generated at runtime

5 What DirectQuery Queries Actually Look Like on SQL Server Intermediate

Power BI wraps DirectQuery-generated SQL in a specific pattern. Understanding the pattern helps DBAs recognize these queries in DMVs and Query Store, and helps explain to developers why certain Power BI report designs cause performance problems.

Every DirectQuery query is wrapped in a subquery alias that Power BI generates. The outer query applies aggregation and filtering. The inner query selects from the source table with column aliases that match the Power BI field names. This wrapping pattern means the query optimizer sees a derived table rather than a direct table reference, which can prevent some index seeks and force table scans in certain cases.

-- Example of a typical Power BI DirectQuery-generated query
-- This is what SQL Server actually receives when a user
-- changes a date slicer on a sales report

SELECT
    [t0].[OrderYear]                    AS [c1],
    [t0].[ProductCategory]              AS [c2],
    SUM([t0].[OrderAmount])             AS [c3],
    COUNT([t0].[OrderID])               AS [c4]
FROM (
    SELECT
        YEAR([OrderDate])               AS [OrderYear],
        [ProductCategory],
        [OrderAmount],
        [OrderID]
    FROM [dbo].[SalesOrders]
    WHERE [OrderDate] >= CONVERT(DATETIME, '2026-01-01', 120)
    AND   [OrderDate] <  CONVERT(DATETIME, '2026-07-01', 120)
) AS [t0]
GROUP BY [t0].[OrderYear], [t0].[ProductCategory]

-- Note the wrapping subquery pattern.
-- The optimizer must resolve the derived table before applying
-- the outer GROUP BY and aggregation.
-- An index on (OrderDate, ProductCategory) INCLUDE (OrderAmount, OrderID)
-- would allow the inner query to seek rather than scan.

-- DirectQuery also generates parameter queries for slicer values:
SELECT DISTINCT
    [t0].[ProductCategory] AS [c1]
FROM (
    SELECT [ProductCategory]
    FROM [dbo].[SalesOrders]
) AS [t0]
ORDER BY [c1] ASC
-- This query runs every time the report page loads to populate
-- the ProductCategory slicer dropdown values.

A report page with 8 visuals and 3 slicers typically generates 8 to 11 queries when the page first loads, one per visual plus one per slicer to populate the available values. When a user changes a slicer, all 8 visual queries re-execute with the new filter applied. A user making 10 filter changes in a session generates 80 to 110 queries against SQL Server from their single session.

6 How to Monitor DirectQuery Load on SQL Server Intermediate

DirectQuery queries appear in all the standard SQL Server monitoring tools. They are identifiable by the wrapping subquery pattern and by the Power BI application name in the connection properties.

-- Find active Power BI DirectQuery connections and queries
SELECT
    r.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    r.status,
    r.command,
    r.wait_type,
    r.wait_time / 1000              AS WaitSeconds,
    r.total_elapsed_time / 1000     AS ElapsedSeconds,
    r.logical_reads,
    LEFT(qt.text, 500)              AS QueryText
FROM sys.dm_exec_requests          r
JOIN sys.dm_exec_sessions          s  ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) qt
WHERE s.program_name LIKE '%Power BI%'
   OR s.program_name LIKE '%PBI%'
   OR s.host_name    LIKE '%Power BI%'
ORDER BY r.logical_reads DESC;

-- Use Query Store to find the most expensive Power BI queries
-- Query Store captures the DirectQuery-generated SQL with full plan history
SELECT TOP 20
    qsqt.query_sql_text,
    qsrs.count_executions,
    ROUND(qsrs.avg_duration / 1000.0, 2)    AS AvgDurationMs,
    ROUND(qsrs.avg_logical_io_reads, 0)     AS AvgLogicalReads,
    qsrs.last_execution_time
FROM sys.query_store_runtime_stats          qsrs
JOIN sys.query_store_plan                   qsp  ON qsp.plan_id        = qsrs.plan_id
JOIN sys.query_store_query                  qsq  ON qsq.query_id       = qsp.query_id
JOIN sys.query_store_query_text             qsqt ON qsqt.query_text_id = qsq.query_text_id
-- Filter for Power BI's subquery wrapping pattern
WHERE qsqt.query_sql_text LIKE '%AS [t0]%'
   OR qsqt.query_sql_text LIKE '%AS [c1]%'
ORDER BY qsrs.avg_logical_io_reads DESC;

-- Monitor connection counts from Power BI Gateway
-- If using an on-premises data gateway, connections appear as the
-- gateway service account rather than individual Power BI users
SELECT
    s.login_name,
    s.host_name,
    s.program_name,
    COUNT(*)                        AS ConnectionCount,
    SUM(r.logical_reads)            AS TotalLogicalReads
FROM sys.dm_exec_sessions          s
LEFT JOIN sys.dm_exec_requests     r ON r.session_id = s.session_id
WHERE s.is_user_process = 1
GROUP BY s.login_name, s.host_name, s.program_name
HAVING s.program_name LIKE '%Power BI%'
    OR s.program_name LIKE '%gateway%'
ORDER BY TotalLogicalReads DESC;

7 Indexes That Matter for DirectQuery Performance Intermediate

DirectQuery performance on SQL Server is almost entirely determined by whether the generated queries can use efficient index seeks. The wrapping subquery pattern Power BI uses means covering indexes are more important for DirectQuery workloads than for typical application queries.

The most important indexes for DirectQuery are on the columns that appear in Power BI slicer filters and report filters. These become WHERE clause conditions in every DirectQuery-generated query. The date column that users filter on most frequently is almost always the highest-priority index candidate.

-- Find missing indexes that would help Power BI DirectQuery queries
-- Run after Power BI has been in production for at least a day
-- The DMV accumulates missing index suggestions from actual queries

SELECT TOP 20
    OBJECT_NAME(mid.object_id)                  AS TableName,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns,
    migs.unique_compiles,
    migs.user_seeks,
    migs.avg_total_user_cost,
    migs.avg_user_impact,
    -- Estimated impact score
    migs.user_seeks * migs.avg_total_user_cost
        * migs.avg_user_impact / 100            AS ImpactScore,
    -- Ready-to-run CREATE INDEX template
    'CREATE INDEX IX_' + OBJECT_NAME(mid.object_id)
        + '_PBI ON ' + mid.statement
        + ' (' + ISNULL(mid.equality_columns, '')
        + CASE WHEN mid.equality_columns IS NOT NULL
               AND mid.inequality_columns IS NOT NULL
               THEN ', ' ELSE '' END
        + ISNULL(mid.inequality_columns, '') + ')'
        + CASE WHEN mid.included_columns IS NOT NULL
               THEN ' INCLUDE (' + mid.included_columns + ')'
               ELSE '' END + ';'                AS SuggestedIndex
FROM sys.dm_db_missing_index_details            mid
JOIN sys.dm_db_missing_index_groups             mig  ON mig.index_handle = mid.index_handle
JOIN sys.dm_db_missing_index_group_stats        migs ON migs.group_handle = mig.index_group_handle
ORDER BY ImpactScore DESC;

-- The pattern for a good DirectQuery supporting index:
-- Table: SalesOrders with Power BI filtering on OrderDate and Region

-- Bad: no index, every DirectQuery causes a full table scan
-- Good: covering index on the filter and grouping columns
CREATE INDEX IX_SalesOrders_PBI_DateRegion
ON dbo.SalesOrders (OrderDate, Region)
INCLUDE (OrderAmount, ProductCategory, CustomerID, OrderID);

-- This index allows the subquery pattern Power BI generates
-- to seek rather than scan, even with the wrapping derived table

8 DirectQuery and Blocking: The Unexpected Problem Intermediate

The most common unexpected SQL Server problem caused by DirectQuery is blocking. Power BI DirectQuery connections run under READ COMMITTED isolation by default. On a database without RCSI (Read Committed Snapshot Isolation) enabled, DirectQuery read queries take shared locks on the rows and pages they read. If a long-running OLTP write transaction holds exclusive locks on pages that a DirectQuery query needs to read, the Power BI report hangs until the transaction completes.

This creates a scenario where a report that worked fine in testing suddenly becomes unresponsive in production because it is running against the same tables as OLTP write transactions that did not exist in the test environment.

-- Detect if Power BI DirectQuery connections are being blocked
SELECT
    r.session_id                        AS BlockedPBISession,
    r.blocking_session_id               AS BlockedBy,
    s.program_name,
    r.wait_type,
    r.wait_time / 1000                  AS WaitSeconds,
    LEFT(qt.text, 300)                  AS BlockedQuery
FROM sys.dm_exec_requests               r
JOIN sys.dm_exec_sessions               s  ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) qt
WHERE r.blocking_session_id > 0
AND  (s.program_name LIKE '%Power BI%'
   OR s.program_name LIKE '%PBI%');

-- The most effective fix: enable RCSI on the database
-- This eliminates reader-writer blocking for DirectQuery queries
-- Readers (Power BI) no longer block on writers (OLTP transactions)

-- Check current RCSI status
SELECT name, is_read_committed_snapshot_on
FROM sys.databases
WHERE name = DB_NAME();

-- Enable RCSI (brief exclusive access needed)
ALTER DATABASE YourDatabase
SET READ_COMMITTED_SNAPSHOT ON;

9 Row-Level Security: Import vs DirectQuery Differences Intermediate

Row-level security behavior is one of the most important practical differences between Import and DirectQuery from a governance and compliance perspective.

In Import mode, data is copied into the Power BI dataset. SQL Server's row-level security rules do not apply to data that has already been extracted. Power BI developers must manually recreate row-level security rules inside the Power BI dataset using DAX expressions. If the SQL Server RLS rules change, the Power BI RLS rules must be updated separately. There is no automatic synchronization.

In DirectQuery mode with Single Sign-On (SSO) enabled, Power BI passes the end user's identity to SQL Server with each query. SQL Server applies its existing row-level security rules to every DirectQuery query. A user who can only see their own region's data in the application will only see their own region's data in Power BI reports, enforced at SQL Server with no additional configuration in Power BI.

-- Verify SQL Server row-level security is working with DirectQuery
-- Check that RLS policies exist on the tables Power BI accesses

SELECT
    sp.name                             AS PolicyName,
    sp.is_enabled,
    sp.is_schema_bound,
    OBJECT_NAME(spp.object_id)         AS TableName,
    spp.filter_predicate_is_null
FROM sys.security_policies              sp
JOIN sys.security_predicates            spp ON spp.object_id = sp.object_id
ORDER BY sp.name;

-- For DirectQuery with SSO to enforce SQL Server RLS:
-- 1. The Power BI Gateway must be configured with SSO enabled
-- 2. The end user's Azure AD identity must map to a SQL Server login
-- 3. The SQL Server RLS policy must reference USER_NAME() or SUSER_SNAME()

-- Example RLS policy that works with Power BI DirectQuery SSO:
CREATE FUNCTION Security.fn_RegionFilter(@Region NVARCHAR(50))
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN (
    SELECT 1 AS fn_securitypredicate_result
    WHERE @Region = (
        SELECT Region
        FROM dbo.UserRegionMap
        WHERE Username = USER_NAME()
    )
    OR IS_MEMBER('db_owner') = 1  -- admins see all data
);

CREATE SECURITY POLICY RegionSecurityPolicy
ADD FILTER PREDICATE Security.fn_RegionFilter(Region)
ON dbo.SalesOrders
WITH (STATE = ON);

10 When to Use Import Mode Beginner

• Historical and trend reports. Any report where data from yesterday or last week is acceptable. Monthly sales dashboards, quarterly reviews, year-over-year comparisons. Import mode is the right choice and delivers the best report performance.
• Complex DAX calculations. Some DAX functions are restricted or unavailable in DirectQuery mode. If the report requires complex time intelligence (SAMEPERIODLASTYEAR, DATEADD, running totals) Import mode avoids DirectQuery DAX limitations.
• High concurrent user reports. If 200 users will be viewing the same report simultaneously, Import mode puts zero additional load on SQL Server during report interactions. DirectQuery with 200 concurrent users sends thousands of queries per minute to SQL Server.
• SQL Server is production OLTP. If the Power BI report is connecting to the primary OLTP database that runs the business application, Import mode separates report query load from application query load. DirectQuery on a busy OLTP database adds unpredictable load at exactly the wrong time.
• The data volume fits within dataset limits. If the reporting data is under a few hundred million rows and fits in the Power BI dataset, Import mode is almost always the better choice for user experience.

11 When to Use DirectQuery Beginner

• Real-time operational dashboards. Manufacturing floor monitoring, live logistics tracking, fraud detection, patient monitoring. When data older than minutes is unacceptable, DirectQuery is the appropriate choice.
• Very large tables that cannot be imported. A billion-row transaction table cannot practically be imported into a Power BI dataset. DirectQuery allows reporting against data at scale that Import mode cannot handle.
• SQL Server row-level security must be enforced. When compliance requires that the database enforces data access rules rather than trusting a separate reporting tool to enforce them correctly, DirectQuery with SSO passes the user identity to SQL Server and lets the database do what it was built to do.
• The SQL Server is dedicated to reporting. Connecting DirectQuery to a read-only replica, a reporting database, or a dedicated Azure SQL Database that receives data from ETL removes the concern about DirectQuery impacting production OLTP performance. The reporting server exists specifically for this query load.
• RCSI is enabled and indexes support the query patterns. These two conditions together make DirectQuery performant. Without them DirectQuery on a busy production server will cause problems.

12 Composite Models: The Best of Both Intermediate

Composite models allow a single Power BI dataset to mix Import and DirectQuery tables. This is now generally available and is the recommended pattern for many enterprise scenarios. A typical composite model uses Import for dimension tables (Customers, Products, Dates) and DirectQuery for large fact tables (SalesTransactions, EventLogs).

The benefit is that dimension slicer selections happen against in-memory Import data, which is fast. The fact table query happens via DirectQuery against SQL Server, filtered by the dimension selections. SQL Server receives well-filtered queries rather than queries that must scan the entire fact table.

-- In a composite model, Power BI generates queries like this:
-- Dimension filtering happens in-memory (Import)
-- Fact table query arrives at SQL Server already filtered

-- Example DirectQuery query from a composite model:
-- User selected Region = 'Northeast' from an imported Customers table
-- Power BI sends the fact table query pre-filtered:

SELECT
    [t0].[OrderMonth]                   AS [c1],
    SUM([t0].[OrderAmount])             AS [c2]
FROM (
    SELECT
        DATEPART(MONTH, [OrderDate])    AS [OrderMonth],
        [OrderAmount],
        [CustomerID]
    FROM [dbo].[SalesTransactions]
    WHERE [CustomerID] IN (
        -- Customer IDs for Northeast region, resolved from Import table
        1042, 1087, 1156, 2034, 2891, 3104
    )
    AND [OrderDate] >= '2026-01-01'
) AS [t0]
GROUP BY [t0].[OrderMonth]

-- This is more efficient than DirectQuery-only where the region
-- filter would require a JOIN to the Customers table on SQL Server
-- An index on (CustomerID, OrderDate) INCLUDE (OrderAmount)
-- handles this query efficiently

13 The DBA Checklist Before Going Live Beginner

Before any Power BI DirectQuery report goes into production against a SQL Server database, run through this checklist.

• Check RCSI status. If RCSI is not enabled and the database serves OLTP workloads, enable it before DirectQuery goes live. Blocking between report users and application transactions will surface quickly under any real user load.
• Run the report on a pre-production copy and capture the queries. Let the report run in a test or staging environment for a week. Review Query Store for the DirectQuery-generated queries. Identify any that cause full table scans and add supporting indexes before production.
• Verify the Power BI login has read-only permissions. DirectQuery connects to SQL Server under a service account or the user's identity via SSO. Confirm that account has SELECT permissions only on the tables it needs and nothing else. Applying least-privilege to Power BI connections is a DBA responsibility.
• Confirm row-level security requirements with the business. If different users should see different data, decide whether RLS lives in SQL Server (DirectQuery with SSO) or Power BI (Import with DAX RLS). Both are valid but they must be explicitly designed, not assumed.
• Set query timeouts. DirectQuery-generated queries do not have an automatic timeout by default. A poorly designed DAX measure can generate an unbounded query that runs for minutes and holds resources. Configure a query timeout on the Power BI dataset or at the SQL Server level using Resource Governor.
• Monitor the first week of production. Add the DirectQuery monitoring queries from Section 6 to your weekly health check for the first month after go-live. Watch for blocking, unusual query volumes, and queries appearing in the missing index DMV.

References

• Microsoft Docs: About using DirectQuery in Power BI
• Microsoft Docs: Manage storage mode in Power BI Desktop
• Microsoft Docs: Use composite models in Power BI Desktop
• Microsoft Docs: DirectQuery model guidance in Power BI Desktop
• Microsoft Docs: Row-level security (RLS) with Power BI
• Microsoft Docs: Row-Level Security in SQL Server
• SQLYARD: SQL Server Blocking vs Deadlocks (RCSI trade-offs)
• SQLYARD: SQL Server Index Tuning Guide
• SQLYARD: SQL Server Query Store Complete Guide
• SQLYARD: SQL Server Performance Tuning Complete Guide