Tuesday, January 26, 2016

SQL - Query Execution Plan

SQL - Query Execution Plan
Query Execution plan is the most valuable concept in SQL because it gives a clear picture about the performance and slowness for any run able SQL script.
Query Execution Plan is nothing but you can be considered it as an ordered set of steps used to access data in a SQL relational database management system. The best thing is that the execution plan may also be viewed in a graphical representation in the Native SQL Window in the Advantage Data Architect utility. By the help of this Graphical Execution plans, you can easily understand that how a query will be executed, or how a query was executed. After going through the execution plans, you would be capable to identified statements that take a long time to complete and execution plan will help to determine tuning needs.
What is 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. When any query reaches SQL Server, the first place it goes to is the relational engine. This query compilation process will occur in three phases:
1. Parsing,
2. Binding and
3. Optimization.

Parsing is the staring stage in the processing of a SQL statement which stands to examining the characters input and recognizing it as a command or statement by looking through the characters for keywords and identifiers, ignoring comments, arranging quoted portions as string constants, and matching the overall structure to the language syntax making sense of it all. Apart from this, it determines whether the process issuing the statement has privileges to run it and allocates a private SQL area for the statement.

Binding is the middle stage in the processing of a SQL statement which stands in SQL Server makes sure that all the used data object names do exist, and associates every table and column name on the parse tree with their corresponding object in the system catalogs. The output of this second process is called an Algebrized Tree, which is then sent to the Query Optimizer.
Optimization is the next stage in the processing of a SQL statement where the SQL engine tries to evaluate what the best way to execute your query would be. It tries to take advantage of existing indexes, clusters and table relationships; find ways around full table scans, utilize caching effectively by avoiding repeated data reads, and so forth.

The best thing in the query execution plan that you are able to view the graphical query performances for each logical operations, such as a Sort, translate to the same physical operation, whereas other logical operations map to several possible physical operations. It will be capable to show you mapped nested loops join, Merge Join, or Hash Join physical operators which are defined in the query.

Type of Execution plans
As we know that Execution plans are not kept in memory forever and whenever a query is submitted to the server, an estimated execution plan is created by the optimizer and stored in cache which is cleared periodically by an internal process called lazywriter process.
Both Estimated and Actual execution plans can be viewed in graphical format which can be differentiate as -
Query Execution Plans
Actual Execution Plan
Estimated Execution Plan
1. Short cut Key:  (CTRL + M)
Short cut Key:  (CTRL + L)
2. It is created after execution of the query.
It is created without executing the query
3. It contains the steps that were performed.
It contains an approximate execution plan
4. It can be Text, Graphical or XML Plans formats
It can be Text, Graphical or XML Plans formats
  • Graphical Execution Plans are quick and easy to read but the detailed data for the plan is masked.
  • Text Execution Plans are a bit harder to read, but contain more information which are immediately available. They are available in three formats such as SHOWPLAN_ALL, SHOWPLAN_TEXT and STATISTICS PROFILE.
  • XML Execution Plans present the most complete set of data available on a plan because SQL Server returns detailed information about how the statements are going to be executed in the form of a well-defined XML document.
Graphical Execution Plans : To view the graphical execution plan, you need to do the following things in Management Studio such- 
  1. Click on the "Display Estimated Execution Plan" icon on the tool bar.
  2. Right-click the query window and select the same option from the menu.
  3. Click on the Query option in the menu bar and select the same choice.
  4. Simply hit CTRL-L on the keyboard.

Actual Execution Plans : To view the include actual execution plan, you need to do the following things in Management Studio such -
  1. Click on the "Include Actual Execution Plan" icon on the tool bar.
  2. Right-click the query window and choose the "Include Actual Execution Plan" menu item.
  3. Click on the Query option in the menu bar and select the same choice.
  4. Simply hit CTRL-M on the keyboard.

The graphical display is very useful for understanding the performance characteristics of a query. Query execution plan enables database developers, DBAs and architects to understand the processing of a query on SQL Server and helps to troubleshoot any performance issue that occurs during its execution. Query execution plans are the very supportive features in sql to identify the loose points in your queries and also provide the supportive information to improve the query performance. They are capable to explain the each point of the query. SQL Server has different components, which work internally and provide you the desired result set in the best possible time.

Ref: https://technet.microsoft.com/en-us/library/ms178071(v=sql.105).aspx 

No comments:

Post a Comment