IO and DiskSpd Reference

Operation	Details	Filetype	R/W %	IO Pattern	Block Size	Treads	Queues	Simulates	DiskSpd Command	Reference
.	.	SQL Data files	⁸⁰⁄₂₀	Random	8K	# Cores	# Files	Typical OLTP data files	diskspd -b8k -d30 -o1 -t4 -w20 -Z1G -h -L -r -c20G D:\iobaseline-datafile.dat > DiskSpd-IOBaselineResults-Datafile.txt
Transaction log write	Threads 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 files	0/100	Sequential	60K	1	32	Transcation Log	diskspd -b60k -d30 -o1 -t4 -w100 -Z1G -h -L -c20G L:\iobaseline-logfile.dat > DiskSpd-IOBaselineResults-TransactionLogfile.txt	https://sqlserverfaq.com/tonyrogerson/2016/01/19/transaction-log-concepts-acid-wal-vlfs-checkpoint-process/
.	.	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
.	.		¹⁰⁰⁄₀	Sequential	512K	1	16	Table Scans
Bulk Loads	SIMPLE / BULK LOGGED mode writes to database, FULL writes to transaction log and flush to database	Dependant on recovery	0/100	Sequential	256K	1	16	Bulk load
.	.		¹⁰⁰⁄₀	Random	32K	# Cores	1	SSAS workload
Backup/Restore		Backup file	¹⁰⁰⁄₀	Sequential	1MB	1	32	Backup	diskspd -b1M -d30 -o32 -t1 -w100 -Z100G -h -Ln -si -c100GB
Checkpoint/Lazywriter	Both 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 files	0/100	Random	64K-256K	# Cores	# Files	Checkpoint		https://sqlinthewild.co.za/index.php/2009/06/26/the-lazy-writer-and-the-checkpoint/
Read-Ahead Scans	Read-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						https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms191475(v=sql.105)
ColumnStore Read-Ahead	When 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					http://www.nikoport.com/2015/04/04/clustered-columnstore-indexes-part-50-columnstore-io/
File Initialization	.	SQL Data and Transaction Logs			8MB
In-Memory OLTP Checkpoint	.				1MB

###

a sql blog