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 http://msdn.microsoft.com/en-gb/library/dn223301.aspx.

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.

DBCC DROPCLEANBUFFERS

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.

SET
STATISTICS
PROFILE ON

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
       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
sys.dm_exec_query_profiles
gives you live running details of your queries as you can see row count is
changed at different times.




Array

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