File vault database indexes can become scattered or fragmented over time based on file operations, such as insert, update or delete. If you conduct an upgrade or a data migration which adds multiple records, the indexes are affected.
It is recommended that you rebuild SOLIDWORKS file vault database indexes at least once a month to improve overall performance. In cases of large databases and significant amounts of activity, you may find that more frequent rebuilds, such as once per week, improve performance. SOLIDWORKS also recommends that you reorganize your indexes every week.
This technical blog explains ways to create a maintenance plan to schedule rebuilding and reorganizing activities.
Estimates suggest that anywhere from 5 to 30 percent of indexes should be reorganized at any given time, while a full 30 percent should be rebuilt, due to fragmentation. Reorganizing the database is a relatively fast process; however, it does not fix everything. A rebuild starts from scratch by dropping the index and building it all over again. It is a resource intensive solution that fully fixes the problem.
How to Reorganize and Rebuild SOLIDWORKS File Vault Database Indexes
Creating and running a maintenance plan to rebuild or reorganize your indexes solves the fragmentation problem and will improve performance of scans, searches and browsing in the vault. Best practices indicate that you should run the index maintenance plan about once a week.
To set up a maintenance plan:
- Open SQL Management studio and log in to the vault.
- Right-click Maintenance plans > Maintenance plan wizard.
- Step through the wizard, set it to run “on demand” (or define a schedule if you prefer to run it at a later time).
- Select “reorganize index” and “rebuild index.”
- Select the vault database(s).
- To run the Maintenance plan, make sure your SQL server agent service is running. Right-click Maintenance Plan and select Execute.
Note that the time it takes to complete the operations depends on the size of the database and how fragmented the indexes currently are. It is possible to run it while the vault is in use, but the overall performance might be affected while the maintenance plan is running. You may wish to schedule the operation to run later in the day when less activity is occurring in the vault.
When to Rebuild SOLIDWORKS File Vault Database Indexes
Best practices suggest that you should rebuild indexes when fragmentation is greater than 30 percent. When fragmentation is between 10 to 30 percent, reorganizing the index is enough. Rebuilding indexes is more intensive than reorganizing and takes more time to complete. You can detect the amount of fragmentation following the instruction in the Detecting Fragmentation chapter of the SQL Server Help document, shown below (click to enlarge).
Need help? Contact the experts at xLM Solutions.