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.
]]>