Tools to Aid your SQL Server Plan Cache

In a recent blog post, we looked at how SQL Server uses its index statistics to help determine an optimal query plan. We will soon look at several examples of the interaction between query plans and the plan cache, but before we do we must develop a set of tools to aid the effort.

Plan cache

SQL Server Query Plan

Of course, SQL Server will cache a query plan in memory when practical to avoid unnecessary repetitive effort optimizing the same, or essentially the same, query plan over and over again. It has always been possible to examine a listing of queries held in the cache using syscacheobjects. While newer techniques can provide much more information, the “classic” technique may still be all you need to answer a question about the procedure cache. When exploring the caching of plans, you will typically want to use two edit windows in the Management Studio. One connected to the database of interest and the other connected to the master database. Using two windows helps avoid cluttering the data with query plans of the queries that show you the query plans. Confusing and not at all helpful.

Execute against the master database:


SELECT cacheobjtype, objtype,sql FROM sys.syscacheobjects WHERE dbid=DB_ID(‘Bigwind’)

SELECT * FROM sys.syscacheobjects WHERE dbid=DB_ID(‘Bigwind’)

DBCC FREEPROCCACHE empties execution plans from the cache. In general, an investigation into a particular query will involve a cycle of emptying the cache, executing the query of interest, and then examining the contents of the cache. As you see here, a WHERE clause is helpful in eliminating plan listings for databases other than the one in use.

First, we’ll remove the index created on the Region column of the customers’ table of Learning Tree’s Bigwind sample database.

DROP INDEX Customers.IX_Region

Next, we’ll execute a simple query.

SELECT * FROM Customers WHERE Region = ‘Delaware’

Now we will query sys.syscacheobjects. Our results look like this:

Note that SQL Server has auto-parameterized the query. That is, SQL Server did not just execute our query to obtain the customers from Delaware, but it prepared a more generalized plan in which the parameter “@1” can be replaced with any other region name that might be queried in the future. Now let’s re-create the index on the Region column that we just dropped a moment ago.Co

CREATE INDEX IX_Region ON Customers(Region)

Once again we will use DBCC FREEPROCCACHE to empty the procedure cache, and once again we will run the query and then look at the cache.

This time there is no auto-parameterized plan!

Most important point: Contrary to the belief of many, it is not always better to cache an execution plan, and performance can suffer if you do!

What has happened here? With no index, SQL Server auto-parameterizes the plan because the optimal plan will always be the same, whether the state is California or Delaware. When we create an index, however, the situation changes. Now, the optimal plan uses an index if the state is Delaware and the optimal plan does not use an index if the state is California. It is therefore impossible to have a single cached plan that is optimal for all potential values of Region. SQL Server knows this and chooses not to cache the plan.

What to do?

There are two courses of action. We can accept that the query will not be cached. This may well be the best choice since the small amount of time it takes to optimize a simple query is more than offset by large performance gains on any possible value for Region. The second possible action is to manually parameterize the plan. To do this effectively, we must know the data in our own database. If most regions had small numbers of rows, we would want to optimize for small regions. In this case, we would simply “bite the bullet” and accept suboptimal performance for the occasional query on a large region. Conversely, if many regions are large, we optimize for large regions and are not too worried since the queries on the smaller regions are probably not performance problems, to begin with.

Obtaining More and Better Information about Cached Execution Plans

As mentioned at the outset, newer techniques for obtaining information about cached plans provide considerably more information. However, this greater power comes at the cost of requiring considerably more complex queries. As is often the case with the distributed management views and functions the information you require may be scattered among several different places. Therefore, joins will be necessary to assemble this information into a single set of results.


In the previous discussion on index statistics, we saw that it is not always better to use an index in a query plan. An inescapable consequence of this fact is that some queries do not benefit from being parameterized and saved in the query plan cache. The simple and easy-to-use compatibility view sys.syscacheobjects permits us to directly observe this. However, further exploration of the plan cache will require more sophisticated tools which we will develop in the next installment.

Interested in learning more about optimizing SQL Server queries? We have a course just for you.

Type to search

Do you mean "" ?

Sorry, no results were found for your query.

Please check your spelling and try your search again.