Auto Update Statistics Async in SAP NetWeaver

  • Article

The cost based query optimizer of SQL Server relies on actual index and column statistics. SAP strongly recommends setting the database options AUTO_UPDATE_STATISTICS and AUTO_CREATE_STATISTICS. As a result, statistics are automatically maintained. We recently described the details in the following BLOG: https://blogs.msdn.com/b/saponsqlserver/archive/2011/09/07/changes-to-automatic-update-statistics-in-sql-server-traceflag-2371.aspx

Benefits of AUTO_UPDATE_STATISTICS_ASYNC for SAP ERP

SAP ERP and almost all other SAP components use parameterized SQL statements. The same SQL query is typically executed by many users. The query execution plan stays valid for days and weeks. It becomes invalid once the statistics are not up-to-date any more. At this point in time, SQL Server performs an automatic update statistics and creates a new execution plan. In many cases, the old and the new execution plans are identical. Even when the execution plans were slightly different, users often do not observe a different runtime of the two execution plans. However, the runtime of the query might be much higher while the automatic update statistics is running.

When the database option AUTO_UPDATE_STATISTICS_ASYNC is set, the runtime of the query will never include the runtime of the update statistics. SQL Server then simply uses the old execution plan and starts an asynchronous update statistics. A new execution plan will be created once the same query is executed the next time and the asynchronous update statistics has finished.

Drawbacks of AUTO_UPDATE_STATISTICS_ASYNC for SAP BW

SAP BW queries do not use parameterized SQL statements. Even for the same BW query, a new SQL query is created for each execution (containing the parameters as literals). Therefore there is never an old execution plan, which could be used while the update statistics is running.

Things become worse, when a temporary BW tables are involved. These tables are created as normal SQL server tables, filled with data and finally used in a BW query for joining other tables. Immediately after the query execution, the temporary BW tables are dropped. When AUTO_UPDATE_STATISTICS_ASYNC is set, the optimizer cannot use valid index statistics. The asynchronously created statistics are only available after the query has been executed the first (and only) time.

This particular issue has been fixed by SAP note 1610269 (BW query performance improvements in rsdu_analyze_table_mss). Once this note is implemented, SAP BW runs a manual update statistics on temporary BW tables, after the table has been filled with data and before the SQL query is executed. Therefore this scenario works fine even when AUTO_UPDATE_STATISTICS_ASYNC is set.

Changes in SAP recommendations

There are two known scenarios, where AUTO_UPDATE_STATISTICS_ASYNC can cause some trouble in SAP BW. In the first scenario, a target cube is completely re-loaded from a source cube or a Data Store Object. This can be easily done in a BW Process Chain using the process types “Complete Deletion of Data Target Contents” and “Data Transfer Process” (DTP). The first process type truncates the fact tables of the target cube and performs a manual Update Statistics. Since the table is empty, the index statistics are empty, too. After running the DTP, the fact tables typically contain millions of rows, but they still have an empty index statistics (If you do not add the process type “Construct Database Statistics” to the Process Chain). This can result in performance issues for the first BW query after such a Process Chain. For the second execution of the same BW query, the asynchronously created index statistics already exist.

Another, similar scenario is running BW queries the first time after a system copy or migration.

In order to address these issues, we have changed SAP's configuration recommendations for SQL Server in SAP note 879941 (Configuration Parameters for SQL Server 2005) and SAP note 1237682 (Configuration Parameters for SQL Server 2008). The official SAP statement now looks like this:

SAP strongly recommends to set the the following database options:

alter database <SID> set auto_create_statistics on
alter database <SID> set auto_update_statistics on
alter database <SID> set auto_update_statistics_async on

However, on SAP BW systems we have seen a few performance issues with AUTO UPDATE STATISTICS ASYNC. These issues can not occur on other SAP systems. Therefore you may turn off this feature on SAP BW systems:

alter database <SID> set auto_update_statistics_async off