CSS SQL Server Engineers

This is the official team Web Log for Microsoft Customer Service and Support (CSS) SQL Support. Posts are provided by the CSS SQL Escalation Services

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

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

  • Comments 6

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

Leave a Comment
  • Please add 8 and 4 and type the answer here:
  • Post
  • Excellent information!  The more transparency we can provide for interpretation of wait stats, the better!

  • Glad to have seen this.  I am working on a new SQL 2008 build and this Resource Waits "Other" being at the top with huge numbers was driving me crazy.

    Glad to hear it is not an issue!!

  • I am also seeing high wait-times when deploying SQLCLR to SQl 2005.

  • Do these stats increment if user CLR code uses manual or auto events? Or do these ONLY refer to SQL Server 'system' usage of these mechanisms?

  • I am using SQL server 2012, I have same problem ., when I am looking activity monitor , I found the top wait category is SQLCLR.

  • Looks like this remains unfixed in SQL 2012 SP1 CU10 (May 2014)

Page 1 of 1 (6 items)