martes, 22 de septiembre de 2015

Getting Started with Diskspd

SQL Server

http://www.brentozar.com/archive/2015/09/getting-started-with-diskspd


 Diskspeed, or diskspd.exe, is Microsoft’s replacement for SQLIO. While I’m not going to replace our SQLIO tutorial, you can use the information in here to replicate the same type of tests on your systems to get the information you need. During the Dell DBA Days, Doug and I used diskspd as one of our techniques for getting a baseline of raw performance. We wanted to get an idea of how fast the servers and storage before running SQL Server specific tests.

HOW DO I GET DISKSPD?

You can download diskspd directly from Microsoft – Diskspd, a Robust Storage Testing Tool, Now Publically Available. That page has a download link as well as a sample command.
The upside is that diskspd is a fully self-contained download. You don’t need Java, .NET, or anything else installed to run it. Apart from Windows – you’ll still need Windows.

HOW DO I USE DISKSPD?

With the command line, of course!
In all seriousness, although diskspd is the engine behind Crystal Disk Mark, it stands on its own as a separate tool. Download the executable and unzip it to an appropriate folder. There are going to be three sub-folders:
  • amd64fre – this is what you need if you have a 64-bit SQL Server
  • armfre
  • x86fre
I took the diskspd.exe file from the appropriate folder and dumped it in C:\diskspd so I could easily re-run the command. Let’s fire up a command prompt and try it out.
Here’s a sample that we ran:


diskspd.exe -b2M -d60 -o32 -h -L -t56 -W -w0 O:\temp\test.dat > output.txt

Breaking it down:
  • -b2M – Use a 2 MB I/O size. For this test, we wanted to simulate SQL Server read ahead performance.
  • -d60 – Run for 60 seconds. I’m lazy and don’t like to wait.
  • -o32 – 32 outstanding I/O requests. This is your queue depth 32.
  • -h – This disables both hardware and software buffering. SQL Server does this, so we want to be the same.
  • -L – Grab disk latency numbers. You know, just because.
  • -t56 – Use 56 threads per file. We only have one file, but we have 56 cores.
  • -W – Warm up the workload for 5 seconds.
  • -w0 – No writes, just reads. We’re pretending this is a data warehouse.
  • D:\temp\test.dat – our sample file. You could create a sample file (or files) by runningdiskspd with the -c flag.
  • > output.txt – I used output redirection to send the output to a file instead of my screen.

HOW DO I READ DISKSPD RESULTS?

You’re going to get a lot of information back from this command. You’re going to want to close the window and back away quickly. Don’t. This is good stuff, I promise.
The first thing you’ll see is a recap of the command line you used. Then you’ll immediately see a summary of the commands:

timespan:   1
-------------
duration: 60s
warm up time: 0s
cool down time: 0s
measuring latency
random seed: 0
path: 'O:\temp\test.dat'
    think time: 0ms
    burst size: 0
    software and hardware write cache disabled
    performing read test
    block size: 2097152
    using interlocked sequential I/O (stride: 2097152)
    number of outstanding I/O operations: 32
    thread stride size: 0
    threads per file: 56
    using I/O Completion Ports
    IO priority: normal

That’s a lot easier than trying to read a set of command line flags. Six months from now, I can review older runs of diskspd and understand the options that I used. diskspd is already winning oversqlio.
Next up, you’ll see a summary of CPU information. This information will help you understand if your storage test is CPU bottlenecked – if you know the storage has more throughput or IOPS capability, but your tests won’t go faster, you should check for bottlencks. The last line of this section (and every section) will provide an average across all CPUs/threads/whatevers.

actual test time:   60.01s
thread count:       56
proc count:     56

CPU |  Usage |  User  |  Kernel |  Idle
-------------------------------------------
   0|  23.17%|  10.36%|   12.81%|  76.83%
   1|   4.24%|   2.40%|    1.85%|  95.76%
   2|   9.71%|   7.37%|    2.34%|  90.29%
   3|   1.48%|   0.70%|    0.78%|  98.52%
...
...
-------------------------------------------
avg.|  14.11%|  12.48%|    1.63%|  85.89%

After the CPU round up, you’ll see a total I/O round up – this includes both reads and writes.

Total IO
thread |       bytes     |     I/Os     |     MB/s   |  I/O per s |  AvgLat  | LatStdDev |  file
-----------------------------------------------------------------------------------------------------
     0 |      2950692864 |         1407 |      46.89 |      23.44 | 1323.427 |   107.985 | O:\temp\test.dat (50GB)
     1 |      3013607424 |         1437 |      47.89 |      23.94 | 1310.516 |   141.360 | O:\temp\test.dat (50GB)
     2 |      2950692864 |         1407 |      46.89 |      23.44 | 1319.540 |   113.993 | O:\temp\test.dat (50GB)
     3 |      2950692864 |         1407 |      46.89 |      23.44 | 1315.959 |   122.280 | O:\temp\test.dat (50GB)
-----------------------------------------------------------------------------------------------------
total:      167975583744 |        80097 |    2669.28 |    1334.64 | 1307.112 |   162.013

Look at all of those bytes!
If the I/O numbers initially seem small, remember that the data is split up per worker thread. Scroll down to the bottom of each section (total, reads, writes) and look at the total line. This rounds up the overall volume of data you’ve collected. The I/Os are recorded in whateverunit of measure you supplied. In our case, the I/Os are 2MB I/Os.

Important Sidebar Your storage vendor probably records their I/O numbers in a smaller I/O measurement, so make sure you do some rough translation if you want to compare your numbers to the advertised numbers. For more discussion, visit IOPS are a scam.
Finally, latency! Everybody wants to know about latency – this is part of what the end users are complaining about when they say “SQL Server is slow, fix it!”

  %-ile |  Read (ms) | Write (ms) | Total (ms)
----------------------------------------------
    min |     13.468 |        N/A |     13.468
...
    max |   1773.534 |        N/A |   1773.534

This table keeps the min, max, and a variety of percentiles about how the storage performed while you were beating on. This information is just as helpful as the raw throughput data – under load your storage may have increased latencies. It’s important to know the storage will behave and respond under load.

HOW OFTEN SHOULD I USE DISKSPD?

Ideally, you should use diskspd whenever you’re setting up new storage or a new server. In addition, you should take the time to use diskspd when you make big changes to storage – use diskspd to verify that your changes are actually an improvement. No, diskspd doesn’t include the work that SQL Server does, but it does show you how your storage can perform. Use it to make sure you’re getting the performance you’ve been promised by your storage vendor.