2

We were building a new node in an alwayson availibility group and this new server was on a different storage than the other nodes in the cluster. Since it was a separate storage we wanted to make sure the performance was comparable to the other nodes. The storage engineer told that the new server was on flash storage and should be considerably faster than the old one which was using HDD. We used dskspd and ran various tests and compared to the existing nodes and the results were not drastically different. Since we did not lose performance we decided to go ahead anyway and added the node to the existing cluster and moved some reports to that server and to our surprise it was much faster.

So I decided to do some simple test. I took a table around 200GB and ran the below query to do a clustered index scan

select count(*) from Tabletest with (index(1)) option (maxdop 6)

When I checked on the old server the perfmon counters were something like this enter image description here

On the new server with flash storage it was enter image description here

We can see the lower latency and higher reads/sec and get an idea of why the new flash storage is faster. Now my question is what parameters do we pass in to diskspd to validate this kind of behavour. We ran diskspd with the below parameters.

diskspd -b8K -d60 -o32 -t8 -Sh -r -w0 -L -c5G G:\MP16\DATA\iotest.dat

New node is the one with flash storage.

enter image description here

We tried different file size and different block sizes. Even if I bump up the thread to 80, I am able to push way higher IO's through diskspd on the older disk than what SQL Server could. Screenshot below on old storage with below run

diskspd -b64K -d60 -o32 -t80 -Sh  -w0 -r  -L -c10G G:\MP10\DATA\iotest.dat

enter image description here

So how do we accurately simulate using diskspd a sql server workload(eg clustered index scan)? Is what I am seeing have something to do with the file that diskspd uses for testing and that somehow is compressed by san? I read about -Z parameter but it seems to be applicable only for write tests. Does anyone know how to create a file that is less compressible by the sam or use an actual sql server data file for the test with diskspd?

  • Note the large Avg. Disk Bytes/Read in your clustered index scan. This is probably largly read-ahead, so up to 512KB IOs. You might increase the IO size. But a single query doing a clustered index scan is a pretty artificial scenario. The diskspd results you have may more accurately reflect the real relative performance of the two servers. – David Browne - Microsoft Apr 17 at 18:57
  • I have production workload which is running faster on the new storage and I have tested couple of reports where I see similar behavior which I described in the question. I gave clustered index scan as an example as its simple and easier to isolate and understand in a question. The bottom line is what ever block size is use(74, 512,2M) and what ever file size i use in diskspd(-C, used up to 500GB), HDD storage and Fash storage has similar performance. So that is why I am guessing that there is something else like compression or dedupe on the SAN that might be affecting the performance. – ioquestion Apr 17 at 21:04
  • It's more like the infrastructure around it - virtualization, HBA, HBA config, firmware, fabric, number of servers, SAN load at the time, etc., are playing a part. If you're getting the exact same throughput on both servers at the same rate then it's an infrastructure issue with the HBA, fabric, or SVCs. – Sean Gallardy Apr 18 at 3:02
1

Now my question is what parameters do we pass in to diskspd to validate this kind of behavour.

We really don't know enough about the clustered index to really make those kind of assumptions. For example, you specify MaxDOP 6 but was it run with MaxDOP 6? Does the clustered index have data in it stored in off-row? How much read-ahead was used?

We can approximate sorta kinda the workload given the disk results, but it would, at best, approximate the workload of scanning your clustered index of this specific table with MaxDOP 6. Since this is a secondary replica that's readable and servicing reports, I highly doubt that's indicative of your overall workload.

DiskSpeed is best when trying to test the storage and validate it against a DiskSpeed on another server, making sure you can hit your latency and bandwidth requirements. It's not made to act like SQL Server and you'll rarely be able to get it to behave in such manner due to multiple other influences that do not exist in DiskSpeed (such as waiting on locks, latches, coordinators, spinlocks, etc.).

Even if I bump up the thread to 80, I am able to push way higher IO's through diskspd on the older disk than what SQL Server could.

See above. SQL Server works cooperatively in scheduling, you won't get more throughput through SQL Server than you will through dedicated threads for testing such as this, which isn't doing nearly as much as SQL Server (not that it's a good or bad thing, it's just different as they have different uses.)

Is what I am seeing have something to do with the file that diskspd uses for testing and that somehow is compressed by san?

Benchmarking software, of which DiskSpeed is no exception, has been targeted by storage vendors and has built in algorithms to detect workloads and artificially speed them up. I'm not saying that's what you're seeing but there are multiple factors at play, here. Some things to think about:

  • Size of SAN cache
  • Size of SAN cache devoted for read
  • HBA settings such as Queue Depth
  • Multipathing setup
  • IO Group port statistics
  • Any SVCs in front of the SAN
  • Any specialized drivers that may also be caching, and their cache sizes
  • File data, such as completely random, all zero's, repeating patterns
  • File initialization such as setting a high watermark vs writing random data
  • Thin or thick provisioning of disk resources

Once you know the cache sizes and initialization types of the files along with the settings per server such as HBA settings and multipathing setup, then you can start to investigate the differences in values between the servers/SANs.

Since we don't know any of this data, at best it'll be pure speculation though given that flash is obviously faster could be a majority, or that it's a new array and less systems are on it thus it's less loaded could be a major factor. Really, more investigation on the SAN backends from the SAN admins is required, such as average IOPs while the tests are running on the entire device, max specifications for the devices, IO group/port statistics on load, etc.

We used dskspd and ran various tests and compared to the existing nodes and the results were not drastically different.

This means that either the workload does not hit any limitations or exceed maximums of the SAN, or it means that there are infrastructural issues that are artificially limiting your latency/throughput.

We tried different file size and different block sizes.

The filesize will need to be large enough that it can't be entirely in read cache of the device, unless of course that's what you want to test. You'll need to check the sizes, generally 2x to 4x the size is enough to not be able to cache the data.

Block size changes and number of threads will determine either latency or throughput. It's unclear which you're attempting to test from the results. Testing each has different setups, generally when testing latency a very small block size is used such as 512 bytes or 4k with 4k being the most prevalent these days. This will lead to a maximum number of IOPs with very small sizes, thus very small throughput. To test throughput, a large block size is used, 2MB, 4MB, 8MB and latency tends to be much higher but the maximum throughput is now tested.

Does anyone know how to create a file that is less compressible by the sam or use an actual sql server data file for the test with diskspd?

DiskSpeed doesn't know what SQL Server is, so while you could use a SQL Server data file... DiskSpeed wouldn't use it like SQL Server does. It would just be a file that it reads offsets and data.

  • For example, you specify MaxDOP 6 but was it run with MaxDOP 6? Does the clustered index have data in it stored in off-row? How much read-ahead was used? -> I guess the point is, on the old SAN, around 2K reads per second with comparable bytes per read, the sec per read jumped to a second. even if it did more maxdop or less maxdop or even if it did more read ahead or less read ahead, the bottom line is the old san hit some kind of limit. The counters are disk counters and if sql did read ahead it would have been captured? – ioquestion Apr 17 at 21:23
  • I highly doubt that's indicative of your overall workload. --> I did the test during a maintenance window so no other work load were running. I ran diskspd and a a few reports to capture the counters and saw similar numbers – ioquestion Apr 17 at 21:24
  • The filesize will need to be large enough that it can't be entirely in read cache --> this i understand, so I used a 1TB file with disk speed, and same results. The thing is sql server files are much smaller(around 300GB). I have run the SQL test multiple times and the results does not seem to improve. I am not sure how SAN caches work, but it does not make sense to me that when I run diskspd for the first time with a 1TB file it can get cached, but an sql server file which is 300GB with multiple runs does not get cached. So i assume cache is probably not the issue – ioquestion Apr 17 at 21:29
  • I guess the bottom line is diskspd cannot in any way predict how SQL Server is going to behave on a particular storage , but it does not stop people on the internet on writing blogs about how we can use disk speed to test IO performance for SQL Server. – ioquestion Apr 17 at 21:31
  • @ioquestion I wouldn't say the old SAN hit a limit just based off the data you've shown, it's not nearly enough to prove that, nor what limit was hit. Also note that SQL Server caches reads, assuming enough memory, so the data written to disk is more likely to be write than read, especially on the log. DiskSpeed cannot predict how SQL Server will work but it can test the servre to see if the IO subsystem is better/worse/same as other servers. That's where the benchmarking comes in, benchmarks in a vacuum mean nothing if you have nothing to compare. – Sean Gallardy Apr 18 at 3:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.