You have upgraded your hardware to new cutting edge technology server machine. Congratulations but do not forget that its the person on machine that matters more than machine. Its right time to tune your new machine to get most out of it. Poor tuning may lead to poor performance no matter how sophisticated or state of art is your server.
- Although heavy-duty hardware can help SQL Server’s performance, application and database design can play a greater part in overall performance than hardware. Keep this in mind, as throwing good money after bad on server hardware does not always fix SQL Server performance problems. Before getting faster hardware, be sure you have thoroughly tuned your applications, Transact-SQL, and database indexing.
- In many cases, adding RAM to a server is the cheapest and fastest way to boost hardware performance of a SQL Server. But before adding more RAM to a SQL Server, ensure first that it will be used by SQL Server. Adding more RAM doesn’t mean that SQL Server will always use it. If the current Buffer Hit Cache Ratio is consistently above 99% and you have well more than 10 MB of Available RAM, your server won’t benefit from adding additional RAM.
- If your SQL Server’s total CPU utilization is consistently above 80% or more, you need more CPUs, faster CPUs, or you need to find a way to reduce the load on the current server.
- If the Physical Disk Object: % Disk Time counter exceeds 55%, and the Physical Disk Object: Avg. Disk Queue Length exceeds a count of 2 for each individual disk drive in your disk storage subsystem, then you most likely experiencing a disk I/O performance issue and need to start looking for solutions.
- Don’t run any applications on your server other than SQL Server, with the exception of necessary utilities.
- NTFS-formatted partitions should not exceed 80% of their capacity. For example, if you have a 100GB logical drive, it should never be fuller than 80GB. Why? NTFS needs room to work, and when you exceed 80% capacity, NTFS become less efficient and I/O can suffer for it.
- If your SQL Server database is mostly reads, then a RAID 5 array offers good protection and adequate performance. If your SQL Server database is mostly writes, then use a RAID 10 array for best protection and performance.
- If your SQL Server’s tempdb database is heavily used by your application(s), consider locating it on an array of its own (such as RAID 1 or RAID 10). This will allow disk I/O to be more evenly distributed, reducing disk I/O contention issues, and speeding up SQL Server’s overall performance.
- The more spindles you have in an array, the faster disk I/O will be.
- Ensure that all hardware is running the latest, approved drivers.
Chosen from SQL Server DBA best practices By Brad M.mcGehee