Microsoft (MS) SQL statistics job for imporved performance

  • Document ID:KM492240
  • Creation Date:28-Aug-2008
  • Modified Date:28-Aug-2008
    • Micro Focus Products:
      universal cmdb

Question

Microsoft SQL Server allows statistical information regarding the distribution of values in a column to be created. This statistical information can be used by the query processor to determine the optimal strategy for evaluating a query. It is recommended to update index statistics daily

Answer

Whether the auto update statistics database option is enabled or disabled, it is strongly recommended that you create an automatic task to update statistics for the CMDB databases on a daily basis, as the data is frequently changed. The job should execute the sp_updatestats API against the specific database.

In addition to running a daily task to update statistics for the management and CMDB databases if the auto update statistics database option is not enabled, it is recommended that you manually refresh statistics for CMDB if major changes to the CMDB schema objects have occurred, usually caused by bulk insert transactions

Utility to Refresh Statistics
The update_statistics.bat utility has two working modes:

  • List tables with out of dated statistics. In this mode, a list of the tables is
    returned, together with the commands needed to update statistics the tables
    at a later time.
  • Update statistics on the tables. In this mode, all tables with outdated
    statistics are being updated.
    To run the update_statistics.bat utility:
    Execute CMDB\db_scripts\update_statistics.bat with the following parameters:
    • SQL Server name
    • Database name
    • SA password
    • Working mode - 0 to provide a script for later use; 1 to update statistics
      automatically.

Note: This Utility is only provided with UCMDB 7.x which and can be located in directory <HP UCMDB Server install directory>\j2f\cmdb\dbscripts\ms_2005\utils

For details in tuning database or for MS 2000 please refer with your database administrator or the Database Guide available with the product documentation.

Use Deploying and Maintaining MS SQL Chapter in the UCMDB/MAM documentation. For MAM 6.x refer to MAMPrepare.pdf, for UCMDB 7.x refer to DatabaseGuide.pdf

For extensive information on Microsoft SQL Server performance tuning, refer to the following documents: