SQL Database Performance Tuning

Step 1

SSMS go to tools & click SQL profiler 

Step 2

Trace property, Add Trace Name="Database Query Tunning" & Use the template="Tuning"

Step 3

Send Request to databases from application, API or Execute DML query Or Procedure in Database for trace Query in profiler

Step 4

Now Stop trace & Save Trace file as "{FileName}.trc"

Step 5

SSMS - go to tools menu & select Database engine tuning advisor

Step 6

SQL server authentication & Login into Server for Database tune advisor

Step 7

Add Session Name="DbQueryTune" & Select Workload File. We already have saved from SQL profiler & go to Tuning Option, select Advanced options & define max. space for recommendation in (MB)

Step 8

Now select Database for workload analysis & select multiple Databases & Table for Tuning

Step 9

Click on Start analysis Button to start workload & Tune Db objects

Step 10

Tuning progress & generating report & checking the index, Statistics for tables

Step 11

Recommendation (in our case there is no recommendation from database tuning advisor because we already tune & Created required statistics for table & we also set AUTO_CREATE_STATISTICS ON & AUTO_UPDATE_STATISTICS ON at Database level)

Step 12

All types of report are generated. You can check all report Select Report from Dropdown & Also check tuning summary

Note: To perform all the above steps in Production Server you need to get permission from DBA