Introduction

When you submit a SQL query to the SQL Server, SQL Server first parses the query to check whether it's syntactically correct or not. Once the query is parsed, a parse tree is generated. The parse tree becomes the input of the next process, which is known as algebraization. The algebrizer resolves all the names, data types, and aliases of columns of various objects and creates a query tree, which the query optimizer can understand. Query optimizer optimizes queries, depending on various factors, such as available indexes and statistics, and produces the execution plan. A query can be executed in a number of different ways to retrieve the same result set. However, it's the job of a query optimizer to select the best possible execution plan for a query so that it can be executed faster and will require fewer hardware resources. In most cases, the query optimizer selects the optimum execution plan, provided that statistics are up to date. However, sometimes it's possible that the...