How we can Improve SQL Server Performance using SQL profiler and Tuning advisor

SQL Server Profiler

SQL Server Profiler shows how SQL Server resolves queries internally. This allows administrators to see exactly what Transact-SQL statements or Multi-Dimensional Expressions are submitted to the server and how the server accesses the database or cube to return result sets.
Using SQL Server Profiler, you can do the following:

·         Create a trace that is based on a reusable template

·         Watch the trace results as the trace runs

·         Store the trace results in a table

·         Start, stop, pause, and modify the trace results as necessary

·         Replay the trace results

Steps for creating Trace and Tuning

1.      On the Tools menu in MSS Management Studio, Click  SQL server profiler

2.      Connect to SQL Server. Trace properties dialog box appears.

3.      Give the name for trace

4.      Select “ Tuning “  from the Use the template 

 

1.      Click on Event Selection pan

2.      Click on Column filters , Edit filter  dialog box appears.

3.      Select Database name. Expand Like and add Database Name .

4.      Run the trace.

 

1.      Save the trace into text file as ‘.trc’ or table.

 

1.       On the Tools menu in SQL Server Profiler, Click Database Engine Tuning Advisor .

2.      Connect to SQL server instance. Database Engine Tuning Advisor dialog box appears.

3.      Give the Session name.

4.      Work load from file or Table.

5.      Select the databases and tables to tune

 

1.      Select  on Tuning options.

2.       Click on advance options, Advance tuning Options dialog box appears.

3.      Check the box, Define max space for recommendations (MB), and type the wanted space.

4.      Click Ok

 

1.      Start  Analysis

2.      Click Progress that shows success.

3.      Click Recommendation that shows recommended indexes and Estimated improvements.

 

After creating the recommended index on the table it increase the performance. This also helps us to find what type of indexes needed for tables.