Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 4 down vote favorite
2

Test scenario:
I am only one user on database. I reset sys.dm_os_wait_stats. I run a SELECT query (table scan).

Result:
Execution time = 20 minutes, but sys.dm_os_wait_stats -> PAGEIOLATCH_SH -> Wait_time_ms = 40 minutes (2 580 252)

Question:
How is it possible that in 20 minutes: wait_time_ms > execution time ?

share|improve this question
4  
Your query could run in parallel, with two threads accumulating twice the wait time – Andomar Feb 22 at 9:02
4  
That is also cumulative wait time across the instance, not just a particular database. – Thomas Stringer Feb 22 at 14:23

migrated from stackoverflow.com Feb 22 at 13:38

1 Answer

up vote 4 down vote

As the two comments mention, there are two things at work here.

  1. sys.dm_os_wait_stats records accumulated wait times since the server was restarted. So if the server has been up for a while, these can be fairly high. To get a meaningful value out of sys.dm_os_wait_stats, you need to take two snapshots e.g. 5 minutes apart and compare them to each other. Keep in mind, there you cannot tie them to a single connection. Anything on the server that is waiting for something during that time will cause a change in one of the values returned by sys.dm_os_wait_stats.

  2. If a query gets executed in parallel, each thread will use up CPU time as well as potentially wait time. So both the total CPU time as well as the total wait time for a single query can be higher than the elapsed time.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.