This post has been republished via RSS; it originally appeared at: SQL Server Support articles.
First published on MSDN on Apr 24, 2013
First, please do not try this on your production server, because it will actually work. Second, this example is to illustrate how Deadlock scheduler issues can and do occur with the goal of increasing your understanding of the problem. Over the years of working with SQL Server, I learned that to understand a technology well one should solve issues that arise using it. Better yet, one of the best ways to learn in depth is to be able to recreating those issues.
For more in-depth discussion on Deadlock Schedulers please see The Tao of the Deadlocked Scheduler in SQL Server .
How to cause a Deadlock Scheduler problem in 2 steps
You will need any tool that can quickly and easily create a stress-test type of workload against the SQL Server. I personally like Ostress.exe that comes with RML Utilities .
1. Create a long-running transaction. Log into SSMS and run the following sequence of statements.
create database DeadlockSchedulers
go
use DeadlockSchedulers
go
create table DS(c1 int, c2 int)
insert into DS values (1, 1),(2,2)
go
begin tran
update DS set c1 = 99
2. Open a Command Prompt and do the following (I am using x64 RML utilities)
cd "Program Files\Microsoft Corporation\RMLUtils"
ostress -SmyServer\sql2008r2 -E -dDeadlockSchedulers -Q"select c1 from DS" -n1000
Detecting the Problem:
Go pour yourself a cup of green tea, meditate for a couple of minutes and then open your Errorlog file (e.g. C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Log\Errorlog). You will surely find something like this in there
2013-04-24 11:13:13.99 Server * *******************************************************************************
2013-04-24 11:13:13.99 Server *
2013-04-24 11:13:13.99 Server * BEGIN STACK DUMP:
2013-04-24 11:13:13.99 Server * 04/24/13 11:13:13 spid 3056
2013-04-24 11:13:13.99 Server *
2013-04-24 11:13:13.99 Server * Deadlocked Schedulers
2013-04-24 11:13:13.99 Server *
2013-04-24 11:13:13.99 Server * *******************************************************************************
2013-04-24 11:13:13.99 Server * -------------------------------------------------------------------------------
2013-04-24 11:13:13.99 Server * Short Stack Dump
2013-04-24 11:13:14.10 Server Stack Signature for the dump is 0x0000000000000150
2013-04-24 11:13:18.83 Server External dump process return code 0x20000001.
External dump process returned no errors.
2013-04-24 11:13:18.83 Server New queries assigned to process on Node 0 have not been picked up by a worker thread in the last 60 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: 97%.
- If you do not feel like green tea or meditation, alternatively you can try to make a new connection to SQL Server. If you did this right, you will find that no new connection can be made.
- If you really feel adventurous and would like to follow the troubleshooting methodology from The Tao of the Deadlocked Scheduler in SQL Server , then you can connect using DAC and run some queries (see Troubleshooting Deadlocked Schedulers using the DAC connection section in the same blog)
sqlcmd -SmyServer\sql2008r2 -E –A
- Finally, if you want to “resolve the problem”, go back to your original connection in SSMS, type ROLLBACK or COMMIT and execute that statement
Discussion: Basically, what I did was create a long running transaction on one connection. Then using Ostress, I launched 1000 threads (-n1000) to make sure I exhaust the SQL Server worker thread pool executed a simple query that contends for the same table. In essence I created a huge blocking chain, which ran SQL Server out of worker threads and thus no further work could be processed. The result: deadlocked schedulers.
Namaste!
Joseph