X hits on this document

Word document

SAP with Microsoft SQL Server 2005: Best Practices for High Availability, Maximum Performance, and ... - page 68 / 76





68 / 76

SAP with Microsoft SQL Server 2005: Best Practices for High Availability, Maximum Performance, and Scalability64

A statement cannot be reexecuted because randomized names are always used for the views, and the origin view was already dropped. The best way to address this issue is to investigate from the SAP BW side.

SAP BW table partitioning

In SQL Server 2005, table partitioning can improve performance and scalability and increase the ease of administration of SAP BW implementations. Table partitioning is fully implemented in SAP BW, starting with release 3.5.

Table partitioning applies to tables and indexes residing in a single database. Table partitioning breaks a single object into multiple manageable parts in a manner that is transparent to the application. Table partitioning allows data to be deleted from the SAP BW transaction log by using the row as the unit of measure.

For SAP BW, data deletion is a resource-consuming process because the transaction log volume is usually quite large and deletions are row by row. The majority of transaction log data is deleted from the SAP BW PSA table, F-Fact table, and E-Fact table.

Range partitioning

In SQL Server 2005, an unpartitioned table contains a data layer, with one or more index B-trees on top. In the data layer, the data is sorted according to the clustered index key if there is a clustered index on the table.

With range partitioning, the physical structures for the data layer and the index B-tree are aligned with the partitioned data, making it easy to move a partition. Whether an index contains the column with which the partition is aligned or not, B-trees are aligned with the partition column.

Notice the example diagram in Figure 27.

When a partition is deleted from a partitioned table, that partition is switched into a normal table outside of the partitioned table by using a metadata operation that takes only one or two seconds. No data is moved. This switch operation can be performed to switch a normal table into a partitioned table.

However, SAP BW supports the switching out of a partitioned table into a stand-alone table as shown in the following diagram.

Figure 28

After a partition is transformed into a table, it can be dropped, truncated, archived, and so on. The stand-alone table can be deleted in minimal time. The deletion of millions of rows takes only a matter of a few seconds. The deleted rows cannot be restored from the transaction logs.

Microsoft Corporation © 2005

Document info
Document views376
Page views376
Page last viewedTue Jan 24 21:34:31 UTC 2017