dm_exec_query_stats

dm_exec_query_stats reports wrong CPU Utilization when a query executes in parallel (MAXDOP > 1): [This article is outdated]

About the article:

This is an excerpt from the 3 Day Level 400 Microsoft SQL Server Performance Monitoring & Tuning Hands-on Workshop; I explain the concepts in the workshop with examples. I try to do the best while writing this article, but it is not the same!!

This article discusses incorrect CPU utilization reported by sys.dm_exec_query_stats when a query executes in parallel.

Introduction:

This article helps you to understand the fact that sys.dm_exec_query_stats returns incorrect CPU utilization when a query executes in parallel.

The purpose of this article is to help you better understand SQL Server Performance Monitoring & Tuning, and not to discuss whether this is a bug or not.

 Description: 

When a query executes serially (MAXDOP  = 1), the total_worker_time reported in sys.dm_exec_query_stats is accurate. But when a query executes in parallel (MAXDOP > 1 or MAXDOP = 0), total_worker_time reported by sys.dm_exec_query_stats is inaccurate.

Usually CPU intensive queries execute in parallel. Most customers use the default configuration where 'max degree of parallelism' is set to '0' where it is more common for CPU intensive queries to execute in parallel.

A customer tells you they have high CPU utilization on their server and asks you to identify the issue. Without knowing that sys.dm_exec_query_stats reports incorrect CPU utilization when a query executes in parallel, you might query sys.dm_exec_query_stats and tell your customer that there is no query that is CPU intensive. Sooner or later the customer might find the query that you missed to point out.

Example:

This SQL Server example needs tables that you have to create. The script to create the table is located at the end of this article(www.sqlworkshops.com/dm_exec_query_stats.htm). You also need a server that has at least 2 CPUs to reproduce this example.

When you execute the below query limiting MAXDOP to 1(serial execution), the CPU_Utilized_in_Seconds reported by sys.dm_exec_query_stats is accurate - it will nearly match your wall clock execution time.

>> in theory sys.dm_exec_query_stats always works:

--example provided by www.sqlworkshops.com
--reset cache to collect fresh set of statistics
dbccfreeproccache
go
--execute a sample query serially that takes x amount of seconds
selectmax(t1.c2+t2.c2)fromtab7 t1crossjointab7 t2option(maxdop1)
go
--now query sys.dm_exec_query_stats to find CPU Utilized by the above query
select(total_worker_time*1.0)/1000000asCPU_Utilized_in_Seconds,*fromsys.dm_exec_query_stats
crossapplysys.dm_exec_sql_text(sql_handle)
wheretextlike'%select max(t1.c2 + t2.c2) from tab7 t1 cross join tab7 t2%'and
textnotlike'%sys.dm_exec_query_stats%'--to eliminate our probe
go

Don't miss our SQL Server Performance Tuning Hands-on Workshop! Register

SQL Server Performance Tuning Consulting & Health Check

sp_whopro - SQL Server Activity Monitoring and Logging Stored Procedure - Free Download

SQLTest - SQL Server Performance, Load, Stress and Unit Test Tool - Free Download

SQLVideo - SQL Server Performance Tuning and Troubleshooting - Free Youtube Videos, now also available on MSDN Channel 9!

>> CPU_Utilized_in_Seconds will be around 6 to 18 seconds based on your CPU speed - which is what you expect

But when you execute the query without limiting MAXDOP to 1, say 0(parallel execution), the CPU_Utilized_in_Seconds reported by sys.dm_exec_query_stats is inaccurate - will not match your wall clock execution time.

>> in practice sys.dm_exec_qyery_stats does not always works:

--example provided by www.sqlworkshops.com
--reset cache to collect fresh set of statistics
dbccfreeproccache
go
--execute a sample query in parallel that takes x amount of seconds
selectmax(t1.c2+t2.c2)fromtab7 t1crossjointab7 t2
go
--now query sys.dm_exec_query_stats to find CPU Utilized by the above query
select(total_worker_time*1.0)/1000000asCPU_Utilized_in_Seconds,*fromsys.dm_exec_query_stats
crossapplysys.dm_exec_sql_text(sql_handle)
wheretextlike'%select max(t1.c2 + t2.c2) from tab7 t1 cross join tab7 t2%'and
textnotlike'%sys.dm_exec_query_stats%'--to eliminate our probe
go

>> CPU_Utilized_in_Seconds will be around 0.00xxxx seconds  - which you do not expect!

Explanation:

When a query executes in serial sys.dm_exec_query_stats reports thread time (kernel_time + usermode_time) of the thread that executed the query. But when the query executes in parallel sys.dm_exec_query_stats does not report thread time of all the threads that were involved in executing the query.

It is beyond the scope of this article to discuss about ways to find out the actual CPU utilization of queries that execute in parallel.

Table:

--example provided by www.sqlworkshops.com
createtabletab7(c1intprimarykeyclustered,c2int,c3char(2000))
go
begintran
declare@iint
set@i=1
while@i<=5000
begin
insertintotab7values(@i,@i,'a')
set@i=@i+1
end
committran
go

Additional Information:

Now you see the theoretical explanation (as described in Books and other Trainings) and practical usage. In this case this behavior exactly hides the expensive queries from you (query that uses more than 1 CPU) - this is a simple example of theory in practice. But the SQL world is much more complicated than this, one has to cross the fine line of theory and start discovering much more practical behavior / solutions.

Well, if you have attended my SQL 2005 workshop(s) in the past you know the fact that you cannot fully rely on sys.dm_exec_query_stats. You also understood how to effectively Monitor and Tune SQL Server Performance in much more complicated situations (that's what differentiates you and your colleague, your practice and their theory!). And best of all you learned how to enhance your knowledge further to eventually become the master of the domain.

I encourage you to attend the 3 Day Advanced Microsoft SQL Server Performance Monitoring & Tuning Hands-on Workshop, where you will gain real practical knowledge.

Microsoft Employees (including senior members of Microsoft Consulting Services, Support and SQL Server Product Development team) have participated in the 3 Day Level 400 Microsoft SQL Server Performance Monitoring & Tuning Hands-on Workshops, and SAP AGs IT Department DBAs in Walldorf, Germany have participated in Level 300 Microsoft SQL Server Administration & Tuning for SAP Customers Hands-on Workshops.

Download Truly Level 400 SQL Server Performance Monitoring and Tuning Webcasts. Firewall might block downloads, if you have issues downloading, let us know, we will provide you with alternate download links. 

To receive example SQL Scripts used in webcasts:  subscribe to our Newsletter. Please take a few minutes and pass on the webcast link to your friends and local user community.

Feedback from a Microsoft Employee: "Your webcast rocked. Your free webcast was very good and informative."

Read some more SQL Server Performance Tuning Articles

This article is for informational purposes only; you use any of the suggestions given here entirely at your own risk.

© 2010-2018 www.sqlworkshops.com. All rights reserved

Back to Top