Monitoring Fabric Data Warehouse at Scale

Do not record or livestream this session Monitoring Fabric Data Warehouse at Scale DMVs, Query Insights, SQL audit logs and capacity metrics. Sam Debruyn Freelance data platform architect · Microsoft Data Platform MVP debruyn.dev | sam@debruyn.dev My background Freelance data platform architect from Belgium 🇧🇪 I design and build Microsoft Fabric data platforms with customer teams. Fabric data architecture, implementation and operations Platform engineering, automation and observability MICROSOFT DATA PLATFORM MVP OPEN-SOURCE WORK dbt adapter for Fabric Data Warehouse fabric-dw CLI for Fabric Data Warehouse debruyn.dev Monitoring questions O P E N I N G > S E S S I O N C O N T E X T Each question uses a different monitoring surface. The session covers access and the relevant T-SQL queries. performance Which query is slow, and since when? Identify the query hash and compare median duration over time. capacity What ran during the capacity spike? Use the warehouse and billing interval from Capacity Metrics to find overlapping queries in Query Insights. governance Who read that table, and when? Use audit events to identify the principal, statement and timestamp. 01 Monitoring surfaces Scope, access and retention of warehouse monitoring data. Monitoring surfaces and their scope Retention, latency and permissions Guided diagnosis with the SQL DW Operations skill Warehouse and SQL analytics endpoint M O N I T O R I N G S U R F A C E S > M O N I T O R I N G S C O P E SQL endpoints serve Lakehouse, mirrored database, Fabric SQL database and Cosmos DB items. Aspect Fabric Data Warehouse SQL analytics endpoint Table management Warehouse engine manages Delta tables Source engine manages Delta tables T-SQL activity Reads and writes in the Warehouse Reads at the endpoint, writes at the source Capacity Metrics Warehouse Query SQL Endpoint Query Configured audit Reads, DML, DDL and permission changes Reads, SQL object and permission changes Audit access T-SQL and OneLake .XEL files T-SQL Both expose DMVs and Query Insights. Include each endpoint in the monitoring inventory. Warehouse monitoring surfaces M O N I T O R I N G S U R F A C E S > M O N I T O R I N G S C O P E Fabric manages infrastructure. Query design, schedules and table layout affect performance and capacity use. live Dynamic management views Current sessions, requests and locks. sys.dm_exec_* , sys.dm_tran_locks . 30 days Query Insights Every completed user query, CPU and data scanned. The queryinsights schema. preview Data Warehouse Monitor Query Insights in the portal. Monitor permission required. public preview coming soon Workspace Monitoring Query activity across warehouses and SQL analytics endpoints. Monitoring Eventhouse. 14 - 30 days Capacity Metrics app Warehouse capacity use by billing interval. Correlate with Query Insights. GA Mar 2026 SQL audit logs Logins, data access and permission events. OneLake .XEL files. SQL DW Operations skill: guided diagnosis across monitoring surfaces Retention and latency differ by surface M O N I T O R I N G S U R F A C E S > A C C E S S A N D R E T E N T I O N learn.microsoft.com/fabric/data-warehouse/query-insights SURFACE AND REPORTING LAG RETENTION AND EXPIRY DMVs live, sub-second Current activity Request removed on completion Query Insights up to 15 min lag 30 days, rolling Rows expire Data Warehouse Monitor up to 15 min lag 30 days; 10,000 shown Same as Query Insights Capacity Metrics ~30 sec timepoints 14 days; 30 in Item History Rows expire SQL audit logs near real time Configurable; 0 = unlimited Deleted with warehouse Fixed windows limit lookback. Audit retention is configurable and can be unlimited. Monitoring permissions M O N I T O R I N G S U R F A C E S > A C C E S S A N D R E T E N T I O N learn.microsoft.com/fabric/data-warehouse/monitor-using-dmv + learn.microsoft.com/fabric/data-warehouse/share-warehouse-manage-permissions ACCESS BY OPERATION Operation Admin Monitor Viewer (base) Sessions / requests All All users Own Connections All All users Own Live SQL text Yes Denied Denied Locks Yes Denied Denied KILL session Any user Any user Denied Query Insights Yes Yes Denied Audit read Yes + Audit + Audit Monitor UI Yes Yes + Monitor Viewer: default access. Viewer + Monitor: use the Monitor column. Member and Contributor include Monitor. Share can grant it separately. Audit reads require Audit permission. SQL DW Operations skill M O N I T O R I N G S U R F A C E S > I N V E S T I G A T I O N E N T R Y P O I N T S github.com/microsoft/skills-for-fabric A guided entry point to Query Insights, Capacity Metrics and SQL pool diagnostics. Operational question warehouse and UTC interval “Explain why FinanceWarehouse was slow between 09:00 and 11:00 UTC yesterday.” A compatible AI tool loads the open-source skill. The skill runs bounded, read-only diagnostics. Diagnostic response measurements and follow-up Failed requests, CPU/GPU, scans and pool pressure. Capacity spikes: match by warehouse and time. Cited results, diagnosis and follow-up checks. 02 Live activity with DMVs Live requests, session owners and cancellation. DMV fields for live activity Find the running request and its owner Cancel a session and assess rollback DMVs and SQL text L I V E A C T I V I T Y W I T H D M V S > L I V E R E Q U E S T S learn.microsoft.com/fabric/data-warehouse/monitor-using-dmv Live rows per connection, session, request and lock; Admin access for locks and SQL text. connections sys.dm_exec_connections Transport, encryption and client IP. sessions sys.dm_exec_sessions Login, application and open transactions. requests sys.dm_exec_requests Status, elapsed time and statement ID. lock inventory sys.dm_tran_locks Lock requests; Admin access. statement text sys.dm_exec_sql_text() Active SQL text via CROSS APPLY . Active requests ordered by elapsed time L I V E A C T I V I T Y W I T H D M V S > L I V E R E Q U E S T S learn.microsoft.com/fabric/data-warehouse/monitor-using-dmv T-SQL SELECT r.session_id, r.status, -- running / suspended / runnable r.total_elapsed_time / 1000.0 AS elapsed_seconds, r.dist_statement_id, s.login_name, s.program_name, t.text AS query_text FROM sys.dm_exec_requests AS r JOIN sys.dm_exec_sessions AS s ON r.session_id = s.session_id CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t WHERE s.is_user_process = 1 AND r.session_id <> @@SPID ORDER BY r.total_elapsed_time DESC; Example result SESSION 210 STATUS running SECONDS 12.258 CLIENT FabricSQLDemo-ETL Capture dist_statement_id for history and audit. DMS_user = portal SQL editor. User sessions only, longest first. Monitor sees other users; live SQL text requires Admin. Session ownership before KILL L I V E A C T I V I T Y W I T H D M V S > S E S S I O N M A N A G E M E N T learn.microsoft.com/fabric/data-warehouse/monitor-using-dmv + learn.microsoft.com/fabric/data-warehouse/troubleshoot-query-blocking dmv / who-owns-it.sql SELECT session_id, login_name , program_name, host_name, status, login_time, open_transaction_count FROM sys.dm_exec_sessions WHERE session_id = 137; login_name identifies the user or service principal. program_name identifies the client application. dmv / stop-it.sql -- Needs Admin or Monitor. -- Stops every request in the session. KILL 137; KILL rolls back the open transaction. Large writes may take time to undo. Confirm the owner and open transaction before KILL. Escalate persistent Fabric system sessions to support. 03 Historical queries with Query Insights Thirty days of completed user queries, including CPU and data scanned metrics. Query Insights views and their purpose query_hash groups executions by shape Cold cache, CPU, failures and labels SQL Pool Insights and query duration Query Insights views H I S T O R I C A L Q U E R I E S W I T H Q U E R Y I N S I G H T S > Q U E R Y H I S T O R Y M O D E L learn.microsoft.com/fabric/data-warehouse/query-insights QUERY INSIGHTS VIEW MAP exec_requests_history One row per completed user query long_running_queries Median duration frequently_run_queries Run count + outcomes exec_sessions_history Status, total query time and connection reuse sql_pool_insights Pool configuration and pressure events Many short sessions from one program_name can indicate limited connection reuse. Fields in exec_requests_history H I S T O R I C A L Q U E R I E S W I T H Q U E R Y I N S I G H T S > Q U E R Y H I S T O R Y M O D E L learn.microsoft.com/sql/relational-databases/system-views/queryinsights-exec-requests-history-transact-sql Identity, submitter, timing and resource fields. I D E N T I T Y distributed_statement_id query_hash session_id connection_id batch_id / root_batch_id label W H O A N D W H A T login_name program_name database_name statement_type command (full text) sql_pool_name T I M E submit_time start_time end_time total_elapsed_time_ms status error_code C O S T A N D I O allocated_cpu_time_ms data_scanned_remote_storage_mb data_scanned_memory_mb data_scanned_disk_mb row_count result_cache_hit query_hash: same shape, different predicates H I S T O R I C A L Q U E R I E S W I T H Q U E R Y I N S I G H T S > Q U E R Y H I S T O R Y M O D E L learn.microsoft.com/fabric/data-warehouse/query-insights SAME SHAPE · DIFFERENT PREDICATES 09:00 run SELECT * FROM dbo.fact_sales WHERE order_date BETWEEN '2025-07-01' AND '2025-07-01' SELECT * FROM dbo.fact_sales WHERE order_date BETWEEN '2025-07-01' AND '2025-07-01' 17:30 run SELECT * FROM dbo.fact_sales WHERE order_date BETWEEN '2024-07-01' AND '2024-07-03' SELECT * FROM dbo.fact_sales WHERE order_date BETWEEN '2024-07-01' AND '2024-07-03' Both runs share a query_hash Predicates are parameterised. Compare individual runs: literals, cache state, duration and row count can differ. 🤖 Compare yesterday's FABCON26_same_shape runs. Why do their durations differ? The triage query H I S T O R I C A L Q U E R I E S W I T H Q U E R Y I N S I G H T S > Q U E R Y T R I A G E learn.microsoft.com/fabric/data-warehouse/query-insights query-insights / triage.sql SELECT TOP (30) start_time, status, login_name, program_name, total_elapsed_time_ms / 1000.0 AS elapsed_seconds, allocated_cpu_time_ms / 1000.0 AS vcore_seconds , data_scanned_remote_storage_mb AS from_onelake_mb , (data_scanned_memory_mb + data_scanned_disk_mb) AS from_cache_mb , sql_pool_name , LEFT(command, 120) AS query_start FROM queryinsights.exec_requests_history WHERE start_time >= DATEADD(hour, -24, GETUTCDATE()) ORDER BY total_elapsed_time_ms DESC; C O L U M N I N T E R P R E T A T I O N from_onelake_mb Data read from OneLake. Compare with cached reads when investigating slow executions. from_cache_mb Data read from memory and disk cache. Compare with remote reads for the same execution. vcore_seconds CPU allocated to the request. Use it to compare executions and workload groups. sql_pool_name SELECT , NON SELECT , or another configured pool. NULL for lightweight metadata queries. Output: triage query H I S T O R I C A L Q U E R I E S W I T H Q U E R Y I N S I G H T S > Q U E R Y T R I A G E Sales demo warehouse; selected fields from the 24-hour query history. Longest executions in the demo window 5/30 rows shown status program_name elapsed_s vcore_s OneLake MB cache MB pool Failed FabricSQLDemo-BI 12.311 0.0 0.0 0.0 (empty) Succeeded FabricSQLDemo-Validate 6.492 4.612 0.0 0.0 Adhoc Succeeded FabricSQLDemo-BI 3.146 3.08 15.585 0.0 BI Succeeded FabricSQLDemo-BI 3.114 2.605 5.171 10.414 BI Succeeded FabricSQLDemo-BI 3.057 2.464 10.414 0.0 BI Rank queries by CPU and remote data scanned H I S T O R I C A L Q U E R I E S W I T H Q U E R Y I N S I G H T S > Q U E R Y T R I A G E learn.microsoft.com/fabric/data-warehouse/query-insights query-insights / top-cpu.sql SELECT TOP (100) distributed_statement_id, query_hash, allocated_cpu_time_ms , label, command FROM queryinsights.exec_requests_history ORDER BY allocated_cpu_time_ms DESC; Queries ordered by allocated CPU time. query-insights / cold-scans.sql SELECT distributed_statement_id, query_hash, data_scanned_remote_storage_mb , data_scanned_memory_mb, data_scanned_disk_mb, label, command FROM queryinsights.exec_requests_history ORDER BY data_scanned_remote_storage_mb DESC; Note: data_scanned_* is reported as 0 for COPY INTO , and doesn't include intermediate data movement. 🤖 Which query shapes used the most CPU and remote reads in the last 12 hours? Output: CPU and remote reads H I S T O R I C A L Q U E R I E S W I T H Q U E R Y I N S I G H T S > Q U E R Y T R I A G E Sales demo warehouse; separate rankings from Query Insights. Highest allocated CPU 3/100 rows shown query_hash CPU ms label 0xB55618581D804028 4,612 (empty) 0xFEFE3A889CD56911 3,080 DEMO|BI|regional-sales 0xFEFE3A889CD56911 2,605 DEMO|BI|regional-sales Highest remote reads 3/442 rows shown query_hash OneLake MB memory MB label 0xFEFE3A889CD56911 15.585 0.0 DEMO|BI|regional-sales 0xB8F7271575F04B36 10.414 0.0 DEMO|BI|customer-tile 0x3CB80D446AF1723D 9.213 22.953 (empty) H I S T O R I C A L Q U E R I E S W I T H Q U E R Y I N S I G H T S > E X E C U T I O N V A R I A B I L I T Y Cold and warm executions TWO EXECUTIONS OF ONE QUERY SHAPE First run 3,057 ms OneLake: 10.414 MB Later run 539 ms Memory: 10.414 MB The plan and data stay the same. Compare OneLake, memory and local SSD reads per execution. Ranking by duration or run count H I S T O R I C A L Q U E R I E S W I T H Q U E R Y I N S I G H T S > E X E C U T I O N V A R I A B I L I T Y learn.microsoft.com/fabric/data-warehouse/query-insights long_running_queries one row per shape, ranked by median duration Identifies query shapes with high median duration median_total_elapsed_time_ms limits the effect of outliers number_of_runs , last_run_total_elapsed_time_ms Execution lookup through last_dist_statement_id frequently_run_queries one row per shape, ranked by run count Identifies frequently executed query shapes Average, minimum and maximum duration show variation between runs number_of_successful_runs / _failed_ / _canceled_ Execution lookup through last_dist_statement_id Run count × average duration estimates cumulative query duration. Median duration reduces the influence of unusually slow executions. Failures and cancellations in the last 24 hours H I S T O R I C A L Q U E R I E S W I T H Q U E R Y I N S I G H T S > W O R K L O A D C O N T E X T learn.microsoft.com/fabric/data-warehouse/query-insights Query Insights · failures SELECT distributed_statement_id, start_time, total_elapsed_time_ms, status, -- Failed / Canceled error_code, login_name, program_name, command FROM queryinsights.exec_requests_history WHERE status IN ('Failed', 'Canceled') AND start_time >= DATEADD(hour, -24, GETUTCDATE()) ORDER BY start_time DESC; Example failure status: Failed error_code: 3602 client: FabricSQLDemo-BI 🤖 What failed or was cancelled in the last 24 hours, and which failures recur? Query labels add workload context H I S T O R I C A L Q U E R I E S W I T H Q U E R Y I N S I G H T S > W O R K L O A D C O N T E X T learn.microsoft.com/fabric/data-warehouse/query-label app / one line added SELECT c.region, s.date_key, SUM(s.amount) FROM dbo.fact_sales AS s JOIN dbo.dim_customer AS c ON c.customer_key = s.customer_key WHERE s.customer_key = 123 GROUP BY c.region, s.date_key OPTION (LABEL = 'BI|sales-dashboard|tile-3') ; OPTION (LABEL = ...) identifies the workload. query-insights / by-label.sql SELECT start_time, total_elapsed_time_ms, allocated_cpu_time_ms, data_scanned_remote_storage_mb, status FROM queryinsights.exec_requests_history WHERE label = 'BI|sales-dashboard|tile-3' ORDER BY start_time DESC; The label links executions to the same workload throughout the 30-day history. Keep the same label when changing SQL to compare performance before and after the change. How SQL Pool Insights records pressure H I S T O R I C A L Q U E R I E S W I T H Q U E R Y I N S I G H T S > P O O L P R E S S U R E learn.microsoft.com/sql/relational-databases/system-views/queryinsights-sql-pool-insights-transact-sql Pool event fields sql_pool_name , timestamp , max_resource_percentage , is_optimized_for_reads , current_workspace_capacity , is_pool_under_pressure . Events record state changes Events record changes in pool configuration, workspace capacity or sustained pressure. One-minute pressure threshold The view records pressure sustained for at least one minute. Shorter spikes fall below that threshold. Logging during activity Periodic logging pauses while the warehouse is idle. Interpret gaps alongside query activity. Monitor permission required Member and Contributor include Monitor. Other users can receive it through Share. Queries during pool pressure H I S T O R I C A L Q U E R I E S W I T H Q U E R Y I N S I G H T S > P O O L P R E S S U R E Match queries to pressure intervals using the pool name and overlapping timestamps. 1 - pressure intervals -- 1. Rows are state CHANGES, so rebuild -- the intervals first WITH windows AS ( SELECT sql_pool_name, is_pool_under_pressure , [timestamp] AS valid_from, LEAD([timestamp]) OVER ( PARTITION BY sql_pool_name ORDER BY [timestamp]) AS valid_to FROM queryinsights.sql_pool_insights WHERE [timestamp] > DATEADD(day, -7, GETUTCDATE())) Events mark state changes. Reconstruct the intervals first. 2 - queries in those intervals -- 2. ...then overlap the queries onto them SELECT w.sql_pool_name, w.valid_from, h.query_hash, h.total_elapsed_time_ms, h.allocated_cpu_time_ms FROM windows AS w JOIN queryinsights.exec_requests_history AS h ON h.sql_pool_name = w.sql_pool_name AND h.start_time < ISNULL(w.valid_to, GETUTCDATE()) AND h.end_time > w.valid_from WHERE w. is_pool_under_pressure = 1 ORDER BY h.total_elapsed_time_ms DESC; Match both the interval and the pool name. 🤖 Was there SQL pool pressure in the last 24 hours? Which queries overlapped it? One query shape over time H I S T O R I C A L Q U E R I E S W I T H Q U E R Y I N S I G H T S > I N T E R P R E T I N G Q U E R Y H I S T O R Y learn.microsoft.com/fabric/data-warehouse/query-insights query-insights / one-shape-over-time.sql SELECT start_time, total_elapsed_time_ms, allocated_cpu_time_ms, data_scanned_remote_storage_mb , data_scanned_memory_mb, data_scanned_disk_mb, row_count , sql_pool_name, status FROM queryinsights.exec_requests_history WHERE query_hash = '0x956ED9423EC8FFEC' ORDER BY start_time DESC; R E A D I N G T H E C U R V E row_count Compare changes in duration with changes in row count. data_scanned_remote_storage_mb Higher remote reads on slow runs can indicate a cold cache. Consistently high reads warrant checking table layout. query_hash Copy it from the aggregated views, or from the Monitor UI's Query hash column. Output: query shape over time H I S T O R I C A L Q U E R I E S W I T H Q U E R Y I N S I G H T S > I N T E R P R E T I N G Q U E R Y H I S T O R Y Sales demo warehouse; first and later executions from the same query hash. Cold and later runs of one query shape 4/80 rows shown start (UTC) duration ms CPU ms OneLake MB memory MB row_count 12:47:24 3,057 2,464 10.414 0.0 1 12:48:16 571 531 0.0 10.414 1 12:48:16 539 492 0.0 10.414 1 12:48:17 603 556 0.0 10.414 1 Query Insights limitations H I S T O R I C A L Q U E R I E S W I T H Q U E R Y I N S I G H T S > I N T E R P R E T I N G Q U E R Y H I S T O R Y learn.microsoft.com/fabric/data-warehouse/query-insights 15 minutes Historical lag Completed queries can take 15 minutes to appear. Lag increases with concurrency. 30 days Fixed retention Rows expire after 30 days. System queries User queries only Query Insights records queries in a user's context. Use Capacity Metrics to examine billed background work. Cross-database Connection context A query joining WH_A and WH_B through a connection to WH_B is recorded in WH_B. The connection item determines where the query appears. New warehouse Invalid object name queryinsights.exec_requests_history becomes available a few minutes after warehouse creation. Workspace Monitoring provides a workspace-wide query view for Warehouses and SQL analytics endpoints. 04 Monitoring in the Fabric portal Workspace signals, query diagnostics and agentic monitoring in the Fabric portal. Warehouse and SQL endpoint activity in Workspace Monitoring Query history and controls in Data Warehouse Monitor Query plans, live troubleshooting and agentic monitoring DW in Workspace Monitoring M O N I T O R I N G I N T H E F A B R I C P O R T A L > W O R K S P A C E S I G N A L S Public preview coming soon. Warehouse and SQL endpoint query activity joins Workspace Monitoring. W O R K S P A C E V I E W 1 Workspace scope : query activity across every warehouse and SQL analytics endpoint in the workspace. 2 Failed queries and performance issues : investigate the query activity behind the signal. 3 Query context : move from the workspace signal to the item and query that need attention. 4 Multiple workspaces : one Monitoring Eventhouse will receive WarehouseExecutions from several workspaces. Monitor tabs and query history M O N I T O R I N G I N T H E F A B R I C P O R T A L > P O R T A L I N V E S T I G A T I O N learn.microsoft.com/fabric/data-warehouse/monitor The Monitor UI is in preview. Its tabs read Query Insights views. T A B S A N D C O N T R O L S 1 Query history ( exec_requests_history ): request-count chart and filterable grid. 2 Long running queries ( long_running_queries ): query shapes by median duration. 3 Frequently run queries ( frequently_run_queries ): run count and outcomes. 4 Run source = program_name . Column options: statement ID, query hash, SQL pool. View similar runs : same hash. Cancel also affects other requests in the same session. Check the owner and open transaction first. Understand query performance before and during execution Public preview coming soon Preview the plan without running the query. Identify costly operators and excessive data movement before execution. Plan before execution → Troubleshoot during execution Graphical Query Plan 01 02 Live Query Troubleshooting Gated preview Inspect execution while the query is running. Pinpoint active operators, data movement, and elapsed time before completion. 01 02 Understand the issue. See the evidence. Take the next action. AI insight cards explain what is wrong directly on the DW Monitor page. Diagnose failed queries, pool pressure, top consumers, and unhealthy SQL endpoint tables. Use Query Insights telemetry and real evidence to recommend the next action. Agentic monitoring + Copilot Limited preview coming soon Insight → Evidence → Action 01 02 03 05 Table health and metadata Stale metadata and Delta table layout can affect correctness and performance on the Lakehouse SQL analytics endpoint. sys.sp_get_table_health_metrics , GA June 2026 Delta table layout metrics PotentialAnomalyType indicates when to optimise Table health metrics are available from T-SQL T A B L E H E A L T H A N D M E T A D A T A > T A B L E D I A G N O S T I C S learn.microsoft.com/sql/relational-databases/system-stored-procedures/sp-get-table-health-metrics-transact-sql T-SQL health check EXEC sys.sp_get_table_health_metrics @table_name = 'dbo.fact_device_event'; Result shape PotentialAnomalyType = 3 File count = 256 File-size histogram → 256 KiB–4 MiB The anomaly code names the issue; the histogram shows the affected file sizes. GA since June 2026. Available on SQL analytics endpoints and Warehouse tables. 🤖 Check table health for dbo.fact_device_event on the Telemetry SQL endpoint. Table anomalies and maintenance T A B L E H E A L T H A N D M E T A D A T A > T A B L E D I A G N O S T I C S learn.microsoft.com/sql/relational-databases/system-stored-procedures/sp-get-table-health-metrics-transact-sql The procedure reports the most severe anomaly. This sample returns code 3 and 256 small files. OBSERVED RESULT Code 3 Many small files Telemetry SQL analytics endpoint dbo.fact_device_event Physical rows 1,000,000 Files 256 Files from 256 KiB to 4 MiB 256 Total file bytes 100,674,692 Code Anomaly Meaning and action 0 None Layout acceptable. No action. 1 Invalid file statistics Inspect the Delta log and re-run ingestion. 2 Many deleted rows Run OPTIMIZE to rewrite the files. 3 Many small files Run OPTIMIZE and review the ingestion batch size. 4 No recent checkpoint Run OPTIMIZE or create a checkpoint from Spark. Run maintenance through Spark or a pipeline. Repeat the health check to identify any remaining anomaly. 06 Capacity Metrics and query activity Compare a warehouse billing interval with the queries that ran during it. Find the warehouse and billing interval Check overlapping requests in Query Insights Compare CPU, scans, duration and pool pressure Correlate capacity and query activity C A P A C I T Y M E T R I C S A N D Q U E R Y A C T I V I T Y > C A P A C I T Y C O R R E L A T I O N learn.microsoft.com/fabric/data-warehouse/how-to-observe-utilization Capacity Metrics measures allocated warehouse compute. Query Insights shows the user requests active in the same period. 1 Capacity Metrics Record the warehouse item and the Start and End of a high-CU billing interval. 2 Query Insights Find requests in that warehouse whose execution overlaps the interval. 3 Compare the workload Review query shape, CPU, duration, scans and pool pressure during the same interval. Since August 2026, Capacity Metrics Operation ID is separate from Query Insights distributed_statement_id. 🤖 What caused the highest-CU dwh_sales interval in the last 24 hours? Find the billing interval in Capacity Metrics C A P A C I T Y M E T R I C S A N D Q U E R Y A C T I V I T Y > C A P A C I T Y C O R R E L A T I O N learn.microsoft.com/fabric/data-warehouse/how-to-observe-utilization 1 Filter Item kind to Warehouse and Operation to Warehouse Query . 2 Sort Total CU(s) descending when investigating a usage spike. 3 Select the warehouse item to investigate, here dwh_sales . 4 Record Start and End for the billing interval. Queries during the billing interval C A P A C I T Y M E T R I C S A N D Q U E R Y A C T I V I T Y > C A P A C I T Y C O R R E L A T I O N Fabric Community: Understanding warehouse consumption with Capacity Metrics and Query Insights capacity / interval-queries.sql DECLARE @start_time datetime2(0) = '2026-08-04T08:00:00'; DECLARE @end_time datetime2(0) = '2026-08-04T09:00:00'; SELECT query_hash, sql_pool_name, status, start_time, end_time, allocated_cpu_time_ms / 1000.0 AS vcore_seconds, data_scanned_remote_storage_mb AS onelake_mb FROM queryinsights.exec_requests_history WHERE start_time < @end_time AND end_time > @start_time ORDER BY allocated_cpu_time_ms DESC; Run in the selected warehouse. Query Insights adds completed user requests after its reporting lag. Capacity usage and warehouse queries C A P A C I T Y M E T R I C S A N D Q U E R Y A C T I V I T Y > C A P A C I T Y C O R R E L A T I O N QUERY AND CAPACITY CORRELATION https://github.com/microsoft/ fabric-toolbox/tree/main/ monitoring/query-capacity-correlation 07 SQL audit logs Identity, object access and configuration changes, with configurable retention for SQL audit logs. SQL audit logs: GA, off by default Action groups, predicates and bounded reads Identity, schema and permission changes Daily refresh and central audit history Performance monitoring and auditing answer different questions S Q L A U D I T L O G S > A U D I T S C O P E Performance Query Insights and DMVs Why was it slow? Aggregated by query shape System queries excluded Query Insights retention: 30 days Incomplete history limits diagnosis Audit SQL audit logs Who did it, and did it succeed? Individual events per principal, best effort under load Logins, permission changes, DDL, DML Configurable retention, including unlimited Completeness matters for audit requirements SQL audit logs record failed logins and permission changes as well as statement activity. Audit action groups S Q L A U D I T L O G S > A U D I T S C O P E learn.microsoft.com/fabric/data-warehouse/sql-audit-logs Enable auditing per warehouse with the Audit queries permission. Select the action groups you need. Friendly name in the portal Action group Events User Failed To Log In FAILED_DATABASE_AUTHENTICATION_GROUP brute force and misconfigured apps User Logged In SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP who connected, from where Schema Was Changed SCHEMA_OBJECT_CHANGE_GROUP CREATE / ALTER / DROP on schemas Object Was Changed DATABASE_OBJECT_CHANGE_GROUP DDL on database objects Schema Object Permission Was Changed SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP GRANT / REVOKE / DENY Role Member Was Changed DATABASE_ROLE_MEMBER_CHANGE_GROUP someone added to a role User Was Impersonated DATABASE_PRINCIPAL_IMPERSONATION_GROUP EXECUTE AS Schema Permission Was Used SCHEMA_OBJECT_ACCESS_GROUP object access by principal Batch Was Completed BATCH_COMPLETED_GROUP completed batches and SQL text Object access and completed batches can generate substantial volume. Reading the log with T-SQL S Q L A U D I T L O G S > A U D I T R E A D S learn.microsoft.com/fabric/data-warehouse/sql-audit-logs audit / read-window.sql SELECT event_time, server_principal_name, database_name, schema_name, object_name, statement, action_id, succeeded, client_ip FROM sys.fn_get_audit_file_v2 ( 'https://onelake.blob.fabric.microsoft.com/' + '<workspaceId>/<warehouseId>/Audit/sqldbauditlogs/', DEFAULT, DEFAULT, '2026-09-28T08:00:00Z' , -- start '2026-09-28T09:00:00Z' ) -- end ORDER BY event_time DESC; A U D I T F I L E A N D T I M E W I N D O W sys.fn_get_audit_file_v2 Use the Audit folder URL. For warehouse items, use DEFAULT for individual file and offset arguments. start / end Limit each read to the time range needed for the investigation. Schema and permission changes S Q L A U D I T L O G S > A U D I T I N T E R P R E T A T I O N succeeded = 0 identifies failed attempts. Inspect the principal and statement to determine the cause. audit / schema-changes.sql -- Who changed the schema, and what did they run? SELECT event_time, server_principal_name, schema_name, object_name, statement, succeeded FROM dbo.audit_events -- your table WHERE action_id IN ('CR','AL','DR') AND event_time >= DATEADD(day, -7, GETUTCDATE()) ORDER BY event_time DESC; CR create, AL alter, DR drop. Requires SCHEMA_OBJECT_CHANGE_GROUP and DATABASE_OBJECT_CHANGE_GROUP . audit / permission-changes.sql -- Who granted what to whom? SELECT event_time, server_principal_name, target_database_principal_name, statement, succeeded FROM dbo.audit_events WHERE action_id IN ('G','GWG','D','R') ORDER BY event_time DESC; G grant, D deny, R revoke. Requires SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP . Database-level GRANTs only show up in BATCH_COMPLETED_GROUP . Failed logins S Q L A U D I T L O G S > A U D I T I N T E R P R E T A T I O N learn.microsoft.com/fabric/data-warehouse/sql-audit-logs audit / failed-logins.sql SELECT CAST(event_time AS date) AS day, server_principal_name, client_ip, COUNT(*) AS failures FROM dbo.audit_events WHERE action_id = 'DBAF' -- auth failed AND event_time >= DATEADD(day, -30, GETUTCDATE()) GROUP BY CAST(event_time AS date), server_principal_name, client_ip HAVING COUNT(*) > 5 ORDER BY failures DESC; Grouped result Date: 2026-09-26 Principal: (empty) Client IP: 20.50.0.46 Failures: 8 The principal field is empty in this captured result; the IP and count remain available. Audit log limitations S Q L A U D I T L O G S > A U D I T I N T E R P R E T A T I O N learn.microsoft.com/fabric/data-warehouse/sql-audit-logs Default workspace Not supported SQL audit logs require a workspace that supports auditing. My Workspace is excluded. Warehouse snapshots Not supported Queries against warehouse snapshots are outside audit coverage. Item deletion Audit files deleted The .XEL files belong to the warehouse item. Export them before deleting the warehouse. SQL analytics endpoint SQL activity only Lakehouse writes run in the source runtime. Read SQL endpoint audit events through sys.fn_get_audit_file_v2 . Under heavy load Events may be dropped The auditing feature might allow transactions to proceed without recording all of the events marked for auditing. Deleting a warehouse also deletes its audit trail. Export the logs when retention is required. Retain audit logs centrally S Q L A U D I T L O G S > A U D I T R E T E N T I O N Microsoft Learn: OneLake shortcuts + Warehouse ingest Each source workspace reloads its retained audit window daily. A central workspace appends unseen events to durable history. Each source workspace Workspace A All warehouses + SQL analytics endpoints Monitoring warehouse: current audit window Workspace B All warehouses + SQL analytics endpoints Monitoring warehouse: current audit window Seven-day window; full daily refresh Central workspace Lakehouse Shortcuts to local audit tables SQL analytics endpoint Central warehouse INSERT ... SELECT after each daily refresh Durable audit history T-SQL read Daily audit refresh and central retention S Q L A U D I T L O G S > A U D I T R E T E N T I O N A seven-day audit retention window gives a daily job six full days to recover. Stage Cadence Method Storage sys.fn_get_audit_file_v2 daily seven-day window OneLake .XEL Local dbo.audit_events same run TRUNCATE + INSERT current window Central warehouse ingest after local refresh event identity durable history The local table mirrors the retained source window. Central history keeps events beyond source retention. Reload the audit window locally S Q L A U D I T L O G S > A U D I T R E F R E S H 1 / reset the local window -- once per workspace run DECLARE @to_utc DATETIME2(6) = GETUTCDATE(), @from_utc DATETIME2(6) = DATEADD(day, -7 , GETUTCDATE()); TRUNCATE TABLE dbo.audit_events; -- then reload every source item 2 / reload every source item INSERT dbo.audit_events ( workspace_id , item_id , item_name, collected_at_utc , event_time, sequence_group_id , sequence_number, action_id) SELECT @ workspace_id , @ item_id , 'dwh_sales', @to_utc, a.event_time, a. sequence_group_id , a.sequence_number, a.action_id FROM sys.fn_get_audit_file_v2 ( @audit_path, DEFAULT, DEFAULT, @from_utc, @to_utc) AS a; -- repeat INSERT for every item Ingest audit shortcuts into central history S Q L A U D I T L O G S > A U D I T R E F R E S H The Lakehouse shortcut and destination Warehouse are in the central workspace. audit / central-ingest.sql -- run in the central monitoring warehouse INSERT INTO dbo.audit_history ( workspace_id , item_id, event_time, sequence_group_id , sequence_number, action_id, server_principal_name, statement) SELECT s. workspace_id , s.item_id, s.event_time, s. sequence_group_id , s.sequence_number, s.action_id, s.server_principal_name, s.statement FROM monitoring_lakehouse .dbo.ws_sales_audit_events AS s WHERE NOT EXISTS ( SELECT 1 FROM dbo.audit_history AS h WHERE h. workspace_id = s. workspace_id AND h.item_id = s.item_id AND h.event_time = s.event_time AND h. sequence_group_id = s. sequence_group_id AND h.sequence_number = s.sequence_number AND h.action_id = s.action_id); What we covered Monitoring sources, Workspace Monitoring and central audit history. Session recap Scope Monitoring surfaces Item type, permissions and retention set the scope. The SQL DW Operations skill runs guided checks. Live activity DMVs Running requests, sessions, locks and cancellation context. Query diagnosis Query Insights and Fabric portal Completed queries, CPU, scans, failures and pool pressure. Announced previews add multi-workspace query signals and operator diagnostics. Capacity and tables Capacity Metrics and table health Correlate billing intervals by warehouse and time. Check table anomalies with T-SQL. Audit SQL audit logs Identity, access and schema changes. Read bounded time windows. Audit retention Daily refresh to central history Each workspace reloads its retained audit window. Lakehouse shortcuts feed a central warehouse for longer retention, reports and alerts. T H A N K Y O U Questions and discussion Sam Debruyn debruyn.dev | sam@debruyn.dev Slides and SQL: FabCon cvent app Fabric DW Live Query Troubleshooting Experience Preview Sign-Up