Jaa


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

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.

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.

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 = 555)

See Also

Reference

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

New content:
  • Updated the introduction and added samples.