If you are a SQL Server DBA or developer who frequently deals with long-running queries, you may have wondered how to monitor the query execution while it is still running. In this article, we will explore a new feature introduced in SQL Server 2014 that allows you to track execution statistics at each operator level while a query is running.

Before we dive into the details, let’s first understand the problem. Normally, you can view the estimated execution plan before executing a query and the actual execution plan after it has completed. However, there was no straightforward way to view the execution plan while the query was still in progress.

With the introduction of the sys.dm_exec_query_profiles dynamic management view (DMV) in SQL Server 2014, we now have a solution to this problem. This DMV tracks execution statistics at each operator level while the query is still running, allowing us to monitor the query execution in real-time.

Let’s walk through an example to see how this works. First, we need to create a test table:

CREATE TABLE Customers (
    ID INT,
    Name CHAR(2000) DEFAULT 'SQLAuthority'
);
GO

CREATE CLUSTERED INDEX CCI_Customers ON Customers (ID);
GO

Next, we will populate the table with some random data:

DECLARE @loop INT = 1;
WHILE @loop <= 100000
BEGIN
    INSERT INTO Customers (ID) VALUES (@loop);
    SET @loop = @loop + 1;
END
GO

Now, let’s enable the actual execution plan for the query. You can do this by pressing CTRL + M. If you forget this step, you won’t be able to see the execution plan in the next script.

Next, execute a sample query that will take a long time to execute:

SELECT *
FROM Customers c
INNER JOIN Customers c1 ON c1.ID = c.ID
WHERE c.ID > 50;
GO

Finally, we can run the DMV query to see the live plans at the operator level for the running query:

SELECT physical_operator_name, row_count, estimate_row_count, session_id
FROM sys.dm_exec_query_profiles;

By executing this query in a separate session, you will be able to see various operators and their plans in the result set. This is extremely useful when you have a query that is expected to take a long time to execute, as it allows you to monitor its progress and identify any potential performance bottlenecks.

In conclusion, the sys.dm_exec_query_profiles DMV in SQL Server 2014 provides a valuable tool for monitoring query execution in real-time. By tracking execution statistics at each operator level, you can gain insights into the query’s progress and optimize its performance if needed. This feature is particularly useful for SQL Server DBAs and developers who frequently work with long-running queries.