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