What Happened There? Post #1

This is the first post in a series. I’m going to do some different things to a test instance of SQL Server, show you what it looks like in SQL Sentry Performance Advisor, then explain what I did and why Performance Advisor looks that way when it happens.

This should be a great series for anyone using SQL Sentry Performance Advisor day to day. If you’ve ever looked at those performance metrics and thought “what caused that?” it could be revealed in one of these posts.

The systems used here are basically “nothing special” VMs running on Hyper-V in a Windows 2008 R2 cluster. I’m sticking with SQL Server 2008 R2, but generally most things will be relevant to 2005 and some to 2000. I will try to keep things as close as possible to something that might really happen in a production environment, but do keep in mind that it is in a controlled test environment, and you may see some things that you know yourself would really (almost) never happen.

With the intro out of the way, let’s take a look at the Performance Advisor dashboard in history mode for today’s “What happened there?”.

Notice in the above screenshot I have circled the “SQL Server Memory” chart. That is really for the benefit of focusing in on that area for this post. Many may notice that this server has a lot of other scary things going on, but this time we’re focusing on SQL Server memory.

What we see in the chart is that memory being used by both the buffer cache and the object cache took a steep dive at about 9:13AM. Then again at around 9:15AM.

There are different things that can cause SQL Server to dump cache. You see it a little when memory pressure exists and things are constantly being flushed out to make room. There are other symptoms that go along with that like higher lazy writes and a low Page Life Expectancy (PLE), but in this case (even though there is normally memory pressure on this server) we see a more drastic immediate change.

Normally when there is a cache dump this large you can count on some pretty nasty behavior from the SQL Server. If it had gone all the way to zero I might even think the SQL Server services had crashed or been restarted, but in this case there is a perfectly logical, and safe (sort of) explanation.

So what happened there? This is a test server, and generally before testing the performance implications of any potential change we dump the cache on purpose in order to get more accurate results.

So, in this case the dramatic change in memory used by SQL Server is just the result of my running the following statements:

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

Oh, and yes, I did it twice just for good measure which explains the second dip at 9:15AM.

Now I have to go tell the QA team why I’m messing with their test boxes.

Until next time,

-JRH

When Is an Administrator NOT a Sysadmin?

When you are working on a default install of SQL Server 2008, that’s when!

In the past, when you installed SQL Server the local administrators group was automatically granted SA privileges. With SQL Server 2008 that is no longer the case. I think this is in general a good move and forces us to be more aware of who can do crazy things to our database servers, but it’s also not overly obvious that this change was made until someone gets "access denied” or “login failed for…”.

With a tool like SQL Sentry that needs a certain level of access, it’s important to keep in mind that during or after installing a new instance of SQL Server 2008 or greater you will need to grant the service account the necessary privileges. This is the case even if the account is already a local administrator or even a domain administrator.

I’ve posted this to our KB as well here.

-JRH

Wild Blackberries!

This is a bit off topic, but I couldn’t help myself.

For anyone that grew up in rural North Carolina one of the greatest things about mid-summer to early fall has to be the wild blackberries. The Charlotte, NC suburbs are not quite in the mountains, and not quite off of them either so they are the prime growing area for blackberry bushes. You can find them just about anywhere from the local park to the side of the road or even the parking lot at your local grocery store.

Our offices were moved toward the end of last year, and in the new location there is a nice path that we can go out for a walk on and get some exercise at lunch time (I certainly need it). We’ve been walking out there for a while, watching patiently as the berries all along the side went from green to red and now finally to black.

Recently, Steve (Twitter) and I decided we would grab a bucket and pick some berries during our walk. The last time I did this I may have been 11 or 12. For me, picking berries takes me back to a time when the only thing I was worried about was how many I needed to have mom bake a pie or cobbler (or both…). Oddly I don’t remember having worried about snakes, thorns and ticks as much back then.

Maybe the perceived element of danger will make the cobbler that much sweeter this time (assuming I can figure out how to make one, or beg my wife to make it).

(Not an actual picture of our blackberry bushes, but they look the same.
Ours are bigger and yummier though.
They also have less berries since Steve and I took them.)

Bet Regards,
-JRH

Right Click!

This is more of a tip to those who are already using or evaluating SQL Sentry than anything else.

I get a lot of questions that are perfectly valid about where different features are located or accessed from. SQL Sentry is, after all, a large suite of products with many features and if you are running multiple products you have quite a few feature choices.

Usually it’s a specific question such as “where do I find…” or “how do I filter…” to which I provide as simple and direct an answer as possible.

The reason for this post is to make folks aware of something that isn’t overly obvious, but maybe should be. If you are familiar with the development work we do here and the amount of thought we put into usability, it is just logical, but alas our society as a whole is just not used to having software that flows from feature to feature in ways that make sense or that make it easy for us to use.

SQL Sentry’s console application is designed for DBAs by DBAs. That is not just marketing fluff. It is 100% true. The thoughts and ideas that go into building SQL Sentry software all come from our internal product staff, all of whom are experienced developers and DBAs and from our customers who often have great ideas for ways they would like to use SQL Sentry products.

All of this background leads me to my point. We are very big on ease of access, just like our customer base. Since getting around to different places in the console usually requires using a mouse, a very logical place to put access to features is in the various context menus, or, the right click menus. I invite everyone to simply right click on anything in SQL Sentry. Whether it be a connection node icon, a history event on the calendar, a column header on a data grid, a runtime graph for an Agent job, an Event Chain Node or even the history mode chart for database file I/O you will always find something useful to do next by simply right clicking.

In summary, if you are ever in the console trying to work out how to do something, just try right clicking on whatever it is you are looking at. You may be pleasantly surprised to discover a set of useful options organized in a logical manner, right where you would expect them to be.

-JRH

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.

 

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.

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:

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

3.) Select a recipient for the notifications:

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

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

SQL Saturday #30: Richmond Virginia

With all the excitement of my first trade show last week, and the mountain of catch-up work waiting when I got back, I forgot to take a moment to talk about SQL Saturday #30 in Richmond, VA. I had the opportunity to deliver my session on “Things You Mom Never Told You About SSMS”, man the SQL Sentry sponsor table and Sit in on a great session about table partitioning called “Loading Data In Real Time” by Mike Femenella.

This was a well organized event, with a great turnout. The folks that attended my session were all very attentive, had some good questions and even had some things to add during and at the end. The event was originally scheduled for January, but wintery weather forced a reschedule to April 10th. There was a great turn out despite that setback, and you really couldn’t tell there had been a setback at all.

In this case, Steve Wright (Twitter) and I got a late start out of Huntersville, NC on Friday, April 9th, and we needed to get back quickly to catch the plane to Las Vegas for SQLConnections Saturday evening. These two circumstances caused us to miss the chance to socialize at the speaker’s dinner and after the event, but I really enjoyed the time we did have there.

My hat is off to Jessica Moss (Blog | Twitter) and her team for a job well done. I am already looking forward to the Richmond, VA event next year.

Until Next Time,

-JRH