SQLCLR wait in SQL Server 2016

 

1.

check the waits

SELECT * FROM sys.dm_os_wait_stats
WHERE wait_type IN (‘CLR_AUTO_EVENT’, ‘CLR_CRST’, ‘CLR_JOIN’, ‘CLR_MANUAL_EVENT’
, ‘CLR_MEMORY_SPY’, ‘CLR_MONITOR’, ‘CLR_RWLOCK_READER’, ‘CLR_RWLOCK_WRITER’, ‘CLR_SEMAPHORE’
, ‘CLR_TASK_START’, ‘CLRHOST_STATE_ACCESS’, ‘ASSEMBLY_LOAD’, ‘FS_GARBAGE_COLLECTOR_SHUTDOWN’
, ‘SQLCLR_APPDOMAIN’, ‘SQLCLR_ASSEMBLY’, ‘SQLCLR_DEADLOCK_DETECTION’, ‘SQLCLR_QUANTUM_PUNISHMENT’)
ORDER BY wait_time_ms DESC, wait_type ASC;

check the wait type explanation

Wait statistics, or please tell me where it hurts

checking each wait type explain

 

3.

index fragmentation

SELECT
    *
   ,wait_time_ms/waiting_tasks_count AS 'Avg Wait in ms'
FROM
   sys.dm_os_wait_stats 
WHERE
   waiting_tasks_count > 0
ORDER BY
   wait_time_ms DESC

https://social.msdn.microsoft.com/Forums/en-US/02385e50-a048-435e-ac53-d8ca2b19932e/what-is-waiting-in-waiting-tasks-in-activity-monitor?forum=sqldatabaseengine

SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName,
ind.name AS IndexName, indexstats.index_type_desc AS IndexType,
indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes ind
ON ind.object_id = indexstats.object_id
AND ind.index_id = indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent > 35
ORDER BY indexstats.avg_fragmentation_in_percent DESC

 

4. MS

High waits on CLR_MANUAL_EVENT and CLR_AUTO_EVENT

 

 

 

 

 

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *