Parameterization of your SQL statements is always a good idea. With parameterized SQL statements you are not able to pollute your Plan Cache – WRONG!!! In this blog posting I want to show you how easy it is to pollute your Plan Cache even with parameterized SQL statements.

ADO.NET – AddWithValue

ADO.NET is the part of the .NET Framework that implements the data access to a relational database like SQL Server – with some serious negative side effects. Don’t get me wrong – ADO.NET is great as long as you use it correctly. But it is very easy to use it incorrectly as you will see. Let’s have a look at the following C# Code that performs an execution of a SQL statement.

for (int i = 1; i <= 100; i++)
{
   val += i.ToString();

   cmd = new SqlCommand(
      "SELECT * FROM Sales.SalesOrderDetail WHERE CarrierTrackingNumber = @CarrierTrackingNumber", 
      cnn);
   cmd.Parameters.AddWithValue("@CarrierTrackingNumber", val);
   SqlDataReader reader = cmd.ExecuteReader();
   reader.Close();
}

We are clever developers, therefore the SQL statement itself is parameterized, and because the ADO.NET Framework is the greatest framework on earth we use the AddWithValue method of the class System.Data.SqlClient.SqlParameterCollection to provide the actual parameter value. I run that SQL statement 100 times in a while loop, and always assign a parameter value with a different length. The column CarrierTrackingNumber is defined as a NVARCHAR(25) in the table Sales.SalesOrderDetail. Therefore we can have up to 25 different parameter data types based on the different string lengths that we supply. Now let’s check the Plan Cache after the executions of our SQL statement.

SELECT
	st.text,
	cp.*
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
GO

Things are getting really crazy now: we have 100 (!) different execution plans stored in the Plan Cache!

There is one execution plan for every possible parameter data type – even when the underlying data type is a NVARCHAR(25). The method AddWithValue is very, very EVIL: it just derives the actual data type based on the parameter value that you supplied. NEVER EVER USE it!

ADO.NET – SqlDbType.VarChar

Because we now know the side effects of the method AddWithValue of ADO.NET we have learnt from our mistakes - and we don’t use it anymore. Now let’s rewrite our C# application code and define an explicit parameter data type as in the following listing.

for (int i = 1; i <= 100; i++)
{
   val += i.ToString();

   cmd = new SqlCommand(
      "SELECT * FROM Sales.SalesOrderDetail WHERE CarrierTrackingNumber = @CarrierTrackingNumber",
      cnn);
   cmd.Parameters.Add(new SqlParameter("@CarrierTrackingNumber", SqlDbType.VarChar));
   cmd.Parameters["@CarrierTrackingNumber"].Value = val;
   SqlDataReader reader = cmd.ExecuteReader();
   reader.Close();
}

As you can see from the code, ADO.NET is now not able to derive the parameter data type anymore, because we have specified the data type as a SqlDbType.VarChar. Let’s again execute this SQL statement 100 times and recheck the Plan Cache:

Nothing has really changed. The problem is still the same: we still have 100 (!) identical execution plans in the Plan Cache. The problem is now that ADO.NET just enforces the data type (SqlDbType.VarChar), but not the *length* of the data type. With 100 different lengths you have again 100 different execution plans in the Plan Cache.

If you specify a parameter data type explicitly within your ADO.NET code, you *also* have to specify its length! Now let’s have a look at some revised C# code.

for (int i = 1; i <= 100; i++)
{
   val += i.ToString();

   cmd = new SqlCommand(
      "SELECT * FROM Sales.SalesOrderDetail WHERE CarrierTrackingNumber = @CarrierTrackingNumber",
      cnn);
   cmd.Parameters.Add(new SqlParameter("@CarrierTrackingNumber", SqlDbType.VarChar, 100));
   cmd.Parameters["@CarrierTrackingNumber"].Value = val;
   SqlDataReader reader = cmd.ExecuteReader();
   reader.Close();
}

This time I have also specified the length of the data type – in our case 100, and now when we execute the SQL statement 100 times again, we finally end up with just 1 execution in the Plan Cache and it was reused 100 times. And that’s the ultimate goal from a SQL Server perspective.

Summary

The moral of the story: ADO.NET is a great data access framework, and provides you with useful functionality (like the method AddWithValue), but from a SQL Server perspective you really have to think about what you are doing. When you work with a parameterized SQL statement, you have to be as explicit as possible: you have to define the actual data type of the parameter value, and also the length to get the data type that you want.

Otherwise ADO.NET submits different permutations of the parameter data type, and SQL Server generates a different execution plan for every possible combination. And your Plan Cache is polluted – even with parameterized SQL statements.

Thanks for your time,

-Klaus