Recently a client asked me during Comprehensive Database Performance Health Check what does it mean to see more preemptive waits on their system. Let us learn about it today.
I have previously in a detailed blog about SQL SERVER – PREEMPTIVE and Non-PREEMPTIVE – Wait Type – Day 19 of 28. I strongly recommend that you read it first.
The definition of preemptive and non-preemptive is as following.
PREEMPTIVE waits: 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 that makes SQL Server yield. This kind of wait can reduce the performance drastically and needs to be investigated properly.
Non-PREEMPTIVE waits: 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.
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 posts in the Wait Types and Queue series. You can also reach out to me on Twitter for further questions.
Reference: Pinal Dave (https://blog.sqlauthority.com)