How to tell if your code should be yielding

As we saw in the last post, cooperative clr code that explicitly yields during long-running computations outperforms clr code that gets forcibly rescheduled.  How should one discover sql/clr code that is considered 'runaway' by the server and could be improved?

There are a number of informative dmvs that contain helpful information in cases like this, such as  sys.dm_clr_tasks, sys.dm_os_waiting_tasks, and sys.dm_os_workers.

For this particular problem, I want to know what state my clr tasks are in and what they have been waiting on recently as well as the number of times that the SQL Server Scheduler has forced them to yield:

select os.task_address, os.state, os.last_wait_type, clr.state, clr.forced_yield_count from sys.dm_os_workers os join sys.dm_clr_tasks clr on (os.task_address = clr.sos_task_address) where clr.type = 'E_TYPE_USER'

When my greedy count query from the last post is running, I notice a number of rows like the following in the output:

SQLCLR_QUANTUM_PUNISHMENT indicates that the task previously exceeded its allowed quantum, causing the 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.

In contrast, when the cooperative version is running, I see rows like the following where the last_wait_type is SOS_SCHEDULER_YIELD and forced_yield_count is 0.

This post is late as I was trying to include more information about all the underlying activity, but it quickly proved too much for a blog post on this simple example.  Hopefully, I and others on the team will be able to be able to go into more detail in future posts.