Planning Server Resource Requirements

There is no formula for this difficult task. You should consider the following:

• If there are any case studies, perhaps you have similar systems in your own organisation
• Consider things at a business level, rather than a server level (ie ‘How long does an order take to save’ rather than ‘How long does an I/O operation take?’)
• Simulate a database of the same size as the final, operational one
• Monitor things after the system goes live.

Planning CPU Requirements

• You should have as many CPUs as possible.
• Anything over 30% will lead to slow systems. Peaks above this are only a problem if they are sustained.
• Any server running production based SQL Server should not have any other major services running

Planning Memory Requirements

SQL Server uses memory while queries are being executed. Each user executing a query needs a separate memory space known as an execution context. A lack of memory can impact a system in such a way that it appears to be a lack of CPU or I/O.

Planning Network Requirements

• Consider the use of multiple network adapters
• Most low-level network protocols (ie Ethernet) become unusable quite quickly
• Remember things like backups cause high volumes of network I/O, which can slow down connections
• Database mirroring and replication might need dedicated connections to perform correctly

Planning storage and I/O Requirements

In larger organisations, the following will be determined by a team of people, each with their own skillset.

• Determine requirements of the application
• Determine the style of storage used (SAN vs DAS).
• DAS (direct attached storage) generates better, more predictable results. For DAS storage, RAID levels 1,5 and 10 are the most common in use on SQL Server systems.
• For most systems, the number of drives/spindles will matter more than the size of the disk. Often, a single large disk will not provide sufficient I/O operations per second or enough data throughput (MB/sec) to be workable.
• Write caches can substantially improve SQL Server I/O performance, but make sure hardware caches guarantee a write, even after a system failure. Many drive write caches cannot survive failures and can lead to database corruptions.

Stress testing (SQLIOSIM)

A common problem occurring in database deployments is when the server platform is delivered and installed and immediately commissioned once SQL Server is installed on it. There are many subtle I/O problems that can occur and can be hard to evaluate.
Pre-installation stress-testing can help using tools such as SQLIOSIM


Further reading: NUMA, SAN vs DAS storage, SSD based systems, SQLIOSIM