As of SQL Server 2005 you can partition table and indexes. The maximum number of partitions used to be 1,000 for SQL Server 2005, 2008 and 2008 R2. Some customers were reaching this limit when running SAP Business Warehouse (SAP BW) on Microsoft SQL Server. Therefore it was decided to increase this limit to 15,000 for SQL Server 2008 and 2008 R2 in a Service Pack.
Benefit of 15,000 partitions for SAP
SAP supports partitioning only for specific tables in SAP BW. In BW 7.00 and newer releases, the F-fact table of an SAP BW cube is automatically partitioned by the packet dimension. Each time a new request is loaded into the cube, a new partition is created on the F-fact table. Typically customers load data once a day or less. Therefore 1,000 partitions are sufficient for almost 3 years. Furthermore, you can reduce the number of partitions by performing the SAP BW cube compression (which you should not confuse with SQL Server data compression). However, some customers loaded data several times a day, which resulted in hitting the 1,000 partition limit quickly.
The 1,000 partition limit also was a pain during migrations of SAP BW systems from ORACLE to SQL Server. ORACLE supports much more than 1,000 partitions since years. Therefore we often see SAP BW systems on ORACLE, which already have more than 1,000 partitions. We had this particular scenario in mind when we decided to set the new limit to 15,000. In practice more than a few thousand partitions make no sense. Having tens of thousands partitions will not increase the overall system performance. It will very likely decrease it.
Prerequisites for SQL Server
The first SQL Server release, which supports 15,000 partitions is SQL Server 2008 Service Pack 2. The next version will be SQL Server 2008 R2 Service Pack 1 (which will be released in 2011). The increased number of partitions is not available out of the box after installing the Service Pack. You first have to activate it per database using the following stored procedure:
exec sp_db_increased_partitions '<database-name>', 'ON'
Activating the increased partition support will also increase the schema version of the database. This version is even higher as the schema version of SQL Server 2008 R2 RTM (without any Service Pack). Therefore you will not be able to directly upgrade a database running on SQL Server 2008 SP1 with more than 1,000 partitions to SQL Server 2008 R2 RTM (which only supports 1,000 partitions). However, you can detach the database, upgrade to SQL Server 2008 R2, apply Service Pack 1 and then attach the database again. A detailed description about enabling and disabling the increased partition support is available in the Microsoft TechNet article Support for 15,000 Partitions in SQL Server 2008 SP2 .
Prerequisites for SAP
SAP BW fully supports the increased number of partitions in the newest SAP Support Packages (SP). For systems with older SAP SPs you can simply apply a correction instruction without the need to apply the newest SAP SP. The correction instruction is published in SAP note 1494789 “Enabling 1000+ partitions support on SQL Server”.
The minimum SAP_BASIS Support Packages are:
SAP Basis
|
Correction delivered
in SP
|
Correction Instruction available
via SNOTE for older SPs
|
7.00
|
SP23
|
SP16 - SP22
|
7.01
|
SP8
|
SP1- SP7
|
7.02
|
SP6
|
SP3 - SP5
|
7.11
|
SP6
|
SP1 - SP5
|
7.30
|
SP2
|
-
|
SAP note 869407 “Partitioning on Microsoft SQL Server” describes in detail, how SAP BW uses SQL Server partitioning and which tables are partitioned. This note is really worth reading for an SAP database administrator.
