SQL SERVER – Live Plans for Long Running Queries

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 (http://blog.sqlauthority.com)

About these ads

One thought on “SQL SERVER – Live Plans for Long Running Queries

  1. Pingback: SQL SERVER – Live Plan for Executing Query – SQL in Sixty Seconds #073 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s