Building Code that Promotes Plan Re-usage

This article is the third article in my T-SQL Best Practices
series. First two articles can be found here: Part
and Part
. In this article, I’ll 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 DMV’s you
can identify a number of things about cached plans, here is a short list of
things you can identify:

  • Text associated with a cached plan
  • Number of times a cached plan has been executed
  • The size of the cached plan

Later on in the article, I’ll show you how to use DMV’s to
explore cached plan information.

Multiple Plans Due to Comments or Extra White Space

I’m 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
SELECT * FROM AdventureWorks.Production.Product — return records

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 I’m
going to run the following code:

SELECT * FROM AdventureWorks.Production.Product
SELECT * FROM AdventureWorks.Production.Product — return records
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

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 don’t 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! That’s
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

SELECT * FROM AdventureWorks.Production.Product
SELECT * FROM AdventureWorks.Production.Product

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 can’t 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 doesn’t 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

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 don’t 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 don’t unnecessarily cause multiple plans to be cached for the
same T-SQL statement.


See All Articles by Columnist
Gregory A. Larsen

Gregory Larsen
Gregory Larsen
Gregory A. Larsen is a DBA at Washington State Department of Health (DOH). Greg is responsible for maintaining SQL Server and other database management software. Greg works with customers and developers to design and implement database changes, and solve database/application related problems. Greg builds homegrown solutions to simplify and streamline common database management tasks, such as capacity management.

Latest Articles