Poking at SQLCLR

A common question on the newsgroups is "what will happen if a SQLCLR procedure allocates a huge chunk of memory or enters a tight, endless loop" by mistake? DBAs are concerned about any language with a looping construct (they've likely coded the tight, endless loop in T-SQL to see) or a malloc or equivalent. The BOL suggests any loop should call Sleep(0) but sleeping is not always the same as yielding.

SQL Server 2005 does respond to memory pressure in a concerted manner releasing buffers and ending procedures if needed. So you might see an error similar to this under memory pressure or if you allocate "too much memory":

Msg 6532, Level 16, State 49, Procedure MyProc, Line 0
.NET Framework execution was aborted by escalation policy because of out of memory.

However, SQL Server doesn't consider "being busy" an error condition. Suppose there was a batch process that actually did run for a long time? In the case of the endless loop, if the server has other work to do, SQL Server 2005 will force a CLR thread to yield and "punish" it (timeslice-wise). This is visible using a dynamic management view, sys.dm_clr_tasks, in the field "force_yield_count". When in an endless loop, this field increases, but SQL Server does not kill the task.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.