We have an application that queries a SQL database periodically throughout the day. There are periods of zero or only light activity, interspersed with individual requests for relatively large amounts of data. When those requests come in, the primary objective is to deliver the data quickly, and the secondary objective is to do that cost-effectively. Due to the nature of the application, it is fairly unlikely that the data/indexes will have been cached in RAM from the previous query (different users, working on different portions of the data).

For a system that experiences relatively steady usage, I have heard the rule of thumb to observe the disk queue length and keep that number relatively small. This will specifically run in AWS, where I have seen the rule of thumb that a disk queue length of 1 per 100 IOPS is reasonable.

How can I estimate the IO requirements for such a system? Is the disk queue length a reliable indicator when dealing with individual, bursty queries? Are there other metrics that I should consider?

  • Are there any writes going on, or is this read-heavy? – Jack Douglas Feb 26 '14 at 20:06
  • @JackDouglas: This is 98% reads. There is a trickle of writes. – Eric J. Feb 26 '14 at 23:07
  • 1
    Next question: are the reads scattered or are your "individual requests for relatively large amounts of data" likely to be doing sequential IO? – Jack Douglas Feb 27 '14 at 6:00
  • @JackDouglas: The largest reads are via an indexed view, such that the WHERE clause corresponds to the index, but returning more data than just what is in the index. I'm not sure what that means for the degree of sequential IO. Since the underlying IO subsystem is AWS EBS, I'm not sure how that impacts physical access. – Eric J. Feb 27 '14 at 18:16
  • The underlying IO subsystem will affect consistency of performance, but will care about scattered v sequential access in a similar manner to local storage. Those large reads, how many distinct blocks do they hit typically? The index scan itself will be sequential but the table access will not be if I've understood you correctly so far. – Jack Douglas Feb 27 '14 at 18:30

The primary metric I've always considered for IO in SQL Server is not IOPs or Disk Queue Length, but disk throughput (sec/reads and sec/writes). Overall, databases aren't about how many operations you can throw at a disk, but how quickly those operations are completed. The general rule of thumb is to have less than 20ms/operation (though lower is always better). More detail can be found in this article.

Disk Queue Length is a bogus stat and no longer relevant. The problem with it is that the value measures the queue for a single drive, but now that we live in an age of RAIDs, SANs, and other distributed storage, there's no way to properly translate this value to a meaningful number. A great starting place for performance metrics is this poster from Quest/Dell that gives you a lot of stuff and explanations for why or why not they are important. You don't have to use all of them, but they are a start.

In order to test your IO, you have to understand your workload at its peak. How many transactions and how much is cached? Unless you know and have measured these, it's really hard to judge. You can create work loads and use tools like SQLIO to test your storage, but you will need workload patterns in order to build a proper test.

Finally, a note on AWS: To my knowledge, Amazon will not guarantee IO performance in AWS. This is primarily because the storage is a massive shared resource and it's impossible to gauge the patterns of you and your neighbors on a particular area of storage (see the Noisy Neighbor problem).

My recommendation would be to allocate as much memory as possible. SQL Server will only push stuff out of memory if it's under pressure and space in the buffer pool (based on LRU-K). So if you're buffer pool can store most of the database in memory, you can mitigate some of the bursty performance. Also, consider tactics that can keep cache objects "warm". Finally, keep an eye on SQL 2014 and the new Hekaton feature.

  • "SQL Server will only push stuff out of memory if it's under pressure" or at a checkpoint? – Jack Douglas Feb 26 '14 at 20:00
  • 5
    Checkpoint does not remove objects from the buffer, but writes dirty pages to disk for recovery. It will still maintain the objects in the buffer pool. – Mike Fal Feb 26 '14 at 20:01
  • Thank you for the detailed answer. AWS now has a premium feature called Provisioned IOPS that ensures that the purchased number of IO operations per second can be performed 99.9% of the time. I think an IO operation is defined as reading or writing a 16K block of data. – Eric J. Feb 26 '14 at 22:27
  • @MikeFal: Do you have any thoughts on the testing methodology specifically for this bursty pattern? Just run a single query and watch the counters in question? Run a number of the (normally periodic) queries one after another, watching the counters? – Eric J. Feb 26 '14 at 22:35
  • Yeah, I'm familiar with PIOPS. As I state, I don't want to know how many operations can be performed, I want to know how fast they are. And this isn't something that can be guaranteed by AWS, even on PIOPs. – Mike Fal Feb 26 '14 at 22:35

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.