New SQL Server 2016 functionality helps SAP supportability

  • Article

Due to the combined effort of the SAP - Microsoft Porting group the SQL Server Development team added a new functionality to the SQL Server UPDATE STATISTICS command and to the way SQL Server automatically updates statistics.

This new functionality enables the SAP customers on SQL Server to persist a sample rate of the manual and automatic statistics update.
In some cases the default sample rate of the manual or automatic UPDATE STATISTICS command is too small to reflect the  real distribution of data within the table. This is especially true for very large tables with a low or very low selectivity on the column in question. One should know that the sample rate that is used for the automatic update statistics depends on the total amount of rows and will decrease with an increase of the table size, means bigger tables have smaller sample rates. With this new addition we can force a sample rate for specific columns that is then used by manual (manual updates without specifying a sample rate) and automatic updates later on.

The new addition of the UPDATE STATISTICS command is a new option in the WITH clause with the syntax:

PERSIST_SAMPLE_PERCENT = { ON | OFF }

It is officially documented in books online and Pedro Lopes from the Microsoft Tiger Team blogged about it in more detail here. It is shipped with SQL Server 2016 SP1 CU4 (13.0.4446.0), so you need to have at least this update if you want to use this feature.

Please handle this new option with care and only when you have strong evidence that the default sample rate is too small. Wrong usage (e.g. very high sample rate for many columns on busy tables) can increase the system load tremendously up to a system standstill.

Comments

  • Anonymous
    September 25, 2017
    Hi Clas,We are on ERP 6.0 EHP7 version of SAP with SQL 2012. We are exploring the possibility to upgrade to SQL 2016. Is it certified by SAP for ERP ? Kindly suggestThanksNeeraj
    • Anonymous
      September 28, 2017
      Hi,yes, see note 2201059 for the details and requirements.RegardsClas