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!