1

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.

  • This doesn't mean SQL Server's IO is slow, it just means your queries are pushing a great deal of IO. In SQL Server we do as much IO as we need to and don't try to flood the system with every single query as that'd lead to a down system really fast. Nothing is wrong IMHO. – Sean Gallardy Oct 26 '17 at 11:25
  • @SeanGallardy-Microsoft , Thank, Its the same query though on the same table but different server. Actually I was trying to troubleshoot a reporting SP where it runs fine on the first system and is much slower on the other. Most of the wait type was PAGELATCH_SH which lead me to check the IO performance. – jesijesi Oct 26 '17 at 19:41
  • Your thread count in diskspd may be a bit higher than SQL will go for you- How many cores are on the actual machine you are testing with? A parallel plan doesn't necessarily equate to the threadcount 1:1 in diskspd. Are you saying PAGELATCH_SH or PAGEIOLATCH_SH ? – Mike Walsh Oct 26 '17 at 20:10
  • Yeah sorry, PAGEIOLATCH_SH. 6 cores, maxdop 3. – jesijesi Oct 27 '17 at 0:47
4

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.

enter image description here

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.

  • Thanks, I know there are a lot of variables but why I am skeptical is our backup ssystem(actifio) which uses some shadow copy thingy to backup SQL db is also able to push 1500MB/s throughput from the disk. Its only the second SQL server that is about 1/5th slower, a second SQL sever we have with the same DB, same Data, using the same SAN doesnt seem to have issues achieving those high numbers. – jesijesi Oct 26 '17 at 23:28
  • Is Actifio able to push the same throughput on both servers? Are these servers virtual or physical? – swasheck Oct 27 '17 at 16:55
  • Yes, I could see that during backup periods the throughput for the disk where my DB is goes to 1500MB/s. The bad server is virtual though. Now I am thinking that SQL Server is somehow not issuing bigger reads and only doing 8K reads. Read-ahead reads can be as high as 512KB/sec apparently. So I guess I have to do some more testing. – jesijesi Oct 27 '17 at 20:40
  • Wait. So the slow server is virtual and the fast one is physical? If that's the case then there are more things to consider, such as just configuration, virtual hardware settings, other guests on the host, and other hosts in the chassis (if there are are multiple physical hosts in one rack). – swasheck Oct 30 '17 at 12:40
2

I went through the same exercise deploying hardware along the lines of Microsoft's Fast Track Data Warehouse strategy. Their Fast Track Data Warehouse Reference Guide for SQL Server 2012 covers the topics you discovered and already tested, like MCR (maximum consumption rate) for a query with data already in the buffer pool and BCR (benchmark consumption rate) for a real-world query where some, if not all data, needs to come from the disk subsystem. Query complexity also affects the overall throughput (using a TPC-H benchmark dataset, their example shows throughput rates from 56 to 201 MB/s per core).

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.