What appears in DMVs for SQLCLR?

A variation of the following question came up in a mail list that I’m on. Can you see currently executing SQLCLR code statements (i.e. the underlying C# code) in any of the sys.dm_exec… DMVs? Or anywhere else? This turned out to be more interesting than I thought.

As far as the engine internals are concerned, there is no CLR code but IL (.NET intermediate language). The assembly’s IL code is available (it’s stored in sys.assembly_files) and there are tools (e.g. an add-in for Reflector) that can extract it and decompile it to the language of your choice. But, even if you catalog the source as a “related file” (using ALTER ASSEMBLY…ADD FILE…, VS autodeploy does this) there’s no lookup into related files by the DMVs (that’s not what this feature is for, it’s for this reason), so the underlying C#, VB.NET, or IL code is never displayed. 

If your SQLCLR code issues a T-SQL statement however, this statement (via sql_handle) does appear in DMVs. You can see the task executing in sys.dm_clr_tasks, for example, but the sys.dm_exec-* ones can be more interesting. For example, if I have a SQLCLR proc that issues a SQL statement this can appear in sys.dm_exe.query_stats, and sys.dm_exec_cached_plans. In cached_plans, this appears as a plan for a SQLCLR proc (with no SQL handle) and a plan for a Prepared Stmt (for the T-SQL statement). If you happen to query sys.dm_exec_requests while the T-SQL statement is executing, there is a sql_handle that points to the T-SQL statement, otherwise the sql_text is NULL.

Interestingly, sys.dm_exec_cached_plans contains a sql_text for the SQLCLR stored procedure, with a text of “exec myproc” and a cacheobjtype of CLR_Compiled_Proc. But sys.dm_exec_query_stats contains no entries for the stored procedure (with or without text), although the is a line for the executed T-SQL. In my simple sample (a single T-SQL SELECT statement using Pipe.ExecuteAndSend), only the executed T-SQL statement. This means that you don’t see any statistic in query_stats for the “clr_time” series of counters in any entry of sys.dm_exec_query_stats with a stored procedure.

Using a SQLCLR UDF (I used a scalar UDF) in a T-SQL statment would contain non-zero values for those counters, because its part of the T-SQL statement that uses it. And, for a UDF that doesn't use T-SQL, there is no entry in sys.dm_exec_cached_plans for the UDF. For a SQLCLR UDF that does use T-SQL, you get a cached_plan for both the T-SQL statement and one for the function, of type CLR Compiled Func, with the function's name as the T-SQL text. There also appears to be two cache entries for Parse Tree/Check with NULL text after executing the UDF containing T-SQL statements.

 @bobbeauch

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.