patternsqlMinor
resource wait SQLCLR - wait_type CLR_SEMAPHORE by SPID 0
Viewed 0 times
sqlclrclr_semaphorewaitresourcewait_typespid
Problem
UPDATE
In the comments under Aaron's post is an extensive explanation why I want to find this out, please read it if you want to know.
The system is running some ETL, reporting services and a replication subscription.
SQL engine is 2012. The situation described below is continues, after a reboot it start within 5 minutes it start and never goes away. Even when no user are connected, no SSIS running, no reports being served and replication suspended this happens.
The reason i'm trying to get to the bottom of this is because on 1 system only I have a nonsensical wait ticking for 20 seconds every minute, while there is nothing running aside from the server instance itself and my ssms window. At the same time trying to work with heavy CLR operations (outer apply some custom clr to a large dataset for example) the processing time has increased from ~20 seonds to ~20 minutes.
These custom assemblies have all been unloaded, server rebooted but the waits keep coming.
The above number are from running the server for ~24 hours with very minimal use of ssis/ssrs and adhoc queries.
/UPDATE
Since a few weeks I have a continues increasing waittime for this specific wat situation
In the comments under Aaron's post is an extensive explanation why I want to find this out, please read it if you want to know.
The system is running some ETL, reporting services and a replication subscription.
SQL engine is 2012. The situation described below is continues, after a reboot it start within 5 minutes it start and never goes away. Even when no user are connected, no SSIS running, no reports being served and replication suspended this happens.
The reason i'm trying to get to the bottom of this is because on 1 system only I have a nonsensical wait ticking for 20 seconds every minute, while there is nothing running aside from the server instance itself and my ssms window. At the same time trying to work with heavy CLR operations (outer apply some custom clr to a large dataset for example) the processing time has increased from ~20 seonds to ~20 minutes.
These custom assemblies have all been unloaded, server rebooted but the waits keep coming.
wait_type waiting_tasks_count wait_time_ms max_wait_time_ms signal_wait_time_ms
------------------------------------------------------------ -------------------- -------------------- -------------------- --------------------
CLR_AUTO_EVENT 21178 269767282 1817841 1117
CLR_SEMAPHORE 4704 83518913 20051 180
CLR_MANUAL_EVENT 58 377 109 0
CLR_TASK_START 2376 376 13 136The above number are from running the server for ~24 hours with very minimal use of ssis/ssrs and adhoc queries.
/UPDATE
Since a few weeks I have a continues increasing waittime for this specific wat situation
Solution
Stop worrying about timer/queue waits like
CLR_SEMAPHORE. These waits always accumulate wait time, but this is not "bad" wait time in 99.999% of all cases. You should be filtering them out of your analysis and focusing on the ones that matter and that you can do anything about anyway. Use Paul Randal's NOT IN list as a guide:- Wait statistics, or please tell me where it hurts
Context
StackExchange Database Administrators Q#83594, answer score: 3
Revisions (0)
No revisions yet.