{"id":1751,"date":"2014-01-27T00:00:00","date_gmt":"2014-01-27T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/the-sql-server-sqlio-utility\/"},"modified":"2021-08-24T13:39:48","modified_gmt":"2021-08-24T13:39:48","slug":"the-sql-server-sqlio-utility","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/database-administration-sql-server\/the-sql-server-sqlio-utility\/","title":{"rendered":"The SQL Server Sqlio Utility"},"content":{"rendered":"
\n

In my last article, “The SQL Server Sqliosim Utility<\/a>,” I discussed how to use sqliosim<\/code> 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<\/code> to conduct benchmark tests or to determine a storage system’s I\/O capacity. For that, you need a tool such as sqlio<\/code>, another free utility available from Microsoft.<\/p>\n

Despite its name, sqlio<\/code> 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<\/i> 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.<\/p>\n

Getting Started with the Sqlio Utility<\/h1>\n

To use sqlio<\/code>, you must first download the utility from the Microsoft Download Center<\/a> 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:<\/code> drive. <\/p>\n

\n

Note:<\/strong> 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. <\/p>\n<\/div>\n

When you first install the sqlio<\/code> utility, you’ll find several files in the installation folder:<\/p>\n

    \n
  • EULA.rtf<\/code> – End-user license agreement that covers sqlio<\/code> usage.<\/li>\n
  • param.txt<\/code> – Configuration options that identify the target drives, test file names, and specifics about running the tests.<\/li>\n
  • readme.txt<\/code> – Comprehensive details about the utility and its supported options.<\/li>\n
  • sqlio.exe<\/code> – The utility’s executable file.<\/li>\n
  • Using SQLIO.rtf<\/code> – General information about using the utility.<\/li>\n<\/ul>\n

    The readme.txt<\/code> file is a particularly valuable reference for understanding the options available to the sqlio<\/code> utility. Keep it close at hand. That said, the starting point for using the utility is the param.txt<\/code> file, so let’s look at how it works.<\/p>\n

    Modifying the Configuration File<\/h1>\n

    The param.txt<\/code> 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<\/code> 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.<\/p>\n

    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:<\/p>\n

    c:\\testfile.dat 2 0x0 100\n#d:\\testfile.dat 2 0x0 100\n\t<\/pre>\n
    The first line identifies  c:\\<\/code>  as the target drive and testfile.dat<\/code> as the test file to create on that drive. The  2<\/code> refers to the number of threads  to use when testing the I\/O patterns, and 0x0<\/code> is the affinity mask to use to bind  operations to specific CPUs. The 0x0<\/code> value indicates that all CPUs should be  used, which is usually fine for most operations.<\/p>\n
    The final value,  100<\/code>,  reflects the size of the test file in megabytes (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.<\/p>\n
    The second line of the file is a placeholder. The  hash mark (#<\/code>) 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.txt<\/code> file, which means you can run a  single test against multiple drives. 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.<\/p>\n
    For this article, I modified only the drive letter in  the first line (from c:<\/code>  to e:<\/code>)  and saved the file. But you can modify the file however you need. Keep in mind, though, if you’re new to the  sqlio<\/code>  utility, you might want to keep your first test short and simple, just to get a feel for how it all works.<\/p>\n
    Creating the Test File<\/h1>\n
    Like many command-line utilities,  sqlio<\/code>  supports multiple ways to get things done. For example, one approach to setting up your tests is to call the  param.txt<\/code>  file for each test you run, no matter how many tests. 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.txt<\/code> file, and then run more extensive  tests after the test files have been created. <\/p>\n
    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.<\/p>\n
    Because our example targets only one drive, we need  to create only one test file, which means we need to run only one sqlio<\/code> 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<\/code> utility. Then, at the command  prompt, enter the following command:<\/p>\n
    sqlio -kW -s10 -fsequential -o8 -b64 -Fparam.txt<\/pre>\n
    The command starts by calling the  sqlio<\/code>  utility. This is followed by the -k<\/code> option, which specifies the I\/O type (R<\/code>  for read operations and W<\/code>  for write operations). Next, the command uses the -s<\/code> option to specify the test  duration in seconds, in this case, 10.<\/p>\n
    The -f<\/code> 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<\/code>  or sequential<\/code>.  A random<\/code>  value indicates that blocks will be chosen randomly, as is often the case with T-SQL queries. The  sequential<\/code>  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<\/code> option works fine.<\/p>\n
    The next argument in our command is  -o<\/code>,  which indicates the number of I\/Os that can be outstanding in a single thread. In this case, I’ve specified  8<\/code>,  so each thread can support up to eight outstanding I\/O requests. Next we come to the  -b<\/code>  option. This is the I\/O block size in kilobytes (KB). In the example, I specified  64<\/code>.  <\/p>\n
    The last option in the command is  -F<\/code>,  which points to the configuration file and the parameters defined within it. 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:<\/p>\n
    sqlio v1.5.SG\nparameter file used: param.txt\n      file e:\\testfile.dat with 2 threads (0-1) using mask 0x0 (0)\n2 threads writing for 10 secs to file e:\\testfile.dat\n      using 64KB sequential IOs\n      enabling multiple I\/Os per thread with 8 outstanding\nusing specified size: 100 MB for file: e:\\testfile.dat\ninitialization done\nCUMULATIVE DATA:\nthroughput metrics:\nIOs\/sec:    40.30\nMBs\/sec:     2.51\n<\/pre>\n
    The results reflect the options we specified within  the configuration file and in the command, such number of threads, block size, target drive, and test file. 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.<\/p>\n
    Creating the Batch File<\/h1>\n
    The next step is to define a set of  sqlio<\/code>  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. <\/p>\n
    The following commands show a subset of tests you  might run against the test file on the target drive:<\/p>\n
    sqlio -kR -frandom -BH -t1 -o4 -b64 -s60 -LS -dE testfile.dat\nsqlio -kR -frandom -BH -t2 -o4 -b64 -s60 -LS -dE testfile.dat\nsqlio -kW -frandom -BH -t1 -o4 -b64 -s60 -LS -dE testfile.dat\nsqlio -kW -frandom -BH -t2 -o4 -b64 -s60 -LS -dE testfile.dat\nsqlio -kR -fsequential -BH -t1 -o4 -b64 -s60 -LS -dE testfile.dat\nsqlio -kR -fsequential -BH -t2 -o4 -b64 -s60 -LS -dE testfile.dat\nsqlio -kW -fsequential -BH -t1 -o4 -b64 -s60 -LS -dE testfile.dat\nsqlio -kW -fsequential -BH -t2 -o4 -b64 -s60 -LS -dE testfile.dat\n \n<\/pre>\n
    Notice that the commands cover read and write  operations (-kR<\/code> and  -kW<\/code>)  as well as random and sequential ones (-frandom<\/code>  and -fsequential<\/code>).  The -B<\/code>  option you have not seen before. This controls test buffering, which you can set to no buffering (N<\/code>),  hardware buffering (H<\/code>),  software buffering (S<\/code>),  or both (Y<\/code>).  We’re going with -BH<\/code>  to best approximate how SQL Server performs, but the default is -BN<\/code>.<\/p>\n
    The next option,  -t<\/code>,  sets the number of threads used to generate the I\/O patterns. The commands specify only  1<\/code>  and 2<\/code>,  but you might want to also include 4<\/code>,  8<\/code>, 16<\/code>, 32<\/code>, 64<\/code>,  or higher. The maximum is 256<\/code>. The examples above also test for only four outstanding requests per thread (-o4<\/code>),  but you should consider mixing this up as well.<\/p>\n
    For each command, I’m also specifying 64-KB block  sizes (-b64<\/code>), but you’ll likely want to  test other values, including 8 KB, 128 KB, and 256 KB. In addition, I’ve used the  -s<\/code> option to specify each test run  only 60 seconds. Normally, you would run your tests longer: 5 to 10 minutes for the initial tests and longer if you  identify any potential problems.<\/p>\n
    Another option you haven’t seen before is  -L<\/code>,  which tells sqlio<\/code>  to include disk latency information in the results. This data can be important to better understanding disk I\/O issues.  You can choose to return system information (S<\/code>)  or processing information (P<\/code>). Generally, you’ll want to go  with -LS<\/code>, especially on SMP machines.<\/p>\n
    The next option,  -d<\/code>,  identifies the target drives. If more than one drive, simply specify all of them, as in  -dEFG<\/code>.  If you do specify multiple drives in the -d<\/code> option, the test file name must be the  same on all target drives.<\/p>\n
    Finally, each of the sample  sqlio<\/code>  commands includes the name of the test file, which we’ve already created. Notice we no longer need to reference the  configuration file because we’re identifying the target drive and test file in our command.<\/p>\n
    \n
    Note:<\/strong> Microsoft recommends that you allow time between each  sqlio<\/code> command to let the I\/O  system return to an idle state. To do so, add the command timeout<\/code> \/T<\/code>  60<\/code>  between the sqlio<\/code> commands in your batch file.  This gives you a 60-second buffer between those commands.<\/p>\n<\/div>\n
    When it comes time for you to actually test your I\/O  subsystem, you’ll likely create many more commands than what we’ve shown here. 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<\/code>  commands to thoroughly test your system.<\/p>\n
    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_commands.bat<\/code> and saved it to  my sqlio<\/code>  installation folder. I can then run those commands at any time simply be calling the batch file at a command prompt.<\/p>\n
    Running the Tests<\/h1>\n
    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:<\/p>\n
    sqlio_commands.bat > sqlio_output.txt\n<\/pre>\n
    As you can see, I’m simply calling the batch file and  saving the results to the sqlio_output.txt<\/code> file. Each sqlio<\/code> 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_output.txt<\/code> file:<\/p>\n
    c:\\sqlio>sqlio -kR -frandom -BH -t1 -o4 -b64 -s60 -LS -dE testfile.dat \nsqlio v1.5.SG\nusing system counter for latency timings, 10000000 counts per second\n1 thread reading for 60 secs from file E:testfile.dat\n      using 64KB random IOs\n      enabling multiple I\/Os per thread with 4 outstanding\n      buffering set to use hardware disk cache (but not file cache)\nusing current size: 100 MB for file: E:testfile.dat\ninitialization done\nCUMULATIVE DATA:\nthroughput metrics:\nIOs\/sec:   427.66\nMBs\/sec:    26.72\nlatency metrics:\nMin_Latency(ms): 2\nAvg_Latency(ms): 8\nMax_Latency(ms): 17\nhistogram:\nms: 0  1  2  3  4  5  6  7  8  9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+\n%:  0  0  0  0  8  0 10 11  0 40  0 20  1  4  4  0  0  0  0  0  0  0  0  0  0\n<\/pre>\n
    Although you can review the general information to  verify that the test you wanted is what you got, it’s the cumulative data that will likely be your focus. For each test,  you’ll receive not only the throughput metrics, as you saw earlier, but also the latency metrics, which include the  minimum, maximum, and average times in milliseconds (ms).<\/p>\n
    Also included with the latency information is a  histogram of latency timings. The first row of the histogram (ms<\/code>)  provides buckets for 0<\/code>  ms through 23<\/code>  ms. The 24+<\/code>  bucket includes everything from 24 ms and above.<\/p>\n
    The second row in the histogram (%<\/code>)  provides a percentage of I\/Os that completed during a particular bucket. For example, in the  4<\/code> ms  bucket, 8% of the I\/Os completed running. Together, the combined bucket percentages should add up to 100% or close to  that. If the numbers don’t match up exactly, it’s because they’re being rounded in the individual buckets.<\/p>\n
    Table 1 lists the cumulative data from each of the  eight sample commands in our batch file, not including the histograms.<\/p>\n\n\n\n\n\n\n\n\n\n\n\n
    \n
    Command<\/b><\/p>\n<\/td>\n
    		\n
    IOs\/sec<\/b><\/p>\n<\/td>\n
    		\n
    MBs\/sec<\/b><\/p>\n<\/td>\n
    		\n
    Min Latency<\/b><\/p>\n<\/td>\n
    		\n
    Avg Latency<\/b><\/p>\n<\/td>\n
    		\n
    Max Latency<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    1<\/p>\n<\/td>\n
    		\n
    427.66<\/p>\n<\/td>\n
    		\n
    26.72<\/p>\n<\/td>\n
    		\n
    2<\/p>\n<\/td>\n
    		\n
    8<\/p>\n<\/td>\n
    		\n
    17<\/p>\n<\/td>\n<\/tr>\n
    \n
    2<\/p>\n<\/td>\n
    		\n
    427.20<\/p>\n<\/td>\n
    		\n
    26.70<\/p>\n<\/td>\n
    		\n
    3<\/p>\n<\/td>\n
    		\n
    18<\/p>\n<\/td>\n
    		\n
    35<\/p>\n<\/td>\n<\/tr>\n
    \n
    3<\/p>\n<\/td>\n
    		\n
    39.61<\/p>\n<\/td>\n
    		\n
    2.47<\/p>\n<\/td>\n
    		\n
    25<\/p>\n<\/td>\n
    		\n
    100<\/p>\n<\/td>\n
    		\n
    176<\/p>\n<\/td>\n<\/tr>\n
    \n
    4<\/p>\n<\/td>\n
    		\n
    39.78<\/p>\n<\/td>\n
    		\n
    2.48<\/p>\n<\/td>\n
    		\n
    25<\/p>\n<\/td>\n
    		\n
    200<\/p>\n<\/td>\n
    		\n
    376<\/p>\n<\/td>\n<\/tr>\n
    \n
    5<\/p>\n<\/td>\n
    		\n
    427.74<\/p>\n<\/td>\n
    		\n
    26.73<\/p>\n<\/td>\n
    		\n
    3<\/p>\n<\/td>\n
    		\n
    9<\/p>\n<\/td>\n
    		\n
    13<\/p>\n<\/td>\n<\/tr>\n
    \n
    6<\/p>\n<\/td>\n
    		\n
    426.58<\/p>\n<\/td>\n
    		\n
    26.66<\/p>\n<\/td>\n
    		\n
    3<\/p>\n<\/td>\n
    		\n
    18<\/p>\n<\/td>\n
    		\n
    22<\/p>\n<\/td>\n<\/tr>\n
    \n
    7<\/p>\n<\/td>\n
    		\n
    39.99<\/p>\n<\/td>\n
    		\n
    2.49<\/p>\n<\/td>\n
    		\n
    25<\/p>\n<\/td>\n
    		\n
    99<\/p>\n<\/td>\n
    		\n
    112<\/p>\n<\/td>\n<\/tr>\n
    \n
    8<\/p>\n<\/td>\n
    		\n
    40.02<\/p>\n<\/td>\n
    		\n
    2.50<\/p>\n<\/td>\n
    		\n
    25<\/p>\n<\/td>\n
    		\n
    199<\/p>\n<\/td>\n
    		\n
    208<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Table 1: Cumulative data returned by the eight sqlio  commands<\/p>\n
    Because of the limitations of our tests, you’re not  likely to glean much meaningful information from these results. (It is a jump drive, after all.) But you can see that  read operations should result in higher throughput rates and I\/Os than write operations and that write operations should  see higher latency rates than read operations. You’ll likely also find that larger I\/O sizes result in higher latencies,  but you might also see increased throughput, although specific configurations can impact these results.<\/p>\n
    When you’re evaluating your test results, you should  look for such issues as where a drive plateaus for random reads, how many outstanding requests it takes to reach maximum  capacity for random writes, or where sequential I\/Os plateau compared to random ones. You’ll also want to compare your  results to the storage system’s specifications to determine whether your system is operating at the capacity that’s  expected.<\/p>\n
    No Turning Back<\/h1>\n
    The sqlio<\/code> utility is easy to set up and simple  to use, and it can provide you with extensive information about a disk drive’s I\/O performance, before you actually  deploy SQL Server. Given the tool’s price tag-free-you have little to lose by trying it out. You can perform benchmark  tests for later comparison, should you run into issues, and you can determine a system’s capacity and whether it will  meet your needs now and in the future. If you’re getting ready to implement a SQL Server solution, now is the time to  run the sqlio<\/code>  utility. Waiting until after you deploy might already be too late.<\/p>\n<\/p><\/div>\n","protected":false},"excerpt":{"rendered":"
    If, before deployment, you need to push the limits of your disk subsystem in order to determine whether the  hardware's I\/O capacity meets the needs of a database application, if you need performance baselines, or if you want to identify any  performance-related issues, then  why not use the sqlio utility?…<\/p>\n","protected":false},"author":221841,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143527],"tags":[4168,4170,4298,4150,4151],"coauthors":[],"class_list":["post-1751","post","type-post","status-publish","format-standard","hentry","category-database-administration-sql-server","tag-database","tag-database-administration","tag-hardware","tag-sql","tag-sql-server"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1751","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221841"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=1751"}],"version-history":[{"count":6,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1751\/revisions"}],"predecessor-version":[{"id":92232,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1751\/revisions\/92232"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=1751"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=1751"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=1751"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=1751"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}