QUERY OPTIMIZER X QUERY EXECUTOR

Both names are part of the SQL Server architecture in order to process a request, or a query I should say. Before we start having a clearer idea about those two items, I need to break a common belief that T-SQL shapes your execution plan, T-SQL is a language that defines your result set and not your execution plan.

Yes, you can guide or even force SQL Server engine into making better or worst decision due to your T-SQL, however, that is not the main purpose of the language. In fact, if we do not add hints or options, SQL Server will make its own choice in a bunch of areas including the order it should process your tables and joins. What that means is, not necessarily your FROM table is going to be executed first. It may, but it also may not.

So, the query optimizer is responsible for generating an execution plan, it may go through dozens of possibilities to interpret your query and design what it thinks is the best execution plan for it. This part of query processing is cost-based. Which means, it is assigns a cost for every plan that it comes up with and it chooses the cheapest one. You can see that number if you hover your mouse through an estimated execution plan in SSMS under Estimated Query Cost. To visualize the Estimated Execution Plan press Ctrl + L in SSMS.

Based on the plan chosen, the executor starts working. One of the most important concepts here is that those two items do not talk to each other, once the plan is designed and execution starts, SQL Server does not come back and restructure the plan based while the query runs, Oracle, however, does that. Yes, you can force that behavior, but that is part of a later post.

The executor is solely responsible for executing the plan designed by the optimizer, after the query execution is complete, you can analyze the Actual Execution plan if that option was previously selected in SSMS and we can use that information to start fining all kinds of performance issues like Parameter Sniffing, Bad Statistics, Missing Indexes and others.

#tsql #performance #sqlserver #databases