May 7, 2008 at 11:27 pm
Hi there! I have an application that uses MS SQL 2005 Server database engine and have about 10 million records. Each day it grows. I thought to improve database's performance and bandwidth by using clusterization, and after invistigating some books and articles I understood that clusterization and improving performance have nothing in common. Everyone says that there is no any performance's improvement after adding clusters BUT what about database's bandwidth? What I need is to improve bandwith by adding clusters and I think that if now my database handle for exmaple 100 queries in a second, after adding new cluster it should handle may be not per 2 times, but 1.5 times more. Am I right? Any thoughts about improving database's bandwidth? Thank you
May 8, 2008 at 2:22 pm
Active/Active clustering can improve performance, Active/Passive won't.
Active/Active is more expensive (requires extra SQL Server licenses).
I can't tell you how much better it will perform, since that's very dependent on the hardware, the drive set-up, the network between the servers, the type of data and how it's being queried, etc. But Active/Active clusters do give you a performance boost in almost all circumstances.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 8, 2008 at 2:48 pm
Instead of clustering, you might care to look at database partitioning, and/or Federated databases. Those are more performance-oriented concept for scaling out on databases.
That being said - 10M rows isn't all that much, and a decent-sized "modern" SQL server, with the right storage setup, some decent code, good indexing and up to date stats should usually perform pretty well. The hardware solutions often involve VERY expensive purchases, which without good code to back it up, yield little or no gains.
Have you actually gone in and spent some time tuning? What is slow? Why is it slow? Does all of that data need to stay in a single table or not?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 9, 2008 at 8:05 am
Fundamental tenet of database performance:
1) find bottleneck
2) fix bottleneck
3) goto 1)
I would be willing to bet a steak dinner that your performance can be improved (probably much more than 2X) for much cheaper and with a hell of a lot less hastle by the above rather than spreading the paltry 10M rows across multiple servers of any configuration.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 9, 2008 at 9:16 am
If you have queries with lots of static information, you can replicate to other instances on other servers and potentialy gain some performance, but your application has to support this.
You can also use replication to make updateable copies if that works, using bidirectional transactional or merge if your application is a fit.
Scale out is hard and usually requires lots of custom applications. If you are slowing down, I'd follow the advice above from TheSQLGuru as well, find the bottleneck and see if you can address that. Maybe adding a little hardware or tuning to this server will fix things.
May 9, 2008 at 1:54 pm
If, after exhausting your tuning options, you discover that the bottleneck is indeed the hardware, active/active will still require some fancy db/application modifications to gain performance.
The Active/Active cluster in SQL Server does not imply that two machines are processing the requests sent to a single SQL Server (like Oracle RAC). It means you have two SQL Server instances on two machines that run independently. You'll still need to figure out how to federate or re-design to gain performance.
Clustering is primarily used as an availability solution, not a performance solution.
Good luck with the tuning!
Kyle
May 12, 2008 at 1:36 am
Thanks for your replies! I will check every suggestion. One more question: what do you think about technology that called linked servers - can it improve performance and bandwidth? I think linked servers that what I need.
May 12, 2008 at 7:33 am
nKognito (5/12/2008)
Thanks for your replies! I will check every suggestion. One more question: what do you think about technology that called linked servers - can it improve performance and bandwidth? I think linked servers that what I need.
Linked servers, at least if you intend to access data directly and pull it back to a local server and/or join to it (as opposed to running a remote sproc that processes data and returns it in output parameters or a non-joined-to resultset) are a performance DOG. Don't go there.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 12, 2008 at 7:58 am
The best hardware solution that I have implemented lately was to just double the amount of RAM. It was cheap, took only minutes and quadrupled our performance.
After that I would do like SQLGuru said... find the bottleneck, fix the bottleneck.
TheSQLGuru (5/12/2008)
nKognito (5/12/2008)
Thanks for your replies! I will check every suggestion. One more question: what do you think about technology that called linked servers - can it improve performance and bandwidth? I think linked servers that what I need.Linked servers, at least if you intend to access data directly and pull it back to a local server and/or join to it (as opposed to running a remote sproc that processes data and returns it in output parameters or a non-joined-to resultset) are a performance DOG. Don't go there.
As for linked servers...
In my experience they are great so long as every query that you perform between is by way of a stored pro, function, or view.
Otherwise they make it too easy to back yourself into poor performance like SQLGuru said.
@SQLvariant
May 12, 2008 at 9:21 am
If you can, memory is a great first step. Often it solves problems quickly. CPU might be next, or splitting out the data across disks. You'd have to look at the system monitor and make some guesses as to where performance is suffering.
May 13, 2008 at 12:06 am
Let me explain my problem:
I have a one table with huge data size (its about millions records and gigabytes in size) and without any foreign keys. My actions are selection and adding data only. What I need is simple way to improve performance and throughtput (bandwidth) by adding new machine, when server's resources are exceeded. After my investigation I realized that MS SQL Server is not supprot such option. In the same time I found that Oracle's Real Application Cluster tool is what I need - they are offer this solution as simple scalable system that can improve performance by adding new cluster to the system. On MS site all that I've found is that SQL Server Cluster is greate for failover and I think there is no MS solution for performance improvement by adding new machines to the system. Thanks people!
May 13, 2008 at 4:07 am
nKognito (5/13/2008)
Let me explain my problem:I have a one table with huge data size (its about millions records and gigabytes in size) and without any foreign keys. My actions are selection and adding data only. What I need is simple way to improve performance and throughtput (bandwidth) by adding new machine, when server's resources are exceeded. After my investigation I realized that MS SQL Server is not supprot such option. In the same time I found that Oracle's Real Application Cluster tool is what I need - they are offer this solution as simple scalable system that can improve performance by adding new cluster to the system. On MS site all that I've found is that SQL Server Cluster is greate for failover and I think there is no MS solution for performance improvement by adding new machines to the system. Thanks people!
If you can afford Oracle RAC then you can afford the small amount of hardware that it would take to make your primary server run correctly. I run a system that contains multiple 10+ million row tables, ranging in size from 1 GB to 12 GB and all of them remain available to users as we add hundreds of thousands of rows every morning.
Have you considered table partitioning?
Or a replicated copy of your db?
Also, I'll share with you this white paper that I was reading yesterday.
http://www.microsoft.com/sql/techinfo/whitepapers/oraclerac.mspx
@SQLvariant
May 13, 2008 at 4:48 am
Can you go into more detail on exactly what is slow in your current app. Selects? Inserts?
With a properly designed table, well-written SQL and optimal indexes, 10 million rows shouldn't be a bottleneck.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 13, 2008 at 5:17 am
I do not have any bottlenecks right now. All that I want is to understand is there any simple way to improve SQL server performance and throughput by adding new machines to system and without modifing server hardware. I am curious because I develop some system that will have 10M records just in the begining and after deployment every day it will grow by 100000-200000 per day
May 13, 2008 at 7:06 am
nKognito (5/13/2008)
I do not have any bottlenecks right now. All that I want is to understand is there any simple way to improve SQL server performance and throughput by adding new machines to system and without modifing server hardware. I am curious because I develop some system that will have 10M records just in the begining and after deployment every day it will grow by 100000-200000 per day
nKognito, sorry, but it is time to break out the blunt stick. You really, really are NOT hearing what we are telling you!! You do NOT need to spread such a tiny amount of data (yes, 10M rows growing at 1-2%/day IS tiny) across multiple servers, be it oracle, DB2, mysql, sql server, etc. I know people who have implemented and maintained Oracle RAC and it is NOT simple or easy and it is VERY expensive. You have been given multiple very good suggestions and have been asked several important questions that you did not answer. I really can't understand your fixation on using multiple servers! :unsure:
Do this: hire a perf tuning consultant to give you a quickie performance review for this table. Shouldn't cost you more than $2K (likely much less) for such a targeted review (certainly MUCH less than any scale out scenario would cost) and I GUARANTEE you that if the pro is worth anything he or she will likely find multiple suggestions to improve your performance. If you do that and aren't satisfied with the results, give me the pro's findings and I will take up the task pro bono. I do this for a living and I have NEVER EVER been to a client that did not have a HUGE range of performance draining 'issues' across their database servers/databases/code/applications. Not one!! And all but a handful do things so poorly that it makes me look like I can walk on water when I fix their problems and double, triple or quadruple performance. I routinely get 3 to 4 ORDERS OF MAGNITUDE improvement on individual sprocs or queries. I am saying these things to let you know that you are not alone and shouldn't feel bad - VERY, VERY few people out there know much about developing efficient database applications and even fewer know how to tune an application after it is operational.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply