SQL Server Disk throughput slower than diskspd This is a bit of a broad question but I am trying to understand a storage performance behavior with two of our servers.

I was following https://www.brentozar.com/archive/2015/09/getting-started-with-diskspd/

On one server I ran dskspd with the below parameters on the same disk as the DB.

diskspd.exe -b2M -d60 -o32 -h -L -t56 -W -w0 -c10G G:\MP13\Data\test.dat


and got around 1400MB/s

I was also able to get comparable throughput using a T-SQL query as below and calculating the throughput from the number of reads and time.  I got this from Glenn Berry SQL Course on PluralSight "Improving Storage Subsystem Performance
".

set statistics io on
set statistics time on
checkpoint
dbcc dropcleanbuffers
select count(*) from table1 with(index(1))


On the other server though, I can get the high throughput numbers from the diskio tool but using SQL server method I am not able to get the throughput numbers.  the SQL numbers are close to what I get if I run diskspd in single thread, even though the plan is running in parallel.

So I was wondering what are the things I can check to see why SQL Server's IO is slow or why SQL Server is not able to push more IO's through.
There are a few conceptual things to work through. When you run a tool like diskspd, you're benchmarking theoretical limits of your storage, not guaranteeing a specific performance profile of a query. Additionally, you're only testing for the configured pattern (2MB block size, queue depth of 32, 56 threads, 100% read [random? sequential?]). SQL Server has varied read/write patterns and there's no guarantee that your query follows this test pattern. Essentially, you're testing two different things with diskspd and a query. 

The following two points are primarily valid if you're using a SAN. 

Additionally, different storage tools run tests in different ways. sqlio sizes a 10GB test file with a null byte (0x0). diskspd appears to have a varying pattern, but it repeats nonetheless. 



fio sizes with random data. I've only used vdbench once and have honestly not checked what it uses to fill files. I bring this up because most SANs will compress and deduplicate empty space and repeating patterns. As such, you're not necessarily testing performance on a files of a similar size (assuming your SQL Server data file is 10GB) on the SAN. Your data file can be compressed and deduped, to be sure, but probably not to the same level as files generated by diskspd. 

This leads to a final point I'd like to make. Depending on your SAN's cache size, generally a 10GB file isn't large enough to actually even test your SAN performance (from an IOPS perspective). The SAN controller is probably able to compress and dedupe this test.dat file down to something of reasonable enough size to sit in controller cache and never actually touch the backing disks. As such, what you're testing is the path between the SAN controller and the OS, which you've identified as 1400MB/s.