System Troubleshooting - Pro SQL Server Internals - page 517

■ every new version of SQl Server introduces new wait types. You can see a list of wait types at:
http://msdn.microsoft.com/en-us/library/ms179984.aspx . make sure to select the appropriate version of SQl Server.

Note

Figure 27-3 illustrates the output of the script from one of the production servers at the beginning of the
troubleshooting process. We will talk about wait types from output later in this chapter.

Figure 27-3. Output of the script on one of the production servers

There are other useful SQLOS related data management views:
sys.dm_os_waiting_tasks returns the list of currently suspended tasks including wait type,
waiting time, and the resource for which it is waiting. It also includes the id of the blocking
session, if any.
The sys.dm_exec_requests view provides the list of requests currently executing on SQL
Server. This includes information about the session that submits the request; the current
status of request; information about the current wait type if a task is suspended; SQL and
plan handles; execution statistics, and several other attributes.
The sys.dm_os_schedulers view returns information about schedulers, including their
status, workers, and task information.
The sys.dm_os_threads view provides information about workers.
The sys.dm_os_tasks view provides information about tasks including their state and
some execution statistics.

Wait Statistics Analysis and Troubleshooting
The process of analyzing top waits in the system is called Wait Statistics Analysis . This is one of the frequently used
troubleshooting and performance tuning techniques in SQL Server. Figure 27-4 illustrates a typical Wait Statistics
Analysis troubleshooting cycle.