Question
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:
- Troubleshooting application performance with Microsoft SQL Server:
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx - Performance counters:
http://www.databasejournal.com/features/mssql/article.php/1477311#disk - How to perform a Microsoft SQL Server performance audit:
http://www.sql-server-performance.com/sql_server_performance_audit.asp - Microsoft SQL Server performance tuning tips:
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp