SQL Server query plan cache – what is it and why should you care?

What is a query plan?

SQL Server like all databases goes through a number of steps when it receives a command. Besides parsing and validating the command text and parameters it looks at the database schema, statistics and indexes to come up with a plan to efficiently query or change your data.

You can view the plan SQL Server comes up with for a given query in SQL Management Studio by selecting Include Actual Execution Plan from the Query menu before running your query.

Show me the cache!

Query plans are cached so subsequent identical operations can reuse them for further performance gains. You can see the query plans in use on your server with the following SQL:

SELECT objtype, p.size_in_bytes, t.[text], usecounts
   FROM sys.dm_exec_cached_plans p
   OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) t
   WHERE objtype IN ('Prepared', 'Adhoc')
   ORDER BY usecounts DESC

Hitting the cache

DBAs know the value in hitting the query plan often and this is one of the reasons they like stored procedures. You can however achieve the same thing with parameterized queries providing the query text and the parameter definitions are identical so you can execute the same thing over and over again just with different parameters.

If your ORM uses parameterized queries then it too can take advantage of it but it is important to remember the query definition and parameters need to be identical for this to happen.

How this applies to ORMs

In .NET 3.5SP1 both LINQ to SQL and Entity Framework did not set the length of variable type parameters (varchar, nvarchar, text, ntext and varbinary) so SQL Client sets it to the actual content length. This means the cache is often missed and instead populated with plans that are different only in the parameter lengths.

In .NET 4.0 variable length parameters now honour the defined length in both LINQ to SQL and Entity Framework where possible or fall back to the maximum length when the actual content doesn’t fit in the defined length.

[)amien