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.
To rebuild the EPIM database indexes:
-
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.
-
Paste the rows to an empty Excel spreadsheet.
-
Copy the text:
=CONCATENATE("ALTER INDEX ALL ON ", A2, " REBUILD")to the B2 cell of the spreadsheet: -
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. -
Copy the contents of the B column and paste it in the SQL Server
Management Studio's Query window.