Perhaps you’ve seen the following note in BOL advising SQLCLR users to explicitly yield in their long-running CLR functions:
“Long-running managed code that accesses data or allocates enough memory to trigger garbage collection will yield automatically. Long-running managed code that does not access data or allocate enough managed memory to trigger garbage collection should explicitly yield by calling System.Thread.Sleep() function of the .NET Framework.”
I’m not sure about you, but reading notes like this brings out the worst in me. Why should I listen to BOL’s recommendation to yield in my code if I don’t want to? Surely my code is more important than any other code running on the system? If I want maximum performance out of my code (and only my code matters) why would I voluntarily sleep and give up the rest of my quantum to someone else?
Sure, BOL claims that the SQL Server scheduler can detect runaway threads and suspend them, but does this really work? Is it effective enough to convince otherwise greedy developers like me that sharing and playing well with others is a better approach to life?
To test this, I came up with some quick CLR code that spawns a bunch of threads to do nothing but count to a big number in a loop. A parameter flag specifies whether the function would try to be greedy and never yield, or cooperative and call sleep(0) periodically in the outer loop. Note that this code needs to run in UNSAFE permission set because of its use of threads, but that’s ok since this is just for demonstration purposes and isn’t something you would ever run in production.
public partial class GreedyOrCoop
public static double StartCounting(bool isGreedy, int numThreads, int countTo)
DateTime startTime = DateTime.Now;
Thread threadPool = new Thread[numThreads];
Worker workerCount = new Worker(isGreedy, countTo);
for (int i = 0; i < numThreads; i++)
threadPool[i] = new Thread(new ThreadStart(workerCount.Count));
for(int i = 0; i < numThreads; i++)
return (DateTime.Now – startTime).TotalMilliseconds;
public class Worker
public Worker(bool IsGreedy, int CountTo)
isGreedy = IsGreedy;
countTo = CountTo;
public void Count()
for (int j = 0; j < 10; j++)
int i = 0;
while (i < countTo)
I wanted the code to run on multiple threads at the same time to ensure there was some contention taking place. I tweaked the numbers I used a little, but settled on using 10 threads that counted to 500 million 10 times per thread because on my system that would take about 3 minutes to run, which is the perfect amount of time to get a cup of coffee.select dbo.StartCounting(1, 10, 500000000)
select dbo.StartCounting(0, 10, 500000000)
And the results? Averaged over 100 iterations, the Cooperative version beat the Greedy version by over 4 seconds, 84.31 seconds vs. 88.79 seconds. Maybe it’s time for me to pay more attention to BOL’s recommendations.
Tomorrow, I’ll talk about some DMVs that are useful to monitor this behavior and how to tell if your threads are being forcible preempted by the SQL Server Scheduler.