Databases Reference
In-Depth Information
This query will help you zero in on what your SQL Server instance is spending the most time waiting
for. Especially if your SQL Server instance is under stress or having performance problems, this can be
very valuable information. Knowing that your top cumulative wait types are all I/O related can point
you in the right direction for doing further evidence gathering and investigation of your I/O subsystem.
However, be aware of several important caveats when using and interpreting the results of this query.
First, this is only a rollup of wait types since the last time your SQL Server instance was restarted, or
the last time your wait statistics were cleared. If your SQL Server instance has been running for several
months and something important was recently changed, the cumulative wait stats will not show the
current actual top wait types, but will instead be weighted toward the overall top wait types over
the entire time the instance has been running. This will give you a false picture of the current situation.
Second, there are literally hundreds of different wait types (with more being added in each new
version of SQL Server), and only a small number of them are documented in SQL Server Books
Online. There is a lot of bad information on the Internet about what many wait types mean, and
how you should consider addressing them. Bob Ward, who works for Microsoft Support, is a very
reliable source for SQL Server wait type information. He has a SQL Server Wait Type Repository
available online at
http://blogs.msdn.com/b/psssql/archive/2009/11/03/
the-sql-server-wait-type-repository.aspx
that documents many SQL Server wait types,
including what action you might want to take to alleviate that wait type.
Finally, many common wait types are called
benign
wait types, meaning you can safely ignore them
in most situations. The most common benign wait types are i ltered out in the
NOT
IN
clause of the
health check query to make the results more relevant. Even so, I constantly get questions from DBAs
who are obsessing over a particular wait type that shows up in this query. My answer is basically
that if your database instance is running well, with no other signs of stress, you probably don't
need to worry too much about your top wait type, particularly if it is an uncommon wait type.
SQL Server is always waiting on something; but if the server is running well, with no other
warning signs, you should relax a little.
Next, using the query shown in Listing 15-21, you are going to look at the cumulative signal (CPU)
waits for the instance.
LISTING 15-21:
Signal waits for the instance
-- Signal Waits for instance
SELECT CAST(100.0 * SUM(signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2))
AS [%signal (cpu) waits],
CAST(100.0 * SUM(wait_time_ms - signal_wait_time_ms) / SUM (wait_time_ms) AS
NUMERIC(20,2)) AS [%resource waits]
FROM sys.dm_os_wait_stats WITH (NOLOCK) OPTION (RECOMPILE);
-- Signal Waits above 15-20% is usually a sign of CPU pressure
Signal waits are CPU-related waits. If you are seeing other signs of CPU pressure on your SQL
Server instance, this query can help coni rm or deny the fact that you are seeing sustained cumula-
tive CPU pressure. Usually, seeing signal waits above 15-20% is a sign of CPU pressure.
Now you will take a look at which logins have the most open database connections, using the query
shown in Listing 15-22.
Search WWH ::
Custom Search