SQL Server Configuration Recommendations

SQL Server

SQL Server Recommendations

The following is the list of general recommendations that are made for your installation of SQL Server. Each instance of SQL Server is unique, so you may have specific configuration requirements that are outside of these recommendations. You will often hear “It depends” when discussing specific recommendations. People often ask about general recommendations, which is really just a place to start when you are starting to configure your new server.

  • Always use Page Checksum to audit data integrity.
  • Consider using compression for read-only filegroups for higher storage efficiency.
  • Use NTFS for security and availability.
  • Use instant file initialization for performance optimization.
  • Use manual file growth database options.
  • Use partitioning (available in Enterprise Edition) for better database manageability.
  • Storage-align indexes with their respective base tables for easier and faster maintenance.
  • Storage-align commonly joined tables for faster joins and better maintenance.
  • Choose your RAID level carefully. For excellent performance and high reliability of both read and write data patterns, use RAID10. For read-only data patterns, use RAID5. Compared to RAID0, all other RAID levels have lower write performance, all else being equal, because RAID0 does not have redundancy. You do not want to use RAID0.
  • For optimized I/O parallelism, use 64 KB or 256 KB stripe size.
  • Although disk performance is commonly attributed to the disk seek time and rotational speed, the amount of cache also plays a role. For servers that frequently perform sequential workloads, like SQL Server, having a large disk cache is often times more important than seek time. Important – Make sure that the cache is backed up by battery.
  • For future scalability and easy of maintenance, use volume mount points.
  • To increase bus bandwidth reliability, use multipathing software.
  • For small servers with less than three disks performing mostly sequential I/O, or servers with approximately eight disks performing random I/O, PCI is sufficient. However, PCI-X is recommended and can service a wider range of servers with varying workload size.
  • Directly attached I/O is recommended for small- to medium-sized servers.
  • SAN systems are recommended for larger servers.
  • NAS systems are not recommended. Use iSCSI instead.
  • For better recoverability, use a SCSI interface instead of SATA and IDE.
  • For larger server loads, use SCSI or SATA with TCQ support.
  • Store transaction logs separate from data files. Do not stripe on the same disk as the data files.
  • For large bandwidth demands on the I/O bus, use a different bus for the transaction log files.
  • The number of data files within a single filegroup should equal to the number of CPU cores. This includes the TempDB and user database files.
  • Don’t assume the person configuring your server knows anything about SQL Server performance versus Windows server performance.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s