SQL Server 2005 performance mismatch (5)

The impact of using SQL waits blocking on overall performance

SQL Server 2000 provides 76 wait types to provide waiting reports. SQL Server 2005 provides an extra 100 wait types to track application performance. When 1 user connections are waiting at any time, SQL Server accumulates the wait time. For example, an application requests resources such as I/O, locks, or memory to wait for resources until they are available. These wait information can be rolled up and sorted across all connections, so the performance configuration can be obtained from a given load. As a result, the SQL wait type identifies and classifies users (or threads) from the application load or user point of view to wait.

This query lists the first 10-bit waits in SQL Server. These are cumulative while waiting, but you can reset this counter using DBCC SQLPERF ([sys.dm_os_wait_stats], clear).

select top 10 *
from sys.dm_os_wait_stats
order by wait_time_ms desc

The following are the outputs, with a few key points to note:

Some waits are normal, such as the wait for a background thread, such as the lazy writer component.

Some sessions wait a long time to get a shared lock

The signal wait is in a worker thread to get access to the resource to which it is getting the CPU scheduled to execute this period of time. Long-time signal waiting may mean high CPU contention.

wait_type　　 waiting_tasks_count　wait_time_ms　　 max_wait_time_ms　signal_wait_time_ms　
------------------ -------------------- -------------------- -------------------- -------
LAZYWRITER_SLEEP　　　415088　　　　　　　 415048437　　　　　　1812　　　　　　　　 156
SQLTRACE_BUFFER_FLUSH 103762　　　　　　　 415044000　　　　　　4000　　　　　　　　 0
LCK_M_S　　　　　　　 6　　　　　　　　　　25016812　　　　　　 23240921　　　　　　 0
WRITELOG　　　　　　　7413　　　　　　　　 86843　　　　　　　　187　　　　　　　　　406
LOGMGR_RESERVE_APPEND 82　　　　　　　　　 82000　　　　　　　　1000　　　　　　　　 0
SLEEP_BPOOL_FLUSH　　 4948　　　　　　　　 28687　　　　　　　　31　　　　　　　　　 15
LCK_M_X　　　　　　　 1　　　　　　　　　　20000　　　　　　　　20000　　　　　　　　0
PAGEIOLATCH_SH　　　　871　　　　　　　　　11718　　　　　　　　140　　　　　　　　　15
PAGEIOLATCH_UP　　　　755　　　　　　　　　9484　　　　　　　　 187　　　　　　　　　0
IO_COMPLETION　　　　 636　　　　　　　　　7031　　　　　　　　 203　　　　　　　　　0

To analyze the wait state, you need to obtain data for later analysis. Appendix B provides 2 examples of stored procedures.