Click here to monitor SSC
  • Av rating:
  • Total votes: 10
  • Total comments: 0
Robert Sheldon

The SQL Server Sqliosim Utility

02 January 2014

The sqliosim utility is provided with SQL Server to test the I/O stability and 'correctness' of a server. It doesn't measure performance but simulates the read, write, checkpoint, backup, sort, and read-ahead activities of a typical SQL Server instance under load. It is generally used before installing SQL Server in order to ensure that new hardware can handle your expected loads. Bob Sheldon explains.

When we consider the features and functionality included with SQL Server, we sometimes overlook the utilities that build on and enhance SQL Server’s capabilities. Yet many of these programs can prove to be powerful adjuncts to our arsenal of tools. One such utility is sqliosim, a simulation tool that lets you stress test I/O activity on the disk subsystem that hosts the SQL Server data and log files.

The sqliosim utility simulates SQL Server read, write, sort, backup, checkpoint and read-ahead patterns for both user and system activity. The tool creates sample data and log files in order to perform SQL Server reliability and integrity tests on the disk subsystem. Because the utility runs independently of the SQL Server engine, you can use it before installing SQL Server in order to ensure that new hardware can handle your expected loads.

NOTE: The sqliosim tool is not designed for performance testing, only I/O stress testing. For example, you should not use the utility to perform benchmark tests or to determine a storage system’s I/O capacity. For this type of testing, a tool such as sqlio is a better fit.

Beginning with SQL Server 2008, sqliosim is included with your installation (in the Binn folder). For versions prior to SQL Server 2008, you can download the tool package at http://support.microsoft.com/kb/231619, where you can also view additional information about the utility.

If you download the package, you’ll find that it contains sample configuration files not included with the SQL Server installation. (We’ll be covering configuration files later in the article.) However, the utility itself is not as current as the one that comes with SQL Server 2008 or later, so be sure to use the one in the Binn folder even if you download the package.

Running the sqliosim Command-Line Utility

The sqliosim tool comes in two versions: a command-line utility (sqliosim.com) and a graphical application (sqliosim.exe). Both tools perform the same functions. We cover the graphical application later in the article. In this section, we focus on the command-line utility.

Before we get into that discussion, however, you might want to add the path for the Binn folder to the Path environment variable on your system, if you haven’t already done so. The method you use to modify the Path system variable varies from one Windows operating to the next. In Windows 7, for example, one way to access the Path variable is to open Control Panel, click System and Security, and then click System. In the left pane, select Advanced system settings, which launches the System Properties dialog box. On the Advanced tab, click the Environment Variables button. When the Environmental Variables dialog box appears, select the Path variable in the System variables list, and then click Edit. This launches the Edit System Variable dialog box. In the Variable value text box, append the current value by adding a semi-colon and the path to the tablediff directory, as in ;C:\Program Files\Microsoft SQL Server\MSSQL11\MSSQL\Binn.

Once you’ve updated the Path variable, you’re ready to test the sqliosim utility, but first, a few words of warning:

  • The sqliosim utility can generate a test load capable of overloading disk subsystems. Use caution running the tool in a production environment. The utility is best used in preparation for a SQL Server deployment.
  • Do not specify actual database files for testing. The utility can overwrite data with random test patterns and damage the file.
  •  Run the test against the location where you’ll be storing your database files so the utility is using the same I/O path that SQL Server will use.

When the sqliosim utility runs, it uses a set of default parameter values to control the various test-related operations. However, you can override some of the default values by including one or more of the utility’s supported options, which are described in Table 1.

Option

Description

-cfg

Specifies a configuration file to use when running a sqliosim command. The configuration file lets you set any of the parameter values that define the stress test operations, which means you can override any of the default parameter values. If you specify a file that doesn’t exist, the utility returns an error.

-d

Specifies the duration of the main test cycle in seconds. The time doesn’t include the preparation and verification cycles. The default is 300 seconds, which is the Microsoft recommendation.

-dir

Specifies the location of the data and log files used for testing. The utility generates the test files automatically in this location. By default, the data file is named sqliosim.mdx, and the log file is named sqliosim.ldx. These extensions are used so that existing .mdf and .ldf files are not inadvertently overwritten.

-log

Specifies the path and file name of the utility’s log file, which is used to record test result information, including warnings and errors. By default, the file is saved to the system’s root directory and named sqliosim.log.xml. If the file already exists when you run the command, the original file is renamed by appending a numeral to the name, as in sqliosim.log.xml.1.

-save

Specifies the path and file name of the configuration file created when you run the sqliosim command and include this option. The file’s parameter settings use any option values specified with the command. For options not specified, the parameters use the default values. This option lets you create an initial configuration file that you can then modify.

-size

Specifies the initial size in megabytes (MB) of the test data file. The file can grow up to twice this size. The default initial size of the data file is 260 MB. The size of the test log file is set to half the size the data file.

Table 1: The options available to the sqliosim command-line utility

If you don’t use a configuration file when running the command-line utility, you must include the -dir option and specify the target directory for the data and log files that will be used for testing. If you do use a configuration file, the values specified in command-line options override those specified in the file. (More on the configuration file shortly.)

Now that we’ve gone over the basics, let’s get started. Open a command-prompt window and enter the following command, using the directory paths appropriate for your system:

sqliosim -dir c:\datafiles\sim -log c:\datafiles\sim\simlog.xml 

We start with the utility name (sqliosim), followed by the -dir and -log options. In this case, I’m using the c:\datafiles\sim folder for the data and log files, as well as for the utility’s log file, which I’ve named simlog.xml.

When you execute the sqliosim command, the utility starts running its test and the command-prompt window displays processing information, such as that shown in Figure 1. The information is updated regularly throughout the testing.

Figure 1: Running a sqliosim command at a command prompt

It can take a fair amount of time to run the full test unless you override some of the default parameter values. However, you can stop the process at any time by pressing Ctrl+C.

Even if you run the test only a minute or two, you’ll find that several files have been created in the target directory: the data and log test files, the utility’s output log file, and a file named ErrorLog.xslt, a style sheet used to display the utility’s output log file in Internet Explorer.

If you double-click the log file (in this case, the simlog.xml file), it will open in Internet Explorer and display the events returned by the sqliosim utility during the test, at least that portion of the test you performed. Figure 2 shows what that file’s data might look like if you ran the test for only a couple minutes.

Figure 2: Viewing the simulator stress test results

Although it’s difficult to read the content from a screenshot, you might be able to glean from this figure that the file contains information about the events generated by the utility, such as creating a file or performing an initial update scan. The log file provides details about each test pass, including any error and warning events that might have been generated.

You can instead view the file in Notepad or in another text editor. As Figure 3 shows, each XML element contains information about the individual events.

Figure 3: Viewing the contents of the stress test results XML file

When you first run the sqliosim utility, you might want to perform a shorter and smaller test just to get a sense of if and how the tool is working. One way to do this is to include the -d and -size options, as shown in the following command:

sqliosim -dir c:\datafiles\sim -log c:\datafiles\sim\simlog.xml -d 60 -size 2

By including these options, the main test cycle will last only 60 seconds, rather than the default 300 seconds, and the initial size of the data file will be only 2 MB, as opposed to the default 260 MB. However, although these settings will limit the test, the sqliosim utility will still run the default 12 test cycles. Unfortunately, the command-line utility does not support an option to control the number of test cycles. For that, we need to use a configuration file.

Creating a configuration file

As you saw in Table 1, the sqliosim command-line utility supports the -cfg option, which specifies a configuration file to use when running the utility. A configuration file supplies a set of parameter values that define the attributes of the test files and how the tests should be performed. The configuration file offers many more options than what are available to the sqliosim command-line utility alone.

The easiest way to create a configuration file is to include the -save option in your sqliosim command. The option specifies the configuration file’s path and file name. The utility then generates a file that includes all the supported parameters and their values. Default values are always used unless options and their values are explicitly included in the sqliosim command. For example, the following command creates a configuration file that uses the c:\datafiles\sim directory for the data and log files as well as the log output file, named simlog.xml:

sqliosim -dir c:\datafiles\sim -log c:\datafiles\sim\simlog.xml -d 60 -size 2 -save c:\datafiles\sim\simconfig.ini

In addition to using the specified directory, the configuration file will specify that the test cycle runs only 60 seconds and that the initial size of the data file is only 2 MB. The following data shows the contents of the configuration file that the command generates:

[CONFIG]

ErrorFile=c:\datafiles\sim\simlog.xml

CPUCount=4

Affinity=0

IOAffinity=0

MaxMemoryMB=2216

StopOnError=TRUE

TestCycles=12

TestCycleDuration=60

CacheHitRatio=1000

NoBuffering=TRUE

WriteThrough=TRUE

MaxOutstandingIO=0

TargetIODuration=100

AllowIOBursts=TRUE

UseScatterGather=TRUE

ForceReadAhead=TRUE

DeleteFilesAtStartup=TRUE

DeleteFilesAtShutdown=FALSE

StampFiles=FALSE

 

[RandomUser]

UserCount=8

JumpToNewRegionPercentage=500

MinIOChainLength=50

MaxIOChainLength=100

RandomUserReadWriteRatio=9000

MinLogPerBuffer=64

MaxLogPerBuffer=8192

RollbackChance=100

SleepAfter=5

YieldPercentage=0

CPUSimulation=FALSE

CPUCyclesMin=0

CPUCyclesMax=0

 

[AuditUser]

UserCount=2

BuffersValidated=64

DelayAfterCycles=2

AuditDelay=200

 

[ReadAheadUser]

UserCount=2

BuffersRAMin=32

BuffersRAMax=64

DelayAfterCycles=2

RADelay=200

 

[BulkUpdateUser]

UserCount=8

BuffersBUMin=64

BuffersBUMax=128

DelayAfterCycles=2

BUDelay=10

 

[ShrinkUser]

MinShrinkInterval=120

MaxShrinkInterval=600

MinExtends=1

MaxExtends=20

 

[File1]

FileName=c:\datafiles\sim\sqliosim.mdx

InitialSize=2

MaxSize=4

Increment=10

Shrinkable=TRUE

LogFile=FALSE

Sparse=FALSE

 

[File2]

FileName=c:\datafiles\sim\sqliosim.ldx

InitialSize=1

MaxSize=1

Increment=0

Shrinkable=FALSE

LogFile=TRUE

Sparse=FALSE

 

[File3]

FileName=

As you can see, the configuration file includes a number of sections. (I added the bold to make it easier to pick them out.) Each section contains a set of related parameters and their values:

  • [CONFIG] – Defines the global testing behavior, such as the number of test cycles and duration of each cycle. The ErrorFile parameter corresponds to the -log option available to the command-line utility, and the TestCycleDuration parameter corresponds to the -d option. In this case, the values for both of these parameters are based on the values passed in through the options specified in the sqliosim command.
  • [RandomUser] – Simulates a SQL Server user performing random queries, such as those associated with online transaction processing (OLTP) operations.
  • [AuditUser] – Simulates Database Console Commands (DBCC) statement activity. DBCC statements are used to gather information and perform maintenance and validation tasks.
  • [ReadAheadUser] – Simulates SQL Server read-ahead activity. Read-ahead functionality is used to maximize asynchronous I/O operations and limit query delays.
  • [BulkUpdateUser] – Simulates bulk operations such as BULK INSERT and SELECT…INTO.
  • [ShrinkUser] – Simulates DBCC shrink operations. The parameters in this section can also be used to expand files.
  • [Filex] – Defines the attributes (such as name and size) associated with a test file. The sections are numbered sequentially, as in [File1] and [File2]. In this case, these refer to the data and log test files, sqliosim.mdx and sqliosim.ldx, respectively. However, you can define multiple data and log files. The path specified in the FileName parameter corresponds to the -dir option included in the sqliosim command. Here, the default file name (sqliosim) is used, but you can provide a different name. The InitialSize parameter corresponds to the -size option. The value for this parameter is based on the value passed in through the option.

Once you’ve created your configuration file, you can edit the parameter values in a text editor such as Notepad. For example, to make our initial demonstrations easier, we can change the TestCycles property in the [CONFIG] section from the default 12 to a setting of 1. Although Microsoft recommends that you perform at least 10 test cycles, starting with only one cycle lets you become familiar with the utility and better understand its capabilities.

Once you’ve updated your configuration file and saved the changes, you can call the file when you run your command, without having to specify any other options, as in the following example:

sqliosim -cfg c:\datafiles\sim\simconfig.ini

You can see how easy a configuration file makes using sqliosim. And because sqliosim is available as a command-line utility, you can include it in your scripts and automate its use, with or without a configuration file.

Running the Graphical Interface

Although the sqliosim command-line utility provides a great deal of flexibility, at times you might want the ease of the graphical application (sqliosim.exe), also located in the Binn folder. To launch the utility, you need only double-click the executable. The main window will open, along with the Files and Configuration dialog box, shown in Figure 4.

Figure 4: Configuring the simulator stress test options

The sqliosim application takes the same parameters and default values as the command-line utility. However, you can change any of those values within the dialog box. To set values specific to the data or log file, you must select the file in the grid and then make the changes in the text boxes above the grid.

The sqliosim application also lets you use a custom configuration file. Simply click the Load another config file button and navigate to the file. The values from that file are then loaded into the Files and Configuration dialog box. Figure 5 shows the dialog box after adding the simconfig.ini file we created in the last section.

Figure 5: Loading a configuration file into the sqliosim graphical interface

As you can see, the path for the data and log files have been updated, as has the error log file and several other options. Once you’ve loaded the configuration file, click OK to close the dialog box and go to the main window. From there, click the Start button to begin the testing process. Figure 6 shows the sqliosim utility window with the test in progress.

Figure 6: Running a stress test in the sqliosim graphical interface

Information about the testing process itself appears in the upper pane, with the details being updated constantly, as you saw at the command-prompt window when the test was running. The bottom pane shows the event data that is being logged to the output file. When the test has completed, only the logged output data is displayed, along with a message indicating that the test has completed, as shown in Figure 7.

Figure 7: Viewing the simulator stress test results

At this point, you need only click OK. You can then browse the log to review the details about the test. The log itself will contain information similar to what you saw when you viewed the log created by the sqliosim command-line utility. The graphical interface just makes things a bit easier when you’re running ad hoc tests. As pointed out earlier, however, if you want to automate your tests, stick with the command-line utility. But whichever you choose, each one provides you with a powerful tool for stress testing your disks for SQL Server I/O activity. And given that it’s already built into the product, you have little to lose by trying the utility.

Robert Sheldon

Author profile:

After being dropped 35 feet from a helicopter and spending the next year recovering, Robert Sheldon left the Colorado Rockies and emergency rescue work to pursue safer and less painful interests—thus his entry into the world of technology. He is now a technical consultant and the author of numerous books, articles, and training material related to Microsoft Windows, various relational database management systems, and business intelligence design and implementation. He has also written news stories, feature articles, restaurant reviews, legal summaries, and the novel 'Dancing the River Lightly'. You can find more information at http://www.rhsheldon.com.

Search for other articles by Robert Sheldon

Rate this article:   Avg rating: from a total of 10 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.
 

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Top Rated

Continuous Delivery and the Database
 Continuous Delivery is fairly generally understood to be an effective way of tackling the problems of... Read more...

The SQL Server Sqlio Utility
 If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

The PoSh DBA - Reading and Filtering Errors
 DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Highway to Database Recovery
 Discover the best backup and recovery articles on Simple-Talk, all in one place. Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.