Dynamic management views and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance. The following Dynamic management views help provide insight into SQL CLR objects, assemblies and AppDomains within a given database context.
---
title: "SQL Server CLR Related Dynamic Management Views"
subtitle: "Applies to SQL Server (starting with 2012)"
author: Microsoft
date: January 4, 2019
source: https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/common-language-runtime-related-dynamic-management-views-transact-sql?view=sql-server-2017
notoc: false
---
## sys.dm_clr_appdomains
There are several types of CLR integration managed database objects. 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**
per-database (if a user registers CLR database objects in different databases,
the CLR database objects will run in different application domains). 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](https://go.microsoft.com/fwlink/p/?LinkId=299658).
| 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)** | A descriptor for the current state of the **AppDomain**. An AppDomain can be in different states from creation to deletion. 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**. |
| **total_processor_time_ms** | **bigint** | Total processor time, in milliseconds, used by all threads while executing in the current application domain since the process started. This is equivalent to **System.AppDomain.MonitoringTotalProcessorTime**. |
| **total_allocated_memory_kb** | **bigint** | Total size, in kilobytes, of all memory allocations that have been made by the application domain since it was created, without subtracting memory that has been collected. This is equivalent to **System.AppDomain.MonitoringTotalAllocatedMemorySize**. |
| **survived_memory_kb** | **bigint** | Number of kilobytes that survived the last full, blocking collection and that are known to be referenced by the current application domain. This is equivalent to **System.AppDomain.MonitoringSurvivedMemorySize**. |
### 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 in DDL operations. These differ from E_APPDOMAIN_SHARED in that shared AppDomains are used for CLR integration executions as opposed to DDL operations. Such AppDomains are isolated from other concurrent 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:
```sql
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**:
```sql
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
);
```
> *Source: [sys.dm_clr_appdomains \(Transact-SQL\)](https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-clr-appdomains-transact-sql?view=sql-server-2017)*
## sys.dm_clr_properties
Returns a row for each property related to SQL Server common language runtime
(CLR) integration, including the version and state of the hosted CLR. The hosted
CLR is initialized by running the `CREATE ASSEMBLY`, `ALTER ASSEMBLY`, or `DROP ASSEMBLY`
statements, or by executing any CLR routine, type, or trigger.
The `sys.dm_clr_properties` view does not specify whether execution of user CLR
code has been enabled on the server. Execution of user CLR code is enabled by
using the [sp_configure](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-configure-transact-sql?view=sql-server-2017)
stored procedure with the clr enabled option set to `1`.
The `sys.dm_clr_properties` view contains the `name` and `value` columns. Each
row in this view provides details about a property of the hosted CLR. Use this
view to gather information about the hosted CLR, such as the CLR install
directory, the CLR version, and the current state of the hosted CLR. This view
can help you determine if the CLR integration code is not working because of
problems with the CLR installation on the server computer.
| Column name | Data type | Description |
|-------------|-------------------|---------------------------|
| **name** | **nvarchar(128)** | The name of the property. |
| **value** | **nvarchar(128)** | Value of the property. |
### Properties
The **directory** property indicates the directory that the .NET Framework was
installed to on the server. There could be multiple installations of .NET
Framework on the server computer and the value of this property identifies which
installation SQL Server is using.
The **version** property indicates the version of the .NET Framework and hosted
CLR on the server.
The **sys.dm_clr_properties** dynamic managed view can return six different
values for the **state** property, which reflects the state of the SQL Server
hosted CLR. They are:
- **Mscoree is and is not loaded** --- The *Mscoree is not loaded* and *Mscoree
is loaded* states show the progression of the hosted CLR initialization on
server startup, and are not likely to be seen.
- **Locked CLR version with mscoree** --- The Locked CLR version with mscoree
state may be seen where the hosted CLR is not being used and, thus, it has not
yet been initialized. The hosted CLR is initialized the first time a DDL
statement (such as [CREATE ASSEMBLY \(Transact-SQL\)](https://docs.microsoft.com/en-us/sql/t-sql/statements/create-assembly-transact-sql?view=sql-server-2017)\)
or a managed database object is executed.
- **CLR is initialized** --- The CLR is initialized state indicates that the
hosted CLR was successfully initialized. Note that this does not indicate
whether execution of user CLR code was enabled. If the execution of user CLR
code is first enabled and then disabled using the Transact-SQL `sp_configure`
stored procedure, the state value will still be CLR is initialized
- **CLR initialization permanently failed** --- The CLR initialization
permanently failed state indicates that hosted CLR initialization failed.
Memory pressure is a likely cause, or it could also be the result of a failure
in the hosting handshake between SQL Server and the CLR. Error message 6512 or
6513 will be thrown in such a case.
- **CLR is stopped** --- The CLR is stopped state is only seen when SQL Server
is in the process of shutting down.
### Remarks
The properties and values of this view might change in a future version of SQL
Server due to enhancements of the CLR integration functionality.
### Permissions
- SQL Server requires `VIEW SERVER STATE` permission.
- SQL Database requires the `VIEW DATABASE STATE` permission in the database.
### Examples
The following example retrieves information about the hosted CLR:
```sql
select name, value
from sys.dm_clr_properties;
```
> *Source: [sys.dm_clr_properties \(Transact-SQL\)](https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-clr-properties-transact-sql?view=sql-server-2017)*
## sys.dm_clr_loaded_assemblies
Returns a row for each managed user assembly loaded into the server address
space. Use this view to understand and troubleshoot CLR integration managed
database objects that are executing in Microsoft SQL Server.
Assemblies are managed code DLL files that are used to define and deploy managed
database objects in SQL Server. Whenever a user executes one of these managed
database objects, SQL Server and the CLR load the assembly (and its references)
in which the managed database object is defined. The assembly remains loaded in
SQL Server to increase performance, so that the managed database objects
contained in the assembly can be called in the future with out having to reload
the assembly. The assembly is not unloaded until SQL Server comes under memory
pressure.
| Column name | Data type | Description |
|-----------------------|--------------|-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| **assembly_id** | **int** | ID of the loaded assembly. The **assembly\_id** can be used to look up more information about the assembly in the `sys.assemblies` (Transact-SQL) catalog view. Note that the Transact-SQL `sys.assemblies` catalog shows assemblies in the current database only. The `sqs.dm_clr_loaded_assemblies` view shows all loaded assemblies on the server. |
| **appdomain_address** | **int** | Address of the application domain (**AppDomain**) in which the assembly is loaded. All the assemblies owned by a single user are always loaded in the same **AppDomain**. The **appdomain\_address** can be used to lookup more information about the **AppDomain** in the `sys.dm_clr_appdomains` view. |
| **load_time** | **datetime** | Time when the assembly was loaded. Note that the assembly remains loaded until SQL Server is under memory pressure and unloads the **AppDomain**. You can monitor **load\_time** to understand how frequently SQL Server comes under memory pressure and unloads the **AppDomain**. |
### Permissions
Requires `VIEW SERVER STATE` permission on the server.
### Remarks
The **dm_clr_loaded_assemblies.appdomain_address** view has a many-to-one
relationship with **dm_clr_appdomains.appdomain_address**. The
**dm_clr_loaded_assemblies.assembly_id** view has a one-to-many relationship
with **sys.assemblies.assembly_id**.
### Examples
The following example shows how to view details of all assemblies in the current
database that are currently loaded.
```sql
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;
```
The following example shows how to view details of the **AppDomain** in which a
given assembly is loaded.
```sql
select appdomain_id,
creation_time,
db_id,
user_id, state
from sys.dm_clr_appdomains as a
where appdomain_address = (
select appdomain_address
from sys.dm_clr_loaded_assemblies
where assembly_id = 555
);
```
> *Source [sys.dm_clr_loaded_assemblies \(Transact-SQL\)](https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-clr-loaded-assemblies-transact-sql?view=sql-server-2017)*
## sys.dm_clr_tasks
Returns a row for all common language runtime (CLR) tasks that are currently
running. A Transact-SQL batch that contains a reference to a CLR routine creates
a separate task for execution of all the managed code in that batch. Multiple
statements in the batch that require managed code execution use the same CLR
task. The CLR task is responsible for maintaining objects and state pertaining
to managed code execution, as well as the transitions between the instance of
SQL Server and the common language runtime.
| Column name | Data type | Description |
|------------------------|-------------------|---------------------------------------------------------------------------------------------------------------------|
| **task_address** | **varbinary(8)** | Address of the CLR task. |
| **sos_task_address** | **varbinary(8)** | Address of the underlying Transact-SQL batch task. |
| **appdomain_address** | **varbinary(8)** | Address of the application domain in which this task is running. |
| **state** | **nvarchar(128)** | Current state of the task. |
| **abort_state** | **nvarchar(128)** | State the abort is currently in (if the task was canceled) There are multiple states involved while aborting tasks. |
| **type** | **nvarchar(128)** | Task type. |
| **affinity_count** | **int** | Affinity of the task. |
| **forced_yield_count** | **int** | Number of times the task was forced to yield. |
### Permissions
- SQL Server requires `VIEW SERVER STATE` permission.
- SQL Database requires the `VIEW DATABASE STATE` permission in the database.
> *Source: [sys.dm_clr_tasks \(Transact-SQL\)](https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-clr-tasks-transact-sql?view=sql-server-2017)*