SQL Server: Too High Difference in CPU and Elapsed Time (Duration) , Don’t Blame IO Every Time


There could be different causes behind too high difference in CPU and Elapsed Time (Duration) value for a query executed by SQL Server.  One of the most common reasons is IO problem.  This can easily be observed by executing following query:
SELECT  *
FROM    sys.dm_os_wait_stats
WHERE   wait_type LIKE‘PAGEIOLATCH%’
If number of waits and average wait time is too high then there is something wrong on IO side. To get the root cause, you have to check different things like queries without proper indexes (high page read by queries), pressure on TempDB side, hard drive and memory performance etc.
Normally when you are facing up to two times higher elapsed time value as compared to CPU then IO waits could be a cause, but what if your query elapsed time is 5, 10 or more times high. 
Recently on one of our production server, a query gave me amazing time stats.


I know it’s a simple query, indexes are properly applied and it return results with fast response. Query executed under 1 sec of response time (SSMS properties can show up to seconds), but Time Stats showing that query elapsed time is more than 6 Seconds.
Why this happening:
This happened because my production server CPUs counter are not synchronized with each other. It could be confirmed from SQL Server Log. (SQL Server 2005 Service Pack 2 and higher edition show this message)
The time stamp counter of CPU on scheduler id 13 is not synchronized with other CPUs.
Good thing is that, there is nothing wrong with your SQL Server performance and everything will work fine. Only problem you can face is that, your performance tuning process will be affected as you can’t collect correct information regarding query execution time.
This basically happens when you make changes in power polices or install utilities that can affect CPU performance and can easily be corrected by setting you machine, power options to “Always On” or “Max Performance”.
Advertisements

Posted on May 28, 2012, in Performance Tuning, Sql Server 2005, Sql Server 2008, SQL Server 2012. Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: