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.

Improve this question
2
  • 5
    I can recommend you watch Data, Faster: Microsoft SQL Server Performance Techniques with SQLCLR by Adam Machanic. Commented Feb 20, 2017 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. Commented Feb 20, 2017 at 15:43

1 Answer 1

Reset to default
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.

Improve this answer
4
  • 1
    I second that advice: Add Thread.BeginThreadAffinity / Thread.EndThreadAffinity to you CLR code entry/exit points. Commented Feb 20, 2017 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. Commented Feb 20, 2017 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. Commented Feb 20, 2017 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. Commented Feb 20, 2017 at 15:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.