How to tune the Microsoft SQL Server database performance for a by joI8Pj


									 How to tune the Microsoft SQL Server
 database performance for a LANDesk
 Management Suite Core server
 The following procedure applies to SQL Server in general but this document contains some
 instruction in relation to the usage of LANDesk Management Suite whose database is hosted on
 Microsoft SQL SERVER 2000

 A similar procedure can be used on Microsoft SQL Server 2005 but some GUIs can be different.

      NOTE: BEFORE to proceed with any kind of database modification please be sure to have a

To have a consistent backup stop all the LANDesk and Intel services and then make a full backup of the


 The whole idea behind the database tuning procedure is based on spot the most time consuming
 queries and use the Microsoft Index Tuning Wizard to understand if it is possible to improve the
 execution speed of them

 The Microsoft Index Tuning Wizard works better if we are able to isolate the relevant data avoiding
 to ‘feed’ it with un-necessary queries (noise)

 To minimize the noise we need to stop all the not relevant services and avoid having other users
 working on the core server with web consoles or other 32 bit additional consoles.

 For example, if we are experiencing slowness creating a new package definition, we can stop the
 Scheduler and the Inventory service because they are not involved in this process.
  We can stop, in this case, all the other services because creating a package definition is only a job
 between the console and the database.

 If the slowness is experienced starting a job, all the other services can be stopped (like the Inventory
 Service) but not the Scheduler Service because it is involved in the process of starting the scheduled

To feed the Optimizer we need, first of all, to capture the workload using the SQL Profiler.

    1.  Launch the SQL Server Enterprise Manager
    2.  Click on Tools and choose SQL Profiler
    3.  In the SQL Profiler window select File -> New -> Trace…
    4.  Provide the connection information to the SQL DBMS where the LANDesk database is hosted.
    5.  In the Trace Properties in the Trace name field enter a name and choose SQLProfilerTuning
         as Template Name
    6. Go to the Filters tab of the Trace Properties window and set DatabaseName -> Like the name
         of the LANDesk Database
    7. Press Run
    8. In the LANDesk console do the action that cause slowness and wait until completes
    9. Press in the SQL Profiler window the stop button (red square)
    10. Select File ->Save As -> Trace File… and save the full trace.
    11. Select File->Properties and click on the Filter tab.
    12. Add a filter under Duration -> Greater than or Equal. The value is expressed in milliseconds.
         The suggested cut-off value is 500. Press Ok.
    13. Now only the SQL statements that require more than half second will be shown.
         Select File ->Save As -> Trace File… and save the trace file with a different name.
         This will be the trace file we will use to feed the Index Tuning Wizard.


    1.   From the SQL Profiler Window go to Tools -> Index Tuning Wizard
    2.   Provide the connection information to the SQL DBMS where the LANDesk database is hosted.
    3.   Select the database to tune
    4.   Set the tuning mode to Through, make sure that both the checkboxes “Keep your indexes”
         and “Add indexed views” are flagged and press the Next button.
    5.   Select “My workload file” option and select the file previously saved then press next
    6.   Press Advanced and uncheck the option “Limit number of workload queries..” then press Ok
    7.   Press Next
    8.   Press Select all tables and press next and the analysis will start. (can take long)

         When the process will finish if the tool will find some optimization suggestions will give the
         possibility to apply the changes immediately or the ability to save the script to achieve them
         in a second time.
         The script can be used through the SQL Query browser.

      After the changes are done to the database restarts all the services that were stopped
      before or reboot the core and try again the operations that were causing slowness to see if
      they are improved.
      The first times maybe not a significant performance improvement will be noticed: this is due
      to the fact that the database needs to populate the indexes and calculate the statistics.
      Try it for a couple of days.
      If the results are still not satisfactory it is possible to repeat the procedure described in this
      article several times until an acceptable result is obtained.
      After a certain point you will see that no more suggestions are given.
      In this case to speed up the situation you need to analyze other possible bottlenecks like the
      network connection between the console and the DBMS, the DBMS hardware and resources
      and the core server resources.
       More information about how to tune the DBMS can be found in the community article
      DOC-2356 available at this URL:

       HELP US TO MAKE LANDesk a better product.

If you like you can send, as comment to this article (DOC-4572), the scripts generated from the SQL
Index Tuning Wizard utility. We will revise them and consider including them in the next releases of
the product.

To top