Manager performance reduction due to database indexes

Owned by Mattias Löfstrand
Last updated: 26 Mar 2020
2 min read

Problem

Manager may become slow after usage a log time. As time goes database indexes may become fragmented and this can cause performance reductions when working in the Manager.

From version 4.5 database indexes are reorganized at a regular basis automatically, but for earlier versions a manual index rebuild may be needed.

Solution

Follow these steps to check if indexes needs to be reorganized and to rebuild database indexes.

Step 1 - check indexes

  1. Open SQL Management Studio and connect to the editor database

  2. Open the script “Reorganize indexes.sql”

  3. Update the name of the database in the script to correspond to the name for the customer. In the script the database is named “Assert4#XXX“, change this to the real name.

  4. Cross check that the script is report only. It should look like this: “SET @reportOnly = 1;”

  5. Run the script.

The script should produce a report similar to the below (example below show indexes in a good shape). If the values in the AvgFragmentationPercentage stays at a low level, and only a few are just above 50% no reorganization is needed. If values are high on some tables, proceed to step 2.

Step 2 - reorganize indexes

  1. Update the name of the database in the script to correspond to the name for the customer. In the script the database is named “Assert4#XXX“, change this to the real name.

  2. Cross check that the script is not a report. It should look like this: “SET @reportOnly = 0;”.

  3. Run the script.

Now indexes will be reorganized and fragmentation level will get reduced. You can cross check this by rerunning the script with reportOnly set to 1 according to step 1 above.

 

 

Note that in version 4.5 and later indexes are reorganized automatically.