Building Code that Promotes Plan Re-usage
August 14, 2009
This article is the third article in my T-SQL Best Practices series. First two articles can be found here: Part 1 and Part 2. In this article, Ill be talking about how to write your code to promote cached plan re-usage. Understanding how white space and comments impact whether a plan is cached or an existing plan is re-used can help you minimize the number of plans your application is caching.
Exploring Cached Plans
Are you taking advantage of plan caching? How well are you utilizing cached plans? Does your application use them once, and are they utilized multiple times? Do you have multiple cached plans for the same query in the procedure cache at the same time? How much space are those cached plans using? These are some of the questions you need to answer to make sure you are optimizing the procedure cache and minimizing the number of cached plans your application is creating. There are small things in how you write your T-SQL code that causes SQL Server to perform extra work to compile and cache execution plans for the same T-SQL code.
Before SQL Server can process a T-SQL batch, it needs to create an execution plan. In order for SQL Server to create an execution plan, it must first burn some valuable resource like CPU to compile a T-SQL batch. Once a plan is compiled, it is cached so it can be re-used should your application call the same T-SQL statement more than once. You can improve your code performance if you write your T-SQL code to promote the re-use of cached plans for frequently executed T-SQL statements.
With the introduction of SQL Server 2005, Microsoft provided some DMV that you can use to explore cached plans. By using these DMVs you can identify a number of things about cached plans, here is a short list of things you can identify:
Later on in the article, Ill show you how to use DMVs to explore cached plan information.
Multiple Plans Due to Comments or Extra White Space
Im sure all of you promote the idea of placing your code in store procedures. We do this for code re-usability inside an application and across multiple applications. However, not all code executed by SQL Server are contained within Stored Procedures. Some application might be written with in-line T-SQL code. If you are writing in-line T-SQL code then you need to be aware of how commenting your code and placing white space might cause SQL Server to create multiple cache plans for the same T-SQL statements.
Here is a sample of a T-SQL script that contains two different T-SQL Statements:
SELECT * FROM AdventureWorks.Production.Product GO SELECT * FROM AdventureWorks.Production.Product -- return records GO
As you can see, I have two similar T-SQL statements. Both are going to return all the rows from the AdventureWorks.Production.Product table. So how many cached plans do you think SQL Server will create if you run this code? To answer this question let me look at the cached plan information using a couple of DMVs provided within SQL Server 2005 and SQL Server 2008. To view the plans produced by these two T-SQL statements Im going to run the following code:
DBCC FREEPROCCACHE GO SELECT * FROM AdventureWorks.Production.Product GO SELECT * FROM AdventureWorks.Production.Product -- return records GO SELECT stats.execution_count AS exec_count, p.size_in_bytes as [size], [sql].[text] as [plan_text] FROM sys.dm_exec_cached_plans p outer apply sys.dm_exec_sql_text (p.plan_handle) sql join sys.dm_exec_query_stats stats ON stats.plan_handle = p.plan_handle GO
In this code, I first free the procedure cache by running the DBCC FREEPROCCACHE command. This command removes all the compiled execution plans from memory. I must mention a word of caution here about this command. You dont want to run the DBCC FREEPROCCACHE command in a production. Doing this in your production environment will remove all your production cached plans and doing this might significantly impact your production environment as frequently used plans are recompiled. After freeing the procedure cache, I execute my two different SELECT statements. Finally, I join together information from a couple of different DMVs to return some cached plan information for the two SELECT statements. When I run this, I get the following output from the SELECT statement that references the different DMVs:
exec_count size plan_text -------------------- ----------- -------------------------------------------------------------------------- 1 40960 SELECT * FROM AdventureWorks.Production.Product -- return records 1 40960 SELECT * FROM AdventureWorks.Production.Product
As you can see from this output my two SELECT statements above create two different cached plans and each one has been executed 1 time (exec_count number). The reason this happened is that the SELECT statements were not exactly the same. One SELECT statement is slightly different because it contains a comment. Also, note the size of the cached plan, 40,960 bytes! Thats a whole lot of memory to take up for such a trivial T-SQL statement.
So you need to be careful how you comment your code. Cut and paste is a great way to copy statements from one part of your application to another, but be careful not to cause multiple plans by placing different comments before and after or within your T-SQL statements that are similar.
Another way to also get multiple cache plans for the same T-SQL commands is to include a few extra characters of white space within your T-SQL statement. Here are two commands that are similar except for the white space:
SELECT * FROM AdventureWorks.Production.Product GO SELECT * FROM AdventureWorks.Production.Product GO
As you can see the second statement contains a couple of extra spaces between the FROM clause and the object name. This extra space will cause SQL Server optimizer to think that these two statements are different, and therefore create different cached plans for these two statements. It is fairly obvious here that there is extra white space in the second T-SQL statement. But if you also add a few extra characters before the SELECT clause or after the end of the statement, the statement might look the same because you cant see the white space, but yet SQL Server can see it, so it creates multiple cached plans due to that extra white space.
When SQL Server is looking at a batch, it compares it with plans already in the procedure cache. If the statement being compiled is exactly the same as an existing cached plan, SQL Server doesnt need to compile and cache the plan in memory. SQL Server does this so it can re-use plans for similar statements, if the plan already exists in the cache. To optimize your code you want to make sure you are reusing cached plans whenever possible.
When you are building application code and placing T-SQL code within your application and not using store procedures you need to take some care to ensure you get the best plan re-usage possible. We all use cut and paste while we write code when we want to use the same code throughout different code blocks, within our application. As you see from the above examples, you need to be careful when you do this. If you add a few extra spaces, or possibly a different comment in one code block you might get different cached plans.
Maximizing Performance and Minimizing Memory
To optimize your code you not only need to worry about each command you write and the design of your databases, but you also need to worry about whether or not you've got extra comments, and white space in similar T-SQL statements. If you dont pay attention to the details around T-SQL statements that are similar you might be causing SQL Server to create multiple cached plans. Having multiple plans for the same T-SQL code causes SQL Server to work harder and waste memory storing those cached plans. It may seem not seem extremely important to have a few extra cached plans in memory for your code, but as writer of T-SQL code we need to make sure we do the best we can at optimizing performance and minimizing resource usage. One way to do that is to make sure you dont unnecessarily cause multiple plans to be cached for the same T-SQL statement.