Database Maintenance Recommendations

If you host Targetprocess locally, you may notice that it becomes slower after some time, especially when you deal with a large amount of data and number of users, whereas upgrades prove to be infrequent. We would like to share some tips about database maintenance that need to be executed on a regular basis to promote a faster and more reliable performance of the application.

Put simply, we recommend scheduling and running a weekly maintenance plan in SQL Management Studio with 3 tasks: Rebuild Index, Reorganize Index, Update Statistics + another plan to run a specific sql query. Here is how this can be done:

1. Open SQL Server Management Studio and start SQL Server Agent if it is stopped
2. Expand Management and right-click Maintenance Plans, select New plan
3. After naming the plan, drag “Rebuild Index Task” from Tools to anywhere at the blank space under maintenance plan
4. Right-click “Rebuild Index Task”, select Edit. Pick the databases you want the task to be executed for:
5. Drag “Reorganize Index Task” next to the the existing task, edit it and pick Targetprocess database the same way.
6. Drag “Update Statistics Task” to the two tasks you already have, edit it and pick Targetprocess database the same way.
7. Clicking a task will display a green arrow that can be used to link the tasks. Link them in the following order: rebuild index -> reorganize index -> update statistics
8. Schedule the plan by clicking calendar icon next to sublan. By default it is a weekly run that is executed on Sunday, that should work for most and click Save Selected Items
   You can execute the maintenance plan right away - it should be available at Object Explorer now
9. Add a new maintenance plan. This one is meant for optimizing the way we store Numeric Priority in the database
10. Add just one item there - Execute TSQL Statement. Edit it and add the following query:

    
    USE [TargetProcess] --please replace it with your DB name
    BEGIN TRANSACTION
    
    EXEC disableTriggers
    
    UPDATE g SET g.[NumericPriority] = updateRow.[NewNumericPriority] FROM [dbo].[General] g
    INNER JOIN
    (
    	SELECT ROW_NUMBER() OVER(ORDER BY [NumericPriority])[NewNumericPriority],[GeneralID] FROM [dbo].[General] WHERE [NumericPriority] IS NOT NULL
    ) updateRow ON g.[GeneralID] = updateRow.[GeneralID]
    
    UPDATE tctp SET tctp.[NumericPriority] = updateRow.[NewNumericPriority] FROM [dbo].[TestCaseTestPlan] tctp
    INNER JOIN
    (
    	SELECT ROW_NUMBER() OVER(ORDER BY [NumericPriority])[NewNumericPriority],[TestCaseTestPlanID] FROM [dbo].[TestCaseTestPlan] WHERE [NumericPriority] IS NOT NULL
    ) updateRow ON tctp.[TestCaseTestPlanID] = updateRow.[TestCaseTestPlanID]
    
    UPDATE tptp SET tptp.[NumericPriority] = updateRow.[NewNumericPriority] FROM [dbo].[TestPlanTestPlan] tptp
    INNER JOIN
    (
    	SELECT ROW_NUMBER() OVER(ORDER BY [NumericPriority])[NewNumericPriority],[TestPlanTestPlanID] FROM [dbo].[TestPlanTestPlan] WHERE [NumericPriority] IS NOT NULL
    ) updateRow ON tptp.[TestPlanTestPlanID] = updateRow.[TestPlanTestPlanID]
    
    EXEC enableTriggers
    
    COMMIT TRANSACTION
    
    
11. Schedule the maintenance plan (we recommend weekly) and save it.

You could also consider adding a separate maintenance plan with History Cleanup Task and Shrink Database Task. This won’t enhance performance that much, though can help to reduce database size. However, such tasks may bring up some side effects, like making rollback more tricky. We recommend that you use these tasks, if you know what you are doing and have backups configured.