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:

Table of Contents

Toggle

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