Deletion of request in SAP BW orders of magnitude faster after installing SQL Server 2008 SP1 CU6

Welcome Hermann Daeubler on this blog site. Hermann is Program Manager in SQL Server Development and our specialist for SAP BW and integration of Microsoft products into SAP BW. Hermann is part of our onsite team in the SAP Netweaver Development in Germany

Scenario:

The most important advantage of table partitioning for SAP BW is the deletion of load requests.
While this task might have taken minutes or even hours in SQL Server 2000 ( depending on the size of the fact tables in a BW InfoCube ) it’s just a meta data operation since table partitioning was introduced in SQL Server 2005. Contrary to deleting millions of rows within one load request and maintaining the indexes it takes only a few seconds to change the corresponding metadata when using table partitioning.
You can find a paper which describes the details about how SQL Server table partitioning works in SAP BW on the Microsoft SAP site

Table Partitioning in SAP BI on Microsoft® SQL Server™ 2005/2008

Problem:

The deletion of the latest request in a BW InfoCube is a matter of seconds. But getting rid of a request in the middle or the oldest request in the list might take much longer than one would expect from a pure metadata operation on the database level. While the first case is indeed a pure metadata change SQL Server will move data in the other cases. More details regarding this effect can be found in a blog on the SQL Server Storage Engine site
https://blogs.msdn.com/sqlserverstorageengine/archive/2010/02/03/performance-improvement-by-orders-of-magnitude-when-merging-partitions-in-sql-server-2008r2.aspx

Solution with the changes in SQL Server 2008 R2 CU1 / CU6 for SQL Server 2008 SP1 :

The behavior of the partition function merge was changed in SQL Server 2008 SP1 CU6 for situations where all table partitions reside within the same file group. The code change applies for SAP BW as well because only one single file group is supported for SAP. Therefore BW customers can benefit dramatically from this improvement which makes sure that any kind of request-deletion will be only a matter of seconds independent from its position. Please note that the improved functionality will not be part of SQL Server 2008 R2 at release time. The functionality discussed will be delivered with SQL Server 2008 R2 CU1.

Results :

The screenshots below show a sample of deleting a request from a non-compressed InfoCube and the difference after installing CU6 for SQL Server 2008 SP1. The test was done on a system with a very slow IO subsystem ( in addition everything - from the Windows page file up to the database transaction log – was sitting on the same device ). Therefore the improvement is quite dramatic - going down from 1750 seconds to less than a second. But even on a very fast and well tuned I/O subsystem it might be still a huge difference in case the request next to the one which should be deleted has many millions of rows.
Here is what you can see on the following screenshots :

1. before installing CU6 for SQL Server 2008 SP1

    a. deletion of the top ( latest ) request takes seconds

    b. deletion of a request in the middle takes 1750 seconds

    c. one request has 8.3 million rows and a size of about 2.8GB ( data + index pages )

2. after installing CU6 for SQL Server 2008 SP1 a, deletion of a request in the middle takes seconds like the deletion of the top
    one before

Figure 1 four requests were loaded into an InfoCube and are not compressed yet. Now the latest one with request id 132 should be deleted

Figure 2 a ST05 SQL trace shows that the partition function merge after switching out the partition of request 132 took 373ms

Figure 3 in a second test a request in the middle of the list with id 127 should be deleted

Figure 4 a ST05 SQL trace shows that this time the partition function merge took 1750 seconds. It obviously wasn’t a pure meta data operation. SQL Server moved all the 8.3 million rows from the next partition.

Figure 5 in DBACOCKPIT one can see that one request with 8.3 million rows took about 2.8GB on disk

Figure 6 after installing CU6 for SQL Server 2008 SP1 the test of deleting a request in the middle was repeated

Figure 7 program RSMSSPARTMON ( start via SE38 ) shows that the partition in the middle representing request id 143 has boundary high value 41

Figure 8 another ST05 SQL trace shows the benefit of the fix in CU6 for SQL Server 2008 SP1. Compared to the 1750 seconds in the first test the partition function merge with boundary 41 now takes just 357ms !