SQL Server performance tuning is usually left until problems arise. Most of the time developers and even database administrators only focus on the tuning elements of their MSSQL servers when performance starts to become slow and adversely affects users. Although the typical reason for slow issues is configuration or development issues (poor stored procedure design), occasionally hardware can be an issue.
Your SQL server can be constantly slammed with requests especially if your business is ecommerce. You can have internal and external elements requesting SQL servers to deliver at high performance. It is important to keep the SQL Server with the best, fastest, and highest quality hardware available to continue catering to a growing customer base.
CPU
Microsoft SQL Server takes full advantage of multiple CPUs. To determine your high end usage of multiple CPUs, determine the spillover to the other CPUs through task manager monitoring systems. This will allow you to see how the workload is being distributed to the multiple processors to decide the need of CPU performance.
Memory
Memory usage is dependent on your version of Microsoft SQL Server. If you have an older version of MSSQL such as Microsoft SQL 2000, then the database system can only take advantage of 2GB of memory. Microsoft SQL 2005 takes advantage of the operating system’s memory usage, so there is a great advantage to upgrade to SQL 2005 if there is a scalability issue. Microsoft SQL 2005 running on a more recent operating system can greatly increase performance issues with only an upgrade in the database software.
Hard Disk
It is no question that a fast, high storage hard disk is best for a Microsoft SQL Server. For small scale databases, most of the calls will be stored in memory, so disk performance is mainly insignificant. For higher end databases with peak calls, a hard disk with top of the line I/O will help increase performance during the times when database calls are at its prime.
Disk space depends on the size of the database. High end ecommerce sites can have databases that expand exponentially with products, customers, and orders continually growing into gigs of information. Scalability is important when choosing hard disk space since bringing down database servers to expand disk space can be an extremely costly effort. Large hard disks partitioned properly can help increase performance and last a considerable amount of time before an upgrade is needed.
Whenever you decide to build an efficient MSSQL server, performance and scalability should always be a prime concern. Although the newest technology may not be necessary, always maintain the best performance possible to eliminate the possibility of lost revenue due to poor performance.