Lesson Learned #339: WAITFOR wait type delay

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Community Hub.

Today, I worked on a service request that our customer got multiple wait stats with the text of WAITFOR.

The WAITFOR wait type doesn't indicate performance issues but will have an impact on duration of the query that is executing the related WAITFOR TSQL command.

While pausing the query using WAITFOR command, the transaction will be onhold until the WAITFOR time specified is reached. That's mean that the thread is begin held by the transaction that cannot be used for other process.

SQL Server also reserves a dedicated thread, so, if you have many associated with WAITFOR you could have a thread exhaustion. So, check these queries and see is needed this or not.

Regarding the public information about WAITFOR here, "Each WAITFOR statement has a thread associated with it. If many WAITFOR statements are specified on the same server, many threads can be tied up waiting for these statements to run. SQL Server monitors the number of WAITFOR statement threads, and randomly selects some of these threads to exit if the server starts to experience thread starvation."

You could check running the following process, using the tool oStress, I'm running using 198 process the command 'WAITFOR delay'.

"C:\Program Files\Microsoft Corporation\RMLUtils\ostress.exe" -ddbname -Q"WAITFOR DELAY '00:00:20'" -Sservername.database.windows.net -Uusername -n198 -r300 -q -l60

Using the TSQL

select req.status, tasks.session_id, task_state, wait_type, wait_time, req.last_wait_type, cpu_time, dop, req.command, blocking_session_id, substring (REPLACE (REPLACE (SUBSTRING (ST.text , (req.statement_start_offset/2) + 1 , ( (CASE statement_end_offset WHEN -1 THEN DATALENGTH(ST.text) ELSE req.statement_end_offset END - req.statement_start_offset)/2) + 1) , CHAR(10), ' '), CHAR(13), ' '), 1, 512) AS statement_text, sched.status, * from sys.dm_exec_requests req join sys.dm_os_workers work on req.task_address = work.task_address join sys.dm_os_tasks tasks on req.session_id = tasks.session_id join sys.dm_os_schedulers sched on sched.scheduler_id = tasks.scheduler_id CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) as ST where req.status <> 'background' and req.session_id<> @@spid order by wait_resource,req.session_id, req.status

You could see the elapsed time per process.

Enjoy!

Leave a Reply Cancel reply