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”.

This entry was posted in SQL Server 2008. Bookmark the permalink.

One Response to A DMV a Day – Day 18

  1. Darcy says:

    Hi Glen,
    I’ve looked at BOL and in the SQL Server System Views Map.pdf and I haven’t seen any relationship information to other DMVs for the dmv sys.dm_os_workers. You’ve joined it to dm_clr_tasks in your query above and I’m wondering if you found out about that join from documentation? It seems like a lot of DMV’s are missing their relationship documentation and I’d really like to find a good source for the information.
    Thank you,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s