Enterprise PDM SQL Maintenance Plan: Reorganizing and Rebuilding Database Indexes

The purpose of this article is to provide instructions for how to set up a SQL Maintenance Plan to maintain and/or improve general EPDM (now SOLIDWORKS PDM Professional) vault performance by scheduling a regular reorganization and rebuilding of EPDM Database Indexes. This procedure is based on the SOLIDWORKS Knowledge Base Solution ID S-042552 and recommended for all SOLIDWORKS Enterprise PDM customers.

Background

Many tables in the file vault database contain indexes – to ensure data is found efficiently when lookups are done by various database operations. The SQL server automatically maintains these indexes whenever insert, update, or delete operations are made to the underlying data. Over time as you perform a lot of file operations in the vault - for example after an upgrade where a lot of records may have been modified, or performed a migration where many new records were added - the various database table indexes become scattered (fragmented - similar to how a hard drive can become fragmented). You can update and refresh the indexes by running a maintenance plan to rebuild/reorganize the indexes. If the indexes are very fragmented, this operation may improve general performance with searching, browsing, etc. in the vault.

The following should be taken into account when implementing an SQL Server Maintenance Plan

1. SQL Server Integration Services is a required Feature on the SQL Server to run Maintenance Plans.
2. ALWAYS ensure that a proper backup is in place before making any changes to the SQL database.
3. NEVER manually create your own Index, or otherwise directly edit the Database.
4. Running the Plan once a week should help maintain a Healthy vault.
5. It is possible to run the Plan while the vault is in use, however, overall performance may be affected while the plan in running. It is recommended to schedule this activity during off-hours.

How to Rebuild and Reorganize Database Indexes

1. Log into SQL Server Management Studio using the ‘sa’ username and password.
   
2. Expand the Management Folder right-click Maintenance Plans, and select Maintenance Plan Wizard.
   
3. Select Plan Properties: Name the plan, and click Change to set up the recurring schedule.
   
4. Job Schedule Properties: Set up a recurring schedule during off-peak hours, then click OK.
   
5. Once the Schedule has been determined, click Next.
   
6. Select Maintenance Tasks: Reorganize Index, Rebuild Index. Then click Next.
   
7. Select Maintenance Task Order: Click Next.
   
8. Define Reorganize Index Task: Select All Databases, then click Next.
   
9. Define Rebuild Index Task: Select All Databases, then click Next.
   
10. Select Report Options: Click Next.
    
11. Complete the Wizard: Click Finish
    
12. Close the Wizard.