Table of Contents
- Microsoft SQL Server Defined
- Microsoft SQL Server Features
- Microsoft SQL Server Administration
- Microsoft SQL Server Programming
- Performance Tuning
- Practical Applications
- Becoming a DBA
- DBA Levels
- Becoming a Data Professional
- SQL Server Professional Development Plan, Part 1
- SQL Server Professional Development Plan, Part 2
- SQL Server Professional Development Plan, Part 3
- Evaluating Technical Options
- System Sizing
- Creating a Disaster Recovery Plan
- Anatomy of a Disaster (Response Plan)
- Database Troubleshooting
- Conducting an Effective Code Review
- Developing an Exit Strategy
- Data Retention Strategy
- Keeping Your DBA/Developer Job in Troubled Times
- The SQL Server Runbook
- Creating and Maintaining a SQL Server Configuration History, Part 1
- Creating and Maintaining a SQL Server Configuration History, Part 2
- Creating an Application Profile, Part 1
- Creating an Application Profile, Part 2
- How to Attend a Technical Conference
- Tips for Maximizing Your IT Budget This Year
- The Importance of Blue-Sky Planning
- Application Architecture Assessments
- Business Intelligence
- Tips and Troubleshooting
- Additional Resources
Last updated Mar 28, 2003.
It might seem strange to see a fairly technical subject like "System Sizing" in the Professional Development section of this guide. After all, I claim in the introduction to this section that it includes content related more to your career than purely technical subjects.
But even though system sizing has a great many technical aspects, it also includes components vital to any professional. Sizing a system for a particular use involves experience with various technologies, understanding modeling techniques, and taking the time to properly evaluate different solutions. Giving this effort a single concept, it's really all about thinking things through. It is those concepts I’ll focus on here.
So this tutorial actually has more application than just sizing a system for SQL Server, although that’s what I’ll focus on. It shows a methodology for any project planning exercise.
In the name of speed, too often the planning component isn't given proper attention. Companies pressure the IT staff to deliver a solution as if a complex system could be purchased "off the shelf." If you were to ask the same executives to design a cruise, they certainly wouldn't expect to do that with little research, off-the-shelf services, and no time to plan. But often that's exactly what happens on many IT projects.
Certainly, much planning is involved in the budget, preparation, and needs-analysis phases of a project, but often the decisions regarding the hardware and software that run the systems is left to the vendor.
In this article, I'll spend a little time on some of the methods you can use to help with the decisions you'll need to make. Two or three of the methods are fairly easy, so I’ll cover those first. The non-trivial way of properly sizing a system takes more time, so I’ll spend the bulk of the article there.
Ask the Vendor
The first place to start is with the vendor. If you’re putting in a new software solution, chances are the vendor of that software will have some sort of tool they use to plan your implementation. Press your salesperson to provide that. In fact, they should have done this as part of the sales process, since that should be part of the overall cost.
Compare with another Installation
If the vendor doesn’t have a ready-made tool to do the work for you, ask them about installations at other sites that are similar to yours and use that as a guide. If you can get them to allow it, get a contact at the IT department of that installation and ask them what they recommend based on their experience.
Keep in mind that they may not want to share this information. There is a danger to them that you take their advice, and then get mad at them when it doesn’t fit your situation exactly. This has happened to me — when I worked at a small firm selling a software solution, we provided guidance to help the client understand what they needed. But we had more than one occasion when a client’s staff would not do due diligence with the suggestion, implement a poorly-thought-out solution, and then scream to high heaven that it was our fault. As you can imagine, we soon stopped helping. So don’t be surprised if they push back on this.
But if you attend a user group (and you should) you can certainly ask there. You can also use other professional gatherings.
Substitute another Application
If all that fails, take a look at a similar application, either in function or layout to your own. Use the same techniques, finding out what that vendor recommends or talking with the owners of those systems.
There’s a danger here that you’ll be way off, but you can use this as a comparison and use the more complicated measures below to check your calculations.
Calculate the Variables
This last method is the most difficult, but if you don’t have any of the information the other methods provide, it is the most accurate. You’ll often have to use this method if you are creating your own application or implementing a solution for the first time anywhere.
You will need to go on an excursion of mapping out the variables involved in the equation — things like "how many users" and "how large is the average transaction". But since a complex system performs multiple functions, these kinds of questions don't show the full picture of system load and activity. Although you need to map this information out, there is more to consider.
There are basically three areas you're going to need to consider for proper system sizing. The first is purpose, the second is environment and the third is capacity. Since the key to large projects is breaking them up into smaller, more manageable units, let's examine each of the areas in turn. When we're through, you can use the same process to componentize your system.
The purpose of a proposed system encompasses all the functions the system is to provide. For instance, the functional breakdown of an Enterprise Resource Planning Application (ERP) includes many areas that can be treated as a unit, such as Finance, Accounting, HR, Supply Chain Management, Planning, and Customer Information. Each of these functional areas should be evaluated scientifically.
What that means is that you’re either going to have to get data from the vendor or another installation for these unit metrics, or measure them yourself. It isn’t as difficult as it sounds. I’ll walk you through the major steps you can take for this process. You’ll need to set up a test environment and then set the baseline of the activity on the system using the methods I’ve explained in this tutorial.
First, you’ll need to select what you’re going to measure. I create two columns right away:
- Workload — This column describes the type of work that is happening. For instance, let’s say you have a Purchase order that a user will enter into the system. I would define this column as “Purchase Order Entry”.
- Transaction — If the workload breaks down into smaller units, such as “Check PO Existence” and “Submit PO”, each transaction gets its own line. It’s up to you how much detail to include here — I normally just put things like “PO Creation” and “PO Alteration” until I find out I need more granularity.
After those columns are defined, I normally evaluate the following areas for each transaction:
- System Storage Load — This column measures how much change was there in the storage of a single unit. Did the database grow by a certain percentage or shrink?
- CPU — This shows the amount of CPU activity when the transaction takes place. Note that this measurement, like the rest in this list, won’t be accurate in the aggregate. SQL Server will devote all of its power to running one query, so that doesn’t mean if you run ten of them it will take ten times the amount. The rest will be optimized and “folded in”, so this is only a guide number.
- Memory — Same warnings as the CPU, but this one tracks memory use.
- IO — Same warnings as the CPU, but this one tracks the amount of hard drive use.
- Network — Same warnings as the CPU, but this one tracks the network traffic.
Next, you’ll need one or more tools to monitor the activity. I use a set of tools for my measurements, but in a pinch, you can use SQL Server Profiler to collect the majority of these. Only the complete network hit is a little harder to gather with Profiler.
I use Profiler for the CPU, Memory and IO measurements, but I use the Storage Vendor’s tools for measuring the hard drive statistics. I lean on the networking team to help me shoot the network line for the numbers for that column.
In the end, my spreadsheet or tables look something like this:
Storage Load CPU
Finally, you run a series of tests based on your workloads. The better the workloads and the more concurrent tests you run, the better your numbers will be.
Taking the “raw” scores of each transaction, you can estimate the number of each type of workload per day, and then extrapolate that out to a year. You should then plan for 4-5 years of use with the current configuration, and add at least 10% to the overall yearly number. That will give you a good starting point.
Along the way, it’s important to monitor your system and compare it against this model. It will help you get more accurate as you do this over and over.
Using a systematic methodology helps the executives in your firm trust the project, and your implementation. It also provides a scientific basis for the systems, rather than the typical "buy what you can afford" approach. I've found that CEO's don't mind paying for the right system, if you can prove that you need it.
InformIT Articles and Sample Chapters
The same concepts hold true for Oracle as for SQL Server. Read Planning an Oracle Database.
Books and eBooks
To see a system sizing exercise, you can find a pretty good one from the state of New Hampshire here. This is a fairly well thought out report, although it doesn't contain quite as many metrics as I've covered here.