sys.dm_clr_appdomains
Returns a row for each application domain in the server. Application domain (AppDomain) is a construct in the Microsoft .NET Framework common language runtime (CLR) that is the unit of isolation for an application. You can use this view to understand and troubleshoot CLR integration objects that are executing in Microsoft SQL Server.
There are several types of CLR integration managed database objects. For general information about these objects, see Building Database Objects with Common Language Runtime (CLR) Integration. Whenever these objects are executed, SQL Server creates an AppDomain under which it can load and execute the required code. The isolation level for an AppDomain is one AppDomain per database per owner. That is, all CLR objects owned by a user are always executed in the same AppDomain. An AppDomain is not destroyed after the code finishes execution. Instead, it is cached in memory for future executions. This improves performance.
For more information, see Application Domains.
Column name | Data type | Description |
---|---|---|
appdomain_address |
varbinary(8) |
Address of the AppDomain. All managed database objects owned by a user are always loaded in the same AppDomain. You can use this column to look up all the assemblies currently loaded in this AppDomain in sys.dm_clr_loaded_assemblies. |
appdomain_id |
int |
ID of the AppDomain. Each AppDomain has a unique ID. |
appdomain_name |
varchar(386) |
Name of the AppDomain as assigned by SQL Server. |
creation_time |
datetime |
Time when the AppDomain was created. Because AppDomains are cached and reused for better performance, creation_time is not necessarily the time when the code was executed. |
db_id |
int |
ID of the database in which this AppDomain was created. Code stored in two different databases cannot share one AppDomain. |
user_id |
int |
ID of the user whose objects can execute in this AppDomain. |
state |
nvarchar(128) |
Current state of the AppDomain. See the Remarks section of this topic for more information. |
strong_refcount |
int |
Number of strong references to this AppDomain. This reflects the number of currently executing batches that use this AppDomain. Note that execution of this view will create a strong refcount; even if is no code currently executing, strong_refcount will have a value of 1. |
weak_refcount |
int |
Number of weak references to this AppDomain. This indicates how many objects inside the AppDomain are cached. When you execute a managed database object, SQL Server caches it inside the AppDomain for future reuse. This improves performance. |
cost |
int |
Cost of the AppDomain. The higher the cost, the more likely this AppDomain is to be unloaded under memory pressure. Cost usually depends on how much memory is required to re-create this AppDomain. |
value |
int |
Value of the AppDomain. The lower the value, the more likely this AppDomain is to be unloaded under memory pressure. Value usually depends on how many connections or batches are using this AppDomain. |
Remarks
There is a one-to-may relationship between dm_clr_appdomains.appdomain_address and dm_clr_loaded_assemblies.appdomain_address.
The following tables list possible state values, their descriptions, and when they occur in the AppDomain lifecycle. You can use this information to follow the lifecyle of an AppDomain and to watch for suspicious or repetitive AppDomain instances unloading, without having to parse the Windows Event Log.
AppDomain Initialization
State | Description |
---|---|
E_APPDOMAIN_CREATING |
The AppDomain is being created. |
AppDomain Usage
State | Description |
---|---|
E_APPDOMAIN_SHARED |
The runtime AppDomain is ready for use by multiple users. |
E_APPDOMAIN_SINGLEUSER |
The AppDomain is ready for use by a single user to perform DDL operations. |
E_APPDOMAIN_DOOMED |
The AppDomain is scheduled to be unloaded, but there are currently threads executing in it. |
AppDomain Cleanup
State | Description |
---|---|
E_APPDOMAIN_UNLOADING |
SQL Server has requested that the CLR unload the AppDomain, usually because the assembly that contains the managed database objects has been altered or dropped. |
E_APPDOMAIN_UNLOADED |
The CLR has unloaded the AppDomain. This is usually the result of an escalation procedure due to ThreadAbort, OutOfMemory, or an unhandled exception in user code. |
E_APPDOMAIN_ENQUEUE_DESTROY |
The AppDomain has been unloaded in CLR and set to be destroyed by SQL Server. |
E_APPDOMAIN_DESTROY |
The AppDomain is in the process of being destroyed by SQL Server. |
E_APPDOMAIN_ZOMBIE |
The AppDomain has been destroyed by SQL Server; however, not all of the references to the AppDomain have been cleaned up. |
Permissions
Requires VIEW SERVER STATE permission on the database.
Examples
The following example shows how to view the details of an AppDomain for a given assembly:
select appdomain_id, creation_time, db_id, user_id, state
from sys.dm_clr_appdomains a
where appdomain_address =
(select appdomain_address
from sys.dm_clr_loaded_assemblies
where assembly_id = 500)
The following example shows how to view all assemblies in a given AppDomain:
select a.name, a.assembly_id, a.permission_set_desc, a.is_visible, a.create_date, l.load_time
from sys.dm_clr_loaded_assemblies as l
inner join sys.assemblies as a
on l.assembly_id = a.assembly_id
where l.appdomain_address =
(select appdomain_address
from sys.dm_clr_appdomains
where appdomain_id = 15)
See Also
Reference
sys.dm_clr_loaded_assemblies
Dynamic Management Views and Functions
Common Language Runtime Related Dynamic Management Views
Help and Information
Getting SQL Server 2005 Assistance
Change History
Release | History |
---|---|
12 December 2006 |
|