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:

• https://blogs.technet.com/josebda/archive/2009/03/27/sql-server-2008-storage.aspx
• https://blogs.technet.com/josebda/archive/2009/03/25/sql-server-2008-dr-testing-and-waits.aspx
• https://blogs.technet.com/josebda/archive/2009/03/17/sql-server-2008-file-related-optimizations.aspx