Database Reference
In-Depth Information
Assembly belongs to one of three different security categories called
permission sets
. You need to specify the
corresponding permission set as part of the
CREATE ASSEMBLY
statement. The categories are as follows:
SAFE
: This code is fully reliable, and it works in-process only. Only a subset of the standard .Net
libraries and classes can be used here. This is the default permission set for the assemblies.
EXTERNAL_ACCESS
: This code can perform some out-of-process calls, which access external
resources, such as the file system, registry, web services, and Windows event log. Similar to
SAFE assemblies, only a subset of .Net libraries and classes can be used. The code is also
guaranteed to be reliable.
UNSAFE
: There are no restrictions in unsafe CLR code. It can do out-of-process calls, utilize
almost all .Net libraries, start its own threads, and perform other actions that can lead to
unreliable code.
When you run CLR code, SQL Server creates separate application domain on a database and assembly-owner
basis. For example, if you have user Mary as the owner of assembly A1 and user Bob as the owner of assemblies A2
and A3, you would have two application domains where CLR code is running—one for Bob's and another for Mary's
assemblies, regardless of how many users are calling CLR routines.
SQL Server can shut down an entire application domain when unhandled exceptions occur. This would affect the
other sessions that are running CLR code. Conditions that can lead to this situation usually occur only with UNSAFE
permission sets, and you need to be extremely careful when dealing with exception handling there.
You can troubleshoot CLR routine performance in a similar manner to that of T-SQL code. Profiler events (and
corresponding Extended Events), such as SQL:Batch Starting, Completed, SP: Starting, Completed, StmtStarting, and
StmtCompleted monitor the execution of both T-SQL and CLR code.
Data management views, such as
sys.dm_exec_query_stats
,
sys.dm_exec_requests
,
and s
ys.dm_os_memory_*
work the same way.
■
Note
We will talk about performance troubleshooting with those DMVs in part 5 of this topic, “practical troubleshooting.”
The performance counter SQL Server:CLR\CLR Execution shows the total time spent in CLR execution.
SQL Server T-SQL threads use cooperative non-preemptive scheduling and yields voluntarily. Managed CLR
threads, on the other hand, use preemptive scheduling and rely on the host to interrupt them. Even if SQL Server
had an ability to detect and interrupt non-yielding threads, run-away CLR code could affect the performance of the
system to a much higher degree than T-SQL. You must avoid such conditions and voluntarily yield from time to time
by calling the
System.Threading.Thread.Sleep(0)
method in the CLR code.
You can identify the sessions that are running non-yielding CLR code with
sys.dm_clr_tasks
, DMV as shown in
Listing 13-1.
Listing 13-1.
Identifying sessions with non-yielding CLR code
select
er.session_id, ct.forced_yield_count,
w.task_address, w.[state], w.last_wait_type, ct.state
from
sys.dm_clr_tasks ct with (nolock) join
sys.dm_os_workers w with (nolock) on
ct.sos_task_address = w.task_address
join sys.dm_exec_requests er with (nolock) on
w.task_address = er.task_address
