If you are a SQL Server DBA or developer who is majorly dealing with queries which are running for a long time, you may have one request every time you see a long running query. The request is how to see a plan for a query when it is still running. For query before you execute it, you can see the estimated execution plan and after you executed you can see the actual execution plan – however, how to see a query plan while the query is still being executed. This is a very interesting question.
Well with SQL Server 2014 we have now a new DMV, which tracks execution stats at each operator level while the query is still running. The dmv for the same is sys.dm_exec_query_profiles.
First execute any long running query. In this example, I will build a test table.
-- Create a table to host data
CREATE TABLE Customers (ID INT, Name CHAR(2000) DEFAULT 'SQLAuthority')
GO
CREATE CLUSTERED INDEX CCI_Customers ON Customers(ID)
GO
Right after that, I will populate it with random data in loop.
-- This can take upto a minute or two based on your system.
DECLARE @loop INT = 1
WHILE @loop <= 100000
BEGIN
INSERT INTO Customers (ID) VALUES (@loop)
SET @loop = @loop + 1
END
GO 3
Now I will enable actual execution plan for the query. You can enable that with CTRL + M. If you forget this step, you will be not able to see the execution plan in the next script.
Next, execute sample query which will take long time to execute.
SELECT *
FROM Customers c
INNER JOIN Customers c1 ON c1.ID = c.ID
WHERE c.ID > 50
GO
Now I will run DMV, which will show me live plans at operator level for the query.
-- Open this on a different session and run it.
SELECT physical_operator_name, row_count, estimate_row_count, session_id
FROM sys.dm_exec_query_profiles
Well, that’s it. It is that simple. You can see various operators and its plan in the next image. This is very handy, when your query is going to take long time to execute.
Reference: Pinal Dave (https://blog.sqlauthority.com)
