Sqlio Disk Subsystem Benchmark Tool DownloadThe SQL Server Sqlio Utility. In my last article, “The SQL Server Sqliosim Utility,” I discussed how to use sqliosim to simulate SQL Server input/output (I/O) patterns in order to stress- test a system’s disk subsystem. Although the tool is useful for simulating user and system activity, it is not designed for performance testing. For example, you would not use sqliosim to conduct benchmark tests or to determine a storage system’s I/O capacity. For that, you need a tool such as sqlio, another free utility available from Microsoft. Despite its name, sqlio is not part of SQL Server, yet it can be an invaluable tool for determining whether the disk drives slated to support SQL Server can meet the system’s I/O needs. The tool lets you push the limits of your disk subsystem in order to determine the hardware’s I/O capacity and to identify performance- related issues before deploying SQL Server. You can use the tool to test a variety of I/O types and sizes against various disk subsystems, including direct attached storage, network attached storage, and storage area networks. Getting Started with the Sqlio Utility. To use sqlio, you must first download the utility from the Microsoft Download Center and install it on the Windows computer where you want to run your tests. You will of course need to be able to access any drives that your tests will target. For this article (in order to keep things simple), I ran my tests against a USB flash drive, which on my system is the E: drive. Note: Thorough testing is a time- consuming process that hits your storage hard. If your initial interest is simply to check out the tool and see how it works, I recommend you start out small, like we’re going here. Whatever you do, don’t run the tool on a live production server or against live production storage. When you first install the sqlio utility, you’ll find several files in the installation folder: EULA. End- user license agreement that covers sqlio usage. Configuration options that identify the target drives, test file names, and specifics about running the tests. Comprehensive details about the utility and its supported options. The utility’s executable file. Appendix A: FIM Disk Subsystem Performance Testing. Updated: August 10, 2010. You can download the SQLIO Disk Subsystem Benchmark Tool (http:// from the Microsoft Download. The SQLIO Disk Subsystem Benchmark Tool is a tool provided by Microsoft (SQLIO from Microsoft Download), which can be used to determine the I/O capacity of a given configuration. However, it comes with the disclaimer that. Using SQLIO. rtf – General information about using the utility. The readme. txt file is a particularly valuable reference for understanding the options available to the sqlio utility. Keep it close at hand. That said, the starting point for using the utility is the param. Modifying the Configuration File. The param. txt file specifies options related to the test files used to test each drive. The configuration file also includes options that determine how to run those tests. When you reference the configuration file in your sqlio command, the utility uses these options to create test files in the target drives, if the files don’t already exist, and to test I/O patterns against the test files, unless those options are overridden within the command. The configuration file includes a listing (row) for each drive to be tested. In other words, each row represents a unique I/O path. You can configure several option values associated with each path, including the target drive and file name. By default, the file includes the following information: c: \testfile. The first line identifies c: \ as the target drive and testfile. The 2 refers to the number of threads to use when testing the I/O patterns, and 0x. CPUs. The 0x. 0 value indicates that all CPUs should be used, which is usually fine for most operations. The final value, 1. MB). In reality, this would be a ridiculously small file to create if you really want to test a system. You should normally aim for a file size at least two- to- four times greater than the size of the storage data cache, unless you simply want to try out the tool, like we’re doing here. The second line of the file is a placeholder. The hash mark (#) at the beginning of the line indicates that it is a comment and will not be processed by the utility. But the second line does point to the fact that you can specify multiple I/O paths within the param. Before you do that, however, you should first test the I/O paths individually to isolate and address any problems and then test them together. For this article, I modified only the drive letter in the first line (from c: to e: ) and saved the file. But you can modify the file however you need. Keep in mind, though, if you’re new to the sqlio utility, you might want to keep your first test short and simple, just to get a feel for how it all works. Creating the Test File. Like many command- line utilities, sqlio supports multiple ways to get things done. For example, one approach to setting up your tests is to call the param. If the test file doesn’t exist, the utility creates it. Another approach is to run the utility once for each I/O path, using the param. For this article, I took that the latter approach, primarily because it allowed me to verify and view the results of the first operation before going on to the next. Also, in an actual testing scenario, each phase can take a significant amount of time, so it’s nice to know that one phase has completed properly before moving on. Because our example targets only one drive, we need to create only one test file, which means we need to run only one sqlio command, at least initially. If you want to follow along, launch a Windows command prompt window and change to the folder where you’ve installed the sqlio utility. Then, at the command prompt, enter the following command: 1sqlio- k. W- s. 10- fsequential- o. Fparam. txt. The command starts by calling the sqlio utility. This is followed by the - k option, which specifies the I/O type (R for read operations and W for write operations). Next, the command uses the - s option to specify the test duration in seconds, in this case, 1. The - f option determines how data blocks are handled. Although you can specify a numerical value, which indicates the number blocks between I/Os, more often than not you’ll use the value random or sequential. The sequential value specifies that blocks should be accessed sequentially, which is typical for operations such as backups and bulk loads. For creating our test file, the sequential option works fine. The next argument in our command is - o, which indicates the number of I/Os that can be outstanding in a single thread. In this case, I’ve specified 8, so each thread can support up to eight outstanding I/O requests. Next we come to the - b option. This is the I/O block size in kilobytes (KB). In the example, I specified 6. When you run the command, it creates the test file on the target drive and returns details about the execution, as shown in the following results: sqliov. SGparameterfileused: param. In addition, you’ll find throughput metrics that show the number of I/Os and MB per second. Although the throughput metrics might provide an early indication of any problems, chances are it’s not until you perform more thorough testing that these values become meaningful. Creating the Batch File. The next step is to define a set of sqlio commands that use a variety of I/O sizes and types to test each I/O path. Note, however, that you’re not trying to simulate SQL Server I/O patterns. Instead, you’re trying to determine your I/O subsystem’s capacity. That means running tests for both read and write operations as well as for random and sequential ones. You should also test against different thread counts with varying outstanding threads for each one. The following commands show a subset of tests you might run against the test file on the target drive: sqlio- k. R- frandom- BH- t. LS- d. Etestfile. R- frandom- BH- t. LS- d. Etestfile. W- frandom- BH- t. LS- d. Etestfile. W- frandom- BH- t. LS- d. Etestfile. R- fsequential- BH- t. LS- d. Etestfile. R- fsequential- BH- t. LS- d. Etestfile. W- fsequential- BH- t. LS- d. Etestfile. W- fsequential- BH- t. LS- d. Etestfile. This controls test buffering, which you can set to no buffering (N), hardware buffering (H), software buffering (S), or both (Y). The commands specify only 1 and 2, but you might want to also include 4, 8, 1. The examples above also test for only four outstanding requests per thread (- o. For each command, I’m also specifying 6. KB block sizes (- b. KB, 1. 28 KB, and 2. KB. In addition, I’ve used the - s option to specify each test run only 6. Normally, you would run your tests longer: 5 to 1. Another option you haven’t seen before is - L, which tells sqlio to include disk latency information in the results. This data can be important to better understanding disk I/O issues. Generally, you’ll want to go with - LS, especially on SMP machines. The next option, - d, identifies the target drives. If more than one drive, simply specify all of them, as in - d. EFG. Notice we no longer need to reference the configuration file because we’re identifying the target drive and test file in our command. Note: Microsoft recommends that you allow time between each sqlio command to let the I/O system return to an idle state. To do so, add the command timeout/T6. The more extensive your set of commands, the more accurately you can arrive at thresholds and identify potential issues. It’s not uncommon, for example, to run a couple hundred sqlio commands to thoroughly test your system. The easiest way to handle multiple commands is to put them into a batch file. For example, I put the sample commands shown above into the file sqlio. I can then run those commands at any time simply be calling the batch file at a command prompt. Running the Tests. Once you’ve created your batch file, you’re ready to go. However, when you call the file at the command prompt, you should also send the results to a text file, as shown in the following command: 1sqlio. Each sqlio command in the file will run, in the order specified, and output the results to the results file. For example, the first command generated the following results on my system and saved them to the sqlio.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
January 2017
Categories |