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 try to resolved it by setting you machine, power options to “Always On” or “Max Performance”. If it doesn't work for your, then better try to install Service Pack 3 for SQL Server 2005.

------------------------------------------------------------------------------------

Read More about SQL Server Log Errors/Messages 

• SQL Server has encountered 1 occurrence(s) of cachestore flush
• DeviceIoControl failed, error 21
• This instance of SQL Server has been using a process ID of xxxx since mm/dd/yyyy
• SQL Server has encountered occurrence(s) of I/O requests taking longer than 15 seconds to complete
• I/O is frozen on database DatabaseName