SQL Server 2008 I/O Performance
Here are some notes on “SQL Server 2008 I/O Performance” I took while attending an advanced class on SQL Server taught by Greg Low (from https://sqlblog.com/blogs/greg_low/ and https://www.sqldownunder.com/).
Please note that, although these notes were taken during the class, I might have added some of my own (mis)interpretation :-). Always check your facts on Books Online (I try to provide links when applicable). As with anything else you get from a blog, never use any of this in production before you thoroughly validate it a test environment. Please keep in mind that some of those will be hard to follow without some pre-requisite knowledge and the right context. Reading the post from top to bottom will help.
Performance
- No single right way to do it. All environments are different.
- In general, for OLAP/DW, bandwidth is more important than spindle count
- In general, for mixed workloads, IO becomes more random, SAN is more flexible
- Ensure storage engineers have knowledge of SQL best practices
- Validate your configuration before deployment
Storage
- How many/what size LUNs? “It depends” :-)
- Results vary, not all storage implementations perform the same. Test it
- Volume alignment – Windows Server 2008 is good at 1MB alignment, older OSes need attention
- Allocation Unit Size – Use 64KB to align with extent size
- Careful – Array and driver firmware is important
- Careful – Use drivers that have been qualified
- Careful – Discuss ideal settings for HBA/controller with the storage vendor
- Careful – Hosts usually have multiple PCI buses. Don’t overload just one of them
- Consider using multiple paths for improved availability
- iSCSI Support – See https://support.microsoft.com/kb/833770
- For iSCSI - ensure appropriate bandwidth, latency, paths
- HBA queue depth discussion
SQL Files
- Log files: RAID 1+0, isolate log from data at the physical level
- Tempdb: improvement if place on RAID 1+0 or some RAMSAN style storage
- In general, do not mix SQL data files with other data
- In general, multiple LUNs are usually better than a single large LUN
- In general, for VLDB, multiple large LUNs are OK.
- Use GPT for LUNs bigger than 2TB
- See https://support.microsoft.com/kb/302873
- More files per database does not necessarily equal better performance
- For tempdb, multiple files could be a good idea to avoid contention on certain structures
- Filegroups: backup per FG, partial availability, partitioned tables, tables and indexes
- In general, if possible, keep the primary FG small, put data in other FG
- Consider read-only filegroups for certain scenarios
- Monitor growth
SQL Server urban legends
- NOT TRUE – One thread per data file
- NOT TRUE – Disk queue length greater than 2 per disk indicates and I/O bottleneck
- https://blogs.msdn.com/psssql/archive/2007/02/21/sql-server-urban-legends-discussed.aspx
Monitoring SQL I/O Performance
- Performance Monitor, per volume or LUN – Look at IOPs, throughput, latency
- sys.dm_io_virtual_file_stats, per database file
- See https://msdn.microsoft.com/en-us/library/ms190326.aspx
- sys.dm_exec_query_stats, per query or batch
- See https://msdn.microsoft.com/en-us/library/ms189741.aspx
- sys.dm_db_index_usage_stats, per table or index
- See https://msdn.microsoft.com/en-us/library/ms188755.aspx
- sys.dm_db_index_operational_stats, per table or index
- See https://msdn.microsoft.com/en-us/library/ms174281.aspx
Fragmentation
- Heaps and clustered indexes
- Clustered indexes – Consider using Identity, GUID with NEWSEQUENTIALID()
- Autogrow – Configure but try to make sure it’s not used
- Autoshrink – Do not use it
- See https://www.sqlskills.com/blogs/paul/post/Auto-shrink-e28093-turn-it-OFF!.aspx
- Careful – Ordered Scans, FillFactor
File Initialization
- File growth – be careful with large growth + autogrow + initialization during regular hours
- Consider using instant file initialization
- See https://www.sqlskills.com/blogs/Kimberly/post/Instant-Initialization-What-Why-and-How.aspx
SQLIO
- SQLIO – Used as performance tool, provided by Microsoft, not supported
- See https://www.microsoft.com/downloads/details.aspx?familyid=9A8B005B-84E4-4F24-8D65-CB53442D9E19&displaylang=en
- Tests a variety of I/O types and sizes, one per command
- Use the correct file sizes, longer runs – to approximate real-time workloads
- Use typical workload for data, log, backup (request size, random/sequential, sync/async)
- Looking to identify when you reach saturation
SQLIOSim
- Replaces SQLIOStress, SQL70IOStress
- Simulates: Read, Write, Checkpoint, Backup, Sort, Read-ahead
- Stress tool for the I/O subsystem, not a performance tool
- Demo: running with 2 drives with different performance characteristics
- Demo: looking at average IO duration
- See https://blogs.msdn.com/sqlserverstorageengine/archive/2006/10/06/SQLIOSim-available-for-download.aspx
- See https://sqlblog.com/blogs/kevin_kline/archive/2007/06/28/understanding-sqliosim-output.aspx
PerfMon + SQL Profiler
- Demo: Start PerfMon, capture.
- Demo: Capture a trace with SQL Profile, save. “Import Performance Data…” grayed out.
- Demo: Load SQL Profile trace, then Use “Import Performance Data…”, compare.
- Books Online: How to: Correlate a Trace with Windows Performance Log Data (SQL Server Profiler)
- See https://msdn.microsoft.com/en-us/library/ms191152.aspx
Related blog posts: