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.
Profiler Trace Events
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.
| Event | Description |
|---|---|
| Assembly Load Event Class | Used to monitor assembly load requests (success and failures). |
| SQL:BatchStarting Event Class, SQL:BatchCompleted Event Class | Provides information about Transact-SQL batches that have started or completed. |
| SP:Starting Event Class, SP:Completed Event Class | Used to monitor the execution of Transact-SQL stored procedures. |
| SQL:StmtStarting Event Class, SQL:StmtCompleted Event Class | Used to monitor the execution of CLR and Transact-SQL routines. |
Performance Counters
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.
| Object | Description |
|---|---|
| SQL Server, CLR Object | Total time spent in CLR execution. |
Windows System Monitor (PERFMON.EXE) Counters
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 Object | Description |
|---|---|
| SqlServer:CLR | Provides CPU statistics for the server. |
| .NET CLR Exceptions | Tracks the number of exceptions per second. |
| .NET CLR Loading | Provides information about the AppDomains and assemblies loaded in the server. |
| .NET CLR Memory | Provides information about CLR memory usage. This object can be used to flag alerts if memory usage gets too large. |
| .NET Data Provider for SQL Server | Tracks the number of connects and disconnects per second. This object can be used for monitoring the level of database activity. |
Catalog Views
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 View | Description |
|---|---|
| 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
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).
| DMV | Description |
|---|---|
| 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. |
See Also
Common Language Runtime (CLR) Integration Programming Concepts