2 Replies Latest reply on Jun 21, 2012 7:46 AM by Chris_W

    Maintenance Plans in SQL

    Chris_W Expert

      Are there any recommended LANDesk ServiceDesk maintenance plans that can assist with performance?

       

      Re-indexing etc.

       

      Cheers,

      Chris

        • 1. Re: Maintenance Plans in SQL
          dgaughran Apprentice

          hi Chris,

          there is a performance.pdf that comes with Service Desk documentation. amongst other things there are a number of recommendations for the database platform. we have a reorganise index/rebuild index/update statistics database plan that we run once a week. which did give us some improvement back in the day. we also delete user sessions nightly as this table can grow quite large (table is tps_user_session).  see excerpt from the pdf from page 12/13 below

           

          Database maintenance planning

           

          We recommend that a regular backup and maintenance plan is in operation against the Service Desk

          database.

          The implementation of such plans is the responsibility of the customer’s Database Administrator.

           

           

          SQL Server and Oracle: Rebuild Indexes

           

          Regular re-indexing of tables or defragmenting of indexes helps to optimise database performance. Also,

          monitor database statistics (especially after large data imports). We recommend that DBAs review index

          fragmentation in the database and if necessary rebuild.

          Microsoft SQL Server provides a standard maintenance plan to rebuild indexes on a database. We

          recommend that you schedule this to run on a weekly basis.

           

           

          SQL Server and Oracle: Update statistics

          Microsoft SQL Server provides a stored procedure called sp_updatestatsistics that updates information

          about the distribution of key values in the database. We recommend that you run this stored procedure

          after importing data. Consider scheduling this stored procedure to run every overnight.

          For Oracle you can configure an automated task in Oracle Database Control to gather optimizer statistics

          during a defined maintenance window.

           

          SQL Server and Oracle: Indexing the database

          Indexing a database can significantly improve performance by providing faster searching. However, take

          care with indexing, because it can affect data writing and deleting because the indexes also need to be

          1. updated. Indexing also makes the database larger.

          For Microsoft SQL Server implementations, use this only after all other performance options have been

          1. explored. For Oracle implementations this is recommended initially and if many design changes are made.

          Object and Query Designer changes affect the indexes.

          Microsoft SQL Server provides tools that enable you to identify and implement database indexes that will

          optimise the performance of procedures that are perceived to be slow.

          On a test copy of the system, time the procedures, add the indexes using the procedure below, and then

          time the same procedures again.

          To create new indexes in SQL Server:

          1. Start SQL Profiler and create a new trace.
          2. Select the Tuning template.
          3. Start the trace and perform the non-performing steps that you previously decided on (for example,

          logging and assigning an incident within Console), timing each stage.When you have finished the predefined steps stop the trace, and save the trace as a trc file.

          4. When you have finished the predefined steps stop the trace, and save the trace as a trc file.

          5. Start the Database Engine Tuning Advisor.

          6. In the Workload group, select the File option button, then browse to your trace file.

          7. Select the check box for the database you want to use for the workload analysis – this is just where

          the tuning advisor does its work; it's best to stick with the same database throughout

          8. Click Start Analysis.

          The Database Engine Tuning Advisor analyses the database using the trace.

          9. When the advisor returns its results, on the Action menu, click Save Recommendations

            NOTE:

          If there are no recommendations, then the performance issue isn't likely to do with the database itself

          "Use, Go" statement can be deleted if you make sure you are running the script on the correct database.

          1. 10. After indexing the test database, run through the steps you decided on and take new timings to

          compare with the original timings. If there is any significant improvement it is probably worth

          running the script on the live system. If not, it's best NOT to create indexes for the sake of it

          because of the aforementioned impact on writing data.

          Similar tools are available for Oracle such as the SQL Tuning Advisor which can be used to recommend and

          implement indexing changes.

          WARNING: These tools can sometimes advise to remove indexes. Do not remove any existing indexes from the database.

          • 2. Re: Maintenance Plans in SQL
            Chris_W Expert

            Appreciate it!

             

            Sometimes forget the guides can contain useful info.