Friday, July 30, 2010

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:

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,

Tuesday, July 27, 2010

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.


Wednesday, July 7, 2010

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,