O'Reilly logo

Microsoft SQL Server 2012 Internals by Bob Beauchemin Kalen Delaney Conor Cunningham, Jonathan Kehayias, Benjamin Nevarez, and Paul S. Randal

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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.

  • Ad hoc 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, you should 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 six possible values.

  • Compiled Plan

  • Compiled Plan Stub

  • Parse Tree

  • Extended Proc

  • CLR Compiled Func

  • CLR Compiled Proc

This section looks at only Compiled Plan and Compiled ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required