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.
Theme design by Jelle Druyts
Pick a theme: BlogXP sqlx BlogXP sqlx
Powered by: newtelligence dasBlog 2.0.7226.0
The opinions expressed herein are my own personal opinions and do not represent my employer's view in any way.
© Copyright 2008, Bob Beauchemin
E-mail