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.
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.
ID of the AppDomain. Each AppDomain has a unique ID.
Name of the AppDomain as assigned by SQL Server.
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.
ID of the database in which this AppDomain was created. Code stored in two different databases cannot share one AppDomain.
ID of the user whose objects can execute in this AppDomain.
Current state of the AppDomain. See the Remarks section of this topic for more information.
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.
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 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 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.
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.
The AppDomain is being created.
The runtime AppDomain is ready for use by multiple users.
The AppDomain is ready for use by a single user to perform DDL operations.
The AppDomain is scheduled to be unloaded, but there are currently threads executing in it.
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.
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.
The AppDomain has been unloaded in CLR and set to be destroyed by SQL Server.
The AppDomain is in the process of being destroyed by SQL Server.
The AppDomain has been destroyed by SQL Server; however, not all of the references to the AppDomain have been cleaned up.
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)