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:
    
    --script is actual for v3.8.8
    --2016-06-27
    
    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] WHERE [EntityTypeID] in (8, 12 , 9 , 4, 5, 17 , 14 , 16, 1 , 24, 27)
    ORDER BY [NumericPriority]
     
    UPDATE g
    SET g. [NumericPriority] = gp . RowNumber
    FROM [General] g
    INNER JOIN @GeneralToProcess gp ON g . GeneralID = gp. ID
    
    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.

  • Paul Goldstraw

    Current advice from a number of SQL Server experts is to NOT shrink your database. Shrinking the database will undo all the work you’re doing reindexing, and consume resources when the database inevitably needs to grow again. There are a number of blogs on this topic, the best of which are probably

    http://www.sqlskills.com/blogs
    http://thomaslarock.com/2009/0

  • Anna Ko

    Indeed, shrinking is not always applicable – that’s why we just mark this as optional step. For example, it helps us a lot to reduce the database size.

    Thanks!

  • Paul Goldstraw

    Hi Anna,

    The place i’m referring to is the very last box out on this page, suggesting using the Shrink Database Task :-

    “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.”

    Thanks

  • Anna Ko

    Indeed, shrinking is not always applicable – that’s why we just mark
    this as optional step. For example, it helps us a lot to reduce the
    database size.

    Thanks!

  • Julia Khmylova

    If you’re using SQL Express (which we don’t recommend for production environment), you need to do maintenance a bit differently: http://kb.verba.com/display/do

  • Bryan Swang

    Is it safe to run a scheduled task with sqlcmd and and input file containing the above statement for those using SQL express and as such don’t have the SQL server agent available?

  • Alex

    Hi Brian,
    The SQL version is not a limitation here. You’re welcome even apply this script manually to your database so you may eliminate scheduled agent from the workflow at all. The only reason to use agent is automation.

    The task should be performed weekly, but if you execute it more rare, it won’t be a problem. If you don’t do a maintenance on a regular basis it can just make your database work slower then it could.

  • Bryan Swang

    Thanks for the confirmation, Alex.

  • John Frantz

    is the script above valid to use with version 2.24.8.24273?
    we self-host TP and are confirming that we have all of the maintenance best practices being followed.

  • Alex

    @johnfrantz:disqus yes this script works for versions older than 3.8.8 listed currently and I confirm that for 2.24.8 it also should work without any issues.

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.

Get started for free

How many people would be using Targetprocess?
  • Myself
  • 2–20
  • 21–100
  • 101–1000
  • 1000+
By clicking Continue you agree to our Terms of service and Privacy policy