# SQL Server CLR procedures and SQLCLR_QUANTUM_PUNISHMENT

I have a CLR stored procedure running on SQL Server 2014. This stored procedure gathers some data (generally numbering in the tens-of-thousands of rows range), runs some computations on the data, generating an output dataset, and writes that output dataset to a couple of destination tables. For small runs, this takes a few seconds. For our larger runs, it's currently taking about 25 minutes. During that 25 minutes, we have observed a significant number of SQLCLR_QUANTUM_PUNISHMENT waits. So I'm trying to educate myself on what we can do to reduce the occurrence of these waits. I've found plenty of resources that tell me, "Occurs when a CLR task is throttled because it has exceeded its execution quantum. This throttling is done in order to reduce the effect of this resource-intensive task on other tasks." And I've read that SQL Server's execution quantum is 4ms. So am I to assume that any time a CLR stored procedure or function takes more that 4ms of CPU time, this wait type will occur?

Also, I found a reference indicating that some CLR procedures should be yielding. Now, our CLR procedure is single-threaded. I can certainly add some sleep calls at key places and see what kind of affect that has. But I would like to have a better understanding of what's going on. So if anyone has some good knowledge to share or has a link to a good document/article, I would appreciate it. Finding information about this wait type beyond that BOL quote has been a bit difficult.

• I can recommend you watch Data, Faster: Microsoft SQL Server Performance Techniques with SQLCLR by Adam Machanic. – Paul White Feb 20 '17 at 14:04
• That was an excellent video! thank you very much for sharing it. It not only answered this question, but gave me some excellent pointers for another issue I was thinking about... how to successfully manipulate the CLR procedure to make user of multiple threads. – DeadZone Feb 20 '17 at 15:43

Unfortunately there is not a lot of information out there on this topic. I can say that yes, you certainly should add the Thread.Sleep(0); to various places in your code. Doing so gives SQL Server an opportunity to control the thread. This is not necessary in places that are doing data access (i.e. SqlClient stuff) as SQL Server already has the ability to manage that.
Also, @Remus Rusanu, in another question (perhaps on Stack Overflow) had recommended using Thread.BeginThreadAffinity / Thread.EndThreadAffinity to manage long-running processes, but using those requires that the Assembly be set to UNSAFE.
• I second that advice: Add Thread.BeginThreadAffinity / Thread.EndThreadAffinity to you CLR code entry/exit points. – Remus Rusanu Feb 20 '17 at 14:43
• @RemusRusanu To all or just long-running? Does the fact that it requires UNSAFE reduce some of the benefit that it provides? And lastly, why exactly does it help? The MSDN documentation makes it sound like it mostly does not apply. – Solomon Rutzky Feb 20 '17 at 15:10