October 2010 - Posts

SQL 2008 sp 2 – partitioning changes

I was interested to see that SQL 2008 service pack 2 includes support for an increased number of partitions. ie In SQL 2008, the maximum number of partitions on a table is 1,000 – but now with sp2, the maximum number is 15,000.

Microsoft have published a whitepaper to document this change – here.

A couple of interesting points…

1. A new stored procedure has been introduced to turn this on, sp_db_increased_partitions.  I do not like how options are now set by ALTER DATABASE – for example recovery model – but then Microsoft introduce new functionality such as this using  ‘older style’ stored procedures... 

2. SQL 2008 R2 does not support this increase in maximum partition numbers – yet…  So you cannot take a database with more than 1,000 partitions in SQL 2008 and upgrade it to SQL 2008 R2.

3 There are also implications with log shipping and mirroring. Again, you need to be very aware of the versions\service packs involved in your log-shipping and mirroring configuration.

When I first saw this improvement, I was very pleased – however after reading the whitepaper, clearly it needs some planning/thought/testing before you decide to implement this feature.

Posted by steveh99999 | 1 comment(s)
Filed under: , ,