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 (http://blog.sqlauthority.com)

About these ads

10 thoughts on “SQL SERVER – PREEMPTIVE and Non-PREEMPTIVE – Wait Type – Day 19 of 28

  1. 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!

  2. 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

  3. 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 ????????

  4. 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?

  5. Pingback: SQL SERVER – Weekly Series – Memory Lane – #017 | SQL Server Journey with SQL Authority

  6. Pingback: SQL SERVER – Wait Stats – Wait Types – Wait Queues – Day 0 of 28 | Journey to SQL Authority with Pinal Dave

  7. Pingback: SQL SERVER – 28 Links for Learning SQL Wait Stats from Beginning | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s