Viewing Execution Plans While Queries are Running

Analyzing execution plans is a key tool when analyzing query performance. As most of you are aware there are two ways of analyzing query plans, namely ‘Include Actual Execution Plan’ and ‘Display Estimated Query Plan’

The Include Actual Execution Plan option will provide the execution plan after the completion of the query. However, this option is not very useful when dealing with long running queries.

Let us assume that there is a long running query and you have the task of improving it. Every time you make a change, you will need to run the query and verify the execution plan. However, this will be a time consuming task as you can view the execution plan only after the completion of the query.

The other option available to you is to use the Display Estimated Query Plan. This provides the estimated query plan without executing the query. However, estimated query plan may not be accurate since it only shows the estimated execution plan.

The optimal situation is viewing query plan while the query is executing. In SQL Server 2014, you have this option available by means of a new DMV – sys.dm_exec_query_profiles

This is the new DMV available in SQL Server 2014 to achieve the above objective. You can get all the details about this DMV from

So let us use following query.

  FROM [dbo].[FactInternetSales] FI
INNER JOIN [dbo].[FactFinance]  FF ON FI.DueDateKey = FF.DateKey
  WHERE Month([DueDate]) = 02
  GO 300

There are few things need to be noted in the above query.


This is to clear the query cache for the demonstration purposes.  The query plan is cached to improve the performance of your queries. When the query plan is cached subsequent queries do need not to be created and the cached plan can be utilized for the queries. We execute above DBCC just to clear the cache to verify our argument, DO NOT execute this in production as it will degrade your production performances.


This is an essential statement in order to capture data to the sys.dm_exec_query_profiles DMV otherwise the DMV will return an empty result.

MONTH([DueDate]) = 02

In the where clause, the MONTH function is included to have a clustered index scan in the clustered index table.

GO 300

This will execute the query 300 times. Again, this is not something you will execute in production frequently. This statement will make the query a long running query.

Let us run the query. the following will be the actual query plan. So we will verify this with the new option.

While query is running, let us run the following query in a different session.

        SELECT session_id
        OBJECT_NAME(Object_id) TableName
FROM sys.dm_exec_query_profiles

The below images were captured at different times from the above when the long running query is executing.  


This demonstrates that sys.dm_exec_query_profiles gives you live running details of your queries as you can see row count is changed at different times.


No comments yet... Be the first to leave a reply!