Back to Blog Listing
The following list of steps can be followed to tune your SQL Server Database using SQL Profiler, providing an estimated benefit of 98%.
1) Get Database ID (In SQL Server Management Studio run the query SELECT DB_ID() )
2) Open SQL Server Profiler and create a new trace (file -> new trace). Give the trace a name and select the "TSQL_Replay" template
3) From the "Events Selection" tab click the "Column Filters" button. On the pop up select "DatabaseID" from the left hand list and then expand the "Equals" item on the right and enter the ID of your database to the text box
*Do whatever function needs tracing against the database i.e. opening area manager or loading custom page*
Stop the trace and then save the trace file (file -> Save as -> Trace file)
4) From the "Tools" menu select the "Database Engine Tuning Advisor"
Select the trace file you just saved.
5) Select the database for workload analysis to be the same as the one you got the ID for and then select it in the list of databases underneath as well.
6) In the "Tuning Options" tab select "Advanced Options" and define the max. space for recommendations to be 400MB (or a size large enough that does not cause “not enough space” messages when Analysis is started).
7) Start analysis
8) Once complete check if there are recommendations.
The Estimated improvement above is 98%.
The recommendations are usually Indexes and Statistics. Normally we do not apply Indexes unless they are on the AEXML table, as indexes are constraints and may cause issues with deletion of data during normal usage. It is safe to apply all statistics, these usually provide the majority of the improvement.
To implement the selected recommendations, go to the "Actions" menu and choose to "Apply Recommendations".