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;
|