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
So let us use following query.
DBCC DROPCLEANBUFFERS SET STATISTICS PROFILE ON SELECT TOP 10000 [SalesOrderNumber] ,[SalesOrderLineNumber] ,[RevisionNumber] ,[SalesAmount] ,[TaxAmt] ,[CustomerPONumber] 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.
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 node_id, physical_operator_name, row_count, estimate_row_count, 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
gives you live running details of your queries as you can see row count is
changed at different times.