Using SQLIOSim to Diagnose SQL Server Reported Checksum (Error 824/823) Failures
This is an extension to by previous post about SQLIOSim data integrity testing.
http://blogs.msdn.com/psssql/archive/2008/03/05/how-it-works-sqliosim-checksums.aspxSQLIOSim (.exe/.com) is the predecessor to SQLIOStress. SQLIOSim is SQL Server independent utility, generating both SQL Server and random I/O patterns.
Demonstration
</EMBED minmax_bound="true">
SQLIOSim demonstration by rdorr
(Larger Video Sizes: http://communityclips.officelabs.com/Video.aspx?videoId=f544ffcc-c92c-4506-8f3d-9f37365768aa)
SQLIOSim is NOT designed to be a performance testing utility as it combines known I/O patterns with random patterns in an effort to test the data integrity capabilities of your I/O path. As an example, some of the testing phases perform unbounded I/O as we have encountered some drivers that will cause blue screens when the I/O depth becomes aggressive.
We recommend you execute SQLIOSim on all I/O paths before you install the actual SQL Server components or anytime you encounter data integrity issues.
Installation
SQLIOSim can be downloaded from the web (http://support.microsoft.com/kb/231619) and is also installed with the SQL Server 2008 components in the MSSQL\BINN location. There are builds available for x86, x64 and ia64 platforms.
SQLIOSim.exe
GUI version of utility
SQLIOSim.com
Command line version of utility
sqliosim.cfg.zip
Contains testing configuration files.
Default.cfg.ini
Recommended for common testing.
Hwcache.cfg.ini
Good for testing behaviors of the hardware cache.
Nothrottle.cfg.ini
Tests pushing the system and drivers at high queue levels.
Seqwrites.cfg.ini
Used to simulate load based activities such as BCP and restore.
Sparse.cfg.ini
Tests sparse file behaviors that are used by DBCC and snapshot databases.
ErrorLog.xslt
Style sheet used to display the sqliosim.log.xml. Created in the specified output directory.
Sqliosim.log.xml
Log file generated by the utility. Multiple executions append to the log file.
Testing
Never use the same file names as a SQL Server database file. SQLIOSim generates random data using the Crypro random data generators. It does not understand the SQL Server I/O format and would damage a database file in an unrecoverable fashion.
For most systems, including those with suspected I/O problems such as checksum failures, edit the default.cfg.ini in your favorite text editor. Update or add files that exercise the same I/O path as the SQL Server database files.
Note: Be sure to increase the size of the files so that any hardware cache performs physical I/O during the tests. Using files that are smaller than the hardware cache does not exercise the I/O path fully.
Note: You typically have to run the utility with elevated administrator privileges.
Default: We recommend at least 10 test cycles at 300 seconds per cycle using the default.cfg.ini for a basic test.
Success or Failure
The log file contains various details about each test pass executed. We recommend you enable stop on error. If SQLIOSim encounters an error that would lead to possible data integrity issues details are logged and the testing is halted.
References
How to use the SQLIOSim utility to simulate SQL Server activity on a disk subsystem
http://support.microsoft.com/kb/231619Should I run SQLIOSim? - An e-mail follow-up from SQL PASS 2008 http://blogs.msdn.com/psssql/archive/2008/11/24/should-i-run-sqliosim-an-e-mail-follow-up-from-sql-pass-2008.aspx
What do I need to know about SQL Server database engine I/O?
http://blogs.msdn.com/psssql/archive/2006/11/27/what-do-i-need-to-know-about-sql-server-database-engine-i-o.aspxSQLIOSim is "NOT" an I/O Performance Tuning Tool
http://blogs.msdn.com/psssql/archive/2008/04/05/sqliosim-is-not-an-i-o-performance-tuning-tool.aspxHow It Works: SQLIOSim - Running Average, Target Duration, Discarded Buffers ...
http://blogs.msdn.com/psssql/archive/2008/11/12/how-it-works-sqliosim-running-average-target-duration-discarded-buffers.aspxHow It Works: SQLIOSim [Audit Users] and .INI Control File Sections with User Count Options
http://blogs.msdn.com/psssql/archive/2008/08/19/how-it-works-sqliosim-audit-users-and-ini-control-file-sections-with-user-count-options.aspxUnderstanding SQLIOSIM Output
http://sqlblog.com/blogs/kevin_kline/archive/2007/06/28/understanding-sqliosim-output.aspxBob Dorr
SQL Server Principal Escalation EngineerPublished Friday, December 19, 2008 7:45 PM by psssql
No comments:
Post a Comment