Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

A DMV a Day – Day 18

The DMV for Day 18 is sys.dm_clr_tasks, which is described by BOL as:

Returns a row for all common language runtime (CLR) tasks that are currently running. A Transact-SQL batch that contains a reference to a CLR routine creates a separate task for execution of all the managed code in that batch. Multiple statements in the batch that require managed code execution use the same CLR task. The CLR task is responsible for maintaining objects and state pertaining to managed code execution, as well as the transitions between the instance of SQL Server and the common language runtime.

This DMV is only relevant if you have enabled the CLR on your SQL Server instance, and you have at least one CLR assembly loaded in one of the user databases on the SQL Server instance. This particular DMV works with SQL Server 2005, 2008, and 2008 R2. It requires VIEW SERVER STATE permission.

    -- Find long running SQL/CLR tasks
    SELECT os.task_address, os.[state], os.last_wait_type, 
           clr.[state], clr.forced_yield_count 
    FROM sys.dm_os_workers AS os 
    INNER JOIN sys.dm_clr_tasks AS clr 
    ON (os.task_address = clr.sos_task_address) 
    WHERE clr.[type] = 'E_TYPE_USER';

You want to be on the lookout for any rows that have a forced_yield_count above zero or that have a last_wait_type of SQLCLR_QUANTUM_PUNISHMENT, which indicates that the task previously exceeded its allowed quantum, causing the SQL OS scheduler to intervene and reschedule it at the end of the queue while forced_yield_count shows the number of times that this has happened. If you see either of these, you would want to be talking to your developers about their CLR assemblies, which are misbehaving, thereby causing SQL Server to put them in the “penalty box”.

Comments

Posted by Anonymous on 18 April 2010

Pingback from  Dew Drop – Weekend Edition – April 16-18, 2010 | Alvin Ashcraft's Morning Dew

Posted by Anonymous on 18 April 2010

Pingback from  Dew Drop – Weekend Edition – April 16-18, 2010 | Alvin Ashcraft's Morning Dew

Posted by Jason Brimhall on 19 April 2010

I don't have any CLR loaded yet - this should prove useful in the future.

Leave a Comment

Please register or log in to leave a comment.