Share via

Diagnosing CLR Execution in SQL Server

  • Article

In this section, i am going to introduce some basic diagnostic tools. You may already be familiar with Dynamic Management Views. We modified a few existing DMVs and added a few new ones to help with some transparency to the execution of sqlclr code in the server. These are:

1) sys.dm_exec_query_stats

    We added four columns for clr execution time per query. These are 'total_clr_time', 'last_clr_time', 'min_clr_time' and 'max_clr_time'. They are same in number and syntax as worker time but indicate the time spent executing in clr. Note that inproc data access, if present, will show as separate query.

2) sys.dm_exec_request

When you execute this in SQL server 2005, you will see a new column 'executing_managed_code'. Together with the 'cpu_time', this can tell you whether a CLR stored procedure is causing high CPU utilization.

3) sys.dm_clr_appdomains

This lists all the clr appdomains created in the server and their attributes. By joining this view with the db and user catalog views, you can get information on who or where the clr code is being executed.

4) sys.dm_clr_loaded_assemblies

This gives the details of all assemblies loaded in an appdomain. Together with the appdomain dmv, you can see all the assemblies in use in the server

5) sys.dm_clr_properties

This gives some general information on the CLR used in the server.

In addition to DMVs, we do have trace events and profiling to our aid. In one of the upcoming posts, i will cover some specific triages.

Comments