共用方式為

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

Comments

  • Anonymous
    December 02, 2008
    Excellent information!  The more transparency we can provide for interpretation of wait stats, the better!

  • Anonymous
    June 09, 2010
    The comment has been removed

  • Anonymous
    January 24, 2011
    The comment has been removed

  • Anonymous
    November 21, 2011
    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?

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

    • Anonymous
      February 28, 2016
      @Vikas, did u find a solution for that?

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

    • Anonymous
      February 28, 2016
      Even in 2012 SP2, still there!

  • Anonymous
    November 17, 2014
    Eu tenho o mesmo problema no SQL 2012 BI