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,