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: http://msdn.microsoft.com/en-us/library/k2e1fb36(VS.80).aspx
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 http://www.dynatrace.com/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:
So – now we have the option to monitor each Managed Stored Procudure and identify why its not performing as we would like it to.