Enterprise TechSQL SQL Server Performance I/O Characteristics February 23, 2016667 views0 Share 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 Disclaimer: The Questions and Answers provided on https://www.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. Share What's your reaction? Excited 0 Happy 0 In Love 0 Not Sure 0 Silly 0 IG Website Twitter
TSQL How to Effectively split a string using STRING_SPLIT function in SQL server By DataGeekApril 15, 2019
Enterprise Tech How to use Azure Ad Join on Windows Server 2019? & Gateway Access Just like Microsoft Windows 10, the Microsoft Server 2019 also lets you make use of ...
Enterprise Tech What is VMware VMotion? Limitations & Requirements – Storage & Shared If you are in the IT industry, especially infrastructure professionals, you may have heard the ...
Cloud Computing Differences Between VMWare SRM Standard and Enterprise Licensing Disaster Management is one of the essential aspects of any virtualization technique. VMWare, being one ...
Enterprise Tech Cisco WebEx vs Skype for Business: Differences and Comparisons The workforce today has become completely mobile. With that change in the work strategy, the ...