Database Maintenance Recommendations | Targetprocess - Visual management software

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 Database Maintenance Recommendations. Image 1
  2. Expand Management and right-click Maintenance Plans, select New planDatabase Maintenance Recommendations. Image 2
  3. After naming the plan, drag “Rebuild Index Task” from Tools to anywhere at the blank space under maintenance planDatabase Maintenance Recommendations. Image 3
  4. Right-click “Rebuild Index Task”, select Edit. Pick the databases you want the task to be executed for:Database Maintenance Recommendations. Image 4
  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 statisticsDatabase Maintenance Recommendations. Image 5
  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 ItemsDatabase Maintenance Recommendations. Image 6 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
    
    DECLARE @GeneralToProcess table ([RowNumber] int IDENTITY(1,1), [ID] int , [NumericPriority] float)
    
    INSERT INTO @GeneralToProcess ( [ID] , [NumericPriority])
    SELECT [GeneralID], [NumericPriority]
     FROM [General]
    ORDER BY [NumericPriority]
    
    UPDATE g
    SET g. [NumericPriority] = gp.RowNumber
    FROM [General] g
    INNER JOIN @GeneralToProcess gp ON g.GeneralID = gp.ID
    WHERE g.[NumericPriority] <> gp.RowNumber
    
    EXEC enableTriggers
    
    COMMIT TRANSACTION
    
    

    Database Maintenance Recommendations. Image 7

  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.

Still have a question?

We're here to help! Just contact our friendly support team

Find out more about our APIs, Plugins, Mashups and custom extensions. Join our community of passionate users and even discuss directly with our developers.