How It Works: What is behind the SQLCLR wait category in SQL Server 2008 Activity Monitor

I was asked a question as the CSS First Aid Station at SQL PASS 2008 where the Activity Monitor kept showing the SQLCLR wait category as the top waiter, even when no other activity was going on.  After some digging I found that this is a bug and currently marked to be fixed SQL Server 2008 SP1.  However, it was an interesting investigation that I would like to share.

The activity monitor rolls up wait type categories.  Shown here are the sys.dm_os_wait_stats (wait_type) values which comprise the SQLCLR category.   The categories are easy to see if you Profile Trace the Activity Monitor activity.

TABLE: [#am_wait_types]

        INSERT INTO #am_wait_types VALUES (N'SQLCLR', N'CLR_AUTO_EVENT', 0); -- Auto Event

        INSERT INTO #am_wait_types VALUES (N'SQLCLR', N'CLR_CRST', 0); -- Critical Section

        INSERT INTO #am_wait_types VALUES (N'SQLCLR', N'CLR_JOIN', 0); -- Thread join

        INSERT INTO #am_wait_types VALUES (N'SQLCLR', N'CLR_MANUAL_EVENT', 0); -- Manual Event

        INSERT INTO #am_wait_types VALUES (N'SQLCLR', N'CLR_MEMORY_SPY', 0);

        INSERT INTO #am_wait_types VALUES (N'SQLCLR', N'CLR_MONITOR', 0); -- Monitor Event

        INSERT INTO #am_wait_types VALUES (N'SQLCLR', N'CLR_RWLOCK_READER', 0); -- Reader writer lock

        INSERT INTO #am_wait_types VALUES (N'SQLCLR', N'CLR_RWLOCK_WRITER', 0); -- Readier writer lock

        INSERT INTO #am_wait_types VALUES (N'SQLCLR', N'CLR_SEMAPHORE', 0); -- Semaphore

        INSERT INTO #am_wait_types VALUES (N'SQLCLR', N'CLR_TASK_START', 0);

        INSERT INTO #am_wait_types VALUES (N'SQLCLR', N'CLRHOST_STATE_ACCESS', 0);

        INSERT INTO #am_wait_types VALUES (N'SQLCLR', N'ASSEMBLY_LOAD', 0);

        INSERT INTO #am_wait_types VALUES (N'SQLCLR', N'FS_GARBAGE_COLLECTOR_SHUTDOWN', 0);

        INSERT INTO #am_wait_types VALUES (N'SQLCLR', N'SQLCLR_APPDOMAIN', 0);

        INSERT INTO #am_wait_types VALUES (N'SQLCLR', N'SQLCLR_ASSEMBLY', 0);

        INSERT INTO #am_wait_types VALUES (N'SQLCLR', N'SQLCLR_DEADLOCK_DETECTION', 0);

        INSERT INTO #am_wait_types VALUES (N'SQLCLR', N'SQLCLR_QUANTUM_PUNISHMENT', 0);

What I found was that several of the wait_types should be ignored as they are expected waits.  

For that statement to make sense I need to provide you with more information.   SQL Server hosts the CLR process.  One of the things the hosting interface allows is for synchronization objects to be supported as SQL Server (SOS*) synchronization objects. 

When creating an event in a Win32 application you would use the following:

HANDLE hEvent = CreateEvent(....)

In CLR you might create a Monitor or other synchronization event.   CLR supports these with the OS primitives (Event, Mutex, Semaphore, ...).   However, when hosting is in place, like SQL Server, the hosting interface exposes methods to create synchronization events.

pHost->CreateEvent(...)

SQL Server creates an SOS_Event to support this and the SOS_Event is integrated with SOS scheduling and deadlock detection.   So in the case of a CLR_AUTO_EVENT wait this is a wait on a SOS_Event that was created for auto reset instead of manual reset.  

When you start a CLR under SQL Server one(1) or more CLR workers are created and they wait for new work.   CLR waits on an auto event and this surfaces as the CLR_AUTO_EVENT wait type.   By including this in the SQLCLR wait category of activity monitor it appears you have a wait that needs attention when all it means is you have a CLR worker waiting for work and it is not interesting to the monitoring of your SQL Server.

Bob Dorr
SQL Server Principal Escalation Engineer