June 9, 2010 at 7:14 am
Hi All,
I'm in a bit of a situation here, and I'm clueless of where to start looking.
We have about 100 servers that are mostly the same, in different locations across the country. These servers each host a decentralized database in every branch we have. This means we cannot have real huge servers, since we do not want to pay too much for hardware.
Now, I know we have 2 Raid Disks, that are mirrored, in every branch, and I think it causes the slow disk problem, and I was asked to point the problem, or find the right ressource to solve this issue.
In example, we have queries that run 3 times faster on my laptop then on these servers. We have a test server in our environment to ensure the queries work correctly, and everytime I run the queries, they tend to be slower in the Server.
Now, where do I start looking, or there another type of Raid we should look, what do you guys use for disks, that is not too expensive, since we have 100 branches...
Or, what kind of specialist should we hire for an analysis of our needs, I know our environment is completely IBM, should we hire an IBM server specialist?
I'd appreciate some input,
Thanks,
Cheers,
J-F
June 10, 2010 at 11:35 am
Any insight on this? Where should I look for a disk, or RAID specialist, that we could hire for a few days, so he can help us setup our environments?
Cheers,
J-F
June 10, 2010 at 1:19 pm
JF, I posted a note to people to see if someone wants to help you out.
As a short note, when you run queries on your laptop, are the data sizes/sets the same?
If you worry about the disks, you'd need an environment to compare things to. you'd also want to see if you have memory pressure, CPU bottlenecks, or something else. Writing better code is the cheapest thing most people can do, but without more information, there's not much to say here.
R1 is typically faster than R5 in most setups since it can return data from either disk. Both don't need to complete. R10 is better, but more expensive.
Is performance consistent across all servers? Universally slow?
June 10, 2010 at 1:28 pm
Steve Jones - Editor (6/10/2010)
JF, I posted a note to people to see if someone wants to help you out.As a short note, when you run queries on your laptop, are the data sizes/sets the same?
If you worry about the disks, you'd need an environment to compare things to. you'd also want to see if you have memory pressure, CPU bottlenecks, or something else. Writing better code is the cheapest thing most people can do, but without more information, there's not much to say here.
R1 is typically faster than R5 in most setups since it can return data from either disk. Both don't need to complete. R10 is better, but more expensive.
Is performance consistent across all servers? Universally slow?
Thanks for your answer Steve,
Actually, yes, I've checked the PerfMon counters, I'm no pro on those counters, but I've been able to identify the difference between my laptop and the server, and there are wait types that point to the disk slowness.
All our servers are a bit slow compared to a simple drive machine, like my laptop, or any development desktop we might have here. So yes, it is universally slow in all the environment we have.
The memory could probably be updated, but still, that wouldn't fix the issue, since they are all using SQL server 2005 Express, which caps out at 1GB memory for the server. Most of the Servers have 1.5Gb to 2Gb, which is fine, in this scenario.
The only thing I can think of is certainly a delay in the disk execution, on every server, and I thought the R1 would be causing this.
We are wiling to hire a specialist in this field, if we need to, but we would certainly like to make sure it could be worth it.
I read a bit on the different Raid types that are available, and R10 looks interesting, even though I do not know if we have to physically change the R1 controllers to do this?
Cheers,
J-F
June 10, 2010 at 1:35 pm
If you have Express, memory probably won't help. Are these all older disks? Query tuning perhaps? Post a set of DDL and sample code in new thread and see if someone can help on that front?
June 10, 2010 at 1:44 pm
Thanks for the query tuning, but I have to say this is pretty much my strength in this field. I have optimised all I could in this query, and now, it all goes to faster hardware.
We need to return around 50k products that are looked up in a database of around a million, and it has to return in less than a second to the app, so they don't lose time when picking the right product to sell to the client, at the counter. This is pretty much mission critical, since the seller is in front of the client, and needs really quick access to the products database.
I came down to around 1.6 seconds, on my laptop, which is superb, compared to what we had before, but when running the query on the server, it gets messy, and returns in around 2.5 secs to 3 seconds.
You might say it's not that much, and I would agree, but we need this difference of time, when the user applies new filters to search for products, the application needs to reply really quickly, even if the user asked for a whole vendor, that returns 50K rows...
Cheers,
J-F
June 10, 2010 at 1:53 pm
Are you running Express on your laptop?
Sounds like you are doing the right things. I wouldn't think it's R1, but possibly just a load issue on that hardware/version.
R10 works on many controllers, but not all. You'd have to check if it worked on yours, and then you'd be looking at 200 disks more for the installation. Is that potentially cheaper than buying newer computers to put there?
June 10, 2010 at 2:15 pm
J- F
I think Steve has it correct, your going to need to look for more disks/better disks. I would look at your Current Disk Que length and Avg disk que length during busy times and see if they are high. Should be around 1.5X number of spindles. In this case probably anything higher than about 3 or 4 would mean delay and the disks are not keeping up I'll take your word that you have done all the performance tuning you can(missing indexes,update stats, defrag, etc...) so if that's the case I would look at a way of getting more disks into these boxes. Perhaps you could migrate them to SSD drives instead? You could also look at cards like Fusion-IO(www.fusion-io.com) this could be cost prohibitive of course just throwing out other ideas to remove your disk issues. 🙂
Pat
June 11, 2010 at 7:12 am
Thanks guys,
I think you've showed me some helpful points, and also that I need to read a lot more on the subject, to be able to come to a conclusion. I will also need to test, and check the performance counters you've just stated, to check for I/O bottleneck, maybe in comparison to my laptop, that could help.
Thanks for the info, I'll start now,
Cheers,
J-F
June 11, 2010 at 6:01 pm
Saw this today. Have you read it?
June 12, 2010 at 9:27 am
I would recommend getting a performance tuning professional to help you out. There is a wide variety of things that could be root causes here, and such a person would have the ability to quickly isolate the actual cause(s) and offer suggestions for correction. He/she should also be able to mentor you to improve your ability to work such issues in the future.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply