How to use the SQLIO Simulate utility Stress Test.


I’ve to bring up an SQL Instance to production, and before install all the components I decided to verify the behaviour of the server with a stress tool for IO. Choose the tool SQLIOsim to simulate this charge test. You can read How to use the SQLIOSim utility here http://support.microsoft.com/kb/231619/en-us,  and the tool can be downloaded using this link download.microsoft.com/download/6/5/2/65286f65-bff2-42b8-b0c9-87f117855069/sqliosimX64.exe.

After download and unzip it to an folder run the tool.

In this point you can choose what test you want to perform, in my case I’ve choose seqwrites, and default configuration file to test.

During the execution of the process the Memory usage and CPU usage add some increase, but that’s the idea. See next images.

The output of the file can be read here, and in red the detected warnings or problems.

Initializing and loading general Configuration test

Starting Microsoft SQL Server(c) Simulator Stress Test Version 9.00.1399.05
Config file sqliosim.cfg.ini, error log sqliosim.log.xml
CPUCount = 8, Affinity = 0, IOAffinity = 0, MaxMemory = 1733 MB, StopOnError = Yes, TestCycles = 1, TestCycleDuration = 300 s, CacheHitRatio = 10.00%
NoBuffering = Yes, WriteThrough = Yes, UseScatterGather = Yes, ForceReadAhead = Yes, MaxOutstandingIO = 0, TargetIODuration = 100 ms
AllowIOBursts = Yes, DeleteFilesAtStartup = Yes, DeleteFilesAtShutdown = No, StampFiles = No
RandomUser: UserCount = 8, JumpToNewRegionPercentage = 5.00%, MinIOChainLength = 50, MaxIOChainLength = 100, RandomUserReadWriteRatio = 90.00%
RandomUser: MinLogPerBuffer = 64, MaxLogPerBuffer = 8192, RollbackChance = 1.00%, SleepAfter = 5 ms, YieldPercentage = 0.00%
AuditUser: UserCount = 2, BuffersValidated = 64, DelayAfterCycles = 2, AuditDelay = 200 ms
ReadAheadUser: UserCount = 2, BuffersRAMin = 32, BuffersRAMax = 64, DelayAfterCycles = 2, RADelay = 200 ms
BulkUpdateUser: UserCount = 8, BuffersBUMin = 64, BuffersBUMax = 128, DelayAfterCycles = 2, BUDelay = 10 ms
ShrinkUser: MinShrinkInterval = 120 s, MaxShrinkInterval = 600 s, MinExtends = 1, MaxExtends = 20
File #0 C:\sqliosim.mdx: InitialSize = 2674 MB, MaxSize = 5348 MB, Increment = 10 MB, LogFile = No, Shrinkable = Yes, Sparse = No
File #1 C:\sqliosim.ldx: InitialSize = 50 MB, MaxSize = 50 MB, Increment = 0 MB, LogFile = Yes, Shrinkable = No, Sparse = No
File #2 D:\sqliosim.mdx: InitialSize = 4096 MB, MaxSize = 8192 MB, Increment = 10 MB, LogFile = No, Shrinkable = Yes, Sparse = No
File #3 D:\sqliosim.ldx: InitialSize = 50 MB, MaxSize = 50 MB, Increment = 0 MB, LogFile = Yes, Shrinkable = No, Sparse = No
Creating file #1 (D:\sqliosim.mdx)
Expanding file D:\sqliosim.mdx from 0MB to 4096MB
Creating file #3 (C:\sqliosim.ldx)
Expanding file C:\sqliosim.ldx from 0MB to 50MB
Expanding file D:\sqliosim.mdx attempt COMPLETE
Creating file #2 (D:\sqliosim.ldx)
Expanding file D:\sqliosim.ldx from 0MB to 50MB
Expanding file C:\sqliosim.ldx attempt COMPLETE
Creating file #0 (C:\sqliosim.mdx)
Expanding file C:\sqliosim.mdx from 0MB to 2674MB
Expanding file D:\sqliosim.ldx attempt COMPLETE
Expanding file C:\sqliosim.mdx attempt COMPLETE

Starting test cycle

Created buffer pool. Size=1733 MB, buffers 221824, locked pages disabled.
Performing initial Update Scan
1 long IO requests are outstanding for file D:\sqliosim.mdx. Handle 0x2f8. Offset of latest long IO 0x8b4c0000, duration 15203 ms 618 IO requests are outstanding for more than 15 sec.
Checkpointing initial Update Scan
Performing Full Test iteration #1
Stopping simulation
Expanding file C:\sqliosim.mdx from 2674MB to 2804MB
Expanding file C:\sqliosim.mdx attempt COMPLETE
Performing final Checkpoint #1
Performing final Audit Scan #1
Closing file C:\sqliosim.mdx

********** Final Summary for file C:\sqliosim.mdx **********

File Attributes: Compression = No, Encryption = No, Sparse = No
Target IO Duration (ms) = 100, Running Average IO Duration (ms) = 53, Number of times IO throttled = 738, IO request blocks = 2181
Reads = 355, Scatter Reads = 1129, Writes = 57, Gather Writes = 11040, Total IO Time (ms) = 43470010
DRIVE LEVEL: Sector size = 512, Cylinders = 17750, Media type = 0, Sectors per track = 12, Tracks per Cylinders = 63
DRIVE LEVEL: Read cache enabled = Yes, Write cache enabled = No
DRIVE LEVEL: Read count = 1543, Read time = 117664, Write count = 11740, Write time = 43904554, Idle time = 16421, Bytes read = 266095104, Bytes written = 2878513152, Split IO Count = 51, Storage number = 1, Storage manager name = FTDISK
Closing file D:\sqliosim.mdx

********** Final Summary for file D:\sqliosim.mdx **********

File Attributes: Compression = No, Encryption = No, Sparse = No
Target IO Duration (ms) = 100, Running Average IO Duration (ms) = 1, Number of times IO throttled = 902, IO request blocks = 2258
Reads = 412, Scatter Reads = 1200, Writes = 139, Gather Writes = 16757, Total IO Time (ms) = 102839765
DRIVE LEVEL: Sector size = 512, Cylinders = 17750, Media type = 0, Sectors per track = 12, Tracks per Cylinders = 63
DRIVE LEVEL: Read cache enabled = Yes, Write cache enabled = No
DRIVE LEVEL: Read count = 1612, Read time = 95240, Write count = 17003, Write time = 102793679, Idle time = 11373, Bytes read = 117497856, Bytes written = 4366751232, Split IO Count = 2, Storage number = 2, Storage manager name = FTDISK
Closing file D:\sqliosim.ldx

********** Final Summary for file D:\sqliosim.ldx **********

File Attributes: Compression = No, Encryption = No, Sparse = No
Target IO Duration (ms) = 100, Running Average IO Duration (ms) = 177, Number of times IO throttled = 2, IO request blocks = 1
Reads = 0, Scatter Reads = 0, Writes = 70, Gather Writes = 0, Total IO Time (ms) = 17750
DRIVE LEVEL: Sector size = 512, Cylinders = 17750, Media type = 0, Sectors per track = 12, Tracks per Cylinders = 63
DRIVE LEVEL: Read cache enabled = Yes, Write cache enabled = No
DRIVE LEVEL: Read count = 1621, Read time = 95293, Write count = 16998, Write time = 102793577, Idle time = 11376, Bytes read = 117948416, Bytes written = 4366730752, Split IO Count = 2, Storage number = 2, Storage manager name = FTDISK
Closing file C:\sqliosim.ldx

********** Final Summary for file C:\sqliosim.ldx **********

File Attributes: Compression = No, Encryption = No, Sparse = No
Target IO Duration (ms) = 100, Running Average IO Duration (ms) = 100, 
Number of times IO throttled = 0, IO request blocks = 1Reads = 0, Scatter Reads = 0, Writes = 0, Gather Writes = 0, Total IO Time (ms) = 0
DRIVE LEVEL: Sector size = 512, Cylinders = 17750, Media type = 0, Sectors per track = 12, Tracks per Cylinders = 63
DRIVE LEVEL: Read cache enabled = Yes, Write cache enabled = No
DRIVE LEVEL: Read count = 1559, Read time = 117781, Write count = 11753, Write time = 43904757, Idle time = 16536, Bytes read = 266775040, Bytes written = 2878571008, Split IO Count = 51, Storage number = 1, Storage manager name = FTDISK

Finishing first test

Cleaning up buffer pool
Buffer Pool: validated buffers 10669, pages 25523, discarded buffers 672020
Simulator Stress Test Attempt Complete

Initializing and loading SeqWrite test

Starting Microsoft SQL Server(c) Simulator Stress Test Version 9.00.1399.05
Config file C:\SQLIOSimX86\sqliosim.seqwrites.cfg.ini, error log sqliosim.log.xml
CPUCount = 8, Affinity = 0, IOAffinity = 0, MaxMemory = 1735 MB, StopOnError = Yes, TestCycles = 1, TestCycleDuration = 300 s, CacheHitRatio = 100.00%
NoBuffering = Yes, WriteThrough = Yes, UseScatterGather = Yes, ForceReadAhead = No, MaxOutstandingIO = 0, TargetIODuration = 100 msAllowIOBursts = Yes, DeleteFilesAtStartup = Yes, DeleteFilesAtShutdown = No, StampFiles = No
RandomUser: UserCount = 0, JumpToNewRegionPercentage = 5.00%, MinIOChainLength = 50, MaxIOChainLength = 100, RandomUserReadWriteRatio = 90.00%
RandomUser: MinLogPerBuffer = 64, MaxLogPerBuffer = 8192, RollbackChance = 1.00%, SleepAfter = 5 ms, YieldPercentage = 0.00%
AuditUser: UserCount = 0, BuffersValidated = 64, DelayAfterCycles = 2, AuditDelay = 200 ms
ReadAheadUser: UserCount = 0, BuffersRAMin = 32, BuffersRAMax = 64, DelayAfterCycles = 2, RADelay = 200 ms
BulkUpdateUser: UserCount = 8, BuffersBUMin = 600, BuffersBUMax = 1000, DelayAfterCycles = 2, BUDelay = 1 ms
ShrinkUser: MinShrinkInterval = 120 s, MaxShrinkInterval = 600 s, MinExtends = 1, MaxExtends = 20
File #0 C:\sqliosim.mdx: InitialSize = 2674 MB, MaxSize = 5348 MB, Increment = 10 MB, LogFile = No, Shrinkable = Yes, Sparse = No
File #1 C:\sqliosim.ldx: InitialSize = 50 MB, MaxSize = 50 MB, Increment = 0 MB, LogFile = Yes, Shrinkable = No, Sparse = No
File #2 D:\sqliosim.mdx: InitialSize = 4096 MB, MaxSize = 8192 MB, Increment = 10 MB, LogFile = No, Shrinkable = Yes, Sparse = No
File #3 D:\sqliosim.ldx: InitialSize = 50 MB, MaxSize = 50 MB, Increment = 0 MB, LogFile = Yes, Shrinkable = No, Sparse = No
Creating file #1 (D:\sqliosim.mdx)
Expanding file D:\sqliosim.mdx from 0MB to 4096MB
Creating file #3 (C:\sqliosim.ldx)
Expanding file C:\sqliosim.ldx from 0MB to 50MB
Expanding file D:\sqliosim.mdx attempt COMPLETE
Creating file #2 (D:\sqliosim.ldx)
Expanding file D:\sqliosim.ldx from 0MB to 50MB
Expanding file C:\sqliosim.ldx attempt COMPLETE
Creating file #0 (C:\sqliosim.mdx)
Expanding file C:\sqliosim.mdx from 0MB to 2674MB
Expanding file D:\sqliosim.ldx attempt COMPLETE
Expanding file C:\sqliosim.mdx attempt COMPLETE

Starting test cycle

Created buffer pool. Size=1735 MB, buffers 222080, locked pages disabled.
Performing initial Update Scan
1 long IO requests are outstanding for file D:\sqliosim.mdx. Handle 0x334. Offset of latest long IO 0x832c0000, duration 15797 ms
1149 IO requests are outstanding for more than 15 sec.
Checkpointing initial Update Scan
Performing Full Test iteration #1
Expanding file D:\sqliosim.mdx from 4096MB to 4296MB
Expanding file D:\sqliosim.mdx attempt COMPLETE
Performing final Checkpoint #1
Performing final Audit Scan #1
Test cycle complete
Stopping simulation
Closing file C:\sqliosim.mdx

********** Final Summary for file C:\sqliosim.mdx **********

File Attributes: Compression = No, Encryption = No, Sparse = No
Target IO Duration (ms) = 100, Running Average IO Duration (ms) = 234, Number of times IO throttled = 8638, IO request blocks = 1
Reads = 18, Scatter Reads = 5782, Writes = 38, Gather Writes = 30970, Total IO Time (ms) = 55008181
DRIVE LEVEL: Sector size = 512, Cylinders = 17750, Media type = 0, Sectors per track = 12, Tracks per Cylinders = 63
DRIVE LEVEL: Read cache enabled = Yes, Write cache enabled = No
DRIVE LEVEL: Read count = 5946, Read time = 892684, Write count = 34873, Write time = 55764445, Idle time = 87698, Bytes read = 3919501824, Bytes written = 8038351872, Split IO Count = 349, Storage number = 1, Storage manager name = FTDISK
Closing file D:\sqliosim.mdx

********** Final Summary for file D:\sqliosim.mdx **********

File Attributes: Compression = No, Encryption = No, Sparse = No
Target IO Duration (ms) = 100, Running Average IO Duration (ms) = 186, Number of times IO throttled = 10782, IO request blocks = 1
Reads = 18, Scatter Reads = 8876, Writes = 38, Gather Writes = 37762, Total IO Time (ms) = 153830931
DRIVE LEVEL: Sector size = 512, Cylinders = 17750, Media type = 0, Sectors per track = 12, Tracks per Cylinders = 63
DRIVE LEVEL: Read cache enabled = Yes, Write cache enabled = No
DRIVE LEVEL: Read count = 8895, Read time = 1289772, Write count = 37856, Write time = 152562390, Idle time = 60838, Bytes read = 5103788032, Bytes written = 9776368640, Split IO Count = 6, Storage number = 2, Storage manager name = FTDISK
Closing file D:\sqliosim.ldx

********** Final Summary for file D:\sqliosim.ldx **********

File Attributes: Compression = No, Encryption = No, Sparse = No
Target IO Duration (ms) = 100, Running Average IO Duration (ms) = 100, Number of times IO throttled = 0, IO request blocks = 1
Reads = 0, Scatter Reads = 0, Writes = 0, Gather Writes = 0, Total IO Time (ms) = 0
DRIVE LEVEL: Sector size = 512, Cylinders = 17750, Media type = 0, Sectors per track = 12, Tracks per Cylinders = 63
DRIVE LEVEL: Read cache enabled = Yes, Write cache enabled = No
DRIVE LEVEL: Read count = 8903, Read time = 1289807, Write count = 37849, Write time = 152562316, Idle time = 60828, Bytes read = 5104238592, Bytes written = 9776339968, Split IO Count = 6, Storage number = 2, Storage manager name = FTDISK
Closing file C:\sqliosim.ldx

********** Final Summary for file C:\sqliosim.ldx **********

File Attributes: Compression = No, Encryption = No, Sparse = No
Target IO Duration (ms) = 100, Running Average IO Duration (ms) = 100, Number of times IO throttled = 0, IO request blocks = 1
Reads = 0, Scatter Reads = 0, Writes = 0, Gather Writes = 0, Total IO Time (ms) = 0
DRIVE LEVEL: Sector size = 512, Cylinders = 17750, Media type = 0, Sectors per track = 12, Tracks per Cylinders = 63
DRIVE LEVEL: Read cache enabled = Yes, Write cache enabled = No
DRIVE LEVEL: Read count = 5957, Read time = 892743, Write count = 34884, Write time = 55764565, Idle time = 87794, Bytes read = 3920075264, Bytes written = 8038396928, Split IO Count = 349, Storage number = 1, Storage manager name = FTDISK

Finishing Second test

Cleaning up buffer pool
Buffer Pool: validated buffers 260732, pages 839906, discarded buffers 1909624
Simulator Stress Test Attempt Complete

In both tests 1 warning appear in this file D:\sqliosim.mdx.

1 long IO requests are outstanding for file D:\sqliosim.mdx.   Handle 0x334. Offset of latest long IO 0x832c0000, duration 15797 ms1149 IO requests are outstanding for more than 15 sec. Handle 0x2f8. Offset of latest long IO 0x8b4c0000, duration 15203 ms 618 IO requests are outstanding for more than 15 sec. 

This does not mean that you have or will have a problem, but only that you should check firmware, drivers and updates for your system related to disks, HBA’s and Storage.

Hope that this information can be useful.

Advertisements

About rodvars
Been working in IT Services/Consulting for the past 15 years. My main areas of work are planning, development, managing and administration System infrastructures focusing on optimizing user processes, enforcing business security, performance enhancements, high availabilty and infrastucture scalability.

One Response to How to use the SQLIO Simulate utility Stress Test.

  1. Should i simply just point out just how nice it is to find a person that really appreciates exactly what they’re speaking about on the web. You without a doubt realize how to put across an issue. Thanks!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: