Monitoring and Troubleshooting Managed Database Objects


This topic provides information about the tools that can be used to monitor and troubleshoot managed database objects and assemblies running in SQL Server.

SQL Server provides SQL Trace and event notifications to monitor events that occur in the Database Engine. By recording specified events, SQL Trace helps you troubleshoot performance, audit database activity, gather sample data for a test environment, debug Transact-SQL statements and stored procedures, and gather data for performance analysis tools. For more information, see SQL Trace and Extended Events.

Assembly Load Event ClassUsed to monitor assembly load requests (success and failures).
SQL:BatchStarting Event Class, SQL:BatchCompleted Event ClassProvides information about Transact-SQL batches that have started or completed.
SP:Starting Event Class, SP:Completed Event ClassUsed to monitor the execution of Transact-SQL stored procedures.
SQL:StmtStarting Event Class, SQL:StmtCompleted Event ClassUsed to monitor the execution of CLR and Transact-SQL routines.

SQL Server provides objects and counters that can be used by System Monitor to monitor activity in computers running an instance of SQL Server. An object is any SQL Server resource, such as a SQL Server lock or a Windows process. Each object contains one or more counters that determine various aspects of the objects to monitor. For more information, see Use SQL Server Objects.

SQL Server, CLR ObjectTotal time spent in CLR execution.

The Windows System Monitor (PERFMON.EXE) tool has several performance counters that can be used to monitor CLR integration applications. The .NET CLR performance counters can be filtered by the "sqlservr" process name to track CLR integration applications that are currently running.

Performance ObjectDescription
SqlServer:CLRProvides CPU statistics for the server.
.NET CLR ExceptionsTracks the number of exceptions per second.
.NET CLR LoadingProvides information about the AppDomains and assemblies loaded in the server.
.NET CLR MemoryProvides information about CLR memory usage. This object can be used to flag alerts if memory usage gets too large.
.NET Data Provider for SQL ServerTracks the number of connects and disconnects per second. This object can be used for monitoring the level of database activity.

Catalog views return information that is used by the SQL Server Database Engine. We recommend that you use catalog views because they are the most general interface to the catalog metadata and provide the most efficient way to obtain, transform, and present customized forms of this information. All user-available catalog metadata is exposed through catalog views. For more information, see Catalog Views (Transact-SQL).

Catalog ViewDescription
sys.assemblies (Transact-SQL)Returns information about the assemblies registered in a database.
sys.assembly_references (Transact-SQL)Identifies assemblies that reference other assemblies.
sys.assembly_modules (Transact-SQL)Returns information about each function, stored procedure, and trigger defined in an assembly.
sys.assembly_files (Transact-SQL)Returns information about the assembly files registered in the database.
sys.assembly_types (Transact-SQL)Identifies the user-defined types (UDTs) defined by an assembly.
sys.module_assembly_usages (Transact-SQL)Identifies the assemblies that CLR modules are defined in.
sys.parameter_type_usages (Transact-SQL)Returns information about parameters that are user-defined types.
sys.server_assembly_modules (Transact-SQL)Identifies the assembly that a CLR trigger is defined in.
sys.server_triggers (Transact-SQL)Identifies the server-level DDL triggers on a server, including CLR triggers.
sys.type_assembly_usages (Transact-SQL)Identifies the assemblies that user-defined types are defined in.
sys.types (Transact-SQL)Returns the system and user-defined types registered in the database.

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. For more information, see Dynamic Management Views and Functions (Transact-SQL).

sys.dm_clr_appdomains (Transact-SQL)Provides information about each application domain in the server.
sys.dm_clr_loaded_assemblies (Transact-SQL)Identifies each managed assembly registered on the server.
sys.dm_clr_properties (Transact-SQL)Returns information about the hosted CLR.
sys.dm_clr_tasks (Transact-SQL)Identifies all the CLR tasks that are currently running.
sys.dm_exec_cached_plans (Transact-SQL)Returns information about the query execution plans that are cached by SQL Server for faster query execution.
sys.dm_exec_query_stats (Transact-SQL)Returns aggregate performance statistics for cached query plans.
sys.dm_exec_requests (Transact-SQL)Returns information about each request that is executing within SQL Server.
sys.dm_os_memory_clerks (Transact-SQL)Returns all the memory clerks currently active in the SQL Server instance, including CLR memory clerks.

Common Language Runtime (CLR) Integration Programming Concepts