Checking the Health of your CLR

Comments: No Comments
Published on: January 16, 2018

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.

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.

Sharepoint Diagnostics and XE

Comments: 2 Comments
Published on: January 3, 2018

One of the all-time greatest and most beloved applications among DBAs happens to be Sharepoint. Most of us would be lying if we said that we loved Sharepoint and the kind of performance issues it can cause on a SQL Server.

When you have an application that comes with a notoriety of causing performance pain, you will want to have some tools or find some tools that can help you find the pain points. Lucky for the savvy DBA, there are tools to be able to capture this kind of information. Hint: some of those tools can be found in this series.

Tools

One of the best tools for capturing performance impacting data is Extended Events. Extended Events is simply a tool that everybody needs to learn. And in this case, it provides an opportunity to support and troubleshoot Sharepoint as well as SQL Server.

Truth be told, the default XE session for Sharepoint does not come installed when you install Sharepoint. But, there is a session that gets installed when you configure SPDiag for your Sharepoint needs. I am not going to dive deeply into that tool or the Sharepoint tooling. That would be well outside the use and scope of this post. Here is the default session created by that tool.

As you can see, there is not much special about the session. This is a basic session to capture statements queries executed against the server. In fact, you will probably note it is similar to both of the default XE Profiler sessions I discussed here. The first caveat here is the application filter for Sharepoint apps. That said, if you are running multiple Sharepoint sites and configure SPDiag on both, and both share the same backend database server, guess what happens? Yup, you guessed it – you get two Sharepoint sessions that capture the exact same data.

The second caveat here is a fun thing about the service that gets created to support the Sharepoint XE Sessions. The SPDiag will alter each of your XE (sharepoint related) sessions many times every few seconds. Every alter of the sessions will be to either stop or restart the session. This seems rather unnecessary and huge miss. There are not many misses with XE, but there can be a misuse of XE similar to all of the abuses of profiler and this is one of those abuses in my opinion. You can easily discover the constant changes to these sessions via an XE session that I will be sharing in a future post. I would recommend you stay tuned for that article and also watch the 60 Day Series for an update that includes that post.

The next caveat here is that this particular session is only configured to go to the ring_buffer. What is the problem there? Well, the ring_buffer means the trace data is volatile and you can easily lose it. In addition, this particular session has been known to cause some memory issues on many installations. You can check for a memory issue by watching for a growing MEMORYCLERK_XE memory count. You can watch it with a query like the following.

In my opinion, I see no good value in running the session as currently built. I would just rather build a better XE session and leave it running instead of constantly stopping and starting the XE session.

The Wrap

In this article I have introduced you to a quick session setup that comes from using the SPDiag tool that could plausibly be useful in the troubleshooting of various different problems (most probably performance related) with Sharepoint. This assuredly is in the absence of a better alternative such as a custom session that you write yourself.

If we browse the data made available to us, we can see that there is adequate info to help us convert all of those SQL Traces to the high-performing XE versions that we should be using these days. I encourage you to browse the capabilities of XE as you work towards converting your profiler based traces to the much friendlier and more powerful counterparts.

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.

page 1 of 1

Calendar
January 2018
M T W T F S S
« Dec    
1234567
891011121314
15161718192021
22232425262728
293031  

Welcome , today is Sunday, January 21, 2018