Sql-server – SQL Server CLR procedures and SQLCLR_QUANTUM_PUNISHMENT

sql serversql server 2014sql-clrwait-types

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.

Best Answer

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.

Related Question