Component : CCMS/Database Monitors for SQL Server - Microsoft SQL ServerBC-DB-ORA OracleHAN-DB SAP HANA Database

Solution : https://service.sap.com/sap/support/notes/1178916 (SAP Service marketplace login required)

Summary :
The SAP CCMS___Update_Tabstats job, introduced in SAP Basis version 7.00 SP12, causes extensive runtimes and system performance issues due to its execution of the sap_update_tabstats stored procedure, particularly when handling large tables. Originally, each table was repeatedly checked, creating high I/O and CPU usage. Updates in SP14 reduced these checks to once per table, significantly decreasing the problem. For optimal resolution, updating to at least support package 14 is recommended, along with scheduling the job during idle weekend hours to minimize user impact. Additionally, users on SQL Server 2005 should restrict dbcc updateusage to a single run or eliminate it if upgraded from SQL Server 2000. Modifications in DEV system include commenting out specific lines in the MSSPROCS report to prevent unnecessary dbcc updateusage executions.

Key words :
symptom job sap ccms_<sid>_<sid>_update_tabstats runs, job executes stored procedure sap_update_tabstats, locate mssprocs item named sap_tabstats, stored procedure execute   dbcc updateusagefor, set @cmd = n'dbcc updateusage, choose edit->execute script, no_infomsgs'      exec sp_executesql @cmd, completely avoid dbcc updateusage, start running dbcc updateusage, sap basis version 7

Related Notes :

1027512

MSSQL: DBA cockpit for basis release 7.00 and later