Rebuild EPIM Database Indexes

The database indexes used by EnterWorks may become fragmented over time, which can significantly impact query performance. To address this, the indexes should be rebuilt on a regular basis, such as weekly. You may also want to rebuild them if many changes have been made to the EPIM database.

In the process of rebuilding the indexes, you will be directed to run theIndexFragmentation script. To determine how frequently indexes should be rebuilt, rebuild the indexes, then immediately afterward create a baseline report by running the IndexFragmentation script again. Save the baseline report, then at periodic intervals, (daily or weekly), run the script again and compare the new report to the baseline report. Specifically, look for tables with a large page count and a high increase in fragmentation. If the fragmentation is increasing dramatically, consider rebuilding your indexes more frequently.

Note: Some tables are transient in nature. Rebuilding the indexes for these tables may not result in a significant improvement in performance. Tables most likely to be transient have names that contain the text "TMP" or "BATCH".

To rebuild the EPIM database indexes:

  1. Connect to the EPIM database using SQL Server Management Studio.
  2. Copy the IndexFragmentation script and run it in SQL Server Management Studio.
  3. In the Results, select the Table column for the rows that have an avg_fragmentation_in_percent column value of 20 or higher. Right-click the selected rows and click Copy with Headers.
  4. Paste the rows to an empty Excel spreadsheet.
  5. Sort the spreadsheet on the Table column.
  6. Select the rows you just pasted and click the Remove Duplicates button.
  7. Check the My data has headers and the Table checkboxes, then click OK.
  8. Copy the text: =CONCATENATE("ALTER INDEX ALL ON ", A2, " REBUILD") to the B2 cell of the spreadsheet:
  9. Copy the value of the B2 cell and paste it to the B column cell for all the other rows except the header. This will result in all the B column values changing to ALTER INDEX ALL ON <table-name> REBUILD.
  10. Copy the contents of the B column and paste it in the SQL Server Management Studio's Query window.
  11. Run the commands. Depending upon the total number of records, this operation may take some time.