SQL IO Characteristics

This is fairly well documented in various books and white papers, but can be tricky to find out on the Internet so I am summarising it here in this blog.

The key questions is “what are SQL Server’s I/O Characteristics”. This helps answer questions like why is a 1MB block size bad; why don’t we often care about random I/O for log files; what extra benefit for I/O does enterprise edition have; and why do we mainly test random I/O for data files in OLTP systems

The table below shows general I/O characteristics for the storage engine that will be important to most OLTP workloads. If you “really” know your workload you can optimise your storage. For example:

  • if you know that most of your workload is index seeks, you may benefit from a 8k block size on the SAN – good luck convincing your SAN team of this 😉
  • If you are doing mostly very large table scans and you have enterprise edition, then it is possible that a 1MB block size may be most performant.
  • If you are not running enterprise edition, then a 1MB block size will never be optimal as the maximum IO size SQL Server will issue is 256k, so a minimum of three quarters of your I/O will be wasted.
OperationRandom / SequentialRead / WriteSize Range
OLTP – LogSequentialWriteSector Aligned Up to 60K
OLTP – LogSequentialReadSector Aligned Up to 120K
OLTP – Data (Index Seeks)RandomRead8K
OLTP – Lazy Writer (scatter gather)RandomWriteAny multiple of 8K up to 256K
OLTP – Checkpoint (scatter gather)RandomWriteAny multiple of 8K up to 256K
Read Ahead (DSS, Index/Table Scans)SequentialReadAny multiple of 8KB up to 256K (1024 Enterprise Edition)
Bulk InsertSequentialWriteAny multiple of 8K up to 128K

This secondary table is useful as trivia, but we rarely optimise storage for these operations. One  observation is that backup and restore can issue up to 4MB IO sizes, and the importance of instant file initialisation in helping create database performance (or file growth).

OperationRandom / SequentialRead / WriteSize Range
CREATE DATABASE (or file growth)SequentialWrite512KB (SQL 2000) , Up to 4MB (SQL2005+)(Only log file is initialized in SQL Server 2005+ if instant file initialisation is enabled)
BACKUPSequentialRead/WriteMultiple of 64K (up to 4MB)
RESTORESequentialRead/WriteMultiple of 64K (up to 4MB)
DBCC – CHECKDBSequentialRead8K – 64K
ALTER INDEX REBUILD – replaces DBREINDEX
(Read Phase)
SequentialReadAny multiple of 8KB up to 256K
ALTER INDEX REBUILD – replaces DBREINDEX
(Write Phase)
SequentialWriteAny multiple of 8K up to 128K
DBCC – SHOWCONTIG (deprecated, use sys.dm_db_index_physical_stats)SequentialRead8K – 64K

Further References:

Hitachi – Tuning SL Server 2005 Performance http://www.servicesorientedstorage.com/assets/pdf/tuning-microsoft-sql-server-2005-performance-wp.pdf

High Availability Best Practices: I/O Subsystem

http://download.microsoft.com/download/f/e/3/fe32de98-8be3-4212-9cf9-be75fc699df8/SQL_Server_Always_On_Tec_IO.ppt

SQL Server 2000 I/O Basics (Old but still mostly relevant today)

http://technet.microsoft.com/en-us/library/cc966500.aspx

Leave a Reply