Monitoring and Troubleshooting Managed Database Objects

  • Article

New: 14 April 2006

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

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 Monitoring 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 XP process. Each object contains one or more counters that determine various aspects of the objects to monitor. For more information, see Using 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 2005 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

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

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 (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.

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.

See Also

Other Resources

Database Engine .NET Framework Programming

Help and Information

Getting SQL Server 2005 Assistance