Common Language Runtime Related Dynamic Management Views

Se more view version maps here: Microsoft Sql System View Version Maps

• sys.dm_clr_appdomains
• sys.dm_clr_loaded_assemblies
• sys.dm_clr_properties
• sys.dm_clr_tasks

sys.dm_clr_appdomains

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) Integration1. 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 Domains2.

Column name	Sql 2005	Sql 2008	Sql 2008 R2	Sql 2012	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.
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.

TSQL

Sql 2005
SELECT [appdomain_address], [appdomain_id], [appdomain_name], [creation_time], [db_id], [user_id], [state], [strong_refcount], [weak_refcount], [cost], [value] FROM sys.dm_clr_appdomains
Sql 2008
SELECT [appdomain_address], [appdomain_id], [appdomain_name], [creation_time], [db_id], [user_id], [state], [strong_refcount], [weak_refcount], [cost], [value] FROM sys.dm_clr_appdomains
Sql 2008 R2
SELECT [appdomain_address], [appdomain_id], [appdomain_name], [creation_time], [db_id], [user_id], [state], [strong_refcount], [weak_refcount], [cost], [value] FROM sys.dm_clr_appdomains
Sql 2012
SELECT [appdomain_address], [appdomain_id], [appdomain_name], [creation_time], [db_id], [user_id], [state], [strong_refcount], [weak_refcount], [cost], [value], [total_processor_time_ms], [total_allocated_memory_kb], [survived_memory_kb] FROM sys.dm_clr_appdomains

Back to Top

---

sys.dm_clr_loaded_assemblies

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. For more information about assemblies and CLR integration, see CLR Hosted Environment1. For more information about managed database objects, see Building Database Objects with Common Language Runtime (CLR) Integration2.

Column name	Sql 2005	Sql 2008	Sql 2008 R2	Sql 2012	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)3 catalog view. Note that the Transact-SQL sys.assemblies3 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_appdomains4 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.

TSQL

Sql 2005
SELECT [assembly_id], [appdomain_address], [load_time] FROM sys.dm_clr_loaded_assemblies
Sql 2008
SELECT [assembly_id], [appdomain_address], [load_time] FROM sys.dm_clr_loaded_assemblies
Sql 2008 R2
SELECT [assembly_id], [appdomain_address], [load_time] FROM sys.dm_clr_loaded_assemblies
Sql 2012
SELECT [assembly_id], [appdomain_address], [load_time] FROM sys.dm_clr_loaded_assemblies

Back to Top

---

sys.dm_clr_properties

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 ASSEMBLY1, ALTER ASSEMBLY2, or DROP ASSEMBLY3 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_configure4 stored procedure with the clr enabled5 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	Sql 2005	Sql 2008	Sql 2008 R2	Sql 2012	Type	Description
name	•	•	•	•	nvarchar(128)	The name of the property.
value	•	•	•	•	nvarchar(128)	Value of the property.

TSQL

Sql 2005
SELECT [name], [value] FROM sys.dm_clr_properties
Sql 2008
SELECT [name], [value] FROM sys.dm_clr_properties
Sql 2008 R2
SELECT [name], [value] FROM sys.dm_clr_properties
Sql 2012
SELECT [name], [value] FROM sys.dm_clr_properties

Back to Top

---

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	Sql 2005	Sql 2008	Sql 2008 R2	Sql 2012	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.

TSQL

Sql 2005
SELECT [task_address], [sos_task_address], [appdomain_address], [state], [abort_state], [type], [affinity_count], [forced_yield_count] FROM sys.dm_clr_tasks
Sql 2008
SELECT [task_address], [sos_task_address], [appdomain_address], [state], [abort_state], [type], [affinity_count], [forced_yield_count] FROM sys.dm_clr_tasks
Sql 2008 R2
SELECT [task_address], [sos_task_address], [appdomain_address], [state], [abort_state], [type], [affinity_count], [forced_yield_count] FROM sys.dm_clr_tasks
Sql 2012
SELECT [task_address], [sos_task_address], [appdomain_address], [state], [abort_state], [type], [affinity_count], [forced_yield_count] FROM sys.dm_clr_tasks

Back to Top

---