SQL Server Execution Model

SQL Server Execution Model

Inside the Database Engine, the SQLOS will schedule tasks to worker threads to be executed by the CPU. This is called non-preemptive or cooperative thread scheduling in where a thread will yield access to the CPU if it has either reached its quantum limit (4 milliseconds) or is waiting on a resource other than CPU access.

Let’s start with a task that needs to run a simple SELECT statement. When that query is executed it is placed in the Runnable List queue with a status of Runnable. This is a queue of all threads that are waiting for the CPU and is organized in a First In, First Out (FIFO) order. Specifically, the first task that was placed in the queue will have the first priority to access the CPU.

Once the CPU is available, the thread is given access to process its task and will have a Running status. If it completes within the 4ms quantum then the task is complete and the thread is returned back to SQLOS to be scheduled for other tasks. If it takes longer then 4ms the task returns to the Runnable List to wait for its next turn to access the processor.

This is true unless the task needs to wait for access to a resource other than the CPU, for example, a table the task is trying to access has been locked by another transaction. In this case, the task is placed in the Waiter List queue, in no specific order, and given a status of Suspended. The task will remain in this state until the requested resource is available. Once the resource is available, the task will be placed back in the Runnable List queue to again wait its turn for the CPU.

There are many reasons why a task would be placed in the Waiter List queue. Aaron Bertrand has a list of the Top Ten Wait Types and how you should handle them. If you would like to see ALL the wait types, I would suggest the SQL Skills Wait Types Library maintained by Paul Randal and staff. For further reading, check out the SQL Performance Tuning: Using Wait Statistics Guide.

Be the first to comment on "SQL Server Execution Model"

Leave a comment

Your email address will not be published.


*


This site uses Akismet to reduce spam. Learn how your comment data is processed.