Optimizing SQL Server 2008 performance

This tip details the key areas that solutions providers should focus on when implementing SQL Server 2008, including memory capacity, storage configuration and disk alignment.

More resources on optimizing SQL Server 2008 performance:
Microsoft SQL Server 2008 guide

FAQ: SQL Server 2008 high-availability services

Reasons to upgrade to SQL Server 2008

When implementing SQL Server 2008 for customers, it is important for solutions providers to understand how to optimize SQL Server 2008 performance. Optimizing SQL Server will ensure that a customer's database system is running at the best possible productivity levels. Solutions providers should focus on the following key elements when optimizing SQL Server 2008:

  • Processor

  • Memory

  • Storage

  • Disk alignment

  • TempDB optimization

  • Windows Server 2008 R2 operating system

Using a 64-bit processor to improve SQL Server 2008 performance

In today's server market, 64-bit processors dominate. Solutions providers that use 64-bit architecture when implementing SQL Server 2008 experience many advantages, including:

  • Greater memory capacity: Architectures with 32-bit processors are limited to 4 GB of memory capacity, whereas the 64-bit architecture offers a larger, directly addressable memory space. Therefore, more memory is available for performing complex queries and supporting essential database operations. This greater processing capacity minimizes I/O latency by using more memory than traditional 32-bit systems.

  • Better support: When using 64-bit architecture, SQL Server 2008 can support more processes, applications and users in a single system via enhanced parallelism. It can also support more processors.

  • Improved bus architecture: The improved bus architecture enhances performance in SQL Server 2008 by moving more data between cache and processors in shorter time periods. A larger on-die cache allows for faster completion of user requests and a more efficient use of processor time.

Memory usage and SQL Server 2008 performance

SQL Server primarily uses memory for processing queries and stored procedures. It first attempts to find requested data in its memory. If it cannot find the data in memory, it will attempt to make a request to disk. Retrieving data from the disk subsystem is much slower than accessing data from memory, so a solutions provider should install as much memory as the customer can afford or install as much memory as the operating system can support, because SQL Server can make use of all the memory in the system.

Addressing SQL Server 2008 storage concerns

Unfortunately, there isn't a single storage configuration that is able to address every SQL Server implementation. It is important for solutions providers to understand the I/O characteristics of the database application to ensure that disk and storage configurations are optimized and that it meets customers' requirements in storage availability and performance. The following list includes general recommendations for optimizing SQL Server storage.

  • RAID 1+0, also referred to as mirroring with stripping, is the preferred RAID configuration for writing applications because it produces excellent performance and redundancy. If the customer can't afford RAID 1+0, then RAID 5 is the next best alternative.

  • RAID 1 is the preferred disk configuration for the transaction logs in SQL Server 2008. RAID 1 is also known as disk mirroring.

  • Always separate the data files and transaction logs on different volumes or logical unit numbers (LUNs). Placing both files on the same volumes or LUNs will cause contention and negatively affect SQL Server 2008 performance.

  • Do not place database or log files on compressed drives because it will negatively affect SQL Server performance.

  • Do not depend on the SQL Server autogrowth feature, because there are negative performance hits when either the data or transaction log files grow. It is a best practice to determine the size of both data and transaction log files before creating a new database.

Considering SQL Server 2008 disk alignment

When working with versions of Windows prior to Windows Server 2008, it is considered a best practice to conduct partition alignment to drastically increase performance and storage. If you don't conduct partition alignment, you can adversely affect I/O response times, depending on what storage product your client is using. Solutions providers should work with their customer's hardware vendors to find the optimal alignment settings.

Note: If you want to make disk alignment changes after setting up SQL Server, then you have to recreate and reformat the partition.

Effect of tempDB on SQL Server 2008 performance

The size and physical placement of tempDB can drastically influence SQL Server 2008 performance. I recommend that solutions providers:

  • Set the recovery model of the tempDB database to "Simple."

  • Configure the tempDB database to automatically grow.

  • Set the file growth increment based on the expected growth requirement to ensure that the files are not constantly expanding and causing performance degradation.

  • Reduce storage contention by using tempDB multiple files. Best practices specify using one file for each CPU core in the SQL Server system.

  • Use disk striping, and place the tempDB databases on a fast I/O subsystem.

Using Windows Server 2008 R2 to improve SQL Server 2008 performance

The operating system chosen to run SQL Server 2008 can significantly improve performance and influence how SQL Server takes advantage of the underlying hardware platform. For example, the Windows Server 2008 R2 Datacenter Edition allows SQL Server to further scale by supporting up to 256 logical processor cores and 2 TB of RAM for a single operating system instance. In addition, if you plan on clustering, the Enterprise and Datacenter Editions also support up to 16 nodes.

About the expert
Ross Mistry is a principal consultant at Convergent Computing, a best-selling author and a SQL Server MVP. He focuses on implementing SQL Server, Active Directory, SharePoint and Exchange solutions for Fortune 500 organizations in the Silicon Valley. His SQL Server and SharePoint specialties include high availability, security, migrations and virtualization. Ross' recent books include
SQL Server 2008 Management and Administration and Windows Server 2008 Unleashed. He was also a contributing writer on SharePoint Server 2007 Unleashed. He is currently working on his latest titles, Windows Server 2008 R2 Unleashed, Exchange Server 2010 Unleashed and SQL Server 2008 R2, which are scheduled for release in late 2009 / early 2010. Ross frequently writes for TechTarget and speaks at international technology conferences around the world. Follow him on twitter @RossMistry.

Dig Deeper on Database software management