Edit

Share via

Monitor and troubleshoot managed database objects

  • Article

Applies to: SQL Server

This article 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 overview.

Event Description
SQL Server Event Class Reference 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 Performance 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 Performance Monitor (perfmon.exe) counters

The Windows Performance 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. You should use catalog views because they're 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 System catalog views.

Catalog view Description
sys.assemblies Returns information about the assemblies registered in a database.
sys.assembly_references Identifies assemblies that reference other assemblies.
sys.assembly_modules Returns information about each function, stored procedure, and trigger defined in an assembly.
sys.assembly_files Returns information about the assembly files registered in the database.
sys.assembly_types Identifies the user-defined types (UDTs) defined by an assembly.
sys.module_assembly_usages Identifies the assemblies that CLR modules are defined in.
sys.parameter_type_usages Returns information about parameters that are user-defined types.
sys.server_assembly_modules Identifies the assembly that a CLR trigger is defined in.
sys.server_triggers Identifies the server-level DDL triggers on a server, including CLR triggers.
sys.type_assembly_usages Identifies the assemblies that user-defined types are defined in.
sys.types 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 System dynamic management views.

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

Related content