Disk & HBA saturation in SQL Server

anushahanna
anushahanna used Ask the Experts™
on
In SQL Server Best Practices Article,
http://msdn.microsoft.com/en-us/library/cc966412.aspx

There is a comment
"
In addition to disk saturation, it is also possible to saturate the capacity of a Host Bus Adapter (HBA) before exhausting disk resources. Typical modern HBAs have a bandwidth of 2 Gb/s. This equates roughly into 200 MB/s. We have observed practical throughput in the range of 180–190 MB/s."

Can you please explain how is the 2 Gb/s bandwidth calculated to 200 MB/s. How can this be observed like it is mentioned above. can profiler/perfmon capture this?

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Distinguished Expert 2017
Commented:
1Byte =8bits

2Gb/s =2 gigabits per second=2gigbits/second multiplied by 1byte/8bits = ~200Megabytes/s

Author

Commented:
then it should be precisely 256 Megabytes/s, right? (2048/8)
Distinguished Expert 2017
Commented:
yes.  for simplicity people divide by 10.
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Author

Commented:
thanks arnold. In the article, they use the words "We have observed practical throughput" What tools will help in this regard?
Distinguished Expert 2017

Commented:
practicle means that they used testing tools to see what is the maximum throughput they can reach through the HBA.
i.e. 2Gb/s is the rating on the media. the 200MB/s is the empirical data.


It all depends on your setup.  One way to maximize the throughput is by lowering I/O when possible.
I.e. where are your transaction logs? are they also on the FC connected storage?
Do you have a single/multi path to the storage? if Single, is adding another HBA an option or getting an HBA with dual/multiple ports

Increasing system memory could reduce the I/O.

Even through the throughput is reaching its threshold, are you currently experiencing issues/slow responses from the application?  What about resource use on the system processor/memory?

Author

Commented:
the system has 2 single port 4GB/s HBAs (Does this make it a multipath) to 16 drives. Are there multipath software solutions, too? The log & indexes are on a diff LUN than data. But the MB of data is high on data drive, sometimes hitting 600 MB/sec. That is why i am wondering the throughput, if there is saturation on the HBA? How can I find out?

yes, the disk response is bad (100s of ms). Processor is hardly used enough; there is no memory pressure.

Thanks Arnold
Distinguished Expert 2017

Commented:
What is the storage device(vendor).  There are different versions for different storage devices.
EMC uses powerpath that can be licensed in load/balance mode.
This way you will allocate the LUNs to Both HBAs.
I think HP, Hitachi, Sun etc. have their own similar multi-pathing tools that are installed on the client system either in failover mode or in load balance mode when licensed.
If your only option is failover, you could designate the LUNs to be distributed between the two HBAs
i.e. LUN1 is primarily available on processor1 of the SAN
while LUN2 is primarily available on processor2 of the SAN.
This way based on the LUNs usage/activity you can distribute them between the processors and the HBAs.

Author

Commented:
So basically, multipath is to load-balance the HBAs?
we have IBM storage in a failover cluster environment.
HBA1 has 8 disks, and so does HBA2. This is shared for all the LUNs.

What do you mean by 'processor1/2 of the SAN'. I guess you mean HBA1/2?
Distinguished Expert 2017

Commented:
I'm more familiar with the EMC storage.
There are two processors on the storage device that handle the access/processing.
The two processors are part of the "head" unit.
hba1 -> FC connection1 on the SAN processor1
hba1 -> FC connection2 on the SAN processor2
hba2 -> FC connection1 on the SAN processor1
hba2 -> FC connection2 on the SAN processor2

This is done through an FC switch that is split/zoned one HBA connects into one half where both processors are pluggedin and the other plugs into the other half where the two processors are connected.

The pair HBAs make it a multipath. i.e. you have more than one way to get to the resources. With a FC switch the two HBA connections can each access the same resource through a different SAN processor.

Do you have something like the IBM SAN256?  Or do you just have a raw enclosure with FC drivers like the DS4700.

Author

Commented:
Thanks for explaining how it is in the EMC environment.

It is the DS8300 w/2145 drives
Distinguished Expert 2017

Commented:
According to http://publib.boulder.ibm.com/infocenter/dsichelp/ds8000ic/index.jsp?topic=/com.ibm.storage.ssic.help.doc/f2c_ds8300models922and9a2_1w2zq9.html

This model also has two four way processors.

do you have a FC switch that the DS8300 is plugged into with zoning? and then you have your HBA connected and configured on the internal switch?
i.e. HBA can talk to the iwm of processor1 and processor2?

Author

Commented:
arnold
can you please guide me what is zoning, in the above context? Does it matter if it is internal or external switch? what is iwm?

back to the main question, will a 4 GB HBA  have 400 MB/S and 8 GB HBA ->800 MB/s and so forth, proportionally.

thanks
Distinguished Expert 2017

Commented:
Not sure whether IWM is the correct term, I think the proper item is the WWN

Zoning is somewhat similar to vlaning on an network switch.
http://searchstorage.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid5_cid566141,00.html
the LUNS are served from either processor.
i.e. on the switch
port0 connects to SAN Processor 1
port1 Connects to SAN processor 2

When you connect a server's HBA1 you zone it to talk to SAN processor 1
port2 Systema HBA1 talks to SAN processor 1
port3 Systema HBA2 talks to SAN processor 2


Yes, a 4Gb HBA if all things are right will have a 400MB/s as will a 8Gb FC setup have a 800 MB/s top throughput.

While the raw throughput on the HBA would rise in comparison, at some point the bottleneck that limits the throughput transfers from the HBA back to the system's ability to process the data.
The answer to the question will depend on whether your CPU/memory can process data fast enough to take advantage of the 8GB FC connection and then the issue transfers to whether the SAN can handle the increased throughput. You're performance is always limited by the slowest component. You could run into a situation where the server/HBA can handle the higher throughput as well as the SAN, but your FC switch is the limiting factor.


Author

Commented:
Arnold
to answer your question, the HBA, Switch, disks are all connected switched fabric, with zoning.

What tool do you use to calculate the throughput- either actual or the simulated one?

Thanks very much for all your help on this.
Distinguished Expert 2017

Commented:
To what settings is your HBA configured and it has a specification i.e. at 2Gig throughput.
Your switch has a backplane bandwidth rating.
Similarly your SAN has specs.
perfmon is one option, depending on your HBA whether you can pull counter data i.e. wmi/snmp integration or whether you can using SNMP pull FC HBA data. along with SNMP polling the fiber switch, SAN interfaces etc.

Author

Commented:
Thanks Arnold. I checked the ratings on the HBA, Switch and SAN - all have their max specs configured.

From your experience, what software would you recommend to integrate perfmon ?
Distinguished Expert 2017

Commented:
What platforms are available in your environment? windows/unix/linux?
cacti.net might be a tool you can use using SNMP.
perfmon running on a separate system with an SQL express backend could be an option.

Author

Commented:
Thanks Arnold. what is your best choice for win?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial