As part of routine maintenance on the portal, we recommend getting the indexes rebuilt for the portal databases regularly. One of the major causes of slowness within the portal is a badly fragmented index, causing SQL queries to take longer to run and results to take longer to return.
A SQL Server Maintenance Plan can be created to re-index the portal databases on a regular basis, as long as the SQL Server Agent is running. Here's how to set one up:
- In SQL Server Management Studio, go down to Management -> Maintenance Plans.
- Right-Click Maintenance Plans and choose New Maintenance Plan... and give the plan name.
- From the toolbox, double-click the item Rebuild Index Task to add it to the grid.
- Double-click the Rebuild Index Task to edit it.
- Click the New.. button next to the Local server connection. Add a connection name, server name, select Use a specific user name and password, and insert the Passageways SQL user information.
- Click on the Database(s) field. This will bring up another popup with database selection information. Select the radio button next to "These Databases", and then put checkmarks next to all of the Passageways portal databases.
- Click OK when done.
- Under Free space options, click the radio button next to "Change free space per page percentage to" and set it to 15%.
- Under Advanced options, check the box next to "Sort results in tempdb."
- (Optional) If you have SQL Server Enterprise Edition, check the box next to "Keep index online while reindexing."
- Click OK to save the task.
- Now that the task part is complete, near the top of the page, click the Schedule button.
- Set the plan's schedule to run. We recommend weekly; Sundays is a good time to run the rebuild indexes.
- Hit OK to save the schedule.
- Close the Maintenance Plan and choose to save it.
- Run the Maintenance Plan to make sure it doesn't error.