As I come to the end of this initial salvo of material about Fabric T-SQL (more to come as I dig deeper), I wanted to do a final posts about getting information about how your queries execute beyond the query plan. The one simple way I have found to show differences in performance has been to employ clustering.

So in this I am going to take a look at the queries you can use to get performance information about your Fabric T-SQL instance, using a very simple example of employing clustering to show some clear differences between example queries.

Clustering

Clustering is one of the data terms that could not have been created by a database design person. Why? Because it has multiple meanings. In PostgreSQL it means the same thing as an instance in SQL Server. In server architecture it means connecting multiple servers together to form a “single” unit of some form to protect against failure. Then it means an index that determines the physical storage of a table in some manner. This usage of the word means the former.

The weirdest part of using a Fabric Data Warehouse when you first try to look at a query is the lack of indexes. So (currently as of writing this is late 2026), you only have this one tool to affect your performance. The tool allows you to order the data physically in your files so query processing can be reduced when searching the physical file.

Again: “I barely understand the practical, experience base value of clustering, and when I do, it gets a blog or more on its own. I just want to point out its existence.

A quick table as an example

Creating a pretty thin table (3 columns) with ten million rows. I am going to cluster by the primary key value (not declared, and it wouldn’t be enforced or change anything)

DROP TABLE IF EXISTS Action;
DROP TABLE IF EXISTS Domain;
CREATE TABLE Action
(
    ActionId int NOT NULL,
    DomainId int NOT NULL,
    RandomValue decimal(10,2) NOT NULL,
    Padding  char(100) NOT NULL
) 
INSERT INTO Action(ActionId, DomainId, Padding,RandomValue)
SELECT value, (Value % 5) + 1, 'pad',ABS(CHECKSUM(CAST(value AS VARCHAR(20)) 
                                     + 'more to get bigger checksum') * .000001)
FROM   GENERATE_SERIES(0,10000000);
CREATE TABLE Domain
(
    DomainId int NOT NULL,
    GroupingValue varchar(10)
);
INSERT INTO Domain(DomainId, GroupingValue)
VALUES (1,'One'),(2,'Two'),(3,'Three'),(4,'Four'),(5,'Five');

After that completes, we have a table that we can use to execute and see the changes when we make similar data structures and data with a difference like clustering. In the following query, I will group on the GroupingValue column and count and sum some values from the table. I will give you my results (yours will be different since I am using a random enough value for the RandomValue column.

SELECT GroupingValue AS Action, 
        COUNT(*) AS ValueCount, 
        SUM(RandomValue) AS RandomValueTotal
FROM   Action 
         JOIN Domain
            ON Action.DomainId = Domain.DomainId
WHERE  ActionId >= 5000000
GROUP BY GroupingValue; 

As most of the plans seem like they will be in Fabric, it is pretty straightforward. Two sets are scanned and Hash Matched.

Now I am going to create a clustered version of the Action tables using the most selective column, the ActionId pk.

DROP TABLE IF EXISTS ActionClustered;
GO
 
CREATE TABLE ActionClustered 
WITH (CLUSTER BY (ActionId))
AS 
SELECT *
FROM  Action; 

Reminder, the SQL Server SELECT INTO syntax works just fine, but doesn’t allow you to do things like add clustering.

Now you can execute this query and see the same results.

--ActionClustered
SELECT GroupingValue AS ActionClustered, 
        COUNT(*) AS ValueCount, 
        SUM(RandomValue) AS RandomValueTotal
FROM   ActionClustered 
         JOIN Domain
            ON ActionClustered.DomainId = Domain.DomainId
WHERE  ActionId >= 5000000
GROUP BY GroupingValue; 

As you can see from the following image, essentially the same plan. In some iterations of writing this blog, I got a slight shift in the costs, with different costs on the Hash Match (Aggregate) and the Columstore Index Scan on the ActionClustered table.

The difference I want to point out, other than the name of the table, is that the larger Columnstore Index Scan now is accessing ActionClustered.DataClustering. But is there any real difference? In this case there doesn’t seem there wouldn’t be, but how do we know? In SQL Server we would use SET STATISTICS IO on and get the answer. Here there are a set of views that we can use to look at the query’s performance.

Note: for both queries, you will see something like this data returned. You may get different values output in the total, and different first column output for the two different queries. But the numbers should be the same in each case):

Viewing query activity

In this section, I will use that query (and a few more optimized to actually use my clustered data). Note too that this is a series of posts on T-SQL, not all the tools at your disposal. I like to be able to write a query and go find the details right in SQL. This is my command line!

All the objects were are going to look at are a part of the queryinsight system schema. All the objects you can access are view objects, but when I looked at the code and tried to view the individual objects I saw this The SELECT permission or external policy action 'Microsoft.Sql/Sqlservers/Databases/Schemas/Tables/Rows/Select' was denied on the.. Not sure if this is a permanent denial, but I think I have all the permissions. Microsoft knows best anyhow, right?

One last thing. These objects appear to be a simple version of querystore, and don’t directly show you the object the query/procedure they is executed in. They also don’t include prefixed comments, but do seemingly have embedded comments, so you could use that fact to differentiate between executions. That does mean multiple plans, so probably not a great plan overall. So if you need the same exact query over and over, stored procedures probably would be the best way.

queryinsights.exec_requests_history

I like this one a lot as it gets me close to one of my favorite stats. How much data is used in a query I have executed. In this (and the other examples, I only picked a few columns to make this easier to take in, but I will make note of the other columns later. These are the ones I needed for this example:

SELECT  TOP 2 allocated_cpu_time_ms AS cpu_ms, 
        data_scanned_memory_mb AS memory_scanned_data,
        data_scanned_remote_storage_mb AS remote_data_scanned, 
        data_scanned_remote_storage_mb + data_scanned_memory_mb 
                                                     AS data_scanned, 
        start_time, LEFT(command, 100) AS [command...]
FROM    queryinsights.exec_requests_history /* ignore */
WHERE   command LIKE '%SELECT GroupingValue AS Action%' 
ORDER BY exec_requests_history.start_time DESC; 

Looking at all the data, you can get the end time, if the query is distributed and/or accelerated, the login name, the number of rows accessed, the program name, result cache use, error codes, if it is using external api, and more. For now, I am just happy to see that scanned information.

Luckily these are the last two queries that were recorded for me:

What’s particularly interesting is how much data you see is scanned in memory or remotely. On the clustered version, it scanned less data than on the one we didn’t cluster.

Following these breadcrumbs, it does feel like the basic architecture (conceptually, of course) we knew from SQL Server still is the same. Data is accessed, put in memory, and acted on in some way. Since it doesn’t always seem to end up in memory maybe that isn’t the case. No idea where the cache is, or anything else, but my curiosity is piqued never the less and when I get back to Fabric as a topic, you bet that query tuning and internals will be in my writing list.

You can also see the amount of CPU it used which in this case seems minor, but it even though the savings wasn’t 50%, there was an appreciable change in data accessed. You can also see the elapsed time. All of this is interesting, and will lead to the next topic on long running queries.

So using this view we can get a view that has similarities to the kind of data we used to get from STATISTICS IO and `CPU. Next up someday will be figuring out how sometimes it is in memory, and sometimes not (even after a few runs.)

queryinsights.long_running_queries

This next query is one that can be quite useful over time, in that it will tell you your longest executing queries and points out a quick place to get some wins in performance tuning. In the next statement, I will show a few of the most important (easy to put on a blog post) columns that will show some of my longest running queries. All of them (it will turn out), have to do with some testing I have done, or a settings capture tool I am working on so I can see when system settings change.

SELECT TOP 5 database_name, median_total_elapsed_time_ms, 
        last_run_total_elapsed_time_ms, number_of_runs, 
        LEFT(last_run_command, 100) AS [last_run_command...],
FROM   queryinsights.long_running_queries
ORDER  BY median_total_elapsed_time_ms DESC;

The first query was from when I was testing a schema locks back in part 5 of this series on Fabric T-SQL. That was 2 weeks ago, so this data has stayed around for a while.

This is my monitoring database, so most of what goes on in here is for testing my monitoring tools I am gradually creating. These stats stick around for a while, so you can look at the runs you have done in data for a while. Somehting fascinating is that it groups queries together that are basically the same query (much like how SQL Server parameterizes). So if I execute the following:

SELECT TOP 5 database_name, median_total_elapsed_time_ms, 
        last_run_total_elapsed_time_ms, number_of_runs, 
        last_run_command
FROM   queryinsights.long_running_queries
WHERE   last_run_command LIKE 'SELECT GroupingValue AS Action%' 
ORDER  BY median_total_elapsed_time_ms DESC; 

In the output you will see these results. I didn’t chop off the end to show that two of the queries were different because I used a hint earlier to try to use data in memory which did not work as I had expected so more to learn there too. I feel like I might say that a lot, and not just in these blogs.

This is really useful, though more valuable over quite a lot more time than I have used it for. In addition to database_name, median_total_elapsed_time_ms, last_run_total_elapsed_time_ms, number_of_runs, and last_run_command; there are columns to give you the last distributed statement id, the last session that executed this, number of executions, and the number of accelerated runs (even more to learn about!) and the query_hash.

This will be quite useful to look not only at queries the report gives you, but specific types of queries.

queryinsights.frequently_run_queries

The last of these objects I will highlight is frequently_run_queries. While it is interesting to know how much it costs to run a very long query, a lot of times the big issue is actually queries run many many times. So this query will give you this information:

SELECT TOP 5 database_name,
       number_of_runs,
       number_of_accelerated_runs,
       min_run_total_elapsed_time_ms,
       max_run_total_elapsed_time_ms,
       avg_total_elapsed_time_ms,
       number_of_successful_runs,
       number_of_failed_runs,
       number_of_canceled_runs,
       LEFT(last_run_command, 100) AS [last_run_command...]
FROM   queryinsights.frequently_run_queries
WHERE   last_run_command LIKE 'SELECT GroupingValue AS Action%' 
ORDER BY number_of_runs DESC;

This is a lot to fit into a table, but the idea here is that you can see the queries that are executed the most (and I explained in the previous section why there are two copies of each query in the truncated output:

If you can query and correlate the longest running queries along with the most frequently executed, you should be able to do some interesting things with this data to find troublesome queries while filtering out known queries that are expected to take the longest.

The rest of the queryinsights views we have

I won’t cover them, because they are not directly about query tuning, but there is queryinsights.sql_pool_insights that returns details about the system, and then queryinsights.external_api_call_stats this covers details about queries that use external APIs from AI functions. It isn’t that these aren’t useful, I just haven’t used them yet, and I only wanted to make sure you had seen them.

Who is executing code on my server?

Finally, lets go back to the old faithful DMV queries that tell you about the sessions and the requests you are making on the Fabric resources.

For this, I will uses some queries about the executing queries and the connections that are lightly adapted from a query I wrote for the book “Performance Tuning with SQL Server Dynamic Management Views” book with Tim Ford (Linkedin) 16 (!!!) years ago that still works with just a minor change. I had to remove a few things to make it work in Fabric since somethings (like query plan function dm_exec_query_plan isn’t supported.)

Both of the following queries monitor active sessions and executing requests using sys.dm_exec_sessions and sys.dm_exec_requests DMVs. Both retrieve session info, request status, wait statistics, execution time, transaction isolation level, and the currently executing SQL text. The differences will be the kind of join done and what this means I am focussing on.

I will also note that while these dmvs are the ones they suggested to use, they may or may not contain data in some columns. However, they do have utility purpose to see some data that is very interesting in your Fabric Data Warehouse.

Session Details

This following query returns ALL sessions (even idle ones without active requests). This provides a broader view including sessions that are connected but not
currently executing anything. Returns more session-focused columns (login_time, original_login_name, nt_user_name, cpu_time, total_scheduled_time, total_elapsed_time).

I won’t show any output from this query now, most of it is self explanatory (and most of it you won’t really need much of the time. Probably my biggest use is to see who is doing what in a database.

SELECT  des.session_id, des.login_name, des.login_time, des.program_name,
        d.name AS database_name,
           des.original_login_name, des.nt_user_name,
		des.cpu_time, 
		des.total_scheduled_time, des.total_elapsed_time,
      -- always  snapshot
      --   CASE des.transaction_isolation_level
      --      WHEN 0 THEN 'Unspecified' WHEN 1 THEN 'ReadUncomitted'
      --      WHEN 2 THEN 'ReadCommitted' WHEN 3 THEN 'Repeatable'
      --      WHEN 4 THEN 'Serializable' WHEN 5 THEN 'Snapshot'
      --END AS transaction_isolation_level,
	  des.last_request_start_time, des.reads, des.writes, des.logical_reads,
	   der.session_id, der.blocking_session_id, der.wait_type, der.wait_time,
       der.start_time, DATEDIFF(SECOND,der.start_time,GETDATE())/60.0 AS executeTime_Minutes,
	   der.percent_complete,
       der.status AS requestStatus, 
       CAST(DB_NAME(der.database_id) AS VARCHAR(30)) AS databaseName,
       der.command AS commandType,
       der.percent_complete,
	   CHAR(13) + CHAR(10) + '-------Current Command-----------' + CHAR(13) + CHAR(10) + 
	   CASE WHEN der.statement_end_offset = -1 THEN '--see objectText--'
						 ELSE SUBSTRING(execText.text, der.statement_start_offset/2, 
											  (der.statement_end_offset - der.statement_start_offset)/2) 
				   END + CHAR(13) + CHAR(10) + '------Full Object------------' AS currentExecutingCommand,
       execText.text AS objectText
       --,execPlan.query_plan
FROM sys.dm_exec_sessions des --returns information about each user and internal system session on a SQL Server 
							  --instance including session settings, security, and cumulative CPU, memory, and I/O usage
        JOIN sys.databases AS d
            ON d.database_id = des.database_id
        LEFT OUTER JOIN sys.dm_exec_requests AS der  --The sys.dm_exec_requests DMV shows us what is currently running
            ON der.session_id = des.session_id		 --on the SQL Server instance, its impact on memory, CPU, disk, and cache.
        OUTER APPLY sys.dm_exec_sql_text(der.sql_handle) AS execText
		
        --sys.dm_exec_query_plan not supported 
        --OUTER APPLY sys.dm_exec_query_plan (der.sql_handle) AS execPlan
--WHERE is_user_process = 1 --this will show you users running ad-hoc queries. May not show pipelines and other queries.

Requests Session Details

This query uses an INNER JOIN between requests and sessions, so it returns ONLY sessions with active requests currently executing. This provides a focused view of what’s actually running right now. It returns a more request-focused column order (starts with request details before session details). Ideal for seeing what connections are actively executing queries.

SELECT  der.session_id, der.blocking_session_id, der.wait_type, der.wait_time,
        der.start_time, DATEDIFF(SECOND,der.start_time,GETDATE())/60.0 AS executeTime_Minutes,
		percent_complete,
        d.name,
        der.status AS requestStatus, 
        des.login_name, 
        CAST(DB_NAME(der.database_id) AS VARCHAR(30)) AS databaseName,
        des.program_name,
        der.command AS commandType,
        der.percent_complete,
        -- always snapshot
        --CASE des.transaction_isolation_level
        --    WHEN 0 THEN 'Unspecified' WHEN 1 THEN 'ReadUncomitted'
        --    WHEN 2 THEN 'ReadCommitted' WHEN 3 THEN 'Repeatable'
        --    WHEN 4 THEN 'Serializable' WHEN 5 THEN 'Snapshot'
        --END AS transaction_isolation_level,
		CHAR(13) + CHAR(10) + '-------Current Command-----------' + CHAR(13) + CHAR(10) + 
		CASE WHEN der.statement_end_offset = -1 THEN '--see objectText--'
						 ELSE SUBSTRING(execText.text, der.statement_start_offset/2, 
											  (der.statement_end_offset - der.statement_start_offset)/2) 
        END + CHAR(13) + CHAR(10) + '------Full Object------------' AS currentExecutingCommand,
        execText.text AS objectText
		
	    --execPlan.query_plan
FROM sys.dm_exec_sessions des --returns information about each user and internal system session on a SQL Server 
							  --instance including session settings, security, and cumulative CPU, memory, and I/O usage
        JOIN sys.dm_exec_requests AS der			  --The sys.dm_exec_requests DMV shows us what is currently running
            ON der.session_id = des.session_id		  --on the SQL Server instance, its impact on memory, CPU, disk, and cache.
        LEFT OUTER JOIN sys.databases d
            ON d.database_id = der.database_id
        OUTER APPLY sys.dm_exec_sql_text(der.sql_handle) AS execText
		
		--OUTER APPLY sys.dm_exec_query_plan (der.plan_handle) AS execPlan
--WHERE is_user_process = 1 --this will show you users running ad-hoc queries. May not show pipelines and other queries.

Summary

As I wrote this post, I realized it was time to pause and do some more learning first. The post is definitely something I wish I had available when I started working on this stuff, as were all the previous entries. But I have reached a limit of my knowledge that I need to add to. As I find new things to share, I clearly will.

In this post I did a quick intro to clustering a table in the Data Warehouse container in Fabric, which can help with filtering data in cases where it makes sense. Then I shows some queries you can use to view the statistics of your queries in Fabric. Hope it all helps and if you have Fabric details to share, post a link in the comments and I will check your page/post out!