SQL Server Performance issue – wait types
Any time a user connection is waiting, SQL Server accumulates wait time. For example, the application requests resources such as I/O, locks, or memory and can wait for the resource to be available. This wait information is summarized and categorized across all connections so that a performance profile can be obtained for a given workload. Thus, SQL wait types identify and categorize user (or thread) waits from an application workload or user perspective.
This query lists the top 10 waits in SQL Server. These waits are cumulative but you can reset them using DBCC SQLPERF ([sys.dm_os_wait_stats], clear).
select top 10 *
order by wait_time_ms desc
Find Wait types in SQL server 2005/2008/2008R2
Execute the following query on SQL server 2005/2008/2008R2 instances and it isolates top waits for server instance since last restart or statistics clear.
WITH Waits AS
(SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,
100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
WHERE wait_type NOT IN (‘CLR_SEMAPHORE’,’LAZYWRITER_SLEEP’,’RESOURCE_QUEUE’,’SLEEP_TASK’
CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
FROM Waits AS W1
INNER JOIN Waits AS W2
ON W2.rn <= W1.rn
GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
HAVING SUM(W2.pct) – W1.pct < 95; — percentage threshold
The output as follows:
CXPACKET waits accounting for more than 5% of total relevant resource waits indicate a query parallelism bottleneck.
The SQL Server configuration option max degree of parallelism determines the maximum number of processors which can participate in query execution. The default value is 0, allowing SQL Server to utilize all available cores at query runtime to contribute in parallel to query processing.
By dividing operations such as range scans into smaller chunks, and amalgamating the results, parallelism can provide performance benefits for queries used for, e.g., for reporting, DSS, & warehouse systems.
Yet, generally speaking, the aforementioned default value, 0, is not optimal for OLTP systems. So we need to concentrate on max degree of parallelism on this SQL server instance and avoid CXPACKET wait types.
The latch waits types of mostly related with IO of the disk and SQL data or log file operation. We beed ti Analyze the disk utilization and database files growth level, shrinking of log files etc. and find out the issue and try to fix it.
Occurs when a backup task is waiting for data, or is waiting for a buffer in which to store data and This type is not typical, except when a task is waiting for a tape mount.
Refer the following links for more info about wait types in SQL server: