dm_exec_query_stats

Download Truly Level 400 SQL Server Performance Monitoring and Tuning Webcast 3 Part 1: SQLWebcast3Part1of3.wmv(103MB), Part 2: SQLWebcast3Part2of3.wmv(116MB) & Part 3: SQLWebcast3Part3of3.wmv(69.1MB) - FREE Download. Webcast 3 released on February 10, 2010 (Box.net download counters as of February 14, 2012: 39,568 downloads, sum of all 3 Parts). Firewall might block downloads, if you have issues downloading let us know, we will provide you with alternate download links.

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.

If you find this article helpful, give me feedback, this motivates me to write some more articles. I also encourage you to attend the 3 Day Level 400 Microsoft SQL Server Performance Monitoring & Tuning Hands-on Workshop. Register for the upcoming 3 Day Level 400 Microsoft SQL Server 2012 and 2008 Performance Monitoring & Tuning Hands-on Workshop, for workshop schedule click here. These are hands-on workshops with a maximum of 12 participants and not lectures. For consulting engagements click here.

To read additional articles I wrote click here.

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. The purpose of this article is not to discuss whether this is a bug or not.

Lubor Kollar, Customer Advisory Team, SQL Server Development, Microsoft Corporation:
I have attended the first two days of Rameshs Performance Monitoring and Tuning Hands-on Workshop in Prague on Oct. 15 and 16.
My main goal was to learn in detail the contents of Rameshs class so that I can suggest his class to other Microsoft customers Im working with in the role of SQL Server Customer Advisory Team manager. I was very pleased both with the content and delivery of Rameshs class. He is providing useful, deep and very accurate information that will help our customers when developing, testing, tuning and deploying their applications using SQL Server or upgrading existing applications from previous SQL Server releases. Ramesh showed extraordinary attention to detail and he explained in depth the internals of the engine.

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
dbcc freeproccache
go
--execute a sample query serially that takes x amount of seconds
select max(t1.c2 + t2.c2) from tab7 t1 cross join tab7 t2 option (maxdop 1)
go
--now query sys.dm_exec_query_stats to find CPU Utilized by the above query
select (total_worker_time * 1.0) / 1000000 as CPU_Utilized_in_Seconds, * from sys.dm_exec_query_stats
cross apply sys.dm_exec_sql_text(sql_handle)
where text like '%select max(t1.c2 + t2.c2) from tab7 t1 cross join tab7 t2%' and
text not like '%sys.dm_exec_query_stats%' --to eliminate our probe
go
>> 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
dbcc freeproccache
go
--execute a sample query in parallel that takes x amount of seconds
select max(t1.c2 + t2.c2) from tab7 t1 cross join tab7 t2
go
--now query sys.dm_exec_query_stats to find CPU Utilized by the above query
select (total_worker_time * 1.0) / 1000000 as CPU_Utilized_in_Seconds, * from sys.dm_exec_query_stats
cross apply sys.dm_exec_sql_text(sql_handle)
where text like '%select max(t1.c2 + t2.c2) from tab7 t1 cross join tab7 t2%' and
text not like '%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.

Download Truly Level 400 SQL Server Performance Monitoring and Tuning Webcast 2 Part 1: SQLWebcast2Part1of3.wmv(35.1MB), Part 2: SQLWebcast2Part2of3.wmv(34.9MB) & Part 3: SQLWebcast2Part3of3.wmv(30.6MB) - FREE Download. Webcast 2 released on January 20, 2010 (Box.net download counters as of February 14, 2012: 23,953 downloads, sum of all 3 Parts).

Download Truly Level 400 SQL Server Performance Monitoring and Tuning Webcast 1  Part 1: SQLWebcast1Part1of3.wmv(29.1MB), Part 2: SQLWebcast1Part2of3.wmv(47.5MB) & Part 3: SQLWebcast1Part3of3.wmv(37.4MB) - FREE Download. Webcast 1 released on November 21, 2009 (Box.net download counters as of February 14, 2012: 36,079 downloads, sum of all 3 Parts).

To receive example SQL Scripts used in webcasts: Sign up for Email Updates. 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.

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 two and a half years 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 (thats 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 Level 400 Microsoft SQL Server Performance Monitoring & Tuning Hands-on Workshop, where you will gain real practical knowledge.

This level 400 workshop requires participants to have basic prior SQL Server experience, specifically knowledge about Cluster, Heap and Non-cluster index, Index Tuning Wizard and SQL Profiler - to avoid repetition. The workshop covers specifically SQL Server Performance Monitoring & Tuning topics - doesn't cover Disaster Recovery topics on the side to keep the focus.

Have you been to a workshop where the instructor explains the difference between Clustered and Heap index (or demonstrates SQL Profiler) and you felt like having an another coffee break ?
Have you been to a workshop where the instructor explains deeply about DBCC CHECKDB and then 2 years later you felt like you could have spent that time on a vacation because it didnt make a dent in your career ?

Well this workshop will be an entirely different experience. This truly level 400 workshop gives you an opportunity to really gain practical knowledge and use that knowledge to advance your career.

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 delivered by R Meyyappan. SAP AGs IT Department DBAs in Walldorf, Germany have participated in Level 300 Microsoft SQL Server Administration & Tuning for SAP Customers Hands-on Workshops delivered by R Meyyappan.

If you find this article helpful, give me feedback, this motivates me to write some more articles. I also encourage you to attend the 3 Day Level 400 Microsoft SQL Server Performance Monitoring & Tuning Hands-on Workshop. Register for the upcoming 3 Day Level 400 Microsoft SQL Server 2012 and 2008 Performance Monitoring & Tuning Hands-on Workshop, for workshop schedule click here. These are hands-on workshops with a maximum of 12 participants and not lectures. For consulting engagements click here.

Charges: 2100 Euro (VAT excl.). For Agenda click here. For Workshop schedule and registration information click here.

Disclaimer and copyright information:
This article refers to organizations and products that may be the trademarks or registered trademarks of their various owners.

Copyright of this article belongs to R Meyyappan / www.sqlworkshops.com. You may freely use the ideas and concepts discussed in this article with acknowledgement (www.sqlworkshops.com), but you may not claim any of it as your own work.

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

Table:
--example provided by www.sqlworkshops.com
create table tab7 (c1 int primary key clustered, c2 int, c3 char(2000))
go
begin tran
declare @i int
set @i = 1
while @i <= 5000
begin
insert into tab7 values (@i, @i, 'a')
set @i = @i + 1
end
commit tran
go