SQL Maintenance script for the Symantec Management Platform database

Products

• IT Management Suite
• Client Management Suite

Issue/Introduction

Index fragmentation in the Symantec Management Platform database (Symantec_CMDB) is a performance concern. It affects console performance, replication, and client data processing.

Index fragmentation is a normal side effect of adding and removing data. Without regular maintenance, fragmented indexes slow down queries across the console, replication, and NSE processing. The attached script targets only indexes that are in active use and above a configurable fragmentation threshold, rebuilding or reorganizing them as needed. A script audit (below) found one functional bug in the original script's low-fragmentation handling and confirmed the script runs on SQL Server versions through 2022, with one deprecated dependency to track. An alternate, built-in scheduled task is also documented for environments that want a second maintenance pass.

Environment

IT Management Suite (ITMS) 8.7.x, 8.8.x

Symantec_CMDB database

SQL Server 2008–2019 (original script scope); verified compatible through SQL Server 2022 during this audit (see Cause)

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.

Added based on script audit: the attached Updated script joins sys.sysindexes, a system view Microsoft has listed as deprecated since SQL Server 2005 and has not yet removed as of SQL Server 2022. This is not a current failure but is worth tracking if a future SQL Server release drops the view.

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 SQL script (UPDATED-Index_Defragmentation_-_2008_-_2022.sql) 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, change the top line of the script from USE Symantec_CMDB to USE <Non-Default DB Name>.

Steps for creating a SQL Job on SQL Server 2012 – 2022:

1. Open SQL Management Studio.
2. Expand SQL Server Agent in Object Explorer if it is not already expanded.
3. Right-click the Jobs folder and select New Job.
4. On the General page:
   1. Name: "Defragmentation and statistics Maintenance"
   2. Owner: change if needed
   3. Category: no change needed
5. On the Steps page:
   1. Click New.
   2. Name it "Run Script".
   3. Type: 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.
6. On the Schedules page:
   1. Click New.
   2. Name: Schedule
   3. Schedule Type: Recurring
   4. Occurs: Daily
   5. Pick a time based on other scheduled activity on the server.
   6. Click OK.
7. Click OK to save the job.

Alternate resolution: NS.SQL defragmentation schedule task

The NS.SQL defragmentation schedule.{cdcd50e9-1c42-402b-921c-8ad6c9ff0d34} task is a built-in alternative or complement to the attached script. Since ITMS 8.1 RU4, if no custom schedule is set, it runs weekly every Saturday at 12:00 PM; a custom schedule overrides that default (Information regarding NS.SQL defragmentation schedule).

The task runs two stored procedures shipped with the CMDB schema (Maintenance of your CMDB - analyzing the defragmentation level of CMDB and performing the defragmentation):

1. csp_IsDefragTables checks the fragmentation level and flags the database if a critical threshold is reached. The threshold defaults to 50% and is configurable through the @CriticalFragmentation parameter.
2. csp_DefragTables performs the defragmentation if that threshold is met. Broadcom notes this can take a few hours on larger databases and affects SQL instance performance while running.

A combined maintenance pattern: run the NS.SQL defragmentation schedule task 2–3 times a week (default or a lighter custom schedule), and run the KB 158633 script on the remaining days. This applies two independent checks with different logic instead of relying on a single traditional SQL Maintenance Plan applying one fixed action to every index regardless of usage.

Verification

1. Run the evaluation query at the bottom of the script (uncomment it) and confirm the Rebuilt, Reorganized, Skipped, and Failed counts match expectations, with low-fragmentation tables now appearing under Skipped rather than Failed.
2. Confirm sp_updatestats completed without error if @updatestats = 1.
3. If also using the NS.SQL defragmentation schedule task, check the task's run history in the Windows Task Scheduler on the Notification Server to confirm it completed on the expected cadence.