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.

A query plan in SQL Management Studio

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

5 responses

  1. Avatar for Andrei Rinea

    I don’t understand why in .NET 3.5 SP1 the ORMs did not set the length of the variable type parameters. After all it knows the schema so there is no excuse there… And having the query plan cache for nothing and recompiling the plans at almost every query is a massive performance issue I suppose.

    Andrei Rinea December 14th, 2009
  2. Avatar for Damien Guard

    Yes, it was a bug hence why it was fixed.

    Damien Guard December 14th, 2009
  3. Avatar for Matthew M

    It should be back-ported somehow :) Nice work D!

    • Matthew
    Matthew M December 15th, 2009
  4. Avatar for Andrew

    Good post. That cached plan query is really useful. Thanks.

    Andrew January 12th, 2010
  5. Avatar for Mike Irwin

    One small point - the query only works with databases with compatibility levels from 90 upwards.

    Mike Irwin – August 29th, 2013