What about LUN Count and Aggregate Service Queue Depth?

There are a few conversations that I have repetitively.  I hope to be able to diagram and illustrate the concepts behind my part in the discussion - because honestly I would rather spend the time in the future learning something new.

Often when I bring these details up with SQL Server experts, responses include:
"SANs are about more than just disks."  (As if I didn't know that!)
"You're wrong... its simple math."  (Well - actually the math AND industry benchmarks agree with me. Show me your numbers and I'll show you mine.)
"This is just Disk101." (Yes, I agree. So I wish folks would start teaching it and applying it rather than shutting me down when I bring it up because... because why, exactly?)

Anyway, I've finally started diagramming these things in a way that I like.  So, here goes.  Here's a Windows LUN on a SQL Server - either a drive letter or a mount point.  Its not using Storage Spaces.  Its not a striped dynamic disk.  I'll deal with those in later posts.  The LUN has a service queue(the dashed lines), and a wait queue.  The wait queue depth is equal to (256 - service queue depth).  On a physical server, assume a LUN queue depth of 32.  (On a virtual server, assume a LUN queue depth of 64.)  Whether physical or virtual server, the maximum transfer size by default is 512kb.  That's important too - we'll talk about maximum transfer size another day.



 *****


Interesting.  32 simultaneous 8kb reads(the blue ovals on the dashes of the line).  Unusual?  Not for SQL Server, if there are any parallel queries that will do index key lookups :-) Not for Oracle either, under the same circumstances.  Note that in SQL Server the default worker count for EE 64bit is at least 512, and each worker can issue its own reads.
128,000 read IOPs to get to 1 gigabyte/s at 8KB read size and 32 outstanding, with .25 ms.  That's a lot. Consider the following Xtremio SLOB test results.
https://community.emc.com/docs/DOC-42804?cmp=soc-cor-glbl-us-sprinklr-TWITTER-Flash-EMCXtremIO-155866600&linkId=12903441
118067 IOPs, with a read latency of 1.1 ms and a write latency of 1.5 ms.  That's quite a bit above the .25 ms read service time in the example above.  That means that the service queue length (and accompanying service queue depth) was greater than 32.  Makes sense - it was 8 VMs :-).
Look - folks keep trying to convince me that *somehow* there is some magic that can make a single SAN LUN scale to 1 gigabyte/s or more with an arbitrary IO size and LUN service queue depth.  Hey - its even hard to hit 1 gigabyte/s with a single LUN and a database workload if you crank queue depth all the way to 256.  I know, I've tried.   If you've got numbers for an all flash array to show me wrong - bring 'em.  I know PCIe flash offers lower latency than an all flash array - that's not in scope of what I'm addressing. (I'm a big fan of PCIe flash for SQL Server tempdb and with read accelerator tier 0 cache software if appropriate to the workload.)

Yesterday there was a kinda "hardee-har-har let's laugh at the math inability of SAN engineers" post*.  I'm willing to take a little constructive criticism.  Truth be told, this wasn't very harsh criticism - and the author makes a valid point (if the example is changed slightly).

Here's the original post.
http://dataidol.com/tonyrogerson/2015/03/20/the-maths-most-san-engineers-do-not-understand-in-context-of-database-servers/

The post doesn't mention average IO size - or the maximum transfer size of the host.  Just specifies that the servers are reporting database server and they move a lot of data.  By default, planning should use 8kb IO size(SQL Server page size and default recommended Oracle block size) unless other numbers are given.  Doesn't mention LUN service queue depth or queue length.    Doesn't mention read or write service times for the setup in the diagram. All it says is: "Assume data is located on a single LUN across all the SSD's." By the way - the storage network protocol also isn't specified.  But including 8 gigabit/s as the server connectivity indicates these are fibre channel connections, as FCoE or iscsi would most likely be using 10gbE of 1 gigabit connectors.  I'm pretty certain this isn't Ficon, its definitely not Infiniband :-)

My comment - which hasn't made it through moderation yet.

*****
"…what happens when you drive down a 10 lane motorway that get’s reduced to just a single lane?"
"Assume data is located on a single LUN across all the SSD's."
A single LUN on the hosts would most likely present a serious DB server bottleneck before getting to the downstream network bottleneck. Assume a LUN service queue depth of 32 and queue length of of the same – since no specification has been made about LUN service queue depth. (32 is maximum LUN queue depth for Hitachi VSP, XP24000 and related offerings from HP as well as for EMC VNX with virtual provisioning.) With those assumptions 128,000 IOPs of 8k each at .25 ms avg latency would be needed to hit 1 gigabyte/sec. 384,000 IOPs of 8k each across the 3 servers to hit 3 gigabytes/sec.
At a LUN service queue depth of 256 it becomes a more manageable 2 ms avg latency. But not all storage controllers will allow such a high LUN service queue depth. Other than 32, the most common LUN queue depth I encounter is 64 - this is the default guest LUN queue depth in a Windows VMWare guest. Without other specification, 32 is the LUN service queue depth that should be used in planning.
With at least 8 or more LUNs at queue depth 32 on a database server I think you could easily be throttled by available downstream bandwidth. But with a single LUN, the most typical case on a database server (with transfer sizes ranging from a single block to the maximum physical transfer size) is that the throughput requirements of the outstanding IO don’t come close to saturating available bandwidth of an 8 gigabit HBA.
Although accommodating for outstanding IO potential isn’t common in SQL Server configurations, for example, those responsible for system design and performance should know that a 4 vcpu VM can drive up to 1000 outstanding IOs, and the more of them that are relegated to wait queue status, the more punishment in latency and throughput the VM will experience.
Its very common for flash and SSD devices to provide more IOPs and bytes/sec than storage controllers can serve, and even more common in my opinion for the storage controllers to be underutilized while host/guest disk IO queues on heavily used reporting database servers are saturated.
*****

If you look at enough perfmon from a SQL Server system, or enough iostat on an Oracle system, you'll see floods of reads come when multiple disk-intensive parallel queries kick off (or a single massively parallel query).  Read-ahead and async reads are important ingredients in high scalability RDBMS.

*****

 *****
The  above illustration is actually idealized.  The truth is, there is a queuing penalty that results in total response time for the waiting rows being greater than the sum of the remaining service time before a slot empties and its own service time.  This additional time for queue management may be negligible (it usually is), or it could be significant in measurement.  Regardless - the numbers here are the *best* that can be achieved in the system as defined.
If the LUN queue length exceeds the service queue depth, average read response time will increase and the system won't hit 1 gigabyte/s.  Decreasing service time even further could allow us to reach 1 gigabyte/s.  But .25 ms avg is about as good as you'll ever get out of external storage.
Increasing the average IO size will also help to recover from queuing penalties.

Here's the thing: the blog post author has a very valid point: to achieve maximum performance out of a storage system, the disks, the pipes to the controller, and the pipes to the server host need to be considered(as well as controller cache & controller CPU).  But before that, we've got to look at the pipes coming out of the server host - namely the LUN and adapter queues (at both physical and virtual server layers if a VM).  Otherwise, you'll just add capacity that can't be tapped by the server anyway.  And if you want to negotiate with a SAN engineer, you don't want to burn political capital by asking for changes that you can't use until after or at least at the same time as changes that will allow you to capitalize on the new performance capacity.

If the example were changed to just say "assume no queuing delay at the server LUNs or adapters", or "assume an adequate aggregate LUN service queue" or "assume the data is evenly accessed across 8 LUNs", the example would be valid.  But making the example with a single LUN assumption works against the author's larger intention - as does the general response of "oh my goodness you've perfectly captured how silly SAN engineers are".  At Summit I usually here exhortations to work as a team with storage administrators.  I think that shouldn't just be a ploy to get what you want.  If there is a bit more learning of storage technology - especially shared storage, I firmly believe there will be more performance success more widely across the SQL Server community.

*I double-checked this because I wanted to make sure I wasn't rushing to judgment.  Re-reading the original post's first line has me, for now, convinced I am not hasty in my appraisal:
"I suppose I write this post to mock some of the things I keep seeing in the industry today."