CLR TVFs Are Not Gracefully Handled During AppDomain Recycles

CLR TVFs Are Not Gracefully Handled During AppDomain Recycles by Adam Machanic

Closed

as Won't Fix Help for as Won't Fix

Type:	Bug
ID:	765930
Opened:	10/3/2012 8:41:29 AM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

When an AppDomain recycle occurs, most CLR module types are able to gracefully handle the situation: in-flight requests continue on the old AppDomain, new requests go to the new AppDomain, and eventually when all of the old requests are complete the old AppDomain is disposed of. This creates a more reliable and robust environment and keeps end-users from seeing exceptions when the server needs to do some housecleaning.

Unfortunately, this graceful handling is not done for TVFs. If a table-valued function is running and a recycle occurs, the function will immediately be aborted. Even worse, it will send the user a confusing and very misleading exception:

Msg 10316, Level 16, State 1, Line 2
The app domain with specified version id (4) was unloaded due to memory pressure and could not be found.

CLR TVFs are, in my experience, both the most useful -- by far -- of the available SQLCLR module types, and are probably also the most frequently used. Putting such functionality into a less robust, less reliable bucket is a major disservice to SQL Server developers and end users of SQL Server based solutions.

Details (expand)

English

SQL Server 2012 - Enterprise Edition

SQL Engine

Not Applicable

First create the objects in the attached .SQL file.

Next, run the following query in the database clr_abort:
---
select *
from master..spt_values as a cross join master..spt_values as b
cross apply dbo.abort_tvf(a.name) as h
where a.name is not null
---

As soon as that query begins returning data, move to a new window and do:
---
DBCC FREESYSTEMCACHE('all')
---

--

For a counter-test, using a different type of CLR module, consider this query:

---
select
*,
dbo.no_abort_udf(a.name)
from master..spt_values as a cross join master..spt_values as b
where a.name is not null
---

Running DBCC FREESYSTEMCACHE('all') as many times as you'd like in another window will not abort this query, despite the fact that it's doing effectively the same work as the other.

I'll also attach a .cs file to this item containing the C# code for these modules.

Note that DBCC FREESYSTEMCACHE is not the only way to repro an AppDomain recycle. These things can also occur, e.g., due to some unsafe code holding a lock, and an Attention event occurring due to a client timeout. So these are not always cases that we can easily control, and the inability to prevent them is a serious problem.

In the case of the TVF:

Msg 10316, Level 16, State 1, Line 2
The app domain with specified version id (4) was unloaded due to memory pressure and could not be found.

In the case of the UDF, the query continues to run.

All SQLCLR modules should behave like the UDF: continue to run, not abort, and not give the user a confusing and misleading exception!

X64

File Name	Submitted By	Submitted On	File Size
tvf_abort.sql	Adam Machanic	10/3/2012	9 KB
abort_functions.cs	Adam Machanic	10/3/2012	773 bytes

Hello Adam,
Thanks for reporting the issue. We looked at this and behavior has existed in previous versions of SQL Server. And given the other high priority requests in our pipeline, fixing this issue is not a priority for us at the moment. I understand the pain and hopefully we can look at it in the future if our business priorities change.

--
Umachandar, SQL Programmability Team