Improve SQL performance using SQL Server Profiler and Tuning Advisor

In this section, We focus on how to create Index for Table and how much percentage this index increase the performance.

SQL Server Profiler

1. Open SQL Server Management Studio and connect the Database Server.
2.  To open SQL Profiler, Choose Tools --> SQL Server Profiler

   

   Open SQL Server Profiler through Management Studio

3. New the SQL Server Profile will open, then Connect to Database Server.

   

   SQL Server Profiler

4. Now the Trace Property Window will open.

   

   SQL Profiler Trace Properties Window

5. Choose the "Use the template:" as "Tuning".

   

   Profiler Tuning Template

6. Click on the Tab "Events Selection".

   

   Tuning Template - Events Selection

7. Click on "Column Filters.." button. Now "Edit Filter" Popup window will open.

   

   Profiler - Event Selection - Edit Filter

8. Now Click on "Database" and Expand the Like & Then specify the Database Name.
   
9.  Then click "OK" button to close the "Edit Filter" Popup window.
10. Now click on "Run" button.
11. Execute the SQL Query or SP which want to optimize in "SQL Server Management Studio".
12. Now check the SQL Server Profiler, the executed Query are logged in the Trace Window.

    

    SQL Profiler Window

13. Now click on the "Stop" icon to stop the trace.

    

    Stop Trace

14. Now save the Trace log as "Trace File". For that, choose File --> Save As --> Trace File..

    

    Save as Trace File

15. Now "Save As" window will open, Type the file and confirm the "SQL Server Profiler trace files (*.trc)" and click on "Save" button.

Database Engine Tuning Advisor

1. To open  "Database Engine Tuning Advisor", In "SQL Server Profiler" choose Tools -> Database Engine Tuning Advisor..

   

   Tuning Advisor from Profiler

2. Now the "Database Engine Tuning Advisor" will open.

   

   Database Engine Tuning Advisor

3. For that, Check "File" radio option under "Workload" section.
4. Now Click on "Browse" icon and choose the ".trc" SQL Server Profiler trace file. Then select the required Databases.
   

   

   choose SQL Server Profiler trace file

5. Now Click on the "Start Analysis".

   

   Start Analysis

6. Now the query will start analyzing, In the "Recommendations" tab, it will show the indexes which need to create & also show the "Estimated Improvements" in percentage at the top.

   

   Recommendations

7. Now click on the "Definition" column to view the create Index query.
8. View the query and execute it.