Does it seem like it is taking longer to do searches or even listing all of the files when browsing? One of the things that could be causing this are fragmented indexes in the database.
Many of the databases in your Enterprise PDM vault have an index to ensure that data is found quickly when accessed by other database operations. Over time as more and more operations are run, these indexes can get scattered (similar to the data on a hard drive getting fragmented as you add and delete files) which makes it more difficult to find the information. This can be especially noticeable after an upgrade or after importing a lot of files into the vault.
The indexes can be updated by setting up a maintenance plan in SQL to rebuild and reorganize them. Doing this on a regular basis should help improve performance during searching, browsing, etc.
1. Open SQL Server Management Studio and log in.
2. Right click on Maintenance Plans and select Maintenance Plan Wizard
3. On the Select Plan Properties page enter a name for the plan and a description if you would like.
4. To set up a schedule select the Change… button.
5. Set up the recurring schedule on the Job Schedule Properties window. The example below is set to run weekly on Sunday at midnight. If there is a lot of activity in your vault it can be run daily as long as there is a convenient time when the vault is not being used. Otherwise weekly should be sufficient.
6. Click Next and select the tasks to run. Check the boxes for Reorganize Index and Rebuild Index.
7. Click Next. Click Next again on the Select Maintenance Task Order window.
8. On the Define Reorganize Index Task window, click on the drop-down to select the database(s). Selecting These databases: radio button will allow you to select a specific database or multiple databases. Or select All user databases (excluding master, model msdb, tempdb). Then click Next.
9. On the Define Rebuild Index Task, again select the database(s). Use the same selection as the Reorganize Database task. Leave the other settings as default. Click Next.
10. You can create a report and save it to a text file. Enter a different location if desired.
11. Click Finish on the last window to complete the set-up of the maintenance plan.
12. A window will open and show the actions while the plan is saved. Click Close when completed.
13. If you want to execute the plan immediately, expand the Maintenance Plans and right click on the plan name and select Execute. Otherwise it will run per the schedule.
The time to run the rebuild/reorganize plan the first time will depend on the size of the database and how fragmented the index is. When it is run on a scheduled basis, the time should be relatively short. From what I’ve read, weekly should be sufficient. Performance may be affected during the process so it’s a good idea to run when there is little or no activity in the vault.
If you are really interested in learning more about index fragmentation and rebuilding and reorganizing indexes here are two articles from Microsoft:
Trackback from your site.