Thursday, May 20, 2010

Performance Advisor Tip for DBAs Managing SharePoint Databases

I’ve had this come up a few times, and I’ve seen it in person with our testing environment, so I thought I would post a quick tip about it here.

Folks that have been working with SharePoint databases for a while probably know that the search index crawling done by SharePoint services can really kick out a crazy amount of deadlocks. In normal applications this would be a huge red flag, but for SharePoint it’s considered ok. In fact, in this SharePoint performance white paper it is actually stated that “An acceptable number of deadlocks is below one per second”.

So, for users of SQL Sentry Performance Advisor who want to be notified of deadlocks, but also have one or more SharePoint installations living in their monitored environment, how do you weed out the SharePoint deadlocks from the “OH NOZ! MAH REPORTZ WUZ TEH VICTIMZ” deadlocks?

There are actually a couple of ways to do this, but I am going to set it up the easiest way configuring it it globally, which will apply to all of the SQL Server instances being monitored by my SQL Sentry installation.

The first thing we want to do is to select the “Devices” node in the Navigator pane. Which sets our conditions and actions lists to the global context, so any changes we make will apply to everything.

image 

Next we want to make sure that “send Email” and “Send Page” are disabled for the “SQL Server: Deadlock” condition. This condition is met any time a deadlock is detected, so we want it turned off, otherwise we’ll still be notified about the SharePoint deadlocks. You can leave the “Log To Database” action enabled here if you like so that you can look at them later in the SQL Sentry logs. The point here is just to make sure we’re not getting notified through email or otherwise about every single deadlock. If you can’t find the General Actions tab, just select it from the View menu at the top of the console application.

image

Now, we still need a way to be notified of the *bad* deadlocks, so what we want is the “SQL Server: Deadlock: Output Content Match” condition. This condition is met when any combination of text values either exists and/or doesn’t exist within the output of an event. For a deadlock, the entire XML definition of the deadlock is included in the output. This means that any text value included in that XML can be used to make sure this condition is either met or not met. What we’re going to do, is set the properties of this condition so that the name(s) of the SharePoint database(s) must not exist in our deadlocks. This is done in a few simple steps.

1.) Enable the “send email” action:
image

2.) Edit the Output Content Match properties (Ignore “Step Name” as it doesn’t apply to deadlocks, or at the global condition level):
image 

3.) Select a recipient for the notifications:
image

Now, we should only get emails about deadlocks from the databases that should *not* be generating them on a regular basis. It’s that simple.

Until next time,

-JRH

1 comment:

  1. Hey Jason, great post! Quick question (possibly silly question). Does this apply to older versions of SQL Sentry as well?

    Thanks! :-)
    SQLPrincess

    ReplyDelete