May 13, 2008 at 12:41 pm
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
First, that's not actually all that big a database. May seem like it, but it really isn't. With proper indexing, not too much, not too little, with proper automated maintenance (stat updates, table and index defrags, etc.), and with the database set up correctly (right file size, right growth rate, etc.), a database like that is no big deal on one server.
If/when you need to actually add more hardware, yes, there is a very easy way to do this with SQL Server. It's called table partitioning (or a federated database). Basically, you split the table across multiple servers, and the queries and such can be smart enough to know which server to look for data on. It's actually quite easy to set up, compared to RAC.
Like another recommended, if you get out of your depth on setting this up to perform well, hire someone for it. The cost will be more than justified (much less than a new server). I don't do that (freelance/consulting), but lots of very skilled people do.
- 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 13, 2008 at 4:43 pm
WOW .....I query our 3.6 billion row table and all my reponses are sub second ..with out partitioning or splitting data in a federated concept neighter partitioned views ...
Good design , good design , good design ...you can make no mistake to follow best pratices of proper database designs ,query design , intelligent indexes and good statistics...Oh ja and our 60 GB of memory and 8 64 bit CPU's ..never the less we do not hide our problems with adding iron.....
May 13, 2008 at 5:51 pm
CoetzeeW (5/13/2008)
WOW .....I query our 3.6 billion row table and all my reponses are sub second ..with out partitioning or splitting data in a federated concept neighter partitioned views ...Good design , good design , good design ...you can make no mistake to follow best pratices of proper database designs ,query design , intelligent indexes and good statistics...Oh ja and our 60 GB of memory and 8 64 bit CPU's ..never the less we do not hide our problems with adding iron.....
With that situation it would seem that you either do an index seek (or perhaps an index intersection) and it is fast or you do a table scan - and pack a lunch waiting for the results. 😀 I wouldn't think there would be much middle ground.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 13, 2008 at 10:52 pm
Thanks to all of you!
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply