Capacity Planning – by Greg Larsen (B|T)

I can file this chapter with many of the others under, “Things I wish I had read earlier in my career”. A good part of that career was at a small company and we were able to gauge our database capacity by the number of clients we had with pretty good results. This worked well because as we added more clients we had capital to add more database resources. Until…we wanted to add a very large number of clients all at once. People came to me for estimates on what we would need to handle the sudden jump and I could give pretty good estimates for disk space, but I had not been tracking our capacity for other things like memory, CPU and I/O.I knew the  infrastructure we had would not be up to the task, but I didn’t have the information to back it up. Greg Larsen’s chapter in Deep Dives not only explains why you need to have these metrics, but he provides examples on which specific counters you should start looking at and how to gather them.

You won’t believe how quickly management will authorize the purchasing of a new machine when you produce a nice colorful graph showing how the various performance metrics are getting close to reaching your machine’s theoretical hardware limits. – Chapter 9, SQL Server MVP Deep Dives 2 

He begins by suggesting that the stats should be gathered weekly, but like many things this frequency depends on your unique environment. If you are growing rapidly then you may want to pull the stats more often. It is a balance though because you don’t want the measures to take up too many resources and add to the capacity planning.

The first metric he outlines is, logically, disk space. He provides a great script for gathering both allocated and used file space for all of the databases on the server. He uses the undocumented proc, sp_MS_foreachdb,  that I hadn’t seen before and runs DBCC showfilestats on each database to gather the data file storage.

Next he shows how to use the sys.dm_os_performance_counters DMV to gather information on the page life expectancy (PLE). He also takes the time to explain the different counter types, point-in-time, per-second, and ratio counters.

Mr. Larsen mentions that configurations with non-uniform memory access (NUMA)  may skew some of the counters. I wasn’t sure what this meant so I did some looking and found a post by Paul Randal that went into this a little deeper. It sounds like if you are configured to use NUMA then PLE may not be your best metric. PLE was a great example for me though because it made sense. The number equates to how long in seconds a page is expected to live in memory.

Next there is a script to gather reads per second from dm_os_performance_counters. He explains that you need to capture two points in time and subtract the values because a per second counter is actually the total number of counter items since the SQL Server was started.

The last counter that Mr. Larsen outlines is calculating the BufferCacheHitRatio. This is done by dividing the Buffer cache hit ratio counter by the Buffer cache hit ratio base counter. Ratio counters on dm_os_performance_counters will always have this base value to use for calculating.

As with many of these chapters this is an area I know a little bit about and wish I knew more when I was managing the operational side of SQL Server.

Chapter Nine SQL Server MVP:

Greg Larsen (B|T) has been working with SQL Server since version 6.5. He works for the state of Washington and does part-time consulting for DesignMind, a San Francisco–based consulting company. Greg has written over 150 articles on topics related to SQL Server. He runs a website that contains numerous SQL Server example scripts at www.sqlserverexamples.com. Greg is also an adjunct professor at South Puget Sound Community College. He has been an MVP since 2009. In his spare time, he races and cruises on his sailboat in and around the Pacific Northwest.

If you have the capacity to read SQL Server MVP Deep Dives Volume 2 you can get a copy at Manning Books.  I don’t get anything out of it if you buy the book, but you might.