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 backup. To have a consistent backup stop all the LANDesk and Intel services and then make a full backup of the database. BASICS 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 job CAPTURING THE WORKLOAD 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. 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. CONCLUSIONS 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: http://community.landesk.com/support/docs/DOC-2356 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.
Pages to are hidden for
"How to tune the Microsoft SQL Server database performance for a "Please download to view full document