Tuning your SAN: Too much HBA Queue Depth?

Published 12 January 09 10:56 AM | Christian

Modifying the “HBA Queue Depth” is a performance tuning tip for servers that are connected to Storage Area Networks (SAN’s).  A Host Bus Adapter (HBA) is the storage equivalent of a network card and the Queue Depth parameter controls how much data is allowed to be “in flight” on the storage network from that card.

 

By default most cards default to a queue depth of 32 which is perfect for a general purpose server and prevents the SAN from getting too busy.  However, as SQL is so I/O dependent you can gain a performance boost by increasing the Queue Depth on your HBA’s to allow more I/O requests to be in-flight from that server.

 

This isn’t a new performance tuning tip and the SQL Server Customer Advisory Team (SQLCAT) recommend increasing it to 64 or even 128 for SQL Servers.  The caveat is that it increases the load on the SAN so if it’s a shared SAN you might affect the performance for the other consumers. 

 

However, you might be lucky and have a dedicated SAN for your SQL Server; maybe to support failover clustering perhaps.  In this case it makes sense that you want to push the SAN to its limit to get the best I/O performance by whacking up the HBA Queue Depth to a much higher value.  It’s a sound theory and you would assume that there is some sort of stupidly large value that might negatively affect performance.  As it turns out, it’s true.  However, after building a Windows Server 2008 failover cluster recently quicker than expected I had some time to test some different values and was surprised at the results which indicate that performance started to decline with an HBA Queue Depth of only 128.

 

I used SQLIOSim to test each configuration as I was interested in a general performance test to give me an idea of how SQL Server might perform, not a pure I/O throughput test.  Here are some links on SQLIOSim you might find useful:

 

 SQLIOSim available for download

http://blogs.msdn.com/sqlserverstorageengine/archive/2006/10/06/SQLIOSim-available-for-download.aspx

 How to use the SQLIOSim utility to simulate SQL Server activity on a disk subsystem

http://support.microsoft.com/kb/231619

 Understanding SQLIOSIM Output – This one is key to understanding the results!

http://sqlblog.com/blogs/kevin_kline/archive/2007/06/28/understanding-sqliosim-output.aspx

 

The hardware I was testing on was a pair of DL380’s connected to an HP MSA2000 storage array fully-populated with 15k SAS drives.  There are dual-paths for redundancy and the HBA’s are from Emulex.

 

When I’ve configured HBA Queue Depth in the past its been through the properties of the HBA in Device Manager but with Emulex cards you need to download and install the Emulex HBAnywhere tool from your storage vendor where you’ll find “Queue Depth” under the “Driver Parameters” tab for the HBA card.

 

The drives I wanted to test were an 8 disk RAID10 volume which I planned to keep a data file on and a 4 disk RAID10 volume for the log file so I expected it to perform well.  I only used a single data file and single log file as it was planned for a single database. I didn’t need to perform disk sector alignment as Windows Server 2008 automatically aligns to 1024KB so I was good to go with the first test at the Default Queue Depth of 32.

 

Queue Depth = 32

11/13/08 09:52:48 4724 Display Monitor ********** Final Summary for file T:\sqliosim2.mdx **********
11/13/08 09:52:48 4724 Display Monitor Target IO Duration (ms) = 100, Running Average IO Duration (ms) = 0, Number of times IO throttled = 42399, IO request blocks = 48
11/13/08 09:52:48 4724 Display Monitor ********** Final Summary for file S:\sqliosim.ldx **********
11/13/08 09:52:48 4724 Display Monitor Target IO Duration (ms) = 100, Running Average IO Duration (ms) = 0, Number of times IO throttled = 0, IO request blocks = 8
 

From Kevin Kline’s blog we can conclude from the results that the log file performance can’t be improved any further, the ‘number of times IO throttled’ value for the data file is strangely large and ‘IO request blocks’ indicates that the data file drive was able to handle 48 concurrent IO’s.

 

Queue Depth = 64

11/13/08 11:10:21 4928 Display Monitor ********** Final Summary for file T:\sqliosim2.mdx **********
11/13/08 11:10:21 4928 Display Monitor Target IO Duration (ms) = 100, Running Average IO Duration (ms) = 0, Number of times IO throttled = 48249, IO request blocks = 162
11/13/08 11:10:21 4928 Display Monitor ********** Final Summary for file S:\sqliosim.ldx **********
11/13/08 11:10:21 4928 Display Monitor Target IO Duration (ms) = 100, Running Average IO Duration (ms) = 0, Number of times IO throttled = 0, IO request blocks = 8

With a queue depth of 64 we can see that the log drive hasn’t changed but the data drive can now handle 162 concurrent IOs!  How much more can we get out of this?

 

Queue Depth = 128

 

11/13/08 11:40:44 4444 Display Monitor ********** Final Summary for file T:\sqliosim2.mdx **********11/13/08 11:40:44 4444 Display Monitor Target IO Duration (ms) = 100, Running Average IO Duration (ms) = 0, Number of times IO throttled = 49695, IO request blocks = 7211/13/08 11:40:44 4444 Display Monitor ********** Final Summary for file S:\sqliosim.ldx **********
11/13/08 11:40:44 4444 Display Monitor Target IO Duration (ms) = 100, Running Average IO Duration (ms) = 0, Number of times IO throttled = 0, IO request blocks = 8

Argh! What happened!? Concurrent IOs have now dropped by more than 50% over the previous results to 72!

 

You might be disappointed to hear after reading all this that I don’t know why and just had to conclude that you can have too much HBA Queue Depth J

 

(I never got to the bottom of my apparently large IO Throttled values either but I’m assuming that more concurrent IOs is a better measure for performance tuning!)

Regards,

Christian

Christian Bolton  - MCA: Database, MCM: SQL Server, MVP
Database Architect
http://coeo.com - SQL Server Consultants & Remote DBA Services

Filed under:

Comments

# Joe Sack's SQL Server WebLog said on January 28, 2009 03:32 PM:

Using SAN storage for your SQL Server instance? If so, you should take into consideration the host bus

# Bonnes pratiques et surveillance de la performance d’un serveur SQL | ::: E-NOVATIC - Le Blog ::: said on March 12, 2012 12:51 PM:

Pingback from  Bonnes pratiques et surveillance de la performance d’un serveur SQL | ::: E-NOVATIC - Le Blog :::

# Bonnes pratiques et surveillance de la performance d???un serveur SQL | OnaWiki said on August 16, 2012 11:38 PM:

Pingback from  Bonnes pratiques et surveillance de la performance d???un serveur SQL | OnaWiki

# SQL Server Queue Depth for HBA « Sladescross's Blog said on October 7, 2012 09:54 PM:

Pingback from  SQL Server Queue Depth for HBA « Sladescross's Blog

# SQL Server Queue Depth for HBA « Sladescross's Blog said on October 7, 2012 09:54 PM:

Pingback from  SQL Server Queue Depth for HBA « Sladescross's Blog

# SQL performance slow on Hyper-V VM (Broadcom Nics and VMQ) | Sergessqlnotes's Blog said on May 29, 2013 11:14 PM:

Pingback from  SQL performance slow on Hyper-V VM (Broadcom Nics and VMQ) | Sergessqlnotes's Blog