Microsoft SQL Server (SQL Server) database tables should be optimized every time the database has been updated with significant amounts of data.
To help you with the administration of the management database (MDB) on SQL Server, CA IT Client Manager supplies the DsmMSSqlOpt.bat maintenance script that administrators can apply regularly.
The DsmMSSqlOpt.bat script helps you optimizing the database tables by performing maintenance tasks such as defragmenting the index and updating statistics. The script affects only tables that are owned by CA IT Client Manager.
The DsmMSSqlOpt.bat maintenance script is automatically installed during CA IT Client Manager installation to the following location:
%Program Files$\CA\DSM\database\mdb_install\mssql\DsmMsSqlOpt.bat
The DsmMSSqlOpt.bat maintenance script is also available on the CA IT Client Manager installation media (DVD) at the following location:
Maintenance\Windows\mssql\DsmMsSqlOpt.bat
The DsmMSSqlOpt.bat maintenance script can be executed with certain options as follows:
DsmMsSqlOpt.bat [-pagecount=n] [-maxfrag=m] [ -usereindex] [ {local | ServerName} [MDBName] ]
Specifies the maximum number n of pages of tables or indexes. Tables or indexes with more than the specified number of pages will be defragmented. n is a numerical value.
Default: 1000
Specifies a degree m of fragmentation. Tables with the specified degree of fragmentation will be defragmented. m is a numerical value.
Default: 10
Specifies that indexes are rebuilt instead of defragmented. By default, the DsmMsSqlOpt script performs defragmentation of indexes.
If you have a DSM enterprise manager in addition to domain managers, remember that the maintenance script needs to be executed against the databases on both tiers. We recommend that you run the script at least once after the first 1,000 computer assets have been registered in the domain database. Subsequently, the script should be run every time an additional 5,000 computer assets have been registered. On the enterprise, maintenance should run every time 5,000 computer assets have been replicated from the associated domain managers.
The DsmMsSqlOpt.bat script must be run locally on the computer where the MDB is installed. The script offers two options: it can be used to rebuild indexes or defragment indexes. Before running the script with the rebuild index option, we recommended that you shut down all manager components that access the MDB. The DSM components should be restarted after the script has finished.
When the script is called with the option to defragment indexes, DSM components may be kept up and running. However, operations initiated by the script are resource-intensive and may have a negative impact on performance. Also, consider that for large databases, defragmentation of indexes may take several hours to complete.
Therefore, you may want to schedule MDB maintenance tasks at times when there is little or no workload on the MDB. For example, the script could be scheduled to launch once a week overnight, or over the weekend.
|
Copyright © 2013 CA.
All rights reserved.
|
|