An execution plan is the result of the query optimizer’s attempt to calculate the most efficient way to implement the request represented by the T-SQL query you submitted. Execution plans can tell you how a query will be executed, or how a query was executed. They are, therefore, the DBA’s primary means of troubleshooting a poorly performing query. As DBA or Developer you can use the execution plan to identify the exact piece of SQL code that is causing the problem. For example, it may be scanning an entire table-worth of data when, with the proper index, it could simply backpack out only the rows you need. All this and more is displayed in the execution plan. .When you submit a query to a SQL Server database, a number of processes on the server go to work on that query. These processes are run for each and every query submitted to the system. While there are lots of different actions occurring simultaneously within SQL Server, we’re going to focus on the processes around T-SQL. They break down roughly into two stages:
1.Processes that occurs in the relational engine and
2.Processes that occur in the storage engine. In the relational engine the query is parsed and then processed by the Query Optimizer, which generates an execution plan. The plan is sent (in a binary format) to the storage engine, which it then uses to retrieve or update (execute) the underlying data. The storage engine is where processes such as locking, index maintenance and transactions occur.
-Query Parsing: When you pass a T-SQL query to the SQL Server system, the first place it goes to is the rel-ation-al engine. As the T-SQL arrives, it passes through a process that checks that the T-SQL is written correctly, that it’s well formed. This process is known as query parsing. The output of the Parser process is a parse tree, or query tree (or even sequence tree). The parse tree represents the logical steps necessary to execute the query that has been requested.
-Compilation: The query processor looks for statements that the optimization process won’t include and runs the statements through a T-SQL language compiler. These statements (e.g., variable declarations and assignments, conditional processing with IF, and iteration with WHILE) aren’t part of the basic data-manipulation language (SELECT, INSERT, UPDATE, and DELETE); they’re the extra features that add functionality to your SQL code. These statements don’t need optimizing, but they need compiling before execution.
-Query Optimizer: The query optimizer is essentially a piece of software that “models” the way in which the database relational engine works. Using the query processor tree and the statistics it has about the data, and applying the model, it works out what it thinks will be the optimal way to execute the query – that is, it generates an execution plan. During every step calculations, a number is assigned to each of the steps within the plan, representing the optimizer’s estimation of the amount of time it thinks that step will take. This shows what is called the estimated cost for that step. The accumulation of costs for each step is the cost for the execution plan itself. Once the optimizer arrives at an execution plan, the actual plan is created and stored in a memory space known as the plan cache.
-Query Execution: Once the execution plan is generated, the action switches to the storage engine, where the query is actually executed, according to the plan.
.Estimated and Actual Execution Plans: There are two distinct types of execution plan. First, there is the plan that represents the output from the optimizer. This is known as an estimated execution plan. The operators, or steps, within the plan will be labeled as logical, because they’re representative of the optimizer’s view of the plan. Next is the plan that represents the output from the actual query execution. This type of plan is known as the Actual execution plan. It shows what actually happened when the query executed.