SQL Server Performance Improvement - TRACE AND TUNING in SQL Server (Profiler Table)

SQL Server Performance Improvement -TRACE AND TUNING in SQL Server (Profiler Table)

SQL TRACING

First Trace the SQL Statements in SQL Profiler for Tuning

Go to Tools --> SQL Server Profiler

Select SQL Server and Name the Tracer Temp Table

Started application activities like fetching and selecting and deleting operations in front end interface which will record in trace table and this table will be used in Tuning.

SQL TUNING

Go to Tools --> Database Engine Tuning Advisor

Start new Session and Select Table option and Database. Choose the table from which is saved in SQL Profiler while tracing.

Then Start Analysis and Recommendation will display as follow

Click and copy Definition and Create it another SQL Query window.

It could be any non clusters index or STATISTICS on table