February 10, 2009 at 1:02 am
Hi,
What is the limit on the number of records sql 2005 table accommodate? Is it 80-100 million? If i have a large table with 90 million records, is that a sign that i should partion it?
February 10, 2009 at 1:19 am
There is no hard limit for the number of rows. Like BOL says it's only limited by the available storage space.
If you should partitition your table or not is depending on the size of the table and how you query it. I once talked to someone from MS who said that tables of 40 Gb or bigger usually profit from partitioning, but that's not a hard rule either.
[font="Verdana"]Markus Bohse[/font]
February 11, 2009 at 3:30 am
About 4 years ago someone calculated that the theoretical maximum amount of storage you could address in a single SQL Server database exceeded the total amount of disk storage that had been manufactured in the world so far. Now that SQL Server 2008 has Filestream storage this is likely to remain true for some years to come. The same applies to other enterprise-class DBMSs such as DB2, Oracle, etc.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
February 11, 2009 at 4:02 am
tendayit (2/10/2009)
What is the limit on the number of records sql 2005 table accommodate?
How many rows can you fit in 500 000 terabytes?
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
February 11, 2009 at 5:30 am
If i have a large table with 90 million records, is that a sign that i should partion it?
Partition could be benefited if the table is queried heavily for performance reasons or according to your business requirements to archive data but as the members suggested I don't think you should partition a table just for the reason that the table is large. Hope am not wrong!!:)
February 11, 2009 at 6:43 am
Krishna (2/11/2009)
Partition could be benefited if the table is queried heavily for performance reasons
If can, but the partition function must be chosen very carefully, considering how the table will be inserted and queried, and ensuring that queries can eliminate partitions effectivly.
Without that, partitioning will not help performance.
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
February 11, 2009 at 6:49 am
GilaMonster (2/11/2009)
Krishna (2/11/2009)
Partition could be benefited if the table is queried heavily for performance reasonsIf can, but the partition function must be chosen very carefully, considering how the table will be inserted and queried, and ensuring that queries can eliminate partitions effectivly.
Without that, partitioning will not help performance.
Thanks Gail!. tendayit, I hope that answers your question.:)
February 12, 2009 at 12:12 am
Thanks for all the replies. They really help. Will monitor things before making the decision to partition, if need be.
February 13, 2009 at 3:57 am
keep in mind partitioning is realy an advanced topic !
Books online has very good info and is a must read and try out !
And off course, Kimberlys very good WP:
Microsoft SQL Server 9.0 Technical Articles
Partitioned Tables and Indexes in SQL Server 2005
http://msdn.microsoft.com/en-us/library/ms345146.aspx
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 13, 2009 at 6:51 am
GilaMonster (2/11/2009)
Krishna (2/11/2009)
Partition could be benefited if the table is queried heavily for performance reasonsIf can, but the partition function must be chosen very carefully, considering how the table will be inserted and queried, and ensuring that queries can eliminate partitions effectivly.
Without that, partitioning will not help performance.
To add to Gail's comment, it also depends on how you want to partition, vertically or horizontally.
Vertical partitions are helpful when the queries only reference a small subset of columns. Horizontal partitioning can be used when breaking up data across servers or instances.
However, as other posters have noted, partitioning can kill performance and is a very advanced topic.
May 13, 2009 at 6:32 am
I need a quick answer on this.
what are the risks if in a certain table the number of row is expected to reach something over 50.000.000.000?
i would appreciate any kind of help
May 14, 2009 at 9:46 am
I have not reached 50 billion row mark yet. However we have numerous (between 50-100)databases with tables that range in row counts in the 100's of millions to just shy of 1 billion rows all over the place. We do not have partitioning anywhere - it's not needed in our many cases due to good maintenance and sound application query design.
However 50 billion just might make a good case for partitioning.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
May 14, 2009 at 10:11 am
thank you very much... I found an other solution... the reason i asked this question is because i had to choose between overloading the CPU or overloading the database.... anyway i found a way to split the load; so this table will reach about 200 million (+/-) rows ...
If you can tell me what might be the problems that may happen. thanks in advance
May 14, 2009 at 11:07 am
performance mainly - due to bad statistics and index fragmentation are concerns.
Additionally, dependent on the characteristics of the system, having a window large enough to perform 'Update Statistics' and 'Index Defragmentation' on a regular basis - but only when things are needed, thus making them less intrusive.
Oh, reindexing, well, that can vary from once a month to once a year - again only when needed.
As an example I have one table in a database that has 775+ million rows and 8 indexes on it. Just this one table and its indexes occupy 350 Gb of space.
Update Statistics with full scan takes between 6 & 7 hours. It is only executed when 10% or more of the rows in the table have been modified.
Index Defragmentation takes between 1 hour and 45 minutes and 2 hours per index. Again this is only executed when the index page fragmentation is greater than 10%.
We are planning a reindex of the clustered index on this table later this year or early next year. The database has been in service since late 2006 and no reindexing has been done - we estimate it will take 12 hours once the clustered index reaches a 30% fragmentation level - we do have a while to wait since its current fragmentation is only 8% !
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
May 15, 2009 at 5:53 am
Your backup strategy may need to be revisited at this point too. If you're still using the "small DB backup" strategy, it's probably taking a ton of maintenance time.
Regardless, if you haven't reviewed the backup / restore scenarios in a while, it's about time you did. Especially given the size of your db.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply