One of the most important steps in working with Microsoft SQL Server data management is to understand what execution plans are, why execution plans are so important and the phases that SQL Server passes through when it executes a query. Execution plans enable you to answer precise that how a query is to be performed. Execution plans are also referred to as query plans.
To prepare and generate the execution plan, SQL Server engine requires to perform the following tasks each time for generating new execution plan:-
- Transact-SQL Parsing: When you are executing query the first phase is to check the statements in the batch follow the rules of the language and there is no syntax error
- Object Name Resolution: In the second phase, SQL Server resolves the names of objects to their underling object IDs. SQL Server needs to know exactly which object is being referred to.
- Query Optimization: In the third phase, SQL Server needs to decide how to execute the overall batch. Based on the available statistics, SQL Server will decide how to access the data in each table that is part of each query. This might create new statistics or update existing statistics before executing the query. SQL Server works based on the cost of plans to find most efficient plan. The cost is based on CPU, memory, and I/O operations. The aim is to find a satisfactory plan in a reasonable period of time.
- Query Plan Execution: After a plan is found, the execution engine and storage engine work to execute the plan. It may or may not succeed because of run-time errors.
- Plan Caching: If the plan is considered sufficiently useful, it can be stored in the plan cache. On later executions, SQL Server will attempt to reuse the execution plans from plan cache.
An execution plan is a map that details how SQL Server would execute a query. SQL Server uses cost-based optimizer.
Execution plans show the method that SQL Server is using to satisfy the requirements of the query. SQL Server is deciding over the types of operations, the order in which operations will be performed and how to access the data in a table and using available indexes. These decisions are based on the statistics that are available at the time.
There are three formats for execution plans as following:-
- Text-based plans are now deprecated (The SET SHOWPLAN_TEXT and SET SHOWPLAN_ALL display the execution plan for the query in the text format. SET SHOWPLAN_ALL displays more information than SET SHOWPLAN_TEXT).
- XML-based plans can be saved as .sqlplan file type and are portable between systems
- Graphical plans that render XML-based plans in an easier to read format. Graphical plans just contain a subset of XML plans information.
Actual vs. Estimated Execution Plans
It is possible to ask SQL Server to return details of the execution plan that was used with results that were returned from a query. These plans are known as “Actual Execution Plan”. It is also possible to ask SQL Server to calculate the cost of an execution plan for a query (or batch). This is calculated without actually executing the query. These plans are known as “Estimated Execution Plan”. Estimated execution plans are very useful for designing or debugging queries that are suffering from performance problems.
In SQL Server Management Studio, on the Query menu, there are Include Actual Execution Plan and Display Estimated Execution Plan options. After the query results are returned, another output tabs are created that shows actual and estimated execution plans.
Execution plans include row counts in each data path. For estimated execution plans, these are based on estimates from the available statistics. For actual execution plans, both the estimated and actual row counts are shown.
Use SET STATISTICS IO and SET STATISTICS TIME to view performance for executed queries. These statements display the output on the Messages tab in the results pane in SSMS.
SET STATISTICS IO display the following information for queries that you execute:
- Scan count is the number of seeks or scans that need to be performed to retrieve all of the required data.
- Physical reads value is the number of pages that have been read from the cache.
- Read-ahead reads value is the number of pages that have been read from the disk into the cache.
- Large object (LOB) logical reads, LOB physical reads, and LOB read-ahead reads values represent the number of logical reads, physical reads, and read-ahead reads
The following example includes SET STATISTICS IO ON in code:
SET STATISTICS IO ON;
MONTH(sh.OrderDate) AS OrderMonth,
SUM(sh.TotalDue) AS Revenue
FROM Sales.SalesOrderDetail AS s
JOIN Production.Product AS p
ON s.ProductID = p.ProductID
JOIN Sales.SalesOrderHeader AS sh
ON sh.SalesOrderID = s.SalesOrderID
WHERE YEAR(sh.OrderDate) = '2008'
GROUP BY MONTH(sh.OrderDate), p.Name
ORDER BY MONTH(sh.OrderDate), p.Name
SET STATISTICS IO OFF;
SET STATISTICS TIME displays the time to parse, compile, and execute a query. The output displays two times: the CPU time that is a measure of the time that the CPU spent performing the task. Elapsed time is the total time that it took to perform the task.
SET STATISTICS PROFILE includes similar output to SET SHOWPLAN_ALL, except that it displays the output after the Transact-SQL statement has executed. It includes the number of rows that were processed in the plan.
SET STATISTICS XML works similar to SET SHOWPLAN_XML, except that it displays the output after the Transact-SQL statement has executed.
Common Execution Plan Elements
Each execution plan most likely will have some of the following operators within the plan to perform data operation. Each common operator has been explained as below:-
- Table Scan: Table scans are a problem in many queries because they apply to heaps.
- Clustered Index Scan: The clustered index scans apply to tables that have clustered indexes.
- Clustered Index Seek: The clustered index seeks follow a clustered index to a specific location within the table.
- Nested Loop: Nested loops are commonly used to implement join operations. For each row in the upper input, perform a lookup to the lower input.
- RID Lookup: Row identifier(RID) lookups perform a lookup on heaps.
- Key Lookup: Key lookups apply to tables that have clustered indexes.
- Merge Join: You can use the merge join operator to implement a variety of join types such as left outer joins, left semi joins, left anti semi joins, right outer joins, right semi joins, right anti semi joins, and unions. Merge joins are commonly used for joins when the inputs are in the same sorted order.
- Hash Match: SQL Server use Hash matches for more difficult join that there is no input in the sorted order. Their presence is often due to a lack of appropriate indexing on the underlying tables. In data warehouses, Hash Match joins are often the most common form of join due to minimal indexing.
- Stream Aggregate: Stream aggregate operations are very efficient. When the data is already in the correct order for processing the aggregate, the stream aggregate operation will be used.
- Hash Match Aggregate: When the data is not in the necessary order, Hash match aggregate operations process the input by using a hashing algorithm.
- Filter: Filter operations implement WHERE or HAVING clauses. These operations are typically low cost and are processed as the data passes through the element.
- Sort: Sort operations are often used to implement ORDER BY clauses, but they have other uses. you could use a Sort operator to sort rows before they are passed to other operations such as Merge Join operations or for performing DISTINCT or UNION operations. Sorting data rows can be an expensive operation. You should avoid unnecessary ORDER BY operations.
Methods for Capturing Plans
You can use SQL Server Management Studio to obtain both estimated and actual execution plans. The same options have been added to Microsoft Visual Studio.SQL Server Profiler is very commonly used to capture plans. SQL Server Profiler has a Performance events > Showplan XML event that you can use to add a column to a trace. The trace will then include the actual execution plans. You should take care when you use this option because you could quickly generate huge trace output if you do not use appropriate filtering. The performance of the system could be degraded.The Extended Events profiling sessions that are integrated into SQL Server Management Studio in SQL Server 2014. The Extended Events profiling capability is more extensive than SQL Server Profiler that will replace profiler in the future.Dynamic management views (DMVs) provide information about recent expensive queries and missing indexes that SQL Server detected when it created the plan. Activity Monitor in SQL Server can display the results of these DMVs.The Data Collector in SQL Server collects information from the DMVs, uploads it to a central database, and provides a set of reports based on the data. Unlike Activity Monitor, which shows recent expensive queries, Data Collector can show historical information. This can be very useful when a user asks about a problem that occurred in the past.
Reusing query plans avoids the overhead of compiling and optimizing the queries. Although this is often desirable, some queries perform poorly when they are executed with a plan that was generated for a different set of parameters that can be counterproductive to performance.SQL Server assigns a cost to each cached plan, to estimate its value. The value is a measure of how expensive the execution plan was to generate. When memory resources become tight, SQL Server will decide which plans are the most useful to keep. The decision to evict a plan from memory is based on this reduced cost value.SQL Server may eventually decide to evict cached plans from the cache and recompile the queries. The two main reasons for this are: Correctness (changes to SET options, schema changes, and so on) and Optimality (data has been sufficiently modified to require a new plan).
DMVs Related to Execution Plans
Dynamic management views are useful to investigate execution plans. Most DMV values are reset whenever the server is restarted.
|sys.dm_exec_connections||User connections to the server|
|sys.dm_exec_sessions||Sessions, including system and user sessions|
|sys.dm_exec_query_stats||Query statistics about plans that are currently in the plan cache|
|sys.dm_exec_requests||Associated with a session and providing currently executing requests|
|sys.dm_exec_sql_text()||Provides the Transact-SQL code that is being executed for a request|
|sys.dm_exec_query_plan()||Provides the execution plan that is associated with a request|
|sys.dm_exec_cached_plans||Details of cached query plans|
|sys.dm_exec_cached_plan_dependent_objects()||Details of dependent objects for those plans|
Learning how to use Execution Plans to tune the databases are one of the most important part of SQL Server administration. Hope this post was informative, please share it with others if you think it worth to read. Stay tuned to learn more about SQL Server.