SQL MDB Maintenance and Optimization

book

Article ID: 127962

calendar_today

Updated On:

Products

CA Automation Suite for Data Centers - Configuration Automation CA Client Automation - Asset Management CA Client Automation - IT Client Manager CA Client Automation CA Client Automation - Remote Control CA Client Automation - Asset Intelligence CA Client Automation - Desktop Migration Manager CA Client Automation - Patch Manager

Issue/Introduction

As Client Automation (ITCM)'s backbone of the product is its database, maintenance & optimization needs to run periodically to help improve overall performance.  

  • MS SQL server tables should be optimized every time the database has been updated with significant data. 
  • The DsmMsSqlOpt.bat script helps you perform this optimization.
  • The script performs maintenance tasks such as defragmenting the index and updating statistics. 
  • This script effects only tables that are owned by Unicenter DSM. 

Environment

Release:
Component: DTSVMG

Resolution

There is a SQL script that runs the maintenance and optimization to accomplish this.  The script can be found on installation media (DVD) at the following location:
\Maintenance\Windows\mssql\DsmMsSqlOpt.bat 
The DSM Script must be run locally on the computer where the MDB is installed. 
 
DsmMsSqlOpt Syntax: 
DsmMsSqlOpt.bat [-pagecount=n] [-maxfrag=m] [-usereindex] [ {local | ServerName} [MDBName] ]

It is good practice to rebuild indexes with a fragmentation degree over 30% as the first maintenance step because rebuilding indexes runs much faster than defragmentation.
To achieve this, you must launch the DsmMsSqlOpt script with the options - usereindex and maxfrag=30 
Ex: DsmMsSqlOpt.bat -maxfrag=30 -usereindex

After the initial step, all tables with a fragmentation over 10% should be defragmented. 
This can be done by calling the DsmMsSqlOpt script with the option -maxfrag=10 
Ex: DsmMsSqlOpt -maxfrag=10

Additional Information

Reminder:
Based on fair usage, this script should be run about once a week

Note:
The %temp% variable must be set to an appropriate working directory before launching the DsmMsSqlOpt.bat script. 

Additional Info:
There is a Proper Care and Feeding of your SQL MDB, guide that goes farther in depth of what you can do to keep your MDB running more efficiently.