Whenever a query is run for the first time in SQL Server, it is compiled and a query plan is generated for the query. Every query requires a query plan before it is actually executed. This query plan is stored in SQL Server query plan cache. This way when that query is run again, SQL Server doesn’t need to create another query plan; rather it uses the cached query plan which improved database performance.
The duration that a query plan stays in the plan cache depends upon how often a query is executed. Query plans that are used more often, stay in the query plan cache for longer durations, and vice-versa.
In this article we will look at:
- How to view the query plan cache
- How to clear the plan cache
- How to use parameterized queries to reuse query plans.
How to view the SQL Server query plan cache
SQL Server provides the following dynamic management views and functions that can be used to find out what is in the plan cache at any given time.
- sys.dm_exec_cached_plans
- sys.dm_exec_sql_text
- sys.dm_exec_query_plan
The first dm_exec_cached_plans is a dynamic management view while the remaining two are dynamic management functions.
Let us use these functions and views to see what is in the SQL Server cached query plan. Execute the following query on your SSMS (SQL Server Management Studio):
|
1 2 3 4 5 |
SELECT cplan.usecounts, cplan.objtype, qtext.text, qplan.query_plan FROM sys.dm_exec_cached_plans AS cplan CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS qtext CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qplan ORDER BY cplan.usecounts DESC |
A SELECT statement is used to select usecounts, object type, query text and an XML representation of the query plan of all the queries that currently reside in the query plan cache. Notice that the CROSS APPLY operator has been used to join the output from dynamic management views and functions. Finally, the result set is sorted in the descending order of the use counts. The output on my machine looks like this (It may differ on your machine depending upon the queries that reside in your query plan):
Here the usecount column contains a count for the number of times a query has been executed. The objtype column contains information about the object through which a query is executed. It is important to mention that up till SQL Server 6.5 only stored procedure queries were stored in the cached plan. From SQL Server 7.0 onwards, dynamic and ad-hoc queries are also stored in the cached plan. The text column contains the text of the query and finally, the query_plan column contains the XML representation of the query. Click any row in the query_plan column to see the detailed XML representation of the plan.
Clearing the plan cache
To clear the plan cache, execute the following:
|
1 |
DBCC FREEPROCCACHE |
Stored procedure query plan
Now let’s execute a simple stored procedure and see what we get in our SQL Server query plan cache.
First let’s create a dummy database and a table inside that database:
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE Database company; USE company; CREATE TABLE department ( id INT PRIMARY KEY, dep_name VARCHAR(50) NOT NULL, ) |
Next insert some records into the table:
|
1 2 3 4 5 6 7 8 9 10 |
USE company; INSERT INTO department VALUES (1, 'Sales'), (2, 'HR'), (3, 'IT'), (4, 'Marketing'), (5, 'Finance') |
Finally, let’s create a simple stored procedure that retrieves all the records from the department table of the company database:
|
1 2 3 4 5 6 7 |
USE company GO CREATE PROCEDURE getdepartment AS SELECT * FROM department GO |
Before we execute the getdepartment stored procedure, clear the query plan cache using following query:
|
1 |
CREATE Database company; |
Now, execute the newly created getdepartment stored procedure:
|
1 |
exec getdepartment |
Once the stored procedure is executed, try to retrieve the information about all the query plans in the plan cache, again execute the following query:
|
1 2 3 4 5 |
SELECT cplan.usecounts, cplan.objtype, qtext.text, qplan.query_plan FROM sys.dm_exec_cached_plans AS cplan CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS qtext CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qplan ORDER BY cplan.usecounts DESC |
When the above query is executed, you will see that two query plans will be retrieved: One query plan for the stored procedure and the other for the query that retrieves the query plan. The output looks like this:
In the first row, you can see the object type Proc. This refers to the stored procedure query plan that we just executed. Inside the text column, you can also see the information about the stored procedure and the actual query that we executed inside the stored procedure.
Here the usecount column displays the number of times query is executed. Execute the stored procedure once more and then retrieve the query plan cache, and you will see following results:
You can see that count for both the stored procedure and the query plan access queries have jumped to 2.
The query plan depends upon the query text
SQL Server generates a query plan using a hash value that is calculated from the query text. When a query is run, SQL Server calculates its hash value and checks if a plan with the same hash value exists in the plan cache. If a plan with same hash value exists, that plan is executed. However, if a plan with the newly calculated hash value doesn’t exist, a new query plan is generated and stored in the cache plan.
The hash value for the query plan is generated from the text. Therefore if there is even a slight change in the query text (e.g. a change of case, comma or space) a new hash value will be generated and thus a new query plan. Let’s see an example of this.
Clear the query plan cache and execute the following query twice:
|
1 |
SELECT * FROM department where dep_name = 'Sales' |
When you retrieve the query plan cache, you can see that usecount for the Adhoc query that we just executed twice is 2:
Now let’s make a slight change to our query:
|
1 |
SELECT * FROM department Where dep_name = 'Sales' |
Here we have replaced small case w of the where clause from the previous query to capital case W. This is a minor change, but when the above query is executed a new hash value is calculated and hence a new query plan is generated for the query. If you look at the query cash plan now, you will see following output:
From the output, you can see that there are two query plans with same functionality but different text.
Similarly, if you change the value from the dep_name from Sales to HR, a new query plan will be generated.
Use parameterized queries for reusability
We know that when a query’s text changes, a new query plan is generated instead of reusing the existing one. Parameterized queries resolve this issue. In a parameterized query we pass dynamic values in the form of parameters to the query. The query text itself doesn’t change; therefore existing query plans can be reused instead of creating a new plan. Let’s take a look at a simple example.
Let’s create a parameterized stored procedure that retrieves the department record by name. Execute the following query:
|
1 2 3 4 5 6 7 |
USE company GO CREATE PROCEDURE getdepartmentbyname @name nvarchar(30) AS SELECT * FROM department WHERE dep_name = @name GO |
The above stored procedure filters department records using department name passed as parameters. Now clear the query plan cache and execute the following stored procedure:
|
1 |
exec getdepartmentbyname 'Sales' |
Execute the getdepartmentbyname stored procedure again but with different parameters this time:
|
1 |
exec getdepartmentbyname 'HR' |
Now if you retrieve the values from the query plan cache you will see that although you executed the stored procedure twice with different text, there is only one plan for the stored procedure getdepartmentbyname and its usecount is 2 which means that the plan has been executed twice:
This shows that using parameterized queries, you can reuse the existing query plans despite changing query text.
Other great articles from Ben
| How SQL Server selects a deadlock victim |
| Identifying Object Dependencies in SQL Server Management Studio |
| Understanding SQL Server query plan cache |
View all posts by Ben Richardson
- Working with the SQL MIN function in SQL Server - May 12, 2022
- SQL percentage calculation examples in SQL Server - January 19, 2022
- Working with Power BI report themes - February 25, 2021