SAP with Microsoft SQL Server 2005: Best Practices for High Availability, Maximum Performance, and Scalability48
In addition, during index creation, the number of CPUs used can be defined, overriding the global setting for parallelism:
Create index x~0 on x (a) with (online = on, maxdop=2)
where maxdop=2 indicates that two CPUs are used.
Modifications to the table are allowed when an index is built, recreated, or dropped online. During this process, a transaction is not blocked if it hits the primary table. Modifications to the table or index create, rebuild, or drop are not blocked. Index maintenance continues while the application runs.
Online indexing can be used to create, rebuild, drop, or reorganize an index, including BLOBs, and to use index-based constraints such as the primary key. SQL Server 2005 tracks the transactions that were created during the index operation. Because this process can be performed during production, maintenance that would otherwise have required downtime or caused blocking on the system can be performed.
Creating an online index can take up to three times longer when using an online clause. Because there is no blocking, the SAP application continues to run until complete.
For SAP systems, online indexing is used on occasions when a new program in SAP requires a new index or index maintenance (rebuild, for example). In a SAP environment, in addition to SQL Server metadata, the application maintains a data dictionary and tools that allow for index and table maintenance.
If custom indexes are created, these indexes must be added to the SAP dictionary for change management. Then the index modification is transported from development to test to production. SAP supports the creation of indexes in online mode.
When custom indexes are transported between SAP instances by using the SAP transport facility (test to production, for example), the SAP data dictionary tools so far are unaware of new SQL Server 2005 online indexing capabilities and use offline mode by default.
To take advantage of online indexing capabilities, create the custom index in the development environment, getting the name of the new index and its exact column order from the development system. Manually apply the index modification to the production instance by using online index creation. Then the SAP transport can be released. The transport imported into the production system will not create the index. It simply updates the SAP Data Dictionary. More recent SAP releases are able to take advantage of online index maintenance by using a system-wide setting.
Microsoft Corporation © 2005