Optimize SQL Server hardware performance

Tuning SQL Server for performance is in many ways like constructing a house. You must find a suitable location with rich soil, build a solid foundation to support two or three levels, install the electrical and plumbing, finish the walls for painting and decorating, and finally conduct ongoing maintenance.

Comparable items to address when tuning SQL Server performance include:

• Hardware
• Windows Server
• SQL Server
• Database design
• Indexes design
• T-SQL development
• Network infrastructure
• Front-end code
• Platform maintenance

Although no single component is more important than any other in achieving high performance, it is imperative to start correctly. If you do not build a solid foundation, the remainder of the application will always be plagued by deficiencies in earlier components. Furthermore, although it is possible to rebuild the hardware once it has been deployed, or simply move to another hardware platform, with proper planning the overall platform can support the application in the long term avoiding a time-consuming upgrade.

TABLE OF CONTENTS    Hardware planning    64-bit architectures    Hardware-based accelerators    Ideal hard drive layout    Local storage or SAN    Conclusion

Hardware planning

Return to Table of Contents

Proper hardware planning is the first step in achieving high performance. It is imperative to determine what hardware to use based on requirements that can be calculated by capacity planning for CPUs, memory, drives, network interface cards (NICs), etc. At times this decision is rather easy; you can use corporate standards to simplify the hardware support. If standards do not exist, seize the opportunity and begin building standards to simplify the overall management.

While standardization is critical to supporting a large number of servers, it should be complimented by the latest technologies so you may continuously achieve higher performance at lower costs. Two current hardware technologies that should be considered in the short and long-term are 64-bit technologies and database accelerators.

64-bit architectures

Return to Table of Contents

As hardware vendors deliver 64-bit servers at reasonable costs, Windows Server 2003 and SQL Server 2000 are able to leverage additional resources to improve overall performance from a scale-up perspective. In the 64-bit world, 64 CPUs and 1 TB of RAM can be addressed directly, far exceeding the typical 32-bit world of 4 CPUs and 3 GB of memory – much more than double the resources.

Currently the 64-bit platform has some limitations in terms of tools and full support from all applications, but this too will change over time. It is important now to keep an eye on the 64-bit architectures as they mature and gain greater support from software vendors to improve scale-up options on some rather large servers.

Hardware-based accelerators

Return to Table of Contents

Database accelerators have been getting a fair amount of press lately for SQL Server. At a conceptual level, database accelerators are hardware devices with CPUs and memory. They store the entire database or a subset of tables in memory to process user transactions that write back to the SQL Server, which ultimately stores the data. The advantage is that these devices can sometimes support the entire database or core tables in dedicated memory, which outperforms accessing the data from disk. This can help from a scale up or scale out perspective because a single database accelerator can alleviate hardware bottlenecks without a change to the existing SQL Server.

One company that offers this type of solution is XPrime. Consider this option if no further software optimizations can be determined and uptime can't be jeopardized until an upgrade can be tested and completed.

Ideal hard drive layout

Return to Table of Contents

Database accelerators have been getting a fair amount of press lately for SQL Server. At a conceptual level, database accelerators are hardware devices with CPUs and memory. They store the entire database or a subset of tables in memory to process user transactions that write back to the SQL Server, which ultimately stores the data. The advantage is that these devices can sometimes support the entire database or core tables in dedicated memory, which outperforms accessing the data from disk. This can help from a scale up or scale out perspective because a single database accelerator can alleviate hardware bottlenecks without a change to the existing SQL Server.

One company that offers this type of solution is XPrime. Consider this option if no further software optimizations can be determined and uptime can't be jeopardized until an upgrade can be tested and completed.

Disk	Disks	Drive Size	Controller	RAID	Size	Volume	Purpose
0	2	72 GB	1 - 1	1	72	C:\	Windows, SQL Server Program Files and System Databases (Master, Model, MSDB) (Random)
1	2	72 GB	1 - 1	1	72	D:\	Tempdb (Random)
2	2	72 GB	1 - 2	1	72	E:\	Database Transaction Logs (Sequential)
3	5	72 GB	2 - 1	5	288	F:\	Databases (Random)
4	3	72 GB	2 - 2	5	144	G:\	Backups and Batch Processing (Sequential)
-	14	-	-	-	648	-	-

Local storage or a storage area network (SAN)

Return to Table of Contents

The big question for many organizations is how to support a high number of drives on a single server. On top of that, they must consider how to manage databases exceeding the designated amount of storage, or having to separate specific databases from one another on a single server.

There are three basic options: The first is to leverage servers with many internal disks to support storage. Second is a server with a few internal disks and a locally attached drive array. Third is a server with a few internal disks and a connection to a storage area network (SAN).

Management can be more difficult with locally attached storage because it is distributed, unlike a SAN, which has centralized storage where adding new storage to a server can be as simple as a point-and-click process. However, performance may be better for local storage because only a single server leverages the disk drive as compared to a SAN, where five to 10 servers may access the same drives and cause contention. The SAN's answer to this performance issue is a large amount of cache rather than access the data from disk.

Then there is the egg-and-basket metaphor: A single server with dedicated storage having an IO issue can only affect one server, whereas that same problem on a SAN can potentially affect many servers. This can be taken a step further when an update is needed to the SAN; all of the servers leveraging storage from the SAN need to be shutdown. When updating a single server with local storage, the downtime only affects the server requiring the update, not all servers. Manageability versus performance must be balanced.

Conclusion

Return to Table of Contents

It is no mystery that hardware is the foundation for SQL Server high performance, but the platform still requires the proper subsequent design and development to achieve the needed performance in the long term. While many hardware innovations are available, keep in mind that slacking off on the development phases is not an option. You must set up SQL Server hardware properly and keep optimization best practices at hand throughout the application's lifecycle to achieve high performance. Good luck!

About the author: Jeremy Kadlec is the Principal Database Engineer at Edgewood Solutions, a technology services company delivering professional services and product solutions for Microsoft SQL Server. He has authored numerous articles and delivers frequent presentations at regional SQL Server Users Groups and nationally at SQL PASS. Jeremy is also the SearchSQLServer.com Performance Tuning expert. Ask him a question here.

More information from SearchSQLServer.com