Mar 072012
 

CLR_MONITOR is one of the wait types in SQL Server 2008. It occurs when a task is currently performing CLR execution and is waiting to obtain a lock on the monitor according to BOL. When this shows up in sys.dm_exec_requests, it means the session is running a CLR which is waiting for lock being granted to an object by using Monitor class.

static class StaticVariables
{
    public static object SyncObj = new object();
}
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlInt32 Wait10Sec()
{
    lock (StaticVariables.SyncObj)
    {
        Thread.Sleep(10000);
        return new SqlInt32(1);
    }
}
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlInt32 Wait10Sec_1()
{
    try
    {
        Monitor.Enter(StaticVariables.SyncObj);
        Thread.Sleep(10000);
        return new SqlInt32(1);
    }
    finally
    {
        Monitor.Exit(StaticVariables.SyncObj);
    }
}

Look at the code above, I created 2 CLR functions. The first one waits for 10 seconds after locking a static object member in an static class. The second function is same as the first one but using Monitor class.

--first window in SSMS, (spid = 55)
select dbo.Wait10Sec()

--second window in SSMS, (spid = 58)
select dbo.Wait10Sec()

--thrid window, it's monitor window.
select  session_id, wait_type, wait_time from sys.dm_exec_requests where session_id = 58

Run the script in the first window(spid = 55) then run the script in the second window (spid = 58) then run the script in the third window:

session_id wait_type      wait_time
---------- -------------- -----------
58         CLR_MONITOR    4005

(1 row(s) affected)

If you change the function call in the first and second window to dbo.Wait10Sec_1(), you should get the same wait type from the DMV.

  2 Responses to “Produce CLR_MONITOR Wait Type”

  1. Hi John,
    Nice explanation. I have a question, do you know if it’s feasible to configure the Max threads or processing so I can reduce the CLR_MONITOR
    For instance I have a similar case as yours where I am waiting 250ms, but in high concurrency environment I get CLR_MONIITOR around 2s

    Which is not allowing me to scale the solution?

    Thanks in advance

    • Yes, it’s possible. you can configure ThreadPool or write your own thread management class to eliminate the max threads.

      However, sometime, the thread is initiated by the users, for instance, you have 1000 concurrent requests, each connection tries to get the semaphore from the same object…In this case, you might want to remove the code that might cause blocking. and if it’s possible, queue the requests and use limited threads to process. You can email me if I can provide help.

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="">

(required)

(required)

C# | HTML | Plain Text | SQL | XHTML | XML | XSLT |

This site uses Akismet to reduce spam. Learn how your comment data is processed.