Basics Concept of Execution Plan in SQL Server
Why is the SQL query executing slowly? Is my index in use? Why is not my index getting used? Why is this query executing faster than the earlier one? Everyday these type questions are encountered by Invorx team of Microsoft SQL experts.
The accurate answer to these sort of question is different in each scenario, but to reach on the answer first it is important to be known on execution plan in SQL server. This concept will enable Microsoft server users to sort out majority of their queries on their own.
What is the Execution Plan for Microsoft SQL Server ?
An execution plan is the outcome of query optimizer’s undertaken to calculate most efficient solution for implementing the request constituted by T-SQL query submitted by end users. Execution plan in SQL server tells users that how a query will be executed, or how a query was previously executed?
Therefore, this plan is a primary means for database administrators to troubleshoot the problem associated with poor performance of query. Instead of looking here and there for the answer ‘why query is performing several scans, entering the I/O via the roof’, simply make use of Microsoft SQL server execution plan. This will help you in identifying exact section of the SQL code, which is resulting in problem. Execution Plan for SQL Server can be considered as a strategy to look deep into the query processing or execution.
What Happens on a Query Submission?
When a client submits query in the SQL server database, number of procedures on the server takes place. The aim of these processes execution is to manage the entire system in such a way that it will provide you the required information, or store it by holding the data integrity throughout. These procedures are executed for each and every server query submitted by users in the system. Already by default, different actions are taking place simultaneously in SQL server. Here in this post, we are going to concentrate on T-SQL. Let’s break the procedures roughly in two different stages for proper understanding :
a) Processes that take place in the relational engine
b) Processes that take place in the storage engine
Talking about the relational engine then, the query is parsed and then, treated by the Query Optimizer. This optimizer is responsible for generating an execution plan in SQL server. The plan is coded in a binary format to sent it to the storage engine for retrieving or updating underlying data. This storage engine is a place where procedures like index maintenance, locking, and transaction take place. We are majorly focusing on relational engine concept because the main execution plan is created at this stage.
Query Parsing
The first place of T-SQL query, when it passes to the Microsoft SQL server, is the relational engine. When it arrives at its destination location, it goes through a procedure that make sure that T-SQL query is written correctly i.e., it is in its proper layout. This process of checking T-SQL query is named as query parsing. The outcome of the Parser procedure is a query tree, sequence tree, or parse tree. The parse tree indicates the logical steps compulsory for executing the query, which has been requested. If the string of T-SQL is not a DML statement, it will not be optimized further. It happens because there’s only one proper format in the SQL server to create a table or do other operations via query in the server.
If proper format is not followed, it might impact the performance. If the string of T-SQL is a DML statement, the query tree goes through a procedure named as algebrizer. Algebrizer fixes the names of several tables, objects, and columns, referred to within a query string. At the individual column level, it helps in identifying all types of objects being used. The algebrizer addresses location of aggregates within a query, a procedure named as aggregate binding. This procedure is considered important because the query might have synonyms or aliases in it, which do not exist in the database but, need to be troubleshooted. At this point of execution plan in SQL server step by step, the algebrizer gives output in binary format named as query processor tree that is then shifted into the query optimizer.
Query Optimizer
The query optimizer is like an essential piece of product, which ‘models’ the method in which the relational engine of database works. It creates Microsoft SQL server execution plan by determining following aspects :
- Use of query processor tree and the statistics, it comprises regarding the data
- Apply the model to determine the things in an optimal manner for query execution
The optimizer helps in figuring out that ‘what is best way to implement the requested query by end users?’ It checks if information can be retrieved from indexes, what kinds of joins are needed and more. The overall decision made by the optimizer is dependent on calculation, in terms of demanded CPU processing and I/O, and how fast a query will be executed.
Well, the product generates and evaluates several plans, and selects the one that is lowest in finance. It tries its best to give best in low cost. The execution plan decided by optimizer executes the query as much faster as it can and make use of least amount of resources. If result in return is required in faster manner, optimizer will utilize a process, which is more CPU-intensive.
All Done With The Basics
The basic concept regarding execution plan in SQL server comprises of query parsing and optimizer. Both these concepts are involved behind creation of an execution plan at lowest cost with minimum requirement of resources. Administrators need to keep one thing in mind that whatever query they are typing in the server is in its correct format. This will make it easy for Microsoft SQL server to execute the query faster without any delay.
