github twitter linkedin email
IO and DiskSpd Reference

OperationDetailsFiletypeR/W %IO PatternBlock SizeTreadsQueuesSimulatesDiskSpd CommandReference
..SQL Data files8020Random8K# Cores# FilesTypical OLTP data filesdiskspd -b8k -d30 -o1 -t4 -w20 -Z1G -h -L -r -c20G D:\iobaseline-datafile.dat > DiskSpd-IOBaselineResults-Datafile.txt 
Transaction log writeThreads fill log buffers & requests log manager to flush all records up to certain LSN - log manager thread writes the buffers to disk.SQL Transaction Log files0/100Sequential60K132Transcation Logdiskspd -b60k -d30 -o1 -t4 -w100 -Z1G -h -L -c20G L:\iobaseline-logfile.dat > DiskSpd-IOBaselineResults-TransactionLogfile.txt
..TempDb Data files      diskspd -b8k -d30 -o4 -t8 -w50 -L -h -Z1G -c20G T:\T_iobaseline.dat > DiskSpd-IOBaselineResults-T.txt 
..TempDb Log files      diskspd -b8k -d30 -o4 -t8 -w80 -L -h -Z1G -c20G I:\I_iobaseline.dat > DiskSpd-IOBaselineResults-I.txt 
.. 1000Sequential512K116Table Scans  
Bulk LoadsSIMPLE / BULK LOGGED mode writes to database, FULL writes to transaction log and flush to databaseDependant on recovery0/100Sequential256K116Bulk load  
.. 1000Random32K# Cores1SSAS workload  
Backup/Restore Backup file1000Sequential1MB132Backupdiskspd -b1M -d30 -o32 -t1 -w100 -Z100G -h -Ln -si -c100GB 
Checkpoint/LazywriterBoth the lazy writer process and a checkpoint both push in-memory pages out to disk. A checkpoint is responsible for pushing dirty pages in the buffer pool out to disk at a specified interval. A dirty page is simply a page that SQL SERVER has changed in memory, but has not yet been written to disk. The pages need to be written to disk regularly in order to ensure that when SQL SERVER restarts, that crash recovery will not take so long. The timing of the checkpoints being issues is determined by the setting ‘recovery interval (min)’ of sp_configure. The lazy writer will push dirty pages to disk for an entirely different reason, because it needs to free up memory in the buffer pool. This happens when SQL SERVER comes under memory pressure. SQL Transaction Log files0/100Random64K-256K# Cores# FilesCheckpoint
Read-Ahead ScansRead-ahead anticipates the data and index pages needed to fulfill a query execution plan and brings the pages into the buffer cache before they are actually used by the query. This allows computation and I/O to overlap, taking full advantage of both the CPU and the disk.SQL Data files Sequential
ColumnStore Read-AheadWhen SQL Server is performing a sequential scan on a table with significant amount of data, the storage engine is trying to improve the performance by issuing read-ahead calls that will ensure that once CPU is ready to process the information, it is already pre-fetched into the memory and hence ready to be worked on.SQL Data files Sequential 8MB
File Initialization.SQL Data and Transaction Logs  8MB     
In-Memory OLTP Checkpoint.   1MB