DaveWentzel.com All Things Data
Subsystem Information Home
Waits and Queues is a performing tuning technique that is different from using SQL Profiler, which is what most SQL Server DBAs do and are most familiar with. Using this method you can quickly spot where you can get the most bang for your performance tuning buck without doing any profiler analysis.
This performance tuning methodology is very similar to the YAPP method that Oracle DBAs have been using for many years (which is why I like it). The basic equation behind YAPP (Yet Another Performance Profiling Method) is:
Response Time = Service Time + Wait Time
What we want to do in SQL Server is find out why the response time is so high by looking at the wait time and diagnosing the issue. This is where DMVs come in very handy too, but you definitely more than just Profiler, which is the tool of choice for many SQL Server DBAs.
I'm not suggesting Profiler's use is not as important, just showing an alternative method. Profiler is but one tool in your arsenal...but it won't show you everything. For instance, compile locks. When one process obtains a LCK_M_X on a procedure to compile it then effectively no other spid can issue a compile lock. Says your proc uses a bunch of temp tables and hence recompiles frequently during its invocation. All Profiler will show, at most, is an SP:Recompile event, what caused it, and the total duration. But the waits and q's methodology will show you how much of that duration is spent actually waiting on that compile lock.
if you need some additional background on Queueing Theory or performance monitoring and measurement see my separate Performance Page.
For basics on the SQL Server Execution Model
Some basic definitions to get started:
Queues: unfulfilled resource requests. Q's measure resource utilization. A request that cannot be immediately provided is q'd. PerfMon gives you lots of Q information as does dm_os_wait_stats.
Waits: waiting usually refers to an idle state in which a task is waiting for something to occur before continuing with its execution. The "something" can be any event (getting a latch), even something external to SQL Server such as waiting on I/O completion (IO_COMPLETION).
Any time a SQL thread is not executing a wait type is set to indicate why it is waiting. Waits are then categorized into Wait Types. The wait information is aggregated by type across all connections so a performance profile can be obtained for the given workload. Once you have the wait type and description you need to know who issued the command. dm_os_waiting_tasks will help you. Using Sysprocesses to look at Waits
Blocking is considered a wait type and is discussed here as well. It is different from waiting in that the wait is not voluntary, it is forced on the waiter by another task. This occurs when the tasks attempt simultaneous access of a shared resource. You can see a ton of information about waits and blocking by looking at nodetitle. A lot of good information can be found in the Blocked Process Report. More on Blocking
Deadlocks are just a blocking chain that forms a cycle. In 2005 deadlock graphs are enabled with trace flag 1222. Locks and Deadlocks
Q's and waits should be used together. They explain application performance and are essential for problem determination. Looking at the correlation you can quickly determine what is NOT the bottleneck and focus on what might be.
You can also determine relevancy this way. For instance, PerfMon might show you that you have 10 million lock requests during your monitoring session. That by itself doesn't have much value. But if I correlate it to lock waits I may see that For those lock requests my total waittime was only a few thousand ms or my total waits where only a few thousand. Not bad. But what if it was significantly higher? Now I know I may want to concentrate on why the lock waits are occurring, where, and what I can do about it.