Partager via

New Limit for Number of Partitions in SQL Server 2008 SP2

  • Article

Good news for all that suffered from the 1.000 partition limit inherent with the current table partitioning implementation in Microsoft SQL Server 2005 and 2008. With the release of SP2 for SQL Server 2008 the bar has been raised to a new threshold of 15.000 partitions, which should be enough for daily data loads and makes even smaller load cycles a reality. See the whitepaper Support for 15,000 Partitions in SQL Server 2008 SP2 for details.

But this feature does not come out of the box. It has to be enabled on a database by database basis after having upgraded your SQL Server 2008 instances to SP2 (users of SQL Server 2008 R2 still have to wait for SP1 to benefit from this change). You do this by calling the new system stored procedure sp_db_increased_partitions. For instance, to enable the database MyDataWarehouse for increased partitions, simply call:
EXEC sp_db_increased_partitions @dbname = ‘MyDataWarehouse’, @increased_partitions = ‘on’

Note, that this operation increases the version of the database from 655 to 662. This has implications for the ability to upgrade this database or to attach a detached copy of the altered database to another server. The target system must have the same or a higher database version. Thus, you cannot upgrade this server to SQL Server 2008 R2 RTM (which has version 661), or attach the database to a SQL Server 2008 SP1 instance. And there are also implications for log shipping or mirroring where the partner servers also have to be on SP2 level. While with log shipping it is sufficient to run the procedure just on the primary server (the corresponding log entry is propagated to the secondary servers and also applied there), with mirroring the mirror has to be broken, and the procedure being executed on both principal and mirror server before the mirror can be re-established.

And there are a few more caveats to consider:

· it can only be applied to user databases.

· you cannot execute this proc within an open transaction.

· you must be the only user in the database since this proc tries to aquire an exclusive lock on the DB.

· this database must not participate in replication, CDC or change tracking.

· it is not supported for this upgraded database to have a nonaligned index defined on any partitioned table.

Also, the partitioning Wizards in SQL Server Management Studio 2008 have not been updated to cater for the increased partition limit. Therefore, you have to use T-SQL to manage those partitions.