October 10, 2011 at 7:25 am
Hi,
Is there any limitation in creating the number of partitions on a table. I have a situation where I might have to create 30 partitions. I know that partitions are created on separate file groups for improving the performance. But, will creating too many partitions cause a performance hit?
Thanks
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
October 10, 2011 at 7:43 am
tut tut really, Books Online details this
Microsoft SQL Server Books Online
A table can have a maximum of 1,000 partitions.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 10, 2011 at 8:23 am
Perry Whittle (10/10/2011)
tut tut really, Books Online details thisMicrosoft SQL Server Books Online
A table can have a maximum of 1,000 partitions.
Not any more, 15,000 now 😀
http://technet.microsoft.com/en-us/library/gg981694.aspx
The bible that is books online..... Reliable as always
October 10, 2011 at 8:30 am
good catch, worth pointing out that 1000 is still the standard theoretical out of the box though.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 10, 2011 at 8:47 am
Not any more, 15,000 now 😀
To be very honest, I was thinking 1000 partitions are more than enough on a table and one might not need to cross this limit.
It seems whatever is available is (was & will) never be sufficient. :hehe:
October 10, 2011 at 9:08 am
It depends on data and what you are partitioning. Also, if you have ability to remove data in a sliding window fashion.
1,000 only equates to approx 3 years of a daily partition. A long time if youre partitioning in months though! 😀
October 10, 2011 at 9:11 am
MysteryJimbo (10/10/2011)
1,000 only equates to approx 3 years of a daily partition. A long time if youre partitioning in months though! 😀
agreed, it all depends on the definition of your partition column and the range values, if its an incrementing id column it may well need 15,000 partitions. If its months, 2,000 partitions could provide just over 5 years.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply