Enterprise TechSQLSQL Server Performance I/O Characteristics February 23, 20163904 views0Share Performance Tuning By IG ShareSQL 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:Note: If you buy something from our links, we might earn a commission. See our disclosure statement. Table of Contents There is no one single “right” way to configure storage for optimal performanceGeneral Performance Considerations:Performance – RAID LevelDesigning Storage Design for Optimal PerformanceDesigning Database Files/Groups for PerformanceManaging File Growth: There is no one single “right” way to configure storage for optimal performanceGeneral Performance Considerations:Storage design considerations differ for large vs. small or consolidated environmentsUnderstanding the I/O characteristics is keyGeneral guidelinesMore/faster spindles is always better for performance;Especially true for OLTP or workloads with random IO patternsEngage the engineers from all sides, early onEnsure storage engineers have at least some knowledge of SQL best practicesTry not to “over” optimize, simpler designs generally offer good performance and more flexibilityValidate configurations prior to deploymentPerformance – RAID LevelBest Practice: log files on RAID 1+0 disksBest Practice: Isolate log from data at the physical disk level (more on isolation later)Tempdb may realize a performance if placed on RAID 1+0Our 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 vendorBenchmarking of the storage can give a clear indication of the performance differences between RAID levels before SQL Server is deployedFor RAID levels other than RAID 5, 1, or 1+0 test to ensure performance is acceptableDesigning Storage Design for Optimal PerformanceMultiple smaller LUNs are preferred over a single large LUNAdverse impact of long running CHKDSK is minimizedPotentially better load balancing across array service processorsFor huge databases, multiple large LUNs are acceptable (though smaller ones are preferred)Fewer large LUNs can accommodate large Databases & would be easier to manageCost of CHKDSK may be acceptable if volumes contain a small number of filesConsider specific array architecture and use multiple LUNs to ensure proper balancing of LUN’s across array service processorsDesign/Plan adequately for growthOther Considerations:More LUNs = multiple independent queues, thus potential for better parallel I/O operationsAssuming scalability at the back end and no bottlenecks exist elsewhere, a system will scale better having multiple pathsRebuild as a result of failed disks will effect LUN’s spanning that RAID groupSQL ConsiderationsBackup/restore – 1 thread per volumeFile initialization – 1 thread per volumeDesigning Database Files/Groups for PerformanceHow many data files/filegroups should I have?More data files does not necessarily equal better performanceDetermined mainly by hardware capacityConsider disaster recovery requirementsWill the target environment for a disaster recovery restore accommodate the file sizes?Number of data files may impact scalabilityROT: .25 to 1 data files (per filegroup) for each CPU (core) on the host serverMainly a concern for applications with high rate of page allocations (insert) on systems with >= 4 CPUsGenerally more of a consideration for Tempdb than for user databasesHowever, consider overall data volume and file sizeCan be used to maximize # of spindles – Data files can be used to “stripe” database across more physical spindlesMultiple filegroups may be optimal for backup / recovery scenarios of larger datasetsBest practice: Pre-size data/log files, use equal size for files within a single file group and do not rely on AUTOGROWManaging File Growth:Design for growth from the beginningDependent 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 vendorTwo types of GROWTHCapacity vs. Additional performance (more physical disks)Windows perspectiveBasic or Dynamic disks – Either can be expandedHowever – dynamic striped volumes cannot be extendedBasic disks can be expanded using Diskpart.exeChanges to underlying LUNs may require a rescan for Windows to recognize them Affiliate Disclosure: Faceofit.com is a participant in the Amazon Services LLC Associates Program. As an Amazon Associate we earn from qualifying purchases. Share What's your reaction? Excited 0 Happy 0 In Love 0 Not Sure 0 Silly 0
Enterprise TechList of the Best Threadripper Pro Workstations – W-3100 Xeon W-3200 & W-1200 Alternatives By IGMay 25, 2021
Enterprise TechList of the Best HPTX Cases – Huge PC Cases for Large BuildsThe computer cases have been one of the essential aspects when it comes to enjoying ...
AzureBest Industrial IoT Routers & Gateways For AWS and Azure IoT ServicesIn this article, we are going to list out the Best Industrial IoT Routers for professional ...
Enterprise TechWhat Is A DataCenter Power Distribution Unit (pdu) Server Rack CabinetsNo matter whether you call it a server closet or server room or a cabinet, ...
Storage SystemsWD SE vs WD Red Pro Specifications Comparison – Enterprise Class NASThe HDDs or the Hard Disk drives are slowly moving into oblivion with the advancements ...