SQL Server: Some Quirks in the Cache, Part I

Monitoring the Procedure Cache

Getting the best performance from SQL Server is a complex task involving virtually every aspect of the system. Memory management is one of the most important aspects, of course. The primary concern is memory for data itself, but that doesn’t mean we can ignore the procedure cache. Procedure cache is the memory storing execution plans for both stored procedures and queries, but we are going to focus our attention just on SELECT statements here.

Before we can begin to look at a few potentially startling aspects of the procedure cache, we must take a few moments to assemble a toolset of the commands we will use to monitor the cache. The oldest is the compatibility view sys.syscacheojects. While there are more powerful tools available among the dynamic management views, sys.syscacheobjects is easy to use, and in many, if not most, situations provides all the information we need.

In general, it is helpful to explore the cache with two windows in the Management Studio. One is connected to the master database; this is where we run the queries to do the monitoring. The second is connected to the database of interest, in this case Bigwind. If we ran all the queries from the same window, we would be looking at the caching of the queries we ran to look at the caching. Useless clutter.

Much of the time we only want to know whether a query plan has been cached or not. For this, the following query will suffice:

USE master

SELECT objtype, cacheobjtype, sql FROM sys.syscacheobjects

WHERE dbid=DB_ID(‘Bigwind’)

As is often the case, DBCC provides some helpful utilities. DBCC FREEPROCCACHE empties the procedure cache in toto. Alternatively, DBCC FLUSHRPOCINDB(db_id) will flush the procedure cache for one specific database only.

Clearly, if we run these two commands, we should see nothing:


SELECT objtype, cacheobjtype, sql FROM sys.syscacheobjects

WHERE dbid=DB_ID(‘Bigwind’)


DBCC provides yet another useful tool; DBCC PROCCACHE summarizes the size of cached query plans, both in terms of 8KB buffer pages and number of cached plans. The cache is empty, of course, just having been flushed. We see that 336 buffer pages are currently allocated to the cache although none are currently being used.

Now we are in a position to execute an actual query! (From the window connected to Bigwind, of course.)

SELECT * FROM Employees WHERE EmployeeID = 7

Querying sys.syscacheobjects, we see two entries.

The “Ad hoc” plan is the query sent from the client, and the prepared plan is the execution plan that will be reused if other similar queries come along. DBCC PROCCACHE shows us that currently there is one query in the cache:

Notice that this single small query takes up nine 8KB procedure buffer pages.

The plan has, of course, been autoparameterized by SQL Server. Note that the data type of the parameter @1 is tinyint, indicating that SQL Server bases the datatype choice on the datatype of the supplied value rather than on the datatype of the column. If we run the following query, a new ad-hoc entry shows up in the output of sys.syscacheobjects, but there is still only one prepared plan.

SELECT * FROM Employees WHERE EmployeeID = 11

DBCC PROCCACHE confirms that there is still only one execution plan.

One negative consequence of SQL Server’s method of choosing numeric datatypes for autoparameterization is that it can unnecessarily prevent plan reuse. For example, consider the following query:

SELECT * FROM Employees WHERE EmployeeID = 256

This query cannot use the cached plan, since 256 is too large for a tinyint datatype. When we run this query, we see a second autoparameterized plan for what is essentially the same query, this time with a smallint datatype.

DBCC PROCACHE now shows two entries:

Manually preparing queries with sp_executesql can eliminate this redundancy, albeit at the const of considerably greater complexity of the query batches themselves.


We have seen that it is fast and easy to monitor the caching of query execution plans using sys.syscacheobjects, DBCC FREEPROCCACHE, and DBCC PROCCACHE. Now that we have established our toolset, In the next section<link SQL Server: Some Quirks in the Cache Part II> will look at some potentially surprising behavior of the SQL Server query engine.

Type to search blog.learningtree.com

Do you mean "" ?

Sorry, no results were found for your query.

Please check your spelling and try your search again.