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

Sunday, May 9, 2010

ASP.NET 4: Could’ve used that…

At SQL Sentry HQ we all tend to wear a few different hats. It’s important to make sure every base is covered regardless of who is or is not available.

That said, besides doing customer support and a portion of product development, I am also generally responsible for development work on internal systems and a good portion of our web site. To be specific in the context of this post, I do a fair amount of work on our forum (forum.sqlsentry.net) and our customer portal (www.sqlsentry.net/portal).

As with any web site, pages move, URIs change over time, and browsers have to be redirected to avoid the dreaded 404.

HTTP 1.1 has supported the permanent redirect status (301) with the location header for some time. This is a redirect so that (if the browser understands it) the next time the old URI is encountered the round trip to the server is avoided saving some resources on both sides and in-between.

Prior to ASP.NET 4 if you wanted to do this you had to set the location header and status on the request like below:

//Response status
Response.Status = "301 Moved Permanently";
//New location header
Response.AddHeader("Location","http://the.new.location");

Then you end the response etc. and this works out fine. It’s just a pain to have to include this in some utility class, and it’s really not that intuitive either. You have to know that you want to do exactly this. It’s not something that you can happen across by studying the Response object’s members in general.

Well, today I was just doing some personal skills maintenance, and I saw that in ASP.NET 4 they’ve added a new Response method called RedirectPermanent. It issues the 301 response as outlined above instead of 302 (temporarily moved) which is sent by the regular Redirect method.

It’s simple to use, it’s just one line as below:

//Redirect permanently
Response.RedirectPermanent(http://the.new.location);

You can use a relative path in there too even though the RFC technically doesn’t support it. You can also specify to end the response immediately as a second parameter.

Anyway, it doesn’t solve world peace and it’s not even a compelling reason to go out and rewrite a bunch of code, but like I said in the title, I could’ve used that! :o)

Until next time,

-JRH