Many times we got a call from the business team regarding the performance issue on the database server. As a first step you can check for the blocking, if the blocking is not there. We have to check for the waits, Query is internally waiting for the resources to complete its process.
By identifying the correct wait type will give you the directions to troubleshooting issue further. You can execute below query to get the 10 wait statistics.
SELECT TOP 10
wait_type ,
max_wait_time_ms wait_time_ms ,
signal_wait_time_ms ,
wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms ,
100.0 * wait_time_ms / SUM(wait_time_ms) OVER ( )
AS percent_total_waits ,
100.0 * signal_wait_time_ms / SUM(signal_wait_time_ms) OVER ( )
AS percent_total_signal_waits ,
100.0 * ( wait_time_ms - signal_wait_time_ms )
/ SUM(wait_time_ms) OVER ( ) AS percent_total_resource_waits
FROM sys.dm_os_wait_stats
WHERE wait_time_ms > 0
ORDER BY wait_time_ms DESC
You have to checkout for the below kind of wait statistics and troubleshoot as per the stats.
CXPACKET :Most of the time it indicates nothing more than that certain queries are executing with parallelism; CXPACKET waits in the server are not an immediate sign of problems, it may be the symptom of another problem, associated with one of the other high value wait types in the instance.
SOS_SCHEDULER_YIELD :The tasks executing in the system are yielding the scheduler, having exceeded their quantum, and are having to wait in the runnable queue for other tasks to execute. This may indicate that the server is under CPU pressure.
THREADPOOL :A task had to wait to have a worker bound to it, in order to execute.
LCK_* :These wait types indicate that blocking is occurring in the system and that sessions have had to wait to acquire a lock of a specific type, which was being held by another database session. This problem can be investigated further using, for example, the information in the sys.dm_db_index_operational_stats.
PAGEIOLATCH_*, IO_COMPLETION, WRITELOG :These waits are commonly associated with disk I/O bottlenecks, though the root cause of the problem may be, and commonly is, a poorly performing query that is consuming excessive amounts of memory in the server.
PAGELATCH_* :Non-I/O waits for latches on data pages in the buffer pool. A lot of times PAGELATCH_* waits are associated with allocation contention issues. One of the best-known allocations issues associated with PAGELATCH_* waits occurs in tempdb when the a large number of objects are being created and destroyed in tempdb and the system experiences contention on the Shared Global Allocation Map (SGAM), Global Allocation Map (GAM), and Page Free Space (PFS) pages in the tempdb database.
LATCH_* :These waits are associated with lightweight short-term synchronization objects that are used to protect access to internal caches, but not the buffer cache. These waits can indicate a range of problems, depending on the latch type. Determining the specific latch class that has the most accumulated wait time associated with it can be found by querying the sys.dm_os_latch_stats DMV.
ASYNC_NETWORK_IO :This wait is often incorrectly attributed to a network bottleneck.
18.520469
73.856621
.
