articles >> performance tuning >> SQL Server Hardware Resource Planning

SQL Server Hardware Resource Planning

By : Robert H. Chin Sep 05, 2004

There is a growing demand for high performing, fast response applications in today’s industry. In many organizations, a SQL Server 2000 server may be dedicated for the use of one particular application. From the ground up, analyzing and determining hardware requirements best suited for individual specific applications may help these application provide the timely response your end-users may be looking for.

This article is intended to serve as a guideline when planning for hardware requirements for SQL Server-enabled applications. Though hardware requirements may vary depending on the use and need of the application, the following guideline may help you assess some of these requirements.

Phase I: Questions

To best determine hardware requirements for SQL Server, one must first ask some of these questions:

• What is the current, if applicable, size of the database(s)? Or, what will the initial size of the database(s) be? The size of the database can determine the number and size of disks drive you would need to accommodate for your database(s).
  
  
• What will the projected growth of the database(s) be over a fixed period of time? Knowledge of the projected database growth puts you in a position to plan for disk space requirements for the data and the retention of its associated backups.
  
  
• Will SQL Server be configured with more than one instance (for SQL Server 2000)? Additional instance(s) will require more resources from your server. The number of instances to be deployed on your server will help you identify additional resource planning and allocation for your SQL Server such as, an additional CPU and/or a faster CPU, more physical memory, additional disk drives, possibly a RAID storage array, and so on.
  
  
• How and who will be accessing this application? Identify the users. Will they be internal to the organization or external? Will the users be accessing your SQL Server via a client/server application or through a web browser? Knowledge of this information will put you in a position to determine network and other system requirements such as, a faster network interface card, additional physical memory, etc.
  
  
• Will this application participate in replication? As a publisher, distributor, or subscriber, replication requires additional system resources. Things to look for include: additional disk space to accommodate for the distribution database, a fast network interface card, more physical memory, etc.
  
  
• Will this application participate in failover clustering? Clustering your SQL Servers for high availability will require additional hardware, such as RAID storage array for shared disk resources, ensuring each server in the cluster is configured with the same amount of physical memory, SCSI, or Fibre-Channel Switches, etc.
  
  
• How will the SQL Server be primarily used? Primarily fetching records with little or no updates, primarily updating data with significant number of record fetches, etc. The performance on the amount and type of activity on your SQL Server can be improved if your hardware is optimized. For instance, if an application requires SQL Server to read records frequently and the users complain of a sluggish response, you may then want to consider distributing your data across many physical disks. (The response time can be improved through appropriate indexing as well, but we won’t get into that for this article). Data distribution will require additional disks, which can be configured to stripe your data. Also, having high-speed RPM (revolutions per minute) disks can boost SQL Server performance for I/O activities.
  
  
• What are the requirements for locally stored backup (i.e., how many backups will reside locally on the server and for how long?) If you plan to store backups of your database locally on the server, you will need to consider how many backups you’d like to keep and for how long before you can safely get rid of them or archive them?

Answers to the above questions will help you in identifying appropriate hardware needs for a particular application.

Phase II: RAIDing the Options

Though this article does not cover all aspects for analyzing hardware requirements, it provides some insight on options and configurations that may help boost your SQL Server’s performance.

To achieve a fault-tolerant system combined with reliability and fast recoverability, the first guideline is to have the data distributed as much as possible. The more distributed the data, the less chance of bottlenecks on a single device. Look into placing your data on a RAID 5 array, and if possible from a cost perspective, place your data on a RAID 10 array. Both options distribute your data across a number of disks providing a high read data transfer rate. The number of disks required for either of these two options will depend on the size of the database and its growth factor over a fixed period of time.

Placing the log files on a separate disk (dedicated disk, if possible) can also boost performance. Look into placing your log files on a RAID 1 array. Doing so gives SQL Server the leverage to utilize all disks for data and log files in parallel, thus improving performance.

If application performance is more important than cost of additional disks, look into placing the tempdb database on its own separate physical disk, preferably on a RAID 0 array (RAID 5 for fault tolerance).

As a minimum approach to this guideline, consider a server with seven hot-swappable disk drives configured as:

• RAID 1 (2 drives, Mirrored) for Operating System
• RAID 5 (3 drives, Stripe Set with Parity) for SQL Server Data files
• RAID 1 (2 drives, Mirrored) for SQL Server Log files

Phase III: Other Options

Consider getting the maximum amount of physical memory, as per vendor hardware specifications and the version of SQL Server you plan to install. Doing so reduces the need for moving data into and out of the disk cache as often, boosting performance.

There are many other options that can play vital roles in determining the performance of your SQL Server. These options include considerations for fast RPM disks, using filegroups to distribute your data, BUS architecture, type of network interface card, a fast disk controller, etc.

Summary

To summarize, for SQL Server to function effectively and efficiently, you must consider getting the appropriate hardware based on your application requirements. First, talk to the application vendor, developer, and other support personnel, if possible, to best determine their suggestions for hardware requirements optimized for SQL Server. Second, investigate business unit and end-user requirements for data retention period. Third, analyze application’s use of SQL Server. And finally, put all these requirements together and draw up a plan as a minimum hardware requirement and another one as a nice-to-have hardware requirement.

 

Robert Chin works as a SQL Server DBA for a large financial institution in Toronto, Canada. He has extensive experience working with SQL Server versions 6.5, 7.0, and 2000.