Bill Ramos, Principal Program Manager, SQL Manageability

In my previous blog post Installing the SQL Server 2008 R2 Management Pack for System Center Operations Manager 2007 R2 I showed how you can install SCOM 2007 R2 on a “bare-metal” VM using  SQL Server 2008 R2 as the back end. One of the big features of the SQL Server 2008 R2 Management Pack is that we reduced the noise (or false alerts) reported by SCOM. This was done by disabling many of the alerts that we initially thought were critical/important and realized, it was better for you – the DBA – to determine what you think is important.

In this post, I’ll demonstrate how to enable and test one particular rule – Blocking Sessions – that is part of the SQL 2008 DB Engine set of rules.

This blog post is part of the series of posts that I’m using to prepare for my SQL PASS 2010 session on the SQL MP.

Using SQL Server Management Studio to find blocking sessions

First, a little background on how to find out your blocking problems within SQL Server Management Studio. This is importance since once you find the blocking SQL Process ID (SPID) , you’ll want to probably kill the SPID and then fix the application or query that is causing the problem for the rest of your system.

Standard Reports – Server – Activity – All Blocking Transactions

The Activity – All Blocking Transactions report is available by right clicking on the Server of interest; select the Reports command; then the Standard Reports command; and finally selecting the Activity – All Blocking Transactions report. As you can see from the example above, you can get a real time view of the blocking problem with this report. The trick is, you need to run the report when the problem is happening to diagnose identify the problem SPID.

Using Activity Monitor

With Activity Monitor, you can quickly filter to the “Head Blocker” SPID by clicking on the [v] control for the Head Blocker column in the Processes section of the tool and select the value 1 – if present – to filter to the offending SPID. In the case shown above, the connection string information for the Application identifies the offending SPID as “BadApplication ver 1.2”. If only all problems were so easy to identify.

By right clicking on the highlighted row in Activity Monitor, you can issue the “Kill Process” command. If the problem reoccurs, you can consider using the APP_NAME() function within the Classifier Function for the Resource Governor feature introduced in SQL Server 2008 Enterprise Edition to block the application from connecting to the server.

Configuring SCOM 2007 and the SQL Server MP to detect blocking sessions

Out of the box, after you have discovered the SQL Server computer using SCOM 2007 – see Installing the SQL Server 2008 R2 Management Pack for System Center Operations Manager 2007 R2. Once the system has cycled and SCOM agent is reporting health results to the Operations Console, you’ll see that even though a blocking situation is in progress, the monitor for the Microsoft SQL Server – Computers – indicates a Healthy state for the computer in question as shown below.

I happen to know that the highlighted server – BILLRAMO-KAT1 – is experiencing a blocking transaction problem. In order to detect the blocking scenario, you need to create an Override for the SQL Server MP.

Creating the Override for Blocking Sessions

To create and override for the blocking sessions scenario, you’ll need to do the following steps:

1. Click on the Authoring workspace bar and select the Monitors item under the Management Pack Objects folder in the Authoring tree as shown below.
   
2. In the Look for: control in the Monitors workspace, type in the phrase Blocking Sessions and then click Find Now. SCOM searches across all of the Management Packs that you have installed looking for the Monitor that contains the phrase. Select the Blocking Sessions for Type SQL 2008 Blocked SID Provider as shown below.
   
3. There should be 2 Blocking Sessions monitors as a result of the search. The second one is located under the SQL 2005 DB Engine target. You’ll need to perform the same steps to set up the override for your SQL Server 2005 computers. Right click on the Blocking Sessions line and select the Overrides > Override the Monitor > For all objects of class: SQL 2008 DB Engine command. This displays the Override Properties.
   
4. Before you go and enable the override, you are going to create a new destination management back. If you were to create the override in the “Default Management Pack”, you will lose the ability to easily export the overrides you setup for SQL Server 2008 computers to share with others.  Creating the New destination management back, click on the New… button.
5. Complete the first page of the Create a Management Pack dialog as shown below with the name: SQL Server 2008 Customizations.
   
6. Click the Next button to go to the Knowledge Article page. You can leave this blank for now. This page allows you to provide information to the SCOM operator/DBA as to the purpose of the MP.  Click the Create button to complete the wizard. You should now see “SQL Server 2008 Customizations” as the destination management pack in the Overrides Properties dialog.
7. Depending on your service level agreement for your application, you will want to set override properties to values that are appropriate for your systems. For example, for systems that are in production, you hope that you tested any new applications to avoid the blocking problems in the first place, so the default Interval (sec) collection value of 300 makes sense. If you have a shop of cowboy coders slinging code into production, you might want to set the value to 30. For demonstration purposes, I’ve used the settings shown below.
   
8. Click the OK button and then switch over to your Monitoring workspace. After 30 seconds, navigate to the Computers node under Microsoft SQL Server and if you are impatient, press F5 to refresh. If all goes well, you should see the following Critical alert.
   
9. I know and you know that the Critical state was caused by the blocking session scenario, but the way you can tell for sure is to double click on the Critical state cell to display the Health Explorer for the computer as shown below.
   
   You can select the critical leaf node “Blocking Sessions” to see the knowledge article for the problem. If you click on the State Change Events tab, you can see when the last time a state transition occurred.

Back to SSMS Activity Monitor

At this point, you need to go back to SSMS to kill the offending SPID using Activity Monitor. SCOM provides an easy way to get to SSMS by going to the Database Engines node under the Server Roles for Microsoft SQL Server. You can then click on SQL Management Studio in the Actions pane as shown below.

To launch SSMS with the proper connection context, select the instance path with the Database Engines list and then click on the SQL Management Studio action indicated by the big red arrows above. Then, launch the Activity Monitor, select the Head Blocker, and kill the session. Problem solved!

Summary

Now you know how to:

• Create a custom management pack (attached to this blog post as “SQL Server 2008 Customizations.xml”). You can save yourself the trouble of using the dialogs by downloading this file to your SCOM machine, going to the Administration workspace, clicking on the “Import Management Packs…” command in the Actions pane, and selecting the XML file.
• Override a monitor that is disabled by default. You can also edit any of the enabled monitors the same way. Again, be sure to use the custom management pack vs. the Default Management pack
• Troubleshoot a blocking session problem using SCOM 2007 and SSMS – as I like to say – better together.

To learn more about the Blocking Sessions monitor and other monitors in the SQL Server Monitoring Management Pack, be sure to Download and read the SQLServerMPGuide.doc file.