Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Sachin.Nand
2937 Posts |
 Posted - 2012-04-10 : 13:28:06
|
| I have a shared EMC VNX 5300 SAN on which a SQL Server DW is to be deployed. The SAN already supports Exchange, JD Edwards and other applications.Even though it is a shared SAN there are 32* 300GB 15K SAS disks dedicated for the data warehouse.We have carved 16 LUNs and I am running some SQLIO tests on these 16 LUNs for benchmarking before the DW is deployed.Case1: Listing 16 Files (1 per LUN) in a single param.txt file of SQLIO, so that all the LUNs are tested and running 1 instance of SQLIO (Serial)Case2: Creating 16 param.txt files having one file listed in each and running 16 instances of SQLIO’s in parallel. (Parallel)Questions:1. Why do we need to run two tests (case 1 and case 2). How are they different in terms of results (MB/sec)? In which case can we expect more throughput?2. To find out SAN wide throughput (for SQL DW), is it required to run SQLIO on all the LUN’s and get a cumulative MB/sec or just running it on 1 LUN will suffice?3. The results of SQLIO are across the entire SAN or specific to a disk group or LUN? Is there a way to get the SQLIO results (MB/sec) for a single disk, single LUN, or single DG ?Any inputs appreciated.After Monday and Tuesday even the calendar says W T F .... |
|
|
Sachin.Nand
2937 Posts |
Posted - 2012-04-11 : 13:03:09
|
| Anyone ????After Monday and Tuesday even the calendar says W T F .... |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-04-11 : 13:13:08
|
| I go to these whenever I use SQLIO:http://www.brentozar.com/archive/2008/09/finding-your-san-bottlenecks-with-sqlio/http://sqlserverpedia.com/wiki/SAN_Performance_Tuning_with_SQLIOThere's also a chapter in this book that details its use:http://www.amazon.com/Professional-Server-2008-Internals-Troubleshooting/dp/0470484284/ref=sr_1_sc_1?ie=UTF8&qid=1334164339&sr=8-1-spellAfter that, I really couldn't answer your questions if those don't. You could try pinging Brent Ozar by email or Twitter, or tweet using the #SQLHELP tag. |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2012-04-11 : 13:42:22
|
| I am not looking on how to use SQLIO. My questions are more specific than that if you read them once again.Just hope to get some answers by tomorrow EOD as I am planning to test it on Friday.After Monday and Tuesday even the calendar says W T F .... |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-04-11 : 13:53:33
|
I understood they weren't on how to use it, but the links I posted also have discussion on what SQLIO numbers mean and how to interpret them. You asked why case 1 and case 2 both need to be run, but didn't explain why you need to run them. Did you develop these cases on your own, or did someone dictate them to you? If the latter you should probably ask them. The only guess I can make is that one configuration would test multipathed I/O while the other does not.I would guess the only way to accurately answer #2 is to test all LUNs, or perhaps just the ones being used for SQL Server. Probably not a good idea to test the Exchange LUNs.  For #3, SQLIO tests drive performance, I would think the correct answer depends on whether the drive your testing is configured as a single disk or LUN, etc. |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2012-04-11 : 14:19:04
|
quote:
Originally posted by robvolk
I understood they weren't on how to use it, but the links I posted also have discussion on what SQLIO numbers mean and how to interpret them. You asked why case 1 and case 2 both need to be run, but didn't explain why you need to run them. Did you develop these cases on your own, or did someone dictate them to you? If the latter you should probably ask them. The only guess I can make is that one configuration would test multipathed I/O while the other does not.
Sorry I mistyped it.Actually that's not what I meant.Let me rephrase the question about those cases.1. Which of the two cases needs to be run? How are they different in terms of results (MB/sec)? In which case can we expect more throughput?I cannot establish which one is more ideal than other.If we take Case1 then theoretically all 16 LUN's should be hit at the same time with same number of I\O threads specified in the param.txt but I am not sure what will be the case if we have 16 param.txt files having 1 file listed in each and running 16 instances of SQLIO's in parallel.quote:
I would guess the only way to accurately answer #2 is to test all LUNs, or perhaps just the ones being used for SQL Server. Probably not a good idea to test the Exchange LUNs.
I also think that should be the casequote:
For #3, SQLIO tests drive performance, I would think the correct answer depends on whether the drive your testing is configured as a single disk or LUN, etc.
I think it depends on how many LUN's or disks I target for the test.For SAN wise throughput I think all the LUN's should be specified.Any thoughts on this ?After Monday and Tuesday even the calendar says W T F .... |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-04-11 : 16:14:21
|
No other thoughts besides what I've already mentioned. I'm not a SAN person, and since we're using FusionIO at work, I don't have to worry about this kind of stuff anymore.  |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2012-04-11 : 17:27:45
|
| I will tell you what I recently did, though it may or may not help.I used SQLIO much like your case 1. I actually think it could've been done with one file, one process. SQLIO can bring the heartiest of servers to their knees. I wanted to stress test the SAN, but also, we virtualized pretty much our entire enterprise infrastructure, so I needed to exercise the VMs as well. Point is, I wanted to see just what the SAN, Network and Virtual environment could take. I think a single instance of SQLIO can exercise the infrastructure enough to get a fair idea.Next, and we used this test far more than SQLIO to judge whether we could safely migrate, I installed SQL Server, restored backups, and played back traces to simulate 2.5x our busiest day ever. Then I ramped it up to find where the breaking point was.I think you can be reasonably certain that a high end SAN will perform, but you need to know where the problems will occur on YOUR system, running your code. As you well know, it only takes one missing index or bad proc to slow your system to a crawl.So, to answer your question, and this is only my opinion (not fact)1. we don't. i expect more throughput with more threads (case2)2. I think one LUN will suffice.3. Unless you have bad disks, it's indicitave of the entire SAN.Hope this helps a little. I am not a SAN expert (not totally ignorant either lol), so take it for what it's worth. |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2012-04-12 : 03:36:29
|
quote:
Originally posted by robvolk
No other thoughts besides what I've already mentioned. I'm not a SAN person, and since we're using FusionIO at work, I don't have to worry about this kind of stuff anymore.
I had heard of FusionIO but dont know how does it help ? Any inputs on this also ???After Monday and Tuesday even the calendar says W T F .... |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-04-12 : 06:49:41
|
| It's a high performance solid state storage device. Best thing to do is go to their website and research. It's not a cure-all, but if it fits your architecture, it's very cost effective and is lightning fast. SQLIO has to work very hard to tax a FusionIO card. |
|
|
|
|
|
|
|
|
SQLIO interpretation