CLR_MONITOR Wait Type Explained

CLR_MONITOR waits manifest when SQL Server's hosted CLR runtime encounters lock contention on .NET monitor objects. The SQL Server process hosts the .NET Framework runtime to execute CLR assemblies, and these assemblies use standard .NET synchronization primitives like Monitor.Enter/Exit, lock statements, and synchronized methods.

When a SQL Server worker thread executing a CLR function attempts to acquire a monitor lock that another thread holds, the thread transitions to a wait state. The SQL Server scheduler recognizes this as a cooperative yield and records it as CLR_MONITOR. This differs from SQLCLR_* waits, which track SQL Server's interaction with the CLR runtime itself rather than internal CLR synchronization.

The wait occurs specifically during these scenarios: CLR functions using lock() statements on shared objects, CLR functions calling synchronized methods, multiple sessions executing the same CLR assembly simultaneously with shared state, and CLR code using explicit Monitor.Enter/Exit calls on contested resources.

The severity depends on frequency and duration. Short CLR_MONITOR waits under 10ms are normal for well-designed CLR functions. Sustained waits exceeding 100ms indicate problematic CLR code design or excessive concurrent execution of the same assembly.

-- Current CLR_MONITOR waits and session details
SELECT 
    s.session_id,
    s.login_name,
    r.command,
    r.database_id,
    r.wait_type,
    r.wait_time,
    r.last_wait_type,
    t.text AS current_sql
FROM sys.dm_exec_requests r
INNER JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.wait_type = 'CLR_MONITOR'
ORDER BY r.wait_time DESC;

-- Historical CLR_MONITOR wait statistics
SELECT 
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    max_wait_time_ms,
    signal_wait_time_ms,
    wait_time_ms / NULLIF(waiting_tasks_count, 0) AS avg_wait_ms
FROM sys.dm_os_wait_stats 
WHERE wait_type = 'CLR_MONITOR'
AND waiting_tasks_count > 0;

-- CLR assemblies and their permission levels
SELECT 
    a.assembly_id,
    a.name AS assembly_name,
    a.permission_set_desc,
    a.create_date,
    a.modify_date
FROM sys.assemblies a
WHERE a.assembly_id > 65536  -- Exclude system assemblies
ORDER BY a.modify_date DESC;

-- Active CLR tasks and their resource usage
SELECT 
    t.session_id,
    t.request_id,
    t.task_state,
    t.context_switches_count,
    t.pending_io_count,
    clr.type,
    clr.state,
    clr.forced_yield_count
FROM sys.dm_os_tasks t
INNER JOIN sys.dm_clr_tasks clr ON t.task_address = clr.task_address
WHERE t.session_id > 50
ORDER BY clr.forced_yield_count DESC;

-- CLR memory pressure and AppDomain statistics
SELECT 
    ad.appdomain_name,
    ad.state,
    ad.strong_ref_count,
    ad.weak_ref_count,
    ad.cost,
    ad.value,
    ad.total_processor_time_ms
FROM sys.dm_clr_appdomains ad
WHERE ad.state <> 'UNLOADED'
ORDER BY ad.cost DESC;

Identify problematic CLR assemblies causing monitor contention

-- Find CLR functions with highest execution counts and times
SELECT 
    o.name AS function_name,
    s.name AS schema_name,
    a.name AS assembly_name,
    dm.execution_count,
    dm.total_worker_time,
    dm.total_worker_time / dm.execution_count AS avg_worker_time_ms,
    dm.last_execution_time
FROM sys.assembly_modules am
INNER JOIN sys.objects o ON am.object_id = o.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
INNER JOIN sys.assemblies a ON am.assembly_id = a.assembly_id
LEFT JOIN sys.dm_exec_function_stats dm ON o.object_id = dm.object_id
WHERE o.type IN ('FN', 'TF', 'IF')  -- CLR functions
AND dm.execution_count > 0
ORDER BY dm.total_worker_time DESC;

-- Review these results to identify frequently executed CLR functions
-- High total_worker_time combined with CLR_MONITOR waits indicates locking issues

Configure CLR strict security for safer execution

-- Enable CLR strict security (SQL Server 2017+)
-- WARNING: Test thoroughly in development first
-- This may break existing CLR assemblies marked as SAFE

-- Check current CLR configuration
SELECT 
    name,
    value,
    value_in_use,
    description
FROM sys.configurations 
WHERE name IN ('clr enabled', 'clr strict security');

-- Enable CLR strict security
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'clr strict security', 1;
RECONFIGURE;

-- Verify CLR assemblies still function after enabling strict security
-- Some assemblies may need to be recreated with updated security settings

Implement CLR assembly monitoring and alerting

-- Create monitoring view for CLR wait detection
CREATE OR ALTER VIEW dbo.vw_clr_monitor_waits AS
SELECT 
    GETDATE() AS sample_time,
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    max_wait_time_ms,
    signal_wait_time_ms,
    wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms
FROM sys.dm_os_wait_stats 
WHERE wait_type = 'CLR_MONITOR'
AND waiting_tasks_count > 0;

-- Set up alerting threshold (adjust based on your environment)
-- Alert when average CLR_MONITOR wait exceeds 50ms
SELECT 
    sample_time,
    wait_time_ms / waiting_tasks_count AS avg_wait_ms
FROM dbo.vw_clr_monitor_waits
WHERE wait_time_ms / waiting_tasks_count > 50;

Disable problematic CLR assemblies temporarily

-- Script to disable CLR assemblies causing excessive waits
-- WARNING: This will break applications using these assemblies
-- Only use as emergency measure while fixing underlying code

-- List all non-system CLR assemblies
SELECT 
    assembly_id,
    name,
    permission_set_desc,
    is_visible,
    create_date
FROM sys.assemblies 
WHERE assembly_id > 65536
AND is_visible = 1;

-- Template to drop specific problematic assembly
-- Replace 'YourProblematicAssembly' with actual assembly name
/*
DROP ASSEMBLY [YourProblematicAssembly];
GO
-- Remember to also drop dependent functions/procedures first
*/

Design CLR assemblies to minimize shared state and locking. Use thread-safe collections like ConcurrentDictionary instead of lock statements around Dictionary objects. Implement read-heavy workloads with ReaderWriterLockSlim rather than exclusive locks.

Set appropriate CLR memory limits using sp_configure 'max server memory' to prevent CLR from consuming excessive memory and triggering garbage collection pressure. Configure 'clr strict security' on SQL Server 2017+ to enforce secure coding practices.

Monitor CLR assembly performance using sys.dm_clr_appdomains and sys.dm_clr_tasks and establish baselines for execution times. Create alerts when CLR_MONITOR waits exceed 10% of total wait time or when individual waits exceed 100ms consistently.

Implement connection pooling in applications calling CLR functions frequently. Multiple sessions executing the same CLR assembly simultaneously often create monitor contention. Consider redesigning CLR functions to be stateless or use SQL Server's native T-SQL equivalents where possible.

Review CLR assemblies during SQL Server upgrades. Microsoft periodically changes CLR hosting behavior, and assemblies that performed well on older versions may exhibit different synchronization patterns on newer releases.