Search This Blog

Tuesday, November 12, 2013

How do we calculate Total Query Response Time?

Waits are represented by SQL Server wait statistics. SQL Server tracks wait information any time that a user connection or sessionid is waiting. This wait information is summarized and categorized across all connections so that a performance profile can be obtained for a given work load. You can see this data exposed in the DMV sys.dmoswaitstats.

Basically all processes or spids go through a cycle that can be represented by the OS Execution Model. For example, the processes waiting (in line) for the CPU to become available are in the "runnable" queue, and the time spent here is known as a signal wait. The spid at the head of the line gets to the CPU, and if it has all the resources it needs to continue, it proceeds to execute and is now "running". If however, when the spid gets to the CPU, and the resource is not available, it get's placed on the waiter list, and is said to be "suspended" until the resource becomes available - this represents the wait time (or resource waits).
The total time is takes for a process to complete its execution (from beginning to end) is called the total query response time.

Based on above information, the total query response time can be calculated as;

Signal Wait + Resource Wait + Cpu Time = Total Query Response Time

No comments:

Post a Comment