SQL Maintenance script for the Symantec Management Platform database

Products

• IT Management Suite
• Client Management Suite

Issue/Introduction

Fragmentation of indexes is a large concern and performance impact on the Symantec Management Platform database (Symantec_CMDB).

It can affect many areas of the product including console performance, replication and client data processing.

Environment

ITMS 8.x

Cause

Index fragmentation is a normal process that happens as data is added and removed from a database.  Maintenance needs to be in place to address it.

Resolution

Note: The attached script can be run using any available method, such as a SQL Job, SQL Maintenance Plan for Scripts, Task Server task from the product, etc.

The attached script has the following benefits:

• Only defragment tables that are being used and have at least 20% (Configurable) fragmentation
• Rebuild for greater than 50% (Configurable) fragmentation and use the more efficient Reorganize for 20-49% (Configurable)
• Contains a list of tables to always Reorganize to help avoid deadlocking
• Continue if any table fails rather than aborting like a typical Maintenance Plan
• Updates the Statistics using Microsoft's sp_updatestats

IMPORTANT: If the name of the database is not the default Symantec_CMDB.  Please make sure to change the top line of the script from USE Symantec_CMDB to USE < Non-Default DB Name>

Example steps for creating a SQL Job on SQL Server 2012 - 2019

1. Open SQL Management Studio
2. Open Object Explorer if it is not and expand "SQL Server Agent"
3. Right-click on the "Jobs" folder and select "New Job"
4. In the Select a page pane
   • General
     1. Name: "Defragmentation and statistics Maintenance"
     2. Owner: Change if needed
     3. Category: does not need to be changed
   • Steps
     1. Click "New"
     2. Name it "Run Script"
     3. Type: should be Transact-SQL script (T-SQL)
     4. Run as: can be left blank
     5. Database: Select the correct Database
     6. Paste the script attached to the KB for the appropriate version
     7. Click "OK"
   • Schedules
     1. Click "New"
     2. Name: Schedule
     3. Schedule Type: Recurring
     4. Occurs: Daily
     5. Pick an appropriate time for the server based on other scheduled tasks.
     6. Click "OK"
   • Click "OK"