January 16, 2019
SQL Server Performance Tuning

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:

SQL Server Performance I/O Characteristics SQL Server Performance I/O Characteristics


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

SQL Server Performance I/O Characteristics

Related Posts

Data Analytics in Excel 2016 Data Analytics in Excel 2016 Data Acquisition Extract Enterprise data easily using PowerQuery built into Excel. Supports New Data Sources l...
Free Top 5 HTML Editors for Windows 10 Of course, HTML tags play a preeminent role in whatever you do on your website. But, unless you're an expert, coding is something that would make ...
How to Fix Airplane mode not turning off in Window... Just as you would expect, the "Airplane mode" in Windows 10 is a system setting that allows you to turn off all wireless communications on your PC. Wi...
Introduction to SQL Server High Availability This post is about the introduction to SQL Server high availability. Here we would be talking about the various replication options available in SQL S...
Disclosure: We are a professional review website that sometimes receive compensation or free units from the companies whose products we review. We test each product thoroughly and give high marks to only the very best. We are independently owned and the opinions expressed here are our own.

FOLLOW @ INSTAGRAM