Galin Iliev's blog

Software Architecture & Development

How (not) to bloat SQL Server execution plan cache?!

Short answer: don't use SqlParameterCollection.AddWithValue() method!

Why?! Because if you have query like this:

   1: string lastName = "Galcho";
   3: SqlCommand selectCommand = new SqlCommand(
   4:         "SELECT ContactID, FirstName, LastName
   5:         FROM AdventureWorks.Person.Contact
   6:         WHERE LastName = @LastName",
   7:         connection);
  10: selectCommand.Parameters.AddWithValue("@LastName", lastName);

the execution plan cache entry is like this

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

So if you execute the code with different value in lastName variable you'll get different first part of the cached executed plan.

Using selectCommand.Parameters.Add("@LastName", SqlDbType.NVarChar, 50).Value = lastName; instead is a way better.

Read full story here