| Author |
Topic |
|
shiyam198
Yak Posting Veteran
94 Posts |
 Posted - 2010-12-23 : 01:32:05
|
| Hi,Our company has been told by a software vendor because of the data load the database is slow and they can't fine tune the software to make the performance better.I have been told that the way to go is either clustering or SAN.I have to start looking in to this and solve it within a month or so; I need some guifance from you guys.. - Is SAN a completely hardware thing? If purchase the right hardware and install them, that is it? are there any SQL server specific configuration? does this require replication to be setup?- Are clustering and replication are same? I thought clustering is done only for fail over. will it improve performance too?- are there any other solutions?- what about vitualisation?Thanks for your time.- Shiyam |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-12-23 : 03:39:35
|
| Huh?Clustering is a High Availability technology to reduce downtime from server failures. It has no performance benefitsSANs are consolidated arrays of disks, sometimes for IO performance, more often for management, scalability, etc and a badly tuned SAN can run slower than direct attached storage. It's *expensive* to get a complete SAN and really needs someone to manage it.I suspect your vendor is trying to deflect blame and hasn't a clue what they're talking about.--Gail ShawSQL Server MVP |
 |
|
|
mrdenny
Starting Member
1 Post |
Posted - 2010-12-23 : 04:02:55
|
| OK, apparently your vendor doesn't know how to tune their own database. I can guarantee that a cluster won't improve performance as a cluster is only for reduced down time. Purchasing a storage array for the purposes of improving the performance of a single application is going to be a very expensive purchase, easily to the tune of several hundred thousand dollars. As Gail said typically storage arrays are purchased to make management of the overall storage easier. When done incorrectly a storage array can be configured to be slower than the current storage.I'm thinking that your vendor may want to come to my storage presentation that I'm doing at SQL Saturday in January 2011.Denny Cherry has over a decade of experience managing SQL Server and is currently the Manager of Information Systems at Awareness Technologies. Denny holds several Microsoft Certifications as well as being a Microsoft MVP. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-12-23 : 04:09:33
|
| Ok, details...How big is the database? What's expected growth over the next year? How many concurrent users?--Gail ShawSQL Server MVP |
|
|
|
alzdba
Starting Member
10 Posts |
Posted - 2010-12-23 : 05:24:31
|
| O please forward these replies to your software vendor.My guess, they smelled the perfume of $$.And they're greedy !In stead of just suggesting a bigger box, they shamelessly go for a SAN infrastructure ??I'm not saying a bigger box will solve their issues !They should start with a decent performance analysis.I bet you're the first one they encountered these kind of problems with, hu ?Without a decent performance analysis report and solidly justified conclusions I wouldn't move an inch.And, yes, they tricked me a couple of times. These days are over !I wonder, is their app (still) needing full sysadmin auth at run time ?Google or Bing on their solutions name and see what other customers have the same problems you are experiencing. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-12-23 : 05:40:51
|
"I suspect your vendor is trying to deflect blame and hasn't a clue what they're talking about."That gets my vote too. We recently spent a lot of money on a SAN solution and had to revert to using local drives because the SAN was not fast enough. Clearly we didn't spend enough money .... so I'd be wary of even assuming that SAN is faster than local disks.As a simple rule-of-thumb: on a "busy" database I would want Operating system, Data files, Index files, Backup files to each be on separate drives, and each drive RAID optimised for the type of operation it is to perform. Moving the TEMPDB system database to its own drives is also a good move for performance.If your vendor has "everything on one drive" then that's a bad sign in terms of their knowledge.Irrespective of that the frequently used queries need careful tuning. If tuning has not been done, and tuning DOES offer a solution, it could improve things by an order of magnitude - so spending money on hardware first would be a bit cart-before-the-horse  |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-12-23 : 13:13:01
|
| You should start with these 2 basic things: When was the last time you rebuilt indexes?Is Auto Update Statics on?Also, have you analyzed your most expensive queries?What perfmon counters are you capturing? Start with Disk Queue length, % CPU time, Page Life Expectancy.What version and edition of SQL server are you using? 32 bit or 64?It is useless to talk about new hardware without having any idea what's going on in the current system. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-12-23 : 14:23:11
|
| If you want some detail on the 'analysing expensive queries', try this:[url]http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/[/url][url]http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/[/url]--Gail ShawSQL Server MVP |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
shiyam198
Yak Posting Veteran
94 Posts |
Posted - 2010-12-25 : 18:34:42
|
| Brilliant ! Thanks a billion everyone. Gail,I have book marked your performance articles even before posting here.. Couldn't beleive you replied to my post here.. :)Russ,I will check every thing you mentioned as well.we are builing the indexes as an automating task on a weekly basis.the counters "Disk Queue length, % CPU time, Page Life Expectancy" are being captures.. but i dont know the standard to measure them against.Again, i will research and check them.I am in Deployment - so bit of programming, networking and getting in to DB (SQL server) management. I write SQL and do basic SQL administration (Installation, backup/restore, setting up replication, etc) on a daily basis. But performance analysis is new to me. But, I am excited for this opportunity to learn and not scared of it.Again, thanks everyone for your time. each of your inputs are valuable.Regards,Shiyam |
|
|
|
|
Clustering VS SAN