6

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.

  • 5
    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
4

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.

  • 1
    I second that advice: Add Thread.BeginThreadAffinity / Thread.EndThreadAffinity to you CLR code entry/exit points. – Remus Rusanu Feb 20 '17 at 14:43
  • 1
    @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
  • 1
    The problem is that CLR code is supposed to be participating in the SQL OS cooperative multitasking as a well behaved threadizen. If the code is stealing the thread and is running away without yielding, it prevents other tasks from making progress as the scheduler is never given a chance to switch context. For this, when finally it yields, is punished (as a warning to the developer...). UNSAFE has nothing to do with it, afaik. – Remus Rusanu Feb 20 '17 at 15:17
  • BTA/ETA is making the thread preemptive. When this happens, the scheduler is given away to the next non-preemptive thread and the preemptive thread continues. when this occurs, 2 threads are 'runnable' from underlying OS pov (I almost said NT...) and there may be CPU contention inside SQL process, but that is a separate topic. The CLR code will run until ETA and then it will try to join back the cooperative mode. There is a cost with switching BTA/ETA, so it should be done only for lengthy CLR code. – Remus Rusanu Feb 20 '17 at 15:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.