SQL Server I/O Bottleneck, I don't have one, YES YOU DO!

Published Jan 15 2019 10:58 AM 56 Views
Microsoft
First published on MSDN on Jul 08, 2008

The mistake I see people make is when looking at the SQL Server PAGE I/O waits and stalled I/O warnings is when comparing it to the Avg. Disk Seconds/Transfer.    Everyone seems to forget that average means average and they look at the value and say I don't see any I/O taking longer than 15 seconds.

Take the following example over a 5 minute window.

2000 I/Os at 8ms   = 16000ms

1 I/O at 15000ms   = 15000 ms

TOTAL TIME: 31000ms

AVG: 15.49ms   (31000 / 2001 I/O Requests)

You are not going to see a spike to 15000ms in the Avg Disk Sec/Transfer but instead a small change from the norm that is very hard to detect from just the Avg Disk Sec/Transfer.

Remember to pay close attention to the following SQL Server data points to help determine your I/O response rates.

  • PAGE I/O* Waits   (select * from sys.dm_os_wait_stats where wait_type like '%PAGEIO%')
  • Virtual File Stats (select * from sys.dm_io_virtual_file_stats(-1, -1))
  • Stalled I/O Warnings
  • Additional disk based performance counters and available hardware utilities

Don't forget that SQL Server drives read ahead, checkpoint and other I/O patterns to depths beyond a disk queue length of 2 and this is expected.

Bob Dorr
SQL Server Senior Escalation Engineer


%3CLINGO-SUB%20id%3D%22lingo-sub-315506%22%20slang%3D%22en-US%22%3ESQL%20Server%20I%2FO%20Bottleneck%2C%20I%20don't%20have%20one%2C%20YES%20YOU%20DO!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-315506%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3E%20First%20published%20on%20MSDN%20on%20Jul%2008%2C%202008%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%3CP%3EThe%20mistake%20I%20see%20people%20make%20is%20when%20looking%20at%20the%20SQL%20Server%20PAGE%20I%2FO%20waits%20and%20stalled%20I%2FO%20warnings%20is%20when%20comparing%20it%20to%20the%20Avg.%20Disk%20Seconds%2FTransfer.%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Everyone%20seems%20to%20forget%20that%20%3CEM%3E%20average%20means%20average%20%3C%2FEM%3E%20and%20they%20look%20at%20the%20value%20and%20say%20I%20don't%20see%20any%20I%2FO%20taking%20longer%20than%2015%20seconds.%3C%2FP%3E%0A%20%20%3CP%3ETake%20the%20following%20example%20over%20a%205%20minute%20window.%3C%2FP%3E%0A%20%20%3CBLOCKQUOTE%3E%0A%20%20%20%3CP%3E2000%20I%2FOs%20at%208ms%26nbsp%3B%26nbsp%3B%20%3D%2016000ms%3C%2FP%3E%0A%20%20%20%3CP%3E1%20I%2FO%20at%2015000ms%26nbsp%3B%26nbsp%3B%20%3D%2015000%20ms%3C%2FP%3E%0A%20%20%20%3CP%3E%3CSTRONG%3E%20TOTAL%20TIME%3A%2031000ms%20%3C%2FSTRONG%3E%3C%2FP%3E%0A%20%20%20%3CP%3E%3CSTRONG%3E%20AVG%3A%2015.49ms%26nbsp%3B%26nbsp%3B%20(31000%20%2F%202001%20I%2FO%20Requests)%20%3C%2FSTRONG%3E%3C%2FP%3E%0A%20%20%3C%2FBLOCKQUOTE%3E%0A%20%20%3CP%3EYou%20are%20not%20going%20to%20see%20a%20spike%20to%2015000ms%20in%20the%20Avg%20Disk%20Sec%2FTransfer%20but%20instead%20a%20small%20change%20from%20the%20norm%20that%20is%20very%20hard%20to%20detect%20from%20just%20the%20Avg%20Disk%20Sec%2FTransfer.%3C%2FP%3E%0A%20%20%3CP%3ERemember%20to%20pay%20close%20attention%20to%20the%20following%20SQL%20Server%20data%20points%20to%20help%20determine%20your%20I%2FO%20response%20rates.%3C%2FP%3E%0A%20%20%3CUL%3E%0A%20%20%20%3CLI%3EPAGE%20I%2FO*%20Waits%26nbsp%3B%26nbsp%3B%20(select%20*%20from%20sys.dm_os_wait_stats%20where%20wait_type%20like%20'%25PAGEIO%25')%3C%2FLI%3E%0A%20%20%20%3CLI%3EVirtual%20File%20Stats%20(select%20*%20from%20sys.dm_io_virtual_file_stats(-1%2C%20-1))%3C%2FLI%3E%0A%20%20%20%3CLI%3EStalled%20I%2FO%20Warnings%3C%2FLI%3E%0A%20%20%20%3CLI%3EAdditional%20disk%20based%20performance%20counters%20and%20available%20hardware%20utilities%3C%2FLI%3E%0A%20%20%3C%2FUL%3E%0A%20%20%3CP%3EDon't%20forget%20that%20SQL%20Server%20drives%20read%20ahead%2C%20checkpoint%20and%20other%20I%2FO%20patterns%20to%20depths%20beyond%20a%20disk%20queue%20length%20of%202%20and%20this%20is%20expected.%3C%2FP%3E%0A%20%20%3CP%3EBob%20Dorr%20%3CBR%20%2F%3E%20SQL%20Server%20Senior%20Escalation%20Engineer%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-315506%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20MSDN%20on%20Jul%2008%2C%202008%20The%20mistake%20I%20see%20people%20make%20is%20when%20looking%20at%20the%20SQL%20Server%20PAGE%20I%2FO%20waits%20and%20stalled%20I%2FO%20warnings%20is%20when%20comparing%20it%20to%20the%20Avg.%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-315506%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EStorage%20Engine%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Version history
Last update:
‎Jan 15 2019 10:58 AM
Updated by: