Don’t Bloat Proc Cache with Parameters

Most of us have long been aware that non-parameterized ad-hoc queries can pollute procedure cache with entries that will never be reused.  Each distinct query string is a separate cache entry and, unless the exact query with the same values is executed again, the plan will remain in cache unnecessarily until aged out.

However, I was surprised to learn recently that even parameterized queries can bloat procedure cache if one isn’t careful.  This was brought to by attention by SQL Server MVP Erland Sommarskog and confirmed by fellow MVP Adam Machanic, who experienced this problem at a customer site.

The issue is that parameter declarations are part of cache entry statement text.  Consider the following code:

string lastName = "Sommarskog";

 

SqlCommand selectCommand =

    new SqlCommand(

        "SELECT ContactID, FirstName, LastName " +

        "FROM AdventureWorks.Person.Contact " +

        "WHERE LastName = @LastName",

        connection);

 

selectCommand.Parameters.AddWithValue("@LastName", lastName);

 

SqlClient defined the parameter as nvarchar(10) because the supplied string value length was 10 and .NET strings are Unicode.  Below is the resulting statement text from sys.dm_exec_query_plan:

(@LastName nvarchar(10))SELECT ContactID, FirstName, LastName FROM AdventureWorks.Person.Contact WHERE LastName = @LastName

 

Now run the same code with a different last name value:

string lastName = "Machanic";

 

SqlCommand selectCommand =

    new SqlCommand(

        "SELECT ContactID, FirstName, LastName " +

        "FROM AdventureWorks.Person.Contact " +

        "WHERE LastName = @LastName",

        connection);

 

selectCommand.Parameters.AddWithValue("@LastName", lastName);

 

You probably guessed it - an additional cache entry was created for the same query due to the different name length:

(@LastName nvarchar(8))SELECT ContactID, FirstName, LastName FROM AdventureWorks.Person.Contact WHERE LastName = @LastName

 

It is likely that no more than a dozen or so cache entries will exist for this particular query since the only differentiator is the last name length.  However, with queries that contain multiple string parameters of varying lengths, the number of cache entries increases dramatically while the likelihood of reuse diminishes.  In Adam’s case, the main culprit was a string with a large list of values (between 1 and 1000+ items) that was split using a technique described in Erland’s Arrays and Lists articles http://www.sommarskog.se. 

Preventing Procedure Cache Bloat

An easy way to prevent unneeded procedure cache entries is to explicitly specify the maximum data length for string and other variable length values.  In fact, it’s a good idea to specify both the length and SqlDbType to ensure the parameter declaration matches the target column.  Not only will this help prevent cache bloat due to different parameter lengths, the matching data type can avoid the performance gotcha of a non-sargable expression caused by implicit data type conversion.

There are a number techniques to specify the parameter length and SqlDbType in .NET.  For example, the code below uses an overloaded SqlCommand.Parameters.Add method.  If you use AddWithValue, be sure to set the Size and SqlDbType properties of the newly created parameter afterward.

string lastName = "Sommarskog";

 

SqlCommand selectCommand =

    new SqlCommand(

        "SELECT ContactID, FirstName, LastName " +

        "FROM AdventureWorks.Person.Contact " +

        "WHERE LastName = @LastName",

        connection);

 

selectCommand.Parameters.Add("@LastName", SqlDbType.NVarChar, 50).Value = lastName;

 

 

posted @ Thursday, February 21, 2008 3:03 PM

Print

Comments on this entry:

# re: Don’t Bloat Proc Cache with Parameters

Left by Jeff M at 2/21/2008 4:46 PM
Gravatar
Thank you so much! Excellent insight. I can't wait to share this with the developers.

# re: Don’t Bloat Proc Cache with Parameters

Left by Mladen at 2/21/2008 4:48 PM
Gravatar
didn't know about this. very cool!

however i must point out that not specifying type and length of a parameter is the silliest thing next to not using params at all. by not specifying them you're simply not gibing enough info to the server.

I think that the option presented here shouldn't even be possible to do. Defaults aren't always good.

# re: Don’t Bloat Proc Cache with Parameters

Left by Jeff at 2/22/2008 10:26 AM
Gravatar
Wow -- great post. Glad to have you aboard here at SQLTeam, looking forward to more of your work.

I am going to review some of my code as we speak to check for this. Great point about AddWithValue and unicode as well.

# re: Don’t Bloat Proc Cache with Parameters

Left by Zack Jones at 3/23/2008 1:23 PM
Gravatar
Very interesting article. Does anyone know what value you would use for varchar(MAX) for the size?

# re: Don’t Bloat Proc Cache with Parameters

Left by Dan Guzman at 3/24/2008 7:50 AM
Gravatar
Zack,

The actual maximum length for varchar(MAX) is 2147483647. I would specify either that size or the length of the largest value that will actually be used, if known.

# re: Don’t Bloat Proc Cache with Parameters

Left by Zack Jones at 3/24/2008 11:52 AM
Gravatar
Dan,

I found some additional info after doing some google searches. When the size isn't known in advance such as varchar(MAX) and varbinary fields use -1 for the size parameter.

# re: Don’t Bloat Proc Cache with Parameters

Left by Dan Guzman at 3/25/2008 7:56 AM
Gravatar
Yes, a -1size specification for varchar(MAX) will work as well. When -1 or a size > 8000 is specified, varchar(MAX) is used as the actual parameter definition and will prevent cache bloat.
Comments have been closed on this topic.