January 2009 - Posts

Download the SQL Server 2008 System Views Map
29 January 09 07:23 AM | Christian | with no comments

Published last night:

http://www.microsoft.com/downloads/details.aspx?FamilyID=531c53e7-8a2a-4375-8f2f-5d799aa67b5c&displaylang=en

Regards,

Christian

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

Filed under:
Failover cluster network binding order warning: Windows Server 2008/SQL Server 2008
22 January 09 09:30 AM | Christian | with no comments

I'm building another Windows Server 2008/SQL Server 2008 cluster today and came across an annoying issue which thankfully has a simple fix.

When I ran 'Advanced Cluster Preparation' from SQL Server 2008 setup the Setup Support Rules fired up a warning about the Network Binding Order:

Rule "Network binding order" generated a warning.

The domain network is not the first bound network.  This will cause domain operations to run slowly and can cause timeouts that result in failures.  Use the Windows network advanced configuration to change the binding order.

You can check the binding order by going to Network Connections and selecting Advanced-->Advanced settings from the menu.  Thats fine for Windows 2003 but in Windows 2008 its missing :-O

However, if you press the Alt key the File menu will appear along with the Advanced option.  Isn't it obvious!? :-\

Apparently, Windows didn't look pretty enough with easily accessible menus!

In my installation I had an extra network for management purposes which wasn't enabled for cluster use and was top of the binding order but if you look here and everything looks fine you might have a haunted ghosted network card:

You receive a warning about the network binding order on the Setup Support Rules page when you install SQL Server 2008 in a failover cluster: http://support.microsoft.com/kb/955963

 

Regards,

Christian

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

Filed under:
Tuning your SAN: Too much HBA Queue Depth?
12 January 09 10:56 AM | Christian | 9 comment(s)

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: