The Common Language Runtime (CLR) is the fundamental nerve center of the Microsoft .NET Framework. It is this nerve center that provides the execution environment for all .NET Framework code. One may sometimes refer to CLR code as managed code.

CLR hosted from within SQL Server (CLR Integration), provides the ability to create stored procedures, triggers, user-defined functions, user-defined types, and user-defined aggregates. And because managed code compiles to native code, we sometimes see a bit of a performance gain for various scenarios.

It is this bit of a performance gain, that we may see more CLR integration use within our SQL environments. And with increased CLR there will be an increased chance for something to go a little haywire. If things can (and will go haywire), it is important to have a means to be able to monitor them.

If you know me, you are probably aware that when it comes to monitoring a problem, I will most probably recommend a solution that involves a little bit of XE. If you don’t know me, here is a hint: some of those monitoring tools can be found in this series.

CLR

I had a client reach out recently because they were having all sorts of issues with their CLR procs. The client is losing some sleep, a little weight, and a fair amount of hair trying to figure out what is causing their CLR nightmares. This client has magic CLR. The CLR works fabulous or a day or three. Then suddenly the CLR procs just disappear and have to be re-created on the server. Granted this seems more like a problem of somebody did something they didn’t realize they were doing and poof the CLR is gone.

So, what can we do about this? The answer is quite simple. I am going to use an extended event session that will monitor the various aspects of CLR from a couple of different angles. First, I need to try and find events that fit my needs. I can do that with the queries and instructions found here.

As I query the XEvent catalog, I can see that there is a limited set of options and they can be seen in the following image.

Most of the events can be found in the sqlclr package and in the debug channel. The exception in both cases being the “assembly_load” event. I will go ahead and add all of those events to my session except for the garbage collection event.

The next step is to help cover my bases and see if somebody might be changing the objects and causing the odd behavior. I will do that with the object_created, object_altered, and object_deleted events. The caveat here is I will limit the scope of those events to only look for CLR specific changes. I will be able to do that by filtering on object_type in each of those three events. Here is a look at those object types.

In Extended Events, we see that we can monitor for object changes in the various different objects such as “TRIGASM”,”PROCASM”, and “FNTABASM”. What do these map to though? Well, here is the answer to that question!

• 16724 TRIGASM = Assembly (CLR) DML trigger
• 17232 PROCASM = Assembly (CLR) stored-procedure
• 21313 ASM = CLR Assembly
• 21318 FNSCLASM = Assembly (CLR) scalar-function
• 21574 FNTABASM = Assembly (CLR) table-valued function
• 17985 AGG — Assembl Aggregate function (CLR)

With all of this rolled together, we can now assemble the XE Session.

/*
This trace is set to filter by the following:
[sqlserver].[database_name] = 'Adventureworks'
Please replace the database name of "Adventureworks" with the databse name where you have CLR installed
*/USE master;
GO
-- Create the Event Session
IF EXISTS ( SELECT *
FROM sys.server_event_sessions
WHERE name = 'CLRHealth' )
DROP EVENT SESSION CLRHealth 
    ON SERVER;
GO
EXECUTE xp_create_subdir 'C:\Database\XE';
GO
CREATE EVENT SESSION [CLRHealth] ON SERVER
ADD EVENT sqlserver.assembly_load (
ACTION ( package0.callstack, sqlserver.client_app_name,
sqlserver.database_name, sqlserver.nt_username, sqlserver.query_hash,
sqlserver.query_plan_hash, sqlserver.session_id, sqlserver.sql_text,
sqlserver.tsql_stack , sqlserver.context_info)
WHERE ( [sqlserver].[database_name] = 'Adventureworks'
) 
),
ADD EVENT sqlclr.clr_allocation_failure (
ACTION ( package0.callstack, sqlserver.client_app_name,
sqlserver.database_name, sqlserver.nt_username, sqlserver.query_hash,
sqlserver.query_plan_hash, sqlserver.session_id, sqlserver.sql_text,
sqlserver.tsql_stack , sqlserver.context_info)
WHERE ( [sqlserver].[database_name] = 'Adventureworks'
) 
),
ADD EVENT sqlclr.clr_virtual_alloc_failure (
ACTION ( package0.callstack, sqlserver.client_app_name,
sqlserver.database_name, sqlserver.nt_username, sqlserver.query_hash,
sqlserver.query_plan_hash, sqlserver.session_id, sqlserver.sql_text,
sqlserver.tsql_stack , sqlserver.context_info)
WHERE ( [sqlserver].[database_name] = 'Adventureworks'
) 
),
ADD EVENT sqlclr.clr_init_failure (
ACTION ( package0.callstack, sqlserver.client_app_name,
sqlserver.database_name, sqlserver.nt_username, sqlserver.query_hash,
sqlserver.query_plan_hash, sqlserver.session_id, sqlserver.sql_text,
sqlserver.tsql_stack , sqlserver.context_info)
WHERE ( [sqlserver].[database_name] = 'Adventureworks'
) 
),
ADD EVENT sqlserver.object_altered ( SET collect_database_name = ( 1 )
ACTION ( sqlserver.sql_text,sqlserver.nt_username,sqlserver.server_principal_name,sqlserver.client_hostname,
package0.collect_system_time,package0.event_sequence ) 
WHERE (object_type = 'FNTABASM' --Assembly (CLR) table-valued function
OR object_type = 'ASM'--Assembly (CLR)
OR object_type = 'TRIGASM'--Assembly (CLR) DML trigger
OR object_type = 'PROCASM'--Assembly (CLR) stored-procedure
OR object_type = 'FNSCLASM'--Assembly (CLR) scalar-function
OR object_type = 'AGG'--Assembly (CLR) aggregate-function
--OR object_type = 'ALL'
)
AND [sqlserver].[database_name] = 'Adventureworks'
),
ADD EVENT sqlserver.object_deleted ( SET collect_database_name = ( 1 )
ACTION ( sqlserver.sql_text,sqlserver.nt_username,sqlserver.server_principal_name,sqlserver.client_hostname,
package0.collect_system_time,package0.event_sequence ) 
WHERE (object_type = 'FNTABASM' --Assembly (CLR) table-valued function
OR object_type = 'ASM'--Assembly (CLR)
OR object_type = 'TRIGASM'--Assembly (CLR) DML trigger
OR object_type = 'PROCASM'--Assembly (CLR) stored-procedure
OR object_type = 'FNSCLASM'--Assembly (CLR) scalar-function
OR object_type = 'AGG'--Assembly (CLR) aggregate-function
--OR object_type = 'ALL'
)
AND [sqlserver].[database_name] = 'Adventureworks'
),
ADD EVENT sqlserver.object_created ( SET collect_database_name = ( 1 )
ACTION ( sqlserver.sql_text,sqlserver.nt_username,sqlserver.server_principal_name,sqlserver.client_hostname,
package0.collect_system_time,package0.event_sequence ) 
WHERE (object_type = 'FNTABASM' --Assembly (CLR) table-valued function
OR object_type = 'ASM'--Assembly (CLR)
OR object_type = 'TRIGASM'--Assembly (CLR) DML trigger
OR object_type = 'PROCASM'--Assembly (CLR) stored-procedure
OR object_type = 'FNSCLASM'--Assembly (CLR) scalar-function
OR object_type = 'AGG'--Assembly (CLR) aggregate-function
--OR object_type = 'ALL'
)
AND [sqlserver].[database_name] = 'Adventureworks'
)
ADD TARGET package0.event_file ( SET filename = N'C:\Database\XE\CLRHealth.xel' )
WITH ( MAX_MEMORY = 512000 KB
, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS
, MAX_DISPATCH_LATENCY = 5 SECONDS
, MAX_EVENT_SIZE = 0 KB
, MEMORY_PARTITION_MODE = NONE
, TRACK_CAUSALITY = OFF
, STARTUP_STATE = OFF );
GO
ALTER EVENT SESSION CLRHealth 
ON SERVER 
STATE = START;
GO

Once deployed, I can go ahead and follow the instructions here to test different CLR managed code objects.

The Wrap

Having a tool to be able to monitor CLR health will be essential as you deploy more and more managed code within SQL Server. Extended Events offers a great lightweight means to do just that. This article has shown how to deploy a session that will capture the various changes with CLR objects within our database environment. In addition, you will be able to capture various conditions related to performance or problems with the managed code. You may even recognize some of the CLR events from the system_health session.

If you are interested in learning more about Extended Events, I recommend you read my “60 day” series of articles on Extended Events. The series continues to grow and covers a pretty decent depth and breadth on the topic.