In this blog post, we are going to talk about a very interesting subject. I often get questions related to SQL Server 2008 Book-Online about various Preemptive wait types. I got a few questions asking what these wait types are and how they could be interpreted. To get current wait types of the system, you can read this article and run the script: SQL SERVER – DMV – sys.dm_os_waiting_tasks and sys.dm_exec_requests – Wait Type – Day 4 of 28.
Before we continue understanding them, let us study first what PREEMPTIVE and Non-PREEMPTIVE waits in SQL Server mean.
PREEMPTIVE: Simply put, this wait means non-cooperative. While SQL Server is executing a task, the Operating System (OS) interrupts it. This leads to SQL Server to involuntarily give up the execution for other higher priority tasks. This is not good for SQL Server as it is a particular external process which makes SQL Server to yield. This kind of wait can reduce the performance drastically and needs to be investigated properly.
Non-PREEMPTIVE: In simple terms, this wait means cooperative. SQL Server manages the scheduling of the threads. When SQL Server manages the scheduling instead of the OS, it makes sure its own priority. In this case, SQL Server decides the priority and one thread yields to another thread voluntarily.
In the earlier version of SQL Server, there was no preemptive wait types mentioned and the associated task status with them was marked as suspended. In SQL Server 2005, preemptive wait types were not listed as well, but their associated task status was marked as running. In SQL Server 2008, preemptive wait types are properly listed and their associated task status is also marked as running.
Now, SQL Server is in Non-Preemptive mode by default and it works fine. When CLR, extended Stored Procedures and other external components run, they run in Preemptive mode, leading to the creation of these wait types.
There are a wide variety of preemptive wait types. If you see consistent high value in the Preemptive wait types, I strongly suggest that you look into the wait type and try to know the root cause.
If you are still not sure, you can send me an email or leave a comment about it and I will do my best to help you reduce this wait type.
Read all the post in the Wait Types and Queue series.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
26 Comments. Leave new
Hi Dave,
I am seeing on quite some new SQL 2008 R1 clusters the following wait typ;
wait_type wait_time_s pct running_pct
PREEMPTIVE_OS_AUTHENTICATIONOPS 7528.53 11.56 35.52
The servers are hosting the sharepoint databases of our enterprise. I have no clue how to debug this further.
Nice blog you have!
Hi Dave,
Your book SQL server Waits stats Joes 2 pros, is a very good book I really liked it
I have a small question, as you said Sql server by default is non- premeptive mode(Co operative mode)
I executed a query in one session which suppose to take some time to execute
in an another session I queried SYS.SYSPROCESSSES with spid as current session and previous session
What I found was for previous session the wait type was Preemptive , and status was runnable, which should be non preemptive and runnable according to our disscussion note (query in the previous session was still executing)
I am using SQL server 2008
can you please explain sir
Thank you
Hi Dave,
I am having problems with distributed querys and i only seeing the following wait type;;
PREEMPTIVE_OS_AUTHENTICATIONOPS
This type wait is related to MS DTC ????????
PREEMPTIVE_OS_AUTHENTICATIONOPS indicates a wait for authentication from one of the Domain Controllers
.
Hello, I’m currently receiving the PREEMPTIVE_OS_AUTHENTICATIONOPS error as well. We know the cause is due to a Domain Server restart. However, the process never stops and eventually consumes the TempDB and causes SQL Server to hault. the transaction is being ran by the SQLAgent – Job Manager by the service account, and is checking the sp_sqlagent_has_Server_Access. I cannot rollback/kill this transaction. Only success has been to restart the SQL Services. This has only been seen in our Dev environment, but today I’m seeing it in Production. Any further suggestions?
Hi Carman… Ive got a very similar issue after trying to diable a server audit. did you get anyware with this…??
Thanks, Mark.
Hi Mark, so far no further updates. It’s happened a second time today, and we will be having a all hands on deck (WinTel, DB and Network team) to go through all possibilities. dug a little more and found the xp_logininfo is the last call from the hung session. https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008/ms190369(v=sql.100) leads us back to the DC/s restarting.
How about “PREEMPTIVE_XE_DISPATCHER”
wait_type sum_wait_time_ms pct_wait_time sum_waiting_tasks avg_wait_time_ms
———————————————————— ——————–
PREEMPTIVE_XE_DISPATCHER 657556625 39.1 4061 161919.9
PREEMPTIVE_XE_CALLBACKEXECUTE which appear to be associated with SQL Sever Auditing, while the OS is writing the audit file, in the case where you have select file type “FILE”. So in this condition, are these idle waits while SQLserver waits for the physical file to be written or is the SQL system halted while the OS files are being written?
Hi Dave,
I’m seeing a lot of the following wait stats for one of my production servers: “PREEMPTIVE_XE_SESSIONCOMMIT”…
The only thing different I’m running on this server is DB_Mirroring (SYNCHRONOUS MODE)…Would this have anything to do with this wait stat?
XE is for eXtended Events. Are you using them?
Yes I am using extended Events
Any idea on PREEMPTIVE_OS_WAITFORSINGLEOBJECT
I am seeing an increase in preemptive_com_getdata with a join to a Sybase server. Threre is also high IO on a ‘worktable’ which, to me indicates large data being transferred back. any ideas where to look or research
There is nothing officially in Books Online regarding waittype ‘PREEMPTIVE_COM_GETDATA’
According to above thread the PREEMPTIVE_COM_GETDATA shows we are waiting on something outside of SQL Server’s scheduler
“The PREEMPTIVE wait types give an indication of everything outside of SQL Server’s scheduler. So this wait type means that SQL Server is waiting for something outside of it’s control, in your case most probably the OLEDB data stream from your oracle provider.”
“Even better to use remote stored procedures to force processing to the right server and minimize communications between remote and local servers.”
related link:
Hi Panel:
I can see the preemptive_os_authenticationops
no.of Wait 15894
Wait Time(Sec) 4.62
%WaitTime 86.31
Max Wait Time(ms) 16
Avg Wait Time (ms) 0.3
Can you please help how to reduce this wait.
Amir Ali
Hi Pinal,
I am running a restore command and it’s stuck at xp_cmdshell for more than 3 hours , waittype preemptive_os_pipeops. Tried to kill the session but it’s hung and seem like not doing anything.
Hi Pinal Dave
We are receiving following Preemptive delays
PREEMPTIVE_OS_CRYPTIMPORTKEY
PREEMPTIVE_COM_GETDATA
can you help me how we reduce this type of wait
Is there any encryption method you are using?
No any more encryption method are used
Hi Pinal Dave, I have PREEMPTIVE_XE_DISPATCHER of 89% wait % utilizing this query:
SELECT wait_type,
wait_time_ms / 1000.0 AS wait_time_sec,
(wait_time_ms – signal_wait_time_ms) / 1000.0 AS resource_sec,
signal_wait_time_ms / 1000.0 AS signal_sec,
waiting_tasks_count,
100.0 * wait_time_ms / SUM(wait_time_ms) OVER() AS wait_pct,
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS row_num
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
N’CLR_SEMAPHORE’, N’LAZYWRITER_SLEEP’,
N’RESOURCE_QUEUE’, N’SQLTRACE_BUFFER_FLUSH’,
N’SLEEP_TASK’, N’SLEEP_SYSTEMTASK’,
N’WAITFOR’, N’HADR_FILESTREAM_IOMGR_IOCOMPLETION’,
N’CHECKPOINT_QUEUE’, N’REQUEST_FOR_DEADLOCK_SEARCH’,
N’XE_TIMER_EVENT’, N’XE_DISPATCHER_JOIN’,
N’LOGMGR_QUEUE’, N’FT_IFTS_SCHEDULER_IDLE_WAIT’,
N’BROKER_TASK_STOP’, N’CLR_MANUAL_EVENT’,
N’CLR_AUTO_EVENT’, N’DISPATCHER_QUEUE_SEMAPHORE’,
N’TRACEWRITE’, N’XE_DISPATCHER_WAIT’,
N’BROKER_TO_FLUSH’, N’BROKER_EVENTHANDLER’,
N’FT_IFTSHC_MUTEX’, N’SQLTRACE_INCREMENTAL_FLUSH_SLEEP’,
N’DIRTY_PAGE_POLL’, N’SP_SERVER_DIAGNOSTICS_SLEEP’,
N’BROKER_RECEIVE_WAITFOR’, N’DBMIRROR_EVENTS_QUEUE’,
N’DBMIRRORING_CMD’, N’DBMIRROR_DBM_EVENT’,
N’ONDEMAND_TASK_QUEUE’)
How do I find the external processes responsible for this? This SQL Server host 2 databases used for Blackberry communications.
Hi Pinal,
What and all we have check when we see PREEMPTIVE_COM_GETDATA wait type in SQL Server? We are see this wait type frequently as we are running data from Oracle to SQL Through SSRS.
Please suggest on this.
i am getting this “XTP_PREEMPTIVE_TASK”
I am getting PREEMPTIVE_OS_WAITFORSINGLEOBJECT
Hi Pinal!
Thank you for that great article. You mentioned that preemptive waits have to be investigated. But what about non-preemptive?
I’m receiving time-to-time same messages, which differ by the scheduler id only
“Long Sync IO: Scheduler 18 had 1 Sync IOs in nonpreemptive mode longer than 1000 ms”
Could you advise me on the way to get the task that was applied to that scheduler? I’ve tried catching waits with XE but haven’t succeeded with it.
Thank you in advance.