Performance of index creation
- This blog has been re-published at https://techcommunity.microsoft.com/t5/Running-SAP-Applications-on-the/Performance-of-index-creation/ba-p/367170
- This blog post might be outdated by now. The latest documentation regarding SQL Server Columnstore on SAP is available in https://www.sap.com/documents/2019/04/023e5928-487d-0010-87a3-c30de2ffd8ff.html
The creation of database indexes is often a significant part in SAP BW process chains. Even on up-to-date database servers the index creation on a BW cube may take several hours. The obvious way to speed-up this procedure would be to increase parallelism by setting the SQL Server configuration option "max degree of parallelism" (MaxDop). However, this has an impact on all SQL statements, not only on the index creation. Therefore this is not recommended by SAP. By implementing SAP note 1454130 (Data dictionary support for parallel/online index) you can configure the degree of parallelism for index creation in SAP BW. Thereby SAP adds the optimizer hint MaxDop, which sets the parallelism for single SQL commands, independent from the general SQL Server configuration. SAP note 1654613 (SQL Server Parallelism for SAP BW) describes in detail, how to configure parallelism in SAP BW for queries and index creation.
Impact of Parallelism
By implementing the notes mentioned above, the maximum degree of parallelism for creating indexes in SAP BW is 8. This means that up to 8 CPU threads on the database server can be used to create a single index. The actual number of CPU threads is set by SQL Server at runtime. It depends on the available CPU and memory resources. Therefore you might see varying execution times for the BW process chains.
We have measured the impact of MaxDop for index creation on a test server with 16 CPU threads. The indexes were created on a cube with 100 million rows and a size of 15GB (page compressed). The performance scales-up quite well with the number of CPU threads used:
Impact of PAGE compression
Our experience on customer side showed an overall performance increase with PAGE compressed databases. This is caused by a reduced number of disk operations for the cost of a slightly increased CPU usage. However, index creation cannot benefit from PAGE compression. It is CPU intensive and not I/O intensive, if all data pages of the table fit into SQL Server data cache. Writing the data to disk is performed asynchronously by the lazy writer and the checkpoint thread. Writing the transaction log takes the same time for PAGE and ROW compressed tables.
Therefore you could expect a performance decrease for the index creation on a PAGE compressed table. On our test system we indeed saw a slightly performance decrease for the index creation job of about 10%. However, this impact is minimal compared with the impact of MaxDop.
Impact of ONLINE index creation
The idea of ONLINE index creation is to allow concurrent access to the table while the index is created. The ONLINE option does not speed-up the index creation, quite the contrary. It reduces the impact of the index creation on other processes accessing the same table. Typically no other processes access a BW cube when the indexes are created. Therefore you do not benefit from ONLINE index creation for SAP BW. In our test system we have measured a performance decrease of over 50% when using ONLINE index creation.
Therefore we recommend to turn this off for SAP BW systems. You can configure the ONLINE index settings in SAP transaction DBACOCKPIT. It is turned off by default.
