CLR_RWLOCK_READER Wait Type Explained

CLR_RWLOCK_READER manifests when the SQL Server CLR hosting layer's reader-writer lock mechanism experiences contention. The CLR runtime maintains internal reader-writer locks to protect access to assembly metadata, application domains, and type information. When multiple sessions simultaneously execute CLR code, they compete for shared reader access to these protected resources.

The wait occurs specifically in the CLR runtime's lock manager, not SQL Server's native lock manager. SQL Server tasks executing CLR stored procedures, functions, aggregates, or user-defined types must acquire reader locks on the assembly's metadata before execution. When writer locks are held (typically during assembly loading, unloading, or permission changes), reader requests queue and generate this wait type.

SQL Server 2016 and later versions improved CLR lock granularity, reducing contention compared to earlier versions where assembly locks were more coarse-grained. The introduction of CLR strict security in SQL Server 2017 added additional metadata validation steps that can increase the frequency of these waits, particularly during initial assembly access after service restarts.

The underlying mechanism involves the CLR AppDomain manager coordinating with SQL Server's scheduler to yield control when locks cannot be immediately acquired. This cooperative yielding appears in sys.dm_exec_requests as CLR_RWLOCK_READER waits, distinct from standard SQL Server locking waits.

-- Identify sessions currently experiencing CLR reader lock waits
SELECT 
    r.session_id,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    t.text AS current_sql,
    r.command,
    r.percent_complete
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.wait_type = 'CLR_RWLOCK_READER'
ORDER BY r.wait_time DESC;

-- Analyze historical CLR wait patterns from wait stats
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 LIKE 'CLR%'
ORDER BY wait_time_ms DESC;

-- Identify CLR assemblies and their current state
SELECT 
    a.name AS assembly_name,
    a.permission_set_desc,
    a.is_visible,
    a.create_date,
    af.name AS file_name,
    af.file_id
FROM sys.assemblies a
INNER JOIN sys.assembly_files af ON a.assembly_id = af.assembly_id
WHERE a.is_user_defined = 1
ORDER BY a.create_date DESC;

-- Find CLR objects and their usage patterns
SELECT 
    o.name AS object_name,
    o.type_desc,
    m.definition,
    a.name AS assembly_name,
    a.permission_set_desc
FROM sys.objects o
INNER JOIN sys.assembly_modules am ON o.object_id = am.object_id
INNER JOIN sys.assemblies a ON am.assembly_id = a.assembly_id
LEFT JOIN sys.sql_modules m ON o.object_id = m.object_id
WHERE o.is_ms_shipped = 0
ORDER BY o.name;

-- Check for CLR configuration and memory usage
SELECT 
    name,
    value,
    value_in_use,
    description
FROM sys.configurations
WHERE name IN ('clr enabled', 'max server memory (MB)', 'min server memory (MB)')
UNION ALL
SELECT 
    'CLR Memory (KB)' AS name,
    SUM(pages_kb + virtual_memory_reserved_kb) AS value,
    SUM(pages_kb + virtual_memory_reserved_kb) AS value_in_use,
    'Current CLR memory consumption' AS description
FROM sys.dm_os_memory_clerks
WHERE type = 'MEMORYCLERK_CLR';

-- Emergency: Kill sessions with excessive CLR wait times
-- WARNING: Test in development first, will terminate user sessions
DECLARE @kill_threshold_ms INT = 30000; -- 30 seconds

DECLARE @session_id INT;
DECLARE kill_cursor CURSOR FOR
SELECT r.session_id
FROM sys.dm_exec_requests r
WHERE r.wait_type = 'CLR_RWLOCK_READER'
  AND r.wait_time > @kill_threshold_ms
  AND r.session_id > 50; -- Avoid system sessions

OPEN kill_cursor;
FETCH NEXT FROM kill_cursor INTO @session_id;

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @kill_cmd NVARCHAR(50) = 'KILL ' + CAST(@session_id AS NVARCHAR(10));
    PRINT 'Executing: ' + @kill_cmd;
    -- EXEC sp_executesql @kill_cmd; -- Uncomment to actually kill
    FETCH NEXT FROM kill_cursor INTO @session_id;
END;

CLOSE kill_cursor;
DEALLOCATE kill_cursor;

-- Restart CLR to clear potential deadlocks in CLR runtime
-- WARNING: This will terminate all CLR execution and may cause application errors
-- Disables and re-enables CLR integration
EXEC sp_configure 'clr enabled', 0;
RECONFIGURE;
EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;

-- Drop and recreate problematic assemblies with better permissions
-- Replace 'YourAssemblyName' with actual assembly name from diagnostic queries
-- WARNING: This will break dependent objects temporarily
/*
-- Step 1: Drop dependent objects first
DROP FUNCTION IF EXISTS dbo.YourCLRFunction;
DROP PROCEDURE IF EXISTS dbo.YourCLRProcedure;

-- Step 2: Drop and recreate assembly
DROP ASSEMBLY IF EXISTS YourAssemblyName;

-- Step 3: Recreate with SAFE permission set instead of EXTERNAL_ACCESS
CREATE ASSEMBLY YourAssemblyName
FROM 'C:\Path\To\Your\Assembly.dll'
WITH PERMISSION_SET = SAFE; -- Changed from EXTERNAL_ACCESS or UNSAFE

-- Step 4: Recreate dependent objects
*/

-- Configure CLR memory limits and timeouts
-- Adjust max server memory to leave room for CLR operations
DECLARE @total_memory_mb BIGINT;
SELECT @total_memory_mb = total_physical_memory_kb / 1024 
FROM sys.dm_os_sys_info;

DECLARE @new_max_memory BIGINT = @total_memory_mb * 0.8; -- Reserve 20% for OS and CLR

EXEC sp_configure 'max server memory (MB)', @new_max_memory;
RECONFIGURE;

-- Enable CLR strict security for SQL Server 2017+
-- This can help prevent some CLR-related hangs
EXEC sp_configure 'clr strict security', 1;
RECONFIGURE;

Implement assembly-level monitoring by tracking CLR wait statistics in your regular monitoring routines. CLR_RWLOCK_READER waits exceeding 1000ms average indicate assembly contention requiring immediate attention.

Design CLR code with thread safety in mind. Avoid static variables and shared state in CLR assemblies that can create artificial serialization points. Use the SAFE permission set whenever possible instead of EXTERNAL_ACCESS or UNSAFE, as SAFE assemblies have fewer internal locks and reduced contention.

Configure SQL Server memory settings to reserve adequate memory for CLR operations. CLR assemblies compete with SQL Server for memory, and memory pressure can exacerbate lock contention. Set max server memory to 80-85% of total system memory on dedicated SQL Server instances.

Establish assembly lifecycle management procedures. Schedule assembly deployments during maintenance windows and implement proper testing of concurrent CLR execution in development environments that mirror production workloads. Many CLR contention issues only manifest under concurrent load.

Monitor CLR assembly loading patterns. Frequent assembly loading and unloading creates writer lock activity that blocks readers. Consider keeping assemblies loaded by executing a lightweight CLR function during SQL Server startup procedures.

Implement application-level connection pooling and avoid short-lived connections that repeatedly initialize CLR contexts. Each new CLR context requires metadata access that can contribute to reader lock contention.