December 16, 2009 at 3:32 pm
Hello All,
I am a beginner at Database design and need some help in managing big data table in SQL Server 2008. Right now the database has about 20 tables with 6-7 having about 1 million records(I know its not that big but if I don't do anything now then by the end of 2010 it might be 5 million and by the end of 2011, 10 million). We will start getting the data for the next year soon and was wondering what would be the most efficient way to store the new data in the SQL tables. Should I just add the next years data to already big current database tables or should I create new database for the new year with same tables and add the data to the blank tables in this new database. Or is there any other better way for managing large tables? What would be the best and most efficient approach going forward and considering next 5 years may be.
Thanks a lot for your help!
December 16, 2009 at 3:54 pm
You can consider partitioning your data on different drives. You can do partitioning by year or may be months if you want to. This way your performance will not suffer. We have tables with more than 100 million rows. With good indexes and partitioning the data we have had good performance
December 16, 2009 at 6:10 pm
Going by the # of rows, (5 Million a Year) is not something to worry about.
What Apurva has suggested would be my suggestion, use database partitioning and have proper indexes on those tables and ensure those Index(es) to use the partitioning schema when possible.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 16, 2009 at 8:43 pm
Apurva (12/16/2009)
You can consider partitioning your data on different drives. You can do partitioning by year or may be months if you want to. This way your performance will not suffer. We have tables with more than 100 million rows. With good indexes and partitioning the data we have had good performance
Thanks a lot Apurva. I will look into partitioning my database tables on an annual basis.
December 16, 2009 at 8:53 pm
Bru Medishetty (12/16/2009)
Going by the # of rows, (5 Million a Year) is not something to worry about.What Apurva has suggested would be my suggestion, use database partitioning and have proper indexes on those tables and ensure those Index(es) to use the partitioning schema when possible.
Thanks for your help Bru!
December 17, 2009 at 9:24 am
Wandrag (12/16/2009)
novice_coder (12/16/2009)
Hello All,I am a beginner at Database design and need some help in managing big data table in SQL Server 2008. Right now the database has about 20 tables with 6-7 having about 1 million records(I know its not that big but if I don't do anything now then by the end of 2010 it might be 5 million and by the end of 2011, 10 million). We will start getting the data for the next year soon and was wondering what would be the most efficient way to store the new data in the SQL tables. Should I just add the next years data to already big current database tables or should I create new database for the new year with same tables and add the data to the blank tables in this new database. Or is there any other better way for managing large tables? What would be the best and most efficient approach going forward and considering next 5 years may be.
Thanks a lot for your help!
Hi,
Like the previous posters said - you can partition the data. The partitioning strategy will depends on the version of SQL 2008 you're running.
If you're running Enterprise edition it will be alot easier, as you can use the build-in partitioning functions.
Standard edition does not support this, but you can easily build a partitioning solution by creating mulitiple tables your self.
Thanks for you reply. We have SQL Server 2008 Standard Edition installed so i guess I will have to create some home made partitioning for these tables. But Since I have 1 million records right now in the table I believe I can just go without partitioning for the next year as well. We will probably have about 3-5 million records by the end of next year. I guess then I will have to think about partioning. Whats your opinion on it? How do I decide when to go for partitioning the data.
Thanks a lot.
December 17, 2009 at 10:32 am
As I have mentioned earlier, you don't need to be panicked just by the # of rows reaching 10 Million after 18 months.
We have multiple tables having something around 300 Million records (without partitioning) and we are doing fine. All you need to do is, optimize your SQL code. Have appropriate Indexes that cover the queries and regularly maintain the indexes and stats.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 17, 2009 at 10:52 am
novice_coder (12/17/2009)
We will probably have about 3-5 million records by the end of next year. I guess then I will have to think about partioning.
That's not a lot of data. If you'll need to delete large chunks then partitioning will be useful. Otherwise it may not be necessary.
I've had 150 million in a single table. I've heard of someone with 100 billion rows in a single table.
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
December 17, 2009 at 12:19 pm
GilaMonster (12/17/2009)
novice_coder (12/17/2009)
We will probably have about 3-5 million records by the end of next year. I guess then I will have to think about partioning.That's not a lot of data. If you'll need to delete large chunks then partitioning will be useful. Otherwise it may not be necessary.
I've had 150 million in a single table. I've heard of someone with 100 billion rows in a single table.
Thanks a lot for your help... GilaMonster, Bru Medishetty, Wandrag !!!
I have a much clealer picture of what big table is actually like and that mine is nowhere near it and probably will never reach that size in next 5 years atleast.
Since you guys are all experts..I want to take this opportunity to have your views on SQL Server Standard 2008 Vs Enterprise 2008 edition. We have Standard edition right now and I know that this edition does not support backup compression, indexed view, table partitioning etc. Is there any reason or something that can't do in standard edition(now or in future) for which I should move to Enterprise edition?
Thanks a ton
December 17, 2009 at 12:26 pm
It basically needs to be addressed by you keeping in mind of your application needs, for example, does it need more than 2 node cluster? Does it need other features that you have mentioned.
Based on what you need and what you don't have in Standard Edition, you will have to say "We need to move to Enterprise Edition for this specific reason".
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 17, 2009 at 12:27 pm
The other question to consider is whether you can afford Enterprise Edition. It's not exactly cheap.
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
December 17, 2009 at 12:30 pm
novice_coder (12/17/2009)
GilaMonster (12/17/2009)
novice_coder (12/17/2009)
We will probably have about 3-5 million records by the end of next year. I guess then I will have to think about partioning.That's not a lot of data. If you'll need to delete large chunks then partitioning will be useful. Otherwise it may not be necessary.
I've had 150 million in a single table. I've heard of someone with 100 billion rows in a single table.
Thanks a lot for your help... GilaMonster, Bru Medishetty, Wandrag !!!
I have a much clealer picture of what big table is actually like and that mine is nowhere near it and probably will never reach that size in next 5 years atleast.
Since you guys are all experts..I want to take this opportunity to have your views on SQL Server Standard 2008 Vs Enterprise 2008 edition. We have Standard edition right now and I know that this edition does not support backup compression, indexed view, table partitioning etc. Is there any reason or something that can't do in standard edition(now or in future) for which I should move to Enterprise edition?
Thanks a ton
Bru is spot on. The approach to take is to determine if there is functionality that your business needs - and you can make a good business case to purchase the enterprise license.
With limited knowledge of the environment or the Business it would be difficult to say you should absolutely go one way or the other.
That said, the tools in Enterprise edition are there for a reason. Many of the tools and features can make life a little easier for a DBA.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 17, 2009 at 12:45 pm
CirquedeSQLeil (12/17/2009)
novice_coder (12/17/2009)
GilaMonster (12/17/2009)
novice_coder (12/17/2009)
We will probably have about 3-5 million records by the end of next year. I guess then I will have to think about partioning.That's not a lot of data. If you'll need to delete large chunks then partitioning will be useful. Otherwise it may not be necessary.
I've had 150 million in a single table. I've heard of someone with 100 billion rows in a single table.
Thanks a lot for your help... GilaMonster, Bru Medishetty, Wandrag !!!
I have a much clealer picture of what big table is actually like and that mine is nowhere near it and probably will never reach that size in next 5 years atleast.
Since you guys are all experts..I want to take this opportunity to have your views on SQL Server Standard 2008 Vs Enterprise 2008 edition. We have Standard edition right now and I know that this edition does not support backup compression, indexed view, table partitioning etc. Is there any reason or something that can't do in standard edition(now or in future) for which I should move to Enterprise edition?
Thanks a ton
Bru is spot on. The approach to take is to determine if there is functionality that your business needs - and you can make a good business case to purchase the enterprise license.
With limited knowledge of the environment or the Business it would be difficult to say you should absolutely go one way or the other.
That said, the tools in Enterprise edition are there for a reason. Many of the tools and features can make life a little easier for a DBA.
Yes, I agree and the real questions is are these tools and features worth their price. As GilaMonster also mentioned, the two have a huge price difference. One is 5500 and the other one is 17000. Looking at my business envirnoment, I dont think we would be needing features like partitioning, index views, clustering anytime in near future. So right now I dont have any good answer to the question: I need enterprise edition for?... So I guess we will stick with the standard edition for now.
Thanks a lot for sharing your views guys.
You all have been of great help.
Happy coding!
December 18, 2009 at 10:26 am
By far the best advice I can give you is to get a good consultant on board to mentor you while the two of you work to figure out a) what your needs really are and then b) design/architect/code/test a system to address those needs. There are a kajillion things that you won't have any clue you are doing wrong, not doing, doing suboptimally if you embark down this path by yourself. 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply