sys.dm_clr_tasks DMV (Dynamic Management View), described by BOL as follows: http://msdn.microsoft.com/en-in/library/ms177528.aspx
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.
sys.dm_clr_tasks DMV is applicable to you if you have enabled the CLR on your SQL Server instance, and you are using at least one CLR assembly loaded in any of one user databases on your SQL Server instance.
You should look for rows having forced_yield_count column value above zero or that have a last_wait_type of SQLCLR_QUANTUM_PUNISHMENT. This will point that the task previously exceeded its allowed quantum & caused the SQL OS scheduler to intervene and reschedule it at the end of the queue. Value of Column forced_yield_count shows the number of time that this has happened.
If you noticed this, you should talk to your developer for this. This could cause issue for you SQL Server.
How to enable CLR?
EXEC SP_CONFIGURE ‘CLR ENABLED’,1
GO
RECONFIGURE
GO
How to disable CLR?
EXEC SP_CONFIGURE ‘CLR ENABLED’,0
GO
RECONFIGURE
GO
Query 1 : 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’;
Remarks
1. To use this DMV, User required VIEW SERVER STATE permission on the server.
If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun
Reference : Rohit Garg (http://mssqlfun.com/)
