Skip to content

Latest commit

 

History

History
87 lines (68 loc) · 8.38 KB

monitoring-and-troubleshooting-managed-database-objects.md

File metadata and controls

87 lines (68 loc) · 8.38 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic helpviewer_keywords
Monitor and Troubleshoot Managed Database Objects
Information about the tools that can be used to monitor and troubleshoot managed database objects and assemblies (CLR).
rwestMSFT
randolphwest
12/27/2024
sql
clr
reference
common language runtime [SQL Server], performance
monitoring [CLR integration]
performance [CLR integration]

Monitor and troubleshoot managed database objects

[!INCLUDE SQL Server]

This article provides information about the tools that can be used to monitor and troubleshoot managed database objects and assemblies running in [!INCLUDE ssNoVersion].

Profiler trace events

[!INCLUDE ssNoVersion] 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 [!INCLUDE tsql] 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 [!INCLUDE tsql] batches that have started or completed.
SP:Starting Event Class, SP:Completed Event Class Used to monitor the execution of [!INCLUDE tsql] stored procedures.
SQL:StmtStarting Event Class, SQL:StmtCompleted Event Class Used to monitor the execution of CLR and [!INCLUDE tsql] routines.

Performance counters

[!INCLUDE ssNoVersion] provides objects and counters that can be used by Performance Monitor to monitor activity in computers running an instance of [!INCLUDE ssNoVersion]. An object is any [!INCLUDE ssNoVersion] resource, such as a [!INCLUDE ssNoVersion] 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 [!INCLUDE ssNoVersion] 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 [!INCLUDE ssNoVersion] 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 [!INCLUDE ssNoVersion].
sys.dm_os_memory_clerks Returns all the memory clerks currently active in the [!INCLUDE ssNoVersion] instance, including CLR memory clerks.

Related content