- 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:
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
