Caching Mechanisms

SQL Server can avoid compilations of previously executed queries by using four mechanisms to make plan caching accessible in a wide set of situations.

  • Adhoc query caching

  • Autoparameterization

  • Prepared queries, using either sp_executesql or the prepare and execute method invoked through your API

  • Stored procedures or other compiled objects (triggers, TVFs, etc.)

To determine which mechanism is being used for each plan in cache, we need to look at the values in the cacheobjtype and objtype columns in the sys.dm_exec_cached_plans view. The cacheobjtype column can have one of five possible values:

  • Compiled Plan

  • Parse Tree

  • Extended Proc

  • CLR Compiled Func

  • CLR Compiled Proc

In this section, the only value we’ll be looking at is Compiled ...

Get Inside Microsoft® SQL Server™ 2005: Query Tuning and Optimization now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial