Tag: SQL University

This week at SQL University we are talking about capacity planning. I am the guest lecturer for this topic, and covered a broad overview of what capacity planning means for DBAs to start the week. In this installment, we will look specifically at disk requirements and how to plan for them.

Old Disk Capacity Analysis

The traditional methods of sizing a database has been to compute the row size for each row, multiply by the number of rows, and then add some growth factor for the next xx months along with a bit of pad. This is an easy formula for each table.

TS = P ( G ( RS * Re) )

where

• TS – Table size
• RS – Row size (bytes)
• Re – Number of rows, estimated
• G – Growth factor, as a percentage. This can be a weekly or monthly growth.
• P – Percentage pad of the growth. I have often used 15%, estimated as 1.15.

If you sum this up for all tables, you can easily determine the size you need for your database.

However there is one factor in there that tends to throw the calculations off. The estimates you make, both in growth, and in the pad, can dramatically throw off your calculations.

There is also the fact that not all tables grow at the same rate. The growth rate for your Orders table probably is larger than that of your Customers table. You hope that each Customer places more than one order, so while each should grow, the Orders table should grow at a faster rate. Determining the growth rate for each table can be a cumbersome task.

I used to have a system in place that counted the rows in every table in my database every night. We stored this number and calculated a monthly average and growth rate, and this allowed me to plan for disk usage, as well as understand the different growth rates of tables. Popular software packages like SAP or Dynamics have a fairly well known rate of growth for each table in their system and that can help you to determine how quickly each table will grow.

This formula is only as good as your estimates, and while it is a very accurate way to determine disk capacity needs in the future, it is also overly complex in my opinion.

Keep It Simple

One of the guiding principles that I have tried to use in my career is to keep things as simple as you can. Not that everything is simple, but I try to keep it as simple as it can be.  Don’t add unnecessary complexity if you don’t need it.

Over time I observed a few things in the databases I managed. One was that relatively few tables in a database contribute heavily to the disk usage of that system. There are often a few tables like order tables, fact tables (in warehouses) auditing tables, that use up most of the disk space. Lookup tables tend to use trivial amounts of space (relatively) and other tables tend to get dwarfed in size relative to your few large tables.

I also noticed another correlation in my database disk usage. The larger the backup size, the more disk space I needed for my database. Since a backup contains mostly data (and some log records), it represents a fairly good estimate of the size of you data on disk. Since you also need to plan for backup space on your server, I found this formula to be very accurate for me.

TS = d ( BS) + BS + p (BS)

where

• TS – Total data size on disk
• d – days of full backups to keep on disk
• BS – Full backup size
• p = percentage pad of data size to keep

Here I calculate my total data size on disk as being the number of full backups I keep multiplied by the backup size. I then add in my database by itself (the single BS in the middle) and finally a pad percentage of this size.

What is even better is that each SQL Server already tracks backup size in msdb.dbo.backupset in the backup_size column. If you have been running backups on your server, you can calculate the average and the growth of this number over time. That will easily allow you to plan for future space needs.

It’s simple, and an easy way to track disk requirements over time. I would recommend storing this data elsewhere as the msdb table periodically get cleaned out, and in the event of a DR situation, you still need this data to be restored on your new server.

A Side Benefit

I would also recommend tracking this on a daily basis, and if it grows an extraordinary amount over one day, you can investigate to see if something has fundamentally changed in the way your database is used, or if this was an unusual event. My rule of thumb was if a backup grew more than 15% of what I expected, I should investigate.

This has alerted me to new development changes, the addition of new customer or partner data being loaded regularly, along with various other changes in my systems. This has allowed me to better manage growth and plan for new disk needs in advance of actually needing them.

This is 15% in unexpected growth, not 15% growth. If I expect 2% growth in my backup file each data, then if I received 17% (15% unplanned + 2% planned growth) in one day, I would investigate to determine what had happened on that database.

Gotchas

There are a fwe issues with this method that you need to be aware of, and perhaps plan for separately. The first is dealing with partitions/filegroups, the second multiple disks, and the third is log space.

I have typically worked on databases that had one filegroup. In this situation. That is a very simple server, but as data sizes grow, and more people take advantage of partition features in SQL Server, you might easily end up with very different sized partitions or filegoups on different disks. If your data tends to distribute fairly evenly, you can still use disk backup sizes to guesstimate future needs. However if you have tables growing at different rates, you will need to track the size of each partition or filegroup separately.

There is also the issue of planning across disks. A best practice dictates that backups are stored separate from the data, on separate physical disks. This means that while the calculations above are relatively simple, you need to be sure that each disk you use has enough space for its needs. Don’t assume that backups can use the space on you data drive or vice versa. As you track space used over time, it needs to be broken out by logical disk drive.

The last issue, which varies from server to server, is the capacity needed for your logs.  The amount of log space needed each day by a database varies with the actual load on that server. If you have very busy servers, you will generate more log records, and larger log backup sizes, than if you have less load. Unlike a full backup, whose size is similar on days when there is little activity (such as weekends for many businesses), log backup sizes can vary tremendously during low activity periods.

My approach with logs has been to guess at log size, or leave it at the current size, schedule log backups, and then calculate the total log backup size each day. This tells me roughly how much disk space I need for log backups each day, and I can multiply this by the number of days I am keeping to determine log backup space needed.

For the actual log size, there are numerous articles, but you essentially need space for your largest log backup plus some padding. This might be the space needed for a single log backup at 5:00pm that is twice the size of other log backups. You cannot take the total day’s size and divide it by the number of log backups. You need to account for the largest log backup, based on whatever interval you choose, and add some pad.

Note that your needs can change dramatically if you change the frequency of your log backups.

Padding

I have mentioned padding numerous times in this lecture, however I have not given concrete guidance. That is because the amount of pad will vary, depending on your tolerance for issues, and the time you want to devote to this topic.

If you only want to manage space once a year, you need a larger pad for your estimates than if you are willing to manage weekly. I prefer to proactively grow space on servers once a quarter. So I estimate (based on backup size) the usage for the next quarter, and then add in a 10% pad to that amount. If this is a critical server, or one with little history, I would increase that pad to 15% or 20% to ensure I do not have issues.

Note that while I don’t want to grow space more than once a quarter, I have processes in place to track space daily and I investigate any unusual growths immediately.

Conclusion

Planning for the disk capacity needed can be a complex and confusing task. It involves a lot of variables, and ultimately is a bit of a guess on the part of the administrator. As a result, I try to keep it as simple as I can and track at the highest (most gross) level possible, which has been the data size in full backups.

However you feel to track space, you should be doing it in some way and reviewing it periodically. I tend to manage space once a quarter, for all servers, proactively growing space as needed for each database. This requires that I have some idea of the past use so I can plan for the next quarter’s use, as well as be ready to order/allocate more as needed.

If you have comments, or would like to add your blog to this lecture, please leave a note and link below. I will add interesting blogs to this lecture as a separate section. Next time we will conclude this week with a look at other issues to consider when capacity planning.

I am honored to be a guest lecturer this week for SQL University. There have been some amazing professors helping guide you through SQL Server this semester, and I hope that this week meets your expectations.

The topic for this week is capacity planning. While not a critical task for DBAs on a regular basis, the failure to properly plan for the resources needed can easily become a critical problem at the worst possible time: when your systems are busiest.

What is Capacity Planning?

Capacity planning involves making estimates of the resources that will be needed in some area or system and then ensuring that you can acquire those resources to meet demand. The resources can be time, money, hardware, people, or just about anything that is used in your environment.

The traditional method of capacity planning deals with the resource requirements that your systems need. For SQL Server this has primarily been CPU, memory, and disk resources for each instance. While this seems to be a fairly benign and perhaps boring topic, it has enough moving parts that it quickly can overwhelm a DBA tasked with planning the resources needed and budgeting for them appropriately. There are other potential issues, and some of those will be discussed on Friday.

As our systems become more complex, include more interconnections between them, and become more important, the ability to properly plan for, and meet, the demand for your services is an important part of the data professional’s job. Even if you have server administrators, SAN administrators and other professionals responsible for physical hardware, if your database is not available or does not scale, you will be blamed.

Server Sizing

People new to managing database servers constantly ask the question “how big a server should I buy?” as if there is a standard size server for a particular number of users or databases. There isn’t one, and these factors don’t really apply to proper server sizing:

• number of users
• number of databases
• number of transactions
• number of records in your largest table

While these numbers do seem to increase as the load on your server increases, resulting in the need for more resources, these aren’t the way that you can size a particular server.

Ultimately for new systems you can only take a guess at what level of CPU, memory, and I/O you will need. Likely it will be a bad guess, and so most DBAs and server administrators try to buy the largest server hardware just in case database use is far beyond what they expected. Or they economize and wind up with a severely underpowered server and a large percentage of unhappy clients.

For existing systems, you should have some idea of the load being placed on your systems. From this load, you can extrapolate to make predictions about future loads and plan accordingly to meet the demand.

Baselining

The only really good way to properly size a system is to extrapolate future needs based on the past usage of the system. For existing databases, it helps to have a process set up that determines a baseline of not only the performance of your server, but also the capacity peaks and averages over time. These will help you extrapolate for future demand on the database.

For a brand new database, you can only take a guess. As good as your guess might be, you will still likely be wrong, but having a baseline process set up at the beginning can help you quickly decide by how much your estimates are wrong and adjust the hardware accordingly.

There are a number of articles written, and any number of product to help with the setting up of a baseline for your servers. The important thing to remember is that this is unique to each server, each company, and each database. The way that Microsoft Dynamics runs on your server instance will be different from mine, even if we have the same number of users. Don’t examine the scores from hardware manufacturers or software vendors as a way of determining what capacities you need to plan for. Those are useful for comparing systems in a theoretical way, but you need something more concrete for your instances.

You need an affordable baseline system that you can deploy to all your server instances. I have had good luck with home grown systems at most of the places I have worked. I have also used commercial off the shelf packages (COTS) with as BMC’s Patrol and HP’s Openview to record data, but often had to extract that data to run my own extrapolation calculations. In SQL Server 2008, Microsoft introduced the Management Data Warehouse, which does a lot of this monitoring for you as well, if you have Enterprise Edition. Technet has a performance baseline white paper that gives some good basic guidance on what to monitor in order to measure the performance of your system.

The specifics of how you gather and store this information are not that important. Use whatever method is convenient for you. The important thing is that you keep this data over time. You do not need extremely detailed information over time, but rather broad averages that allow you to determine if your resource requirements are increasing.

I used to track the performance at 5 minute intervals for all server instances across a month, and average that down to hour increments for the past year. This easily allowed me to extrapolate for future planning by loading the data into Excel and performing a linear regression analysis. This will produce a line graph that best fits your data points.

Third Party Tools

As mentioned, there are a number of products that can help you with baselining and measuring the performance of your systems. Any tool that measures the various Performance Monitoring counters can be used if you can store those measurements over time. What’s Up Gold, Unicenter, even SQL Response (from my employer, Red Gate Software) can help you determine a baseline for your system.

While it can be a relatively simple task to build a monitoring system on SQL Server, there are a lot of factors to consider, and building a new piece of software might not be the best use of your time. There are often other servers in your environment that also need capacity planning. File server, mail (Exchange) servers, and more. Ask your system administrators if they have a system in place, or software available that can measure the load of your servers over time. It might make sense to use a system already available or purchase one rather than building your own.

Moving Forward

That’s all we have for this lecture. Please feel free to chime in on the topic capacity planning and specifically any systems or software that you have preferred for tracking a baseline over time.

In the next installment of SQL University, we will examine disk capacity specifically as this is an area that most often administrators struggle with managing.

If you have other links, your own blog, or comments to add to this topic, please feel free to leave a note/link below.