Writing SQL queries can be considered by some as a form of art: everyone has a different style of writing and improving them (I’m talking about complex queries). Queries can achieve the same goal while being written in various and sometimes completely different way. While improving and optimizing a query should first and foremost include making sure that everything is written correctly and there are no logical errors, I wanted to share a tool, that some of you might not know, which can help make queries more efficient in times where you’re just not sure how to improve it further.

Include Actual Execution Plan

This feature in SSMS instructs the server to show the “cost” of every part of the query and subqueries (including loops).

Execution plan in SSMSGoto Query->Include Actual Execution Plan (or use Ctrl+M) to active it. After you run a query (F5) in the query window, you will be able to see graphically see how the breakdown of the query.

Optimize a query with Execution Plan

When using the Actual Execution Plan feature with two consecutive queries, in the same query, you are presented with “Query Cost” which is the parameter to how much this section was more “heavy” on the server – resources wise.

compare two or more queries with SSMS

The idea behind this is to make changes to how the query is written (while not affecting the logic) and have the immediate ability to see which one is better, comparing the “Query Cost” parameter.