Plan cache pollution or how important it is to properly define parameters in code

  • Article

Recently I was involved in couple of cases concerning plan cache taking more than 20% of the SQL Server Buffer Cache and although there is no fixed threshold/best practices value, I noticed that there were a lot of identical statements getting cached. Effective plan caching is important for optimal performance on SQL Server and I will not get into details why. If you want to further read on plan caching, there is a great whitepaper on MSDN – Plan Caching and Parametrization

Back to my scenario – the plan cache was looking very similar to this:

1

So I have absolutely the same query, cached three times with one difference only – the parameter definition of the CourseID parameter. So what was the reason to have this behavior?

In order to reproduce it, I started with a very simple console application, using Visual Studio Samples. Based on that code, I created couple of methods, so I can reproduce different behaviors. What I noticed is that this behavior is only applicable on parameters with variable length (varchar, nvarchar, etc.), that do not have the size defined in the code of the application itself. Here are the examples:

3

4

  • Using the SqlParameter class with specifying the size

2

What happens is that if you do not specify the size of your parameter and you are executing your queries adhoc (not using stored procedures), the client driver (in that case the ADO.NET library) is evaluating the size of the parameter at runtime and passes it to the SQL Server instance. So if you have 10 inputs with different sizes, then you will have 10 different cached plans.

If you notice in the cached plans, I have 4 different plans and only 1 of them is with nvarchar(11) – this is the one, executing the method, where I used SqlParameter class with explicitly defining the size of the parameter. Exactly this plan always gets reused (usecounts=4). The others will only be reused if the input parameter at runtime is with the same size of any of the cached plans. But in real world, that would be rarely the case.

Note that OptimizeForAdhocWorkloads parameter will not help in this case!

Conclusion: if you are sending dynamic/adhoc queries from your application to the SQL Server instance, make sure to properly define the size of your variable parameters. If you are using int for instance, this behavior will not be in place