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
On the new server with flash storage it was
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.
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
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?
Best Answer
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.).
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.)
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:
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.
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.
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.
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.