SQL SERVER – What is Deadlock Scheduler? How to Reproduce it?

If you have come on this page by internet search, then you must have seen below error in the ERRORLOG which is about deadlock scheduler.

2016-07-25 08:50:29.50 Server * *******************************************************************************
2016-07-25 08:50:29.50 Server *
2016-07-25 08:50:29.50 Server * BEGIN STACK DUMP:
2016-07-25 08:50:29.50 Server * 07/25/16 08:50:29 spid 1448
2016-07-25 08:50:29.50 Server *
2016-07-25 08:50:29.50 Server * Deadlocked Schedulers
2016-07-25 08:50:29.50 Server *
2016-07-25 08:50:29.50 Server * *******************************************************************************

Most of the time this error causes hang of SQL Server and restart is the only solution. First, let’s make sure that you are not thinking above message as same what you see in SQL RDBMS. This is NOT same as two processes blocking each other and waiting for each other to release locks.  The Deadlocked of Schedulers applies to SQL OS Schedulers and gives indication that things are not moving on that scheduler.

Here is how you can reproduce it. Basically, we would cause heave blocking in SQL Server after reducing the number of worker threads.  Bold, Red means Danger!

PLEASE DO NOT ATTEMPT THIS ON PRODUCTION SERVER.

Step 1: Reduce Max Worker threads

You can run below in SSMS

sp_configure 'max worker threads', 128
go
reconfigure with override
go
--Confirm the new value i.e. 128
sp_configure 'max worker threads'
go

Step 2: Download and Install RML utilities

Here is the link. We are going to use ostress tool to run the same query via multiple connections.

Step 3: Create Database and Objects

Run below in SSMS.

create database DeadlockSchedulersDemo
go
use DeadlockSchedulersDemo
go
Create Table BlockingTable( i int)
Go

Step 4: Generate error

We need to run below in SSMS. This would leave an open transaction and no one can read data from table in default isolation level (read committed)

begin tran
insert into BlockingTable values (1)

Open command prompt and go to the location where you have installed RML (step 2) and run below. Replace the server name in S parameter

ostress -S. -dDeadlockSchedulersDemo -Q"Select * from BlockingTable" -n200

At this point you would NOT be able to connect to SQL Server via SSMS. You need to wait for some time and soon you would start seeing below in ERRORLOG

2016-07-25 08:50:32.91 Server New queries assigned to process on Node 0 have not been picked up by a worker thread in the last 300 seconds. Blocking or long-running queries can contribute to this condition, and may degrade client response time. Use the “max worker threads” configuration option to increase number of allowable threads, or optimize current running queries. SQL Process Utilization: 0%. System Idle: 98%.

As we can see above, we have exhausted worker threads (remember that we reduced it to 128). The scheduler on Node 0 is not able to make any progress and printing the message in ERRORLOG every 5 minutes (300 seconds increment)

Step 5: Cleanup

You can hit Ctrl + C on ostess command prompt and also run commit transaction in the window where we had performed insert statement under a transaction.

Now, reset the value to zero for worker threads.

sp_configure 'max worker threads', 0
go
reconfigure with override
go
--Confirm the new value i.e. 0
sp_configure 'max worker threads'
go

Did this demo help you in understanding the error about deadlock scheduler? It might take less time to reproduce it.

Reference: Pinal Dave (https://blog.sqlauthority.com)