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

Disclaimer: The Questions and Answers provided on https://gigxp.com are for general information purposes only. We make no representations or warranties of any kind, express or implied, about the completeness, accuracy, reliability, suitability or availability with respect to the website or the information, products, services, or related graphics contained on the website for any purpose.