How to fix query optimizer for better performance
This article helps to understand when to turn on the query optimiser fixes for better performance starting from SQL Server 2016
Database engine tuning advisor is inbuilt tool which comes with Microsoft SQL server management studio which helps in analyzing required indexes, statistics, partitioning, strategy and physical design structure for performance improvement.
Step 1:
First, choose the database for which you want to tune. In this blog, I have used Adventureworks2014 as my database for tuning.
I have used the below query for tuning from the Adventureworks2014,
Here we can see from the execution plan the ‘Estimated Subtree Cost’ is 0.713995. The goal is to reduce this cost.
Step 2: Execute the selected query from the editor, then right click on the query and select the ‘Analyze Query in Database Engine Tuning Advisor’ option.
Step 3: In this step, click the query radio button and checkbox the database for which you want to tune.
Once we click the dropdown button next to the database. Here we have an option to choose the particular tables for tuning purpose.
Step 4: On top of the tool, we have the tuning tab option click on that we can set the tuning stop date & time, and we can use the PDS option for the database according to your requirements. Moreover partitioning strategy option can also be implemented accordingly.
On the previous page click on the advanced options and define the max space for recommendation in (MB) and max columns per index.
Step 5: In this step, choose start analysis option.
Now the tuning analysis has begun for the given query.
Step 6: Once the tuning has completed successfully, the tuning advisor will give recommendations on the estimated improvements. Here the estimated improvements was 96%.
The recommendation given by the advisor is to create non-clustered index script for the further improvements.
Step 7: We can apply the recommended improvements as script or can choose from the Actions tab-> ‘Apply recommendations’ option.
Once the recommendation is finished the below success message will be displayed.
Step 8: Finally when we run the query plan again, we can see that the cost has been reduce significantly from 0.713995 (as shown in step 1) to 0.0261428 and the clustered index scan has been changed to non-clustered index seek.
