In the previous articles of this series (see the index at bottom), we discussed the characteristics of the SQL Execution Plan from multiple aspects, that include the way the SQL Execution Plan is generated by the SQL Server Query Optimizer internally, what are the different types of plans, how to identify and analyze the different components and operators of the Execution Plans, how to work with the plans using different tools and finally, tuning the performance of simple and complex T-SQL queries using the Execution Plans. In this, the last article of this series, but not the least, we will discuss where the Execution plan is stored and how to save it for future use.
Plan Cache Overview
The process of generating the SQL Execution Plan is not an easy or cheap operation, in terms of resources and time consumption.
- For more information about the Execution Plans generation process, check the SQL Server Execution Plans Overview.
For each submitted T-SQL query, the SQL Server Engine stores the generated plans in a special section of the memory called the Plan cache, to use it whenever the same query is executed again, eliminating the overhead of creating a new SQL Execution Plan for the same query.
The story starts when a T-SQL query is submitted, where the SQL Server Engine will search for any Actual Execution Plan, for this query, that matches the estimated plan for the query, in the Plan cache memory. If a matching plan is found, the SQL Server Engine will use it to execute the query, otherwise, a new plan will be generated, stored in the Plan cache and used to execute the query. In some cases, the SQL Server Query Optimizer finds that using a parallel plan for that query is faster than using the cached serial plan. In this case, the optimizer will generate a new parallel plan will be generated and stored in the Plan cache for the same query.
The SQL Execution Plan is also sensitive to different type of changes, that leads the query to be recompiled again. These changes include table schema changes, index changes, statistics update, large number of data modification or insertion in the table, or explicitly recompile the query using query hints or calling the sp_recompile function.
If most of your system workload consists of queries that are executed once and never executed again, also known as Ad Hoc queries, it is better not to keep the plans for such queries and waste the Plan Cache memory resources. To improve the efficiency of the Plan Cache usage and reduce the memory pressure in such workload type, you can enable the Optimize for Ad hoc Workloads option, to store the SQL Execution Plan of the query in the Plan Cache at the second execution of the query.
- For more information about that new feature, check Saving the Plan Cache storage using the Optimize for Ad hoc Workloads option.
SQL Server provides us with number of dynamic management views, that allows us to check the content of the Plan cache memory, such as the sys.dm_exec_query_plan, that returns the Showplan in XML format for the batch specified by the plan handle, and the sys.dm_exec_cached_plans, that returns a row for each query plan that is cached by SQL Server for faster query execution. Assume that you need to check the plan for a query that is taking a long time to complete. Checking the plan for that query from the Plan Cache will allow you analyzing and tuning the plan for that query without the need to run the query. For example, the below query can be used to list all plans stored in the Plan cache, with the number of times this plan is used:
|
1 2 3 4 5 6 7 8 |
SELECT EX_SQLTXT.dbid, EX_SQLTXT.objectid, EX_SQLTXT.text, EX_QP.query_plan, EX_CP.usecounts FROM sys.dm_exec_cached_plans EX_CP CROSS APPLY sys.dm_exec_query_plan(EX_CP.plan_handle) EX_QP CROSS APPLY sys.dm_exec_sql_text(EX_CP.plan_handle) EX_SQLTXT |
And the result in our case will be like below:
The SQL Execution Plan will not be kept in the Plan cache forever, where the SQL Server Engine will remove the plan from the Plan Cache if the system requires more memory or the age of the plan, that depends on the cost of the plan and the number of times this plan is called, reached the zero value. The system process that is responsible for cleaning these aged plans is called the Lazy Writer process.
For testing purposes, you can explicitly clear the Plan cache using the DBCC FREEPROCCACHE T-SQL command. Take into consideration that it is not recommended to perform that DBCC command on the production environment, as this will cause an extra overhead generating all the plans for any submitted query from scratch. You can see from the snapshot below that, the DBCC FREEPROCCACHE command will clear the Plan Cache memory, similar to the result of restarting the SQL Server Service on that cache:
Saving Query Execution Plan
SQL Server provides us with the ability to export the generated SQL Execution Plan as a sqlplan file, to send it as an evidence for the related SQL development team or use it in the future for performance comparison purposes.
To save the generated SQL Execution Plan for your query from the SQL Server Management Studio, right-click on the white space on the graphical Execution Plan and choose the Save Execution Plan As… option, as shown below:
In the displayed Save As window, specify the path where you will save the sqlplan file and a meaningful name for that file, as below:
The graphical Execution Plan can be also saved using the SQL Server Management Studio, by choosing the Save ExecutionPlan1.sqlplan As… option from the File menu, as below:
You can also save the generated SQL Execution Plan in XML format. First, you should show the Execution Plan in XML format, by right-clicking in the white space of the graphical plan and choose the Show Execution Plan XML… Option, that open a new window for the SQL Execution Plan in XML format. Once the XML plan is displayed, choose the Save ExecutionPlan1.xml As… option from the File menu, to export the XML plan, as shown below:
In the displayed Save As window, specify the path where you will save the XML file and a meaningful name for that file, as below:
Now, you can attach the exported sqlplan or XML file from the production environment to your colleague in the SQL development team, who does not have permission on the production server to generate the Execution Plan, to review it and tune the query performance. Once received, the developer can open that plan using the Open -> File option from the File main menu. And from the displayed Open File window, locate the execution plan file and click on the Open button, as shown below:
Or simply, double-click on the exported Execution Plan file from the Windows Explorer, and the plan will be opened in SQL Server Management Studio.
Compare Execution Plans
Another benefit that can be gained from saving the Execution Plans is checking the enhancement in the queries performance, by comparing the new execution plan with a previously saved plan before performing the change. For example, assume that you need to check the enhancements that will be gained from creating an index on your table. To do that, the query should be executed before creating the index, including the Actual Execution Plan, then you can save the sqlplan file in your machine for comparing purposes, as below:
After that, create the index using the CREATE INDEX T-SQL statement below:
|
1 |
CREATE NONCLUSTERED INDEX IX_EMP_Salaries_EMP_ID ON EMP_Salaries (EMP_ID) INCLUDE (EMP_HireDate,EMP_Salary ) |
Once the index is created successfully, execute the same SELECT query again, including the Actual Execution Plan. On the newly generated SQL Execution Plan, right-click on the white space on that plan and choose the Compare Showplan option, as shown below:
From the opened window, specify the location of the previously saved plan, then a Showplan Comparison windows will be displayed, with a full comparison between the saved and new plans, from graphical and execution statistics aspects, as shown clearly below:
At this point, we have reached the end of the SQL Execution Plan articles series. I hope the way these articles were presented made this important and complex subject simpler for you. Feel free to share any feedback or questions in the comments below.
Table of contents
He is a Microsoft Certified Solution Expert in Data Management and Analytics, Microsoft Certified Solution Associate in SQL Database Administration and Development, Azure Developer Associate and Microsoft Certified Trainer.
Also, he is contributing with his SQL tips in many blogs.
View all posts by Ahmad Yaseen
- Azure Data Factory Interview Questions and Answers - February 11, 2021
- How to monitor Azure Data Factory - January 15, 2021
- Using Source Control in Azure Data Factory - January 12, 2021