This article will discuss some of the point to consider when capacity planning and managing the Capacity of SQL Server systems (Capacity Planning). It is not a definitive guide but hopefully it does provide a starting point for the SQL Server DBA.
Principles of Capacity Planning
Microsoft�s SQL Server Operations guide identifies the following key points of capacity planning:
Although the operations guide was initially written for SQL Server 2000 much of it is still valid on the 2005 version of the product.
- Be familiar with your system's use of hardware resources
- Maintain a tangible record of your system's performance over time
- Use this information to plan for the future hardware needs or software projects required
The complexity of Capacity planning really does depend on the complexity of the system you are implementing. It is true to say that capacity planning becomes really important when expanding the system has high costs involved. That said it does not need to be a complicated process but it will require numeric precision and be fully documented for future reference.
The capacity planning process should tell you how much hardware you need to support a specific load on your system, it is important to stress here that this will be an iterative process and the numeric result gained will influence your decisions, as these figures change, your hardware configuration has the potential to change too.
This section will look at planning for your CPU(s) and is fairly straight forward. Using a tool like Perf Mon (which ships with Windows) or possibly Idera, if you are prepared to pay a little more, you need to monitor your current CPU utilisation (\\\\Processor(_Total)\\\\% Processor Time). If the average value of this counter is over 50 percent, or if you have frequent spikes usage where CPU utilisation climbs above 90 percent then you should consider adding either additional processor(s) or faster processors.
In general, the processors you choose should be able to deliver the speed implied in your other hardware items. If your system is highly specialized and filled with processor-intensive activities, you will become aware of that as you observe the system over time.
SSIS (Integration Services) and advanced calculations are examples of such activities. SQL Server itself is a CPU intensive application so CPU�s with high speed cache will help performance. A rule of thumb when it comes to your CPU �When it come to the processor always get the fastest and newest� a slow processor can be bottleneck for the whole system.
If you have a dedicated SQL Server computer then use all of the computers processors for SQL Server. If your computer runs other applications in addition to SQL Server then consider restricting SQL Server from using one or more processors. SQL Server can be resource intensive and the other applications could suffer as result.
Memory is used mainly to optimise data access. SQL Server uses memory to store execution plans, store data pages etc. Without enough memory, you will incur more disk I/O in reading data. If your system does many reads, you might reduce disk I/O by significantly increasing your memory, because the data will then remain in cache. Insufficient memory, or over-allocation of memory, can result in paging. Memory plays an important role in SQL Server, and it is a resource you should carefully monitor.
For those systems where Reads (OLAP) is the most frequent activity and also the highest priority the more memory your system has the greater the performance. Memory can be used to compensate for disk I/O in these systems, and large amounts of memory can significantly decrease the number of disks (spindles) you will need to achieve high performance.
For systems for which writes are the highest priority (or OLTP), memory is still an important part of the system, but you may benefit more from the addition of disk spindles, and more or faster controller channels, rather than memory. It is important that you carefully monitor your system to decide which resources are in highest demand.
Microsoft SQL Server uses Microsoft Windows operating system input/output (I/O) calls to perform read and write operations on your disk. SQL Server manages when and how disk I/O is performed, but the Windows operating system performs the underlying I/O operations. The I/O subsystem includes the system bus, disk controller cards, disks, tape drives, CD-ROM drive, and many other I/O devices. Disk I/O is frequently the cause of bottlenecks in a system.When planning your hardware and disk configuration it is important to remember that the number of disks is far more important than the total storage size. Generally the more disks you have the better the performance will be. As an example if you have 20GB spread over disks, performance will be far better than 20GB spread over a single disk.
Database file placement can have a significant impact on I/O on your server. If you have a set of tables that is used frequently then consider putting these on a separate file group on separate physical drives, if you do this on large heavily used systems then you may notice a significant difference.
If you identify I/O as a problem but you are unable to add further spindles to a set of disks considering putting your non-clustered indexes in a separate file group on a separate disk.
Depending on how quickly you expect your database to grow you may want consider the following rules when purchasing hardware, these are recommended as best practise by Microsoft, and they are basically common sense:
It is important to size your database appropriately at the outset. This can avoid significant performance overhead when the database needs to grow. Ideally your database will be sized appropriately for the next 6 to 12 months. I�m not an advocate of the auto-grow function of SQL Server, I feel that the database size is best managed manually, thus minimising any overhead caused by this process.
- If you expect the database to grow suddenly, buy hard that can be added to. Thus allowing you to expand as needed
- If you expect your growth to be minimal then buy what you need.
This article has discussed some of the main principles in SQL Server capacity planning including CPU, Memory, I/O and disk configuration and database file placement, this article is not a definitive guide but hopefully it does provide a starting point.
Capacity Planning - Memory Management