SQL Server 2005 and 2008 implementation best practices and performance tuning

What's covered

Implementation references

Physical database storage design

Disk Partition alignment best practices for SQL server

SQL server best practices

File placement

Performance troubleshooting process

Before you begin

Steps

Cheat sheet - categories

Memory - operating system

Memory - SQL server

Settings - SQL server instance

Disk - SQL server system databases

Disk - SQL server user databases

Database settings - user databases

References

Related Articles

This article consolidates best practices for SQL Server 2005/2008 server implementation. It also presents a simplistic process that can be used to tune and troubleshoot performance.

The focus is on Operating System and SQL Server settings that should be taken into consideration. The information contained in this article is geared toward servers that are dedicated to SQL. Consider each setting's potential effect on the Operating System and other applications and services that may be hosted along-side your SQL instance before making changes. Make one change at a time and test its impact before making other changes to help keep your testing simple, and to actually be able to draw accurate conclusions about the impact of the change.

Implementation references

Physical database storage design

Back to top

http://technet.microsoft.com/en-us/library/cc966414.aspx

• "RAID10 yields excellent read-write performance."
• "RAID5 can have much lower write performance than any other configuration because it requires extra reading and writing activities for the parity blocks in addition to reading and writing the data."
• "For optimized I/O parallelism, use [a] 64 KB or 256 KB stripe size", when creating a RAID.

• If necessary to combat excessive Page Latch Waits on tempdb-based activity, the tempdb data file can be split up along the following guideline:

  Number of files = Number of physical CPU's allocated for use by SQL / 4 (up to a maximum of 8 data files per filegroup)

  It should be noted that splitting tempdb into multiple files on the same spindle can be beneficial, which could seem counter-intuitive. Where possible, doing this across multiple spindles would likely further improve the performance. The default recommendation is to split tempdb into two files, e.g. tempdb.mdf and tempdb2.ndf. Further splitting should only be done when indicated by excessive Page Latch Waits on tempdb.

Disk partition alignment best practices for SQL server

Back to top

http://msdn.microsoft.com/en-us/library/dd758814(v=sql.100).aspx

Correctly implement disk partition alignment on each RAID:

• "Disk alignment produced significant improvement compared to nonaligned disks. The measurements document enhancements in excess of 30% for disk latency and duration."
• "The performance of six aligned disks was comparable to or better than eight nonaligned disks."
• Check your RAID's partition alignment using the following at a command line: "wmic partition get BlockSize, StartingOffset, Name, Index"

SQL server best practices

Back to top

http://technet.microsoft.com/en-us/library/cc966412.aspx

• "When formatting the partition that will be used for SQL Server data files, it is recommended that you use a 64KB allocation unit size for data, logs, and tempdb."

File placement

Back to top

Where possible, place the database files, transaction log files, and the tempdb files each on separate, physical I/O devices (spindles/arrays/LUNs). This improves performance by allowing multiple physical devices to concurrently service reads and writes to these files.

Performance troubleshooting process

Back to top

Use the steps and information below to identify and address specific performance issues.

Before you begin

Back to top

• It is generally recommended to update to the most recent SQL Server Service Pack that is currently supported by your Symantec/Arellia application. Often, these Service Packs will include performance fixes.

• The above is generally also true for the Server Operating System and you should update to the most recent OS Service Pack supported.

Steps

Back to top

1. Use Performance Monitor while the performance problem is occurring to identify a bottleneck
   See Common Performance Monitor counter thresholds. If you identify a bottleneck on the SQL Server itself, continue, otherwise, look for bottlenecks on other servers or clients in the system.
2. After you have identified the bottleneck, check for bottleneck category, Memory, CPU, Disk, within the "Categories" section below to see if a related setting can be configured more optimally.
3. Run the Database Health scan tool contained in SSE Tools and have the results reviewed by Arellia Support.
4. If an appropriate change is found, make the change and then observe whether performance has improved.
5. Once SQL and the OS have been optimally configured for the server's hardware, you are generally left with the following options:
   1. Reduce the amount of load on SQL, where appropriate, by decreasing the frequency of:
      1. Client policy updates
      2. Client inventory reporting
      3. Client task execution
      4. Client task polling
      5. Scheduled tasks
   2. Identify long-running or blocking SQL statements using SQL Activity Monitor and evaluate whether they can be optimized
   3. Upgrade the hardware associated with the bottleneck, e.g. adding more RAM, faster CPUs, more or faster Disks, etc.

Cheat Sheet - Categories

Memory - operating system

Back to top

• Windows Server 2003/2008
  • Set Windows' memory usage to favour Programs over System Cache. SQL Server does its own data caching to improve performance, and the more memory reserved for its use, the more efficient its caching can be.
    • Open Computer Properties, click the Advanced Tab, click the Settings button within the Performance Section and click the Advanced Tab.
    • Set the Memory Usage to favour Programs and click OK. Restart the server for this to take effect.
  • Set the File and Printer Sharing service memory to 'Minimize memory used'. The File and Printer Sharing service can be optimized for varying degrees of file sharing activity. Minimizing the memory used for this service can provide more free memory for SQL Server's use.
    • In Control Panel, open Network Connections then open the active network connection.
    • On the General tab of the network connection Properties dialog box, select File and Printer Sharing for Microsoft Networks, and click Properties.
    • In the File and Printer Sharing for Microsoft Networks dialog box, choose 'Minimize memory used' and click OK.
• 32-bit OS
  • If your server has more than 4GB of physical memory, ensure that PAE is enabled at the hardware level so that SQL Server can use AWE to map physical memory addresses above 4GB.
• 64-bit OS
  • Using a 64-bit OS in combination with an x64 version of SQL Server is highly recommended for dedicated SQL servers that have more than 4GB of physical memory to take advantage of the memory addressing capabilities of 64-bit hardware.

Memory - SQL server

Back to top

• Enable the Optimize for Ad hoc Workloads setting in the SQL Server instance settings, Advanced page. This allows more memory to be used for the buffer pool, rather than for procedure caching of single-use batches.
• Set a value in the Maximum server memory option of the 'Server memory options' page, that leaves enough memory for the Operating System itself and tools such as SQL Management Studio to run effectively.
• If AWE support is enabled in SQL, ensure that the account used for the SQL Server service has the Lock Pages in Memory right.²
  • Both AWE and Lock Pages in Memory can benefit 64-bit SQL Servers as well as 32-bit.³

Settings - SQL server instance

Back to top

• If your SQL server has more than 8 cores, lowering the Max Degree of Parallelism (rather than leaving at the default of 0 (unlimited)) will provide better performance. To set:
  • In Server Properties, on the Advanced page, set the Max Degree of Parallelism to half the number of physical processor cores on your system, up to a maximum value of 8.⁴
    • For a client-facing server that does not do much reporting, the recommended value is 2.
    • For a reporting server, or parent server in a hierarchy, the recommended value is 4.
    • For servers that are queried by IT Analytics, the recommended value is 4.
• Set the Cost Threshold for Parallelism to 10
• Enable Arithmetic Abort in the Server/Instance Properties on the Connections page.

Disk - SQL server system databases

Back to top

If tempdb is on a single spindle, or I/O channel:

• Ensure that tempdb has two data files:
• Right-click on tempdb and click Properties
• Go to the Files page and click the Add button
• Set the Logical Name of the new file to tempdev2 and the File Name to tempdb2.ndf
• Set the Initial Size of both Data files to 100MB and the Autogrowth to grow by 10%

Disk - SQL server user databases

Back to top

• Ensure that the account used for the SQL Server Service has the Perform volume maintenance tasks right. Without this, data files cannot be expanded using Instant file initialization which improves performance of data file growth.
• Plan for the expected maximum sizes of your your data and transaction log files and set their initial sizes to that value plus 10-20%. One way to estimate this is to monitor the database files' growth in a pre-production environment and extrapolate.
  • In SQL Management Studio, right-click your database and click Properties.
  • On the Files page, in the Database Files table, set the Initial Size fields to the values you have estimated.
• Set a healthy Autogrowth increment on your data and transaction log files as a contingency in the event that the data files need to grow during a non-maintenance period. You should not rely on Autogrowth to manage your database file sizes, but should monitor the files and re-size them according to your projected needs during maintenance periods.
  • In SQL Management Studio, right-click your database and click Properties
  • On the Files page, in the Database Files table, click the ellipsis button in the Autogrowth column of a file row
  • Enable Autogrowth
  • Set the File Growth to a value such as 500MB or 10%
  • Set the Maximum File Size to a value that will allow for some Autogrowth, but also ensures that the files do not grow out of control.
  • Repeat for each database file

Database settings - user databases

Back to top

• Reporting server, or parent server in a hierarchy:

  • Enable Read Committed Snapshot mode (replace database name with appropriate name) using:

    ALTER DATABASE Symantec_CMDB
    SET Read_Committed_Snapshot ON
    WITH ROLLBACK AFTER 30;
    

  • Enable the Auto Update Statistics Asynchronously setting in the Database Properties, Options page.
• Disable Autoshrink. Shrinking the database automatically is not recommended for multiple reasons:
  • Autoshrink runs periodically in the background, consuming CPU and I/O cycles which can cause unexpected performance hits.
  • Continually shrinking and regrowing the data files can lead to physical fragmentation of the database file, which hurts both sequential transfers and random accesses.⁵

References

Back to top

1. Configuring Hosts and Host Instances - http://msdn.microsoft.com/en-us/library/ee308915(BTS.10).aspx
2. How to: Enable the Lock Pages in Memory Option (Windows) - http://technet.microsoft.com/en-us/library/ms190730(SQL.90).aspx
3. Using AWE, locked pages in memory, on 64 bit - http://blogs.msdn.com/slavao/archive/2005/04/29/413425.aspx
4. General guidelines to use to configure the MAXDOP option - http://support.microsoft.com/kb/329204
5. Considerations for the "autogrow" and "autoshrink" settings in SQL Server - http://support.microsoft.com/kb/315512

Related Articles

Back to top

How can I optimize the performance of my Virtual Machine environment?