Use of Database Engine Tuning advisor in sql server

Database Engine Tuning Advisor is a utility that comes with SQL Server and can be used by both novice and experienced database administrators to get recommendations to improve the performance of SQL Server queries by making required physical structural changes. Based on our workload, Database Engine Tuning Advisor provides recommendations for best mix of indexes (clustered and non-clustered indexes) or indexed views, aligned or non-aligned partitions and required statistics.

We can use direct queries, trace files, and trace tables generated from SQL Server Profiler as workload input when tuning databases.

We can open this by two ways.

Select the query and right click on that and click on the Database engine tuning adviser.

Click on that

Window will be open

Here by default sleeted database is Test because query belongs from that database.

Go to the Tuning option

Select option for tuning.

Now click on the start analysis button

Below window will be open

Once it completed it will suggested us to how we tune this query.

As per this tool we can improve 94% performance using this suggestion.

We can also able to see the script.

Click on definition

See below

In report section we will see the report

Another way to open the Database Engine Tune Advisor

In SSMS goes to tool and select