SQL Server Performance Tuning

SQL Server Performance I/O Characteristics

  • SQL Server Performance I/O Characteristics : SQL Server has different IOPS characteristics and it is difficult to generalize IO patterns of SQL Server. SQL is a back-end platform on which applications are built hence IO patterns may differ significantly from one application to another. Monitoring of I/O is necessary to determine specifics of each scenario. Understanding the I/O characteristics of common SQL Server operations/scenarios can help determine how to configure storage requirements.

General IO characteristics of common scenarios:

SQL Server Performance I/O Characteristics SQL Server Performance I/O Characteristics


There is no one single “right” way to configure storage for optimal performance

General Performance Considerations:

  • Storage design considerations differ for large vs. small or consolidated  environments
  • Understanding the I/O characteristics is key
  • General guidelines
  • More/faster spindles is always better for performance;
  • Especially true for OLTP or workloads with random IO patterns
  • Engage the engineers from all sides, early on
  • Ensure storage engineers have at least some knowledge of SQL best practices
  • Try not to “over” optimize, simpler designs generally offer good performance and more flexibility
  • Validate configurations prior to deployment

Performance – RAID Level

  • Best Practice: log files on RAID 1+0 disks
  • Best Practice: Isolate log from data at the physical disk level (more on isolation later)
  • Tempdb may realize a performance if placed on RAID 1+0
  • Our results indicate performance gain on RAID 1+0 for write intensive workloads but at a higher cost ($)
  • The performance difference between RAID 1+0 and RAID 5 can vary by vendor
  • Benchmarking of the storage can give a clear indication of the performance differences between RAID levels before SQL Server is deployed
  • For RAID levels other than RAID 5, 1, or 1+0 test to ensure performance is acceptable

Designing Storage Design for Optimal Performance

  • Multiple smaller LUNs are preferred over a single large LUN
  • Adverse impact of long running CHKDSK is minimized
  • Potentially better load balancing across array service processors
  • For huge databases, multiple large LUNs are acceptable (though smaller ones are preferred)
  • Fewer large LUNs can accommodate large Databases & would be easier to manage
  • Cost of CHKDSK may be acceptable if volumes contain a small number of files
  • Consider specific array architecture and use multiple LUNs to  ensure proper balancing of LUN’s across array service processors
  • Design/Plan adequately for growth
  • Other Considerations:
  • More LUNs = multiple independent queues, thus potential for better parallel I/O operations
  • Assuming scalability at the back end and no bottlenecks exist elsewhere, a system will scale better having multiple paths
  • Rebuild as a result of failed disks will effect LUN’s spanning that RAID group

SQL Considerations

Backup/restore – 1 thread per volume

File initialization – 1 thread per volume

Designing Database Files/Groups for Performance

  • How many data files/filegroups should I have?
  • More data files does not necessarily equal better performance
  • Determined mainly by hardware capacity
  • Consider disaster recovery requirements
  • Will the target environment for a disaster recovery restore accommodate the file sizes?
  • Number of data files may impact scalability
  • ROT:  .25 to 1 data files (per filegroup) for each CPU (core) on the host server
  • Mainly a concern for applications with high rate of page allocations (insert) on systems with >= 4 CPUs
  • Generally more of a consideration for Tempdb than for user databases
  • However, consider overall data volume and file size
  • Can be used to maximize # of spindles – Data files can be used to “stripe” database across more physical spindles
  • Multiple filegroups may be optimal for backup / recovery scenarios of larger datasets
  • Best practice: Pre-size data/log files, use equal size for files within a single file group and do not rely on AUTOGROW

Managing File Growth:

  • Design for growth from the beginning
  • Dependent on features offered by storage array in terms of IO performance.
  • Most modern storage arrays offer the ability to dynamically grow a LUN – consult with your storage vendor

Two types of GROWTH

  • Capacity vs. Additional performance (more physical disks)
  • Windows perspective
  • Basic or Dynamic disks – Either can be expanded
  • However – dynamic striped volumes cannot be extended
  • Basic disks can be expanded using Diskpart.exe
    • Changes to underlying LUNs may require a rescan for Windows to recognize them

SQL Server Performance I/O Characteristics

Affiliate Disclosure: Faceofit.com is a participant in the Amazon Services LLC Associates Program. As an Amazon Associate we earn from qualifying purchases.