wait types

Question

Experts ,

This is the wait type we are getting on a SQL 2017 server.

wait_type wait_time_s pct running_pct
CXPACKET 22223396.71 14.33 14.33
BROKER_RECEIVE_WAITFOR 21545448.11 13.89 13.89
SP_SERVER_DIAGNOSTICS_SLEEP 13941053.55 8.99 14.33
BROKER_TRANSMITTER 13202216.87 8.51 14.33
HADR_FILESTREAM_IOMGR
_IOCOMPLETION 6972380.54 4.5 4.5
BROKER_EVENTHANDLER 6971916.74 4.5 4.5
FT_IFTSHC_MUTEX 6969787.7 4.49 4.49
ONDEMAND_TASK_QUEUE 6969594.6 4.49 14.33
SQLTRACE_INCREMENTAL_FLUSH
_SLEEP 6961554.8 4.49 4.49

running this query :

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
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK'
,'SLEEP_SYSTEMTASK','SQLTRACE_BUFFER_FLUSH','WAITFOR', 'LOGMGR_QUEUE','CHECKPOINT_QUEUE'
,'REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT','BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_MANUAL_EVENT'
,'CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT'
,'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN'))
SELECT W1.wait_type,
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

Can you please advise for the bottleneck

Answer 1

Hi,

If the overall performance of the server has dropped, rather than some specific queries, check whether there is pressure on CPU, memory resources.Then find out the expensive queries.

The CXPACKET wait type occurs with parallel query plans when waiting to synchronize the Query Processor Exchange Iterator, and when producing and consuming rows. If waiting is excessive and cannot be reduced by tuning the query (such as adding indexes), consider adjusting the Cost Threshold for Parallelism or lowering the Max Degree of Parallelism (MaxDOP).

Answer 2

The top wait is CXPACKET. The next that follows are benign waits.

Since you are on SQL 2017, turn on Query Store and track down which query that are taking most resources. You probably need to add an index or two, or make some query rewrites.

Answer 3

Thanks for answering , Query taking longer than expected

Answer 4

it has started showing overall for all the queries on the Instance