SQL SERVER – PREEMPTIVE and Non-PREEMPTIVE – Wait Type – Day 19 of 28

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)

Previous Post
SQL SERVER – LOGBUFFER – Wait Type – Day 18 of 28
Next Post
SQL SERVER – MSQL_XP – Wait Type – Day 20 of 28

Related Posts

25 Comments. Leave new

  • D4v1d14N_SQL
    March 8, 2011 5:24 pm

    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!

    Reply
  • 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

    Reply
  • 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 ????????

    Reply
  • Prashant Kumar
    October 8, 2012 3:22 pm

    PREEMPTIVE_OS_AUTHENTICATIONOPS indicates a wait for authentication from one of the Domain Controllers
    .

    Reply
  • 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?

    Reply
  • 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

    Reply
  • 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?

    Reply
  • 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?

    Reply
  • Venkata Vadapalli
    February 19, 2016 10:48 pm

    Any idea on PREEMPTIVE_OS_WAITFORSINGLEOBJECT

    Reply
  • 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

    Reply
    • 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:

      Reply
  • 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

    Reply
  • 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.

    Reply
  • 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

    Reply
  • Steve Kirchner
    August 17, 2018 8:03 pm

    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.

    Reply
  • Raghav Reddy
    May 1, 2019 1:05 pm

    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.

    Reply
  • i am getting this “XTP_PREEMPTIVE_TASK”

    Reply
  • I am getting PREEMPTIVE_OS_WAITFORSINGLEOBJECT

    Reply

Leave a Reply

Menu