Instrumenting SQL Server Managed Stored Procedures

Starting with SQL Server 2005 a database developer has the option to implement Stored Procedures in Managed Code. SQL Server offers a feature which is called “CLR Integration”. A good starting point for additional technical background is the following link:

This feature is a great improvment for database developers. Its also possible to debug the managed code when you execute the procedure. The upcoming question for me was: “Can we monitor it in the same way Dynatrace can monitor any other .NET or Java Application?” – the answer is: YES we can!


SQL Server hosts the CLR Runtime and runs it in a special security context. A .NET Assembly that implements a stored procedure can ONLY use certain system assemblies to interact with. By default – the assembly is also restricted in terms of accessing external resources, memory allocations and thread management. These restrications make perfect sense in order to achieve a stable running SQL Server Instance.

In order for Dynatrace to work in this environment we have to do several steps and we have some pre-requirements:
a) the database that we want to monitor needs to be “trustworthy”
b) the database owner must own the role to add so called “unsafe” assemblies
c) we have to add several assemblies to the database’s internal assembly cache

With the SQLServerCLR Knowledge Sensor Pack – which can be downloaded from our /community – you can now monitor the interactions between the Stored Procedure and SQL Server. Here is a simple stored procedure that executes a SQL Statement and returns its results:

When this Stored Procedure is executed we get the following PurePath with Dynatrace Diagnostics:

Cool Stuff!
So – now we have the option to monitor each Managed Stored Procudure and identify why its not performing as we would like it to.

Andreas Grabner has 20+ years of experience as a software developer, tester and architect and is an advocate for high-performing cloud scale applications. He is a regular contributor to the DevOps community, a frequent speaker at technology conferences and regularly publishes articles on You can follow him on Twitter: @grabnerandi