• Home
  • MAD
    • IOS Series
    • Android Series
    • Flutter Series
    • Xamarin Series
  • Concept Series
    • Software Design
    • Software Arch
    • GIT & Github
    • System Design
    • Cloud
    • Database Integration
    • Push Notification
    • API Integration
    • Cocoa PODS
  • DSA
  • Interview
  • Tips&Tricks
  • YT
  • Home
  • MAD
    • IOS Series
    • Android Series
    • Flutter Series
    • Xamarin Series
  • Concept Series
    • Software Design
    • Software Arch
    • GIT & Github
    • System Design
    • Cloud
    • Database Integration
    • Push Notification
    • API Integration
    • Cocoa PODS
  • DSA
  • Interview
  • Tips&Tricks
  • YT
  • #News
  • #APPS
  • #Events
    • #WWDC
    • #I/O
    • #Ignite
  • #Let’s Talk

MyCodeTips mycodetips-newlogocopy1

  • Home
  • MAD
    • IOS Series
    • Android Series
    • Flutter Series
    • Xamarin Series
  • Concept Series
    • Software Design
    • Software Arch
    • GIT & Github
    • System Design
    • Cloud
    • Database Integration
    • Push Notification
    • API Integration
    • Cocoa PODS
  • DSA
  • Interview
  • Tips&Tricks
  • YT
Programming, Database

Reasons for Statement Recompilation

What is recompilation in SQL Server?

Recompilation is the same process as a compilation, just executed again. If the database structure or data changes significantly, a recompilation is required to create a new query execution plan that will be optimal for the new database state and ensure better procedure performance.

There are multiple reasons why a recompilation can occur. A recompile of an execution plan could be due to database level configuration changes, schema changes, index changes, etc.

Why do we need to recompile stored procedures?

When a procedure is compiled for the first time or recompiled, the procedure’s query plan is optimized for the current state of the database and its objects. … Another reason to force a procedure to recompile is to counteract the “parameter sniffing” behavior of procedure compilation.

What does Option recompile do?

OPTION(RECOMPILE) tells the server not to cache the pan for a given query. This means that another execution of the same query will require elaborating a new(maybe different) plan. This is used in the queries with parameters to prevent parameter sniffing issues.

If you have collected trace using either SQL Server Profiler or an Extended Events session, you can capture SP: Recompile event in your trace. When you look at the event it displays the reason for recompiling the statement. However, when you run the following statement in SQL Server Management Studio, you can find all the possible reasons for statement recompilation.

SELECT dxmv.name,
dxmv.map_key,
dxmv.map_value
FROM sys.dm_xe_map_values AS dxmv
WHERE dxmv.name = N'statement_recompile_cause'
ORDER BY dxmv.map_key;

When I ran this query, I got the following 14 different reasons for statement recompilation. I personally was not aware of all the 14 and it was interesting learning for me.

map_key map_value
1 Schema changed
2 Statistics changed
3 Deferred compile
4 Set option change
5 Temp table changed
6 Remote rowset changed
7 For browse permissions changed
8 Query notification environment changed
9 PartitionView changed
10 Cursor options changed
11 Option (recompile) requested
12 Parameterized plan flushed
13 Test plan linearization
14 Plan affecting database version changed

Happy Coding :)
Liked it? Take a second to support Ranjan on Patreon!
become a patron button
  • Click to share on Reddit (Opens in new window)
  • Click to share on Facebook (Opens in new window)
  • Click to share on Twitter (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Click to share on Tumblr (Opens in new window)
  • More
  • Click to share on Pocket (Opens in new window)
  • Click to share on Pinterest (Opens in new window)
Written by Ranjan - 1202 Views
Tags | SQL
AUTHOR
Ranjan

Namaste, My name is Ranjan, I am a graduate from NIT Rourkela. This website is basically about of what i learnt from my years of experience as a software engineer on software development specifically on mobile application development, design patterns/architectures, its changing scenarios, security, troubleshooting, tools, tips&tricks and many more.

You Might Also Like

Database Normalization

SQL SERVER – Solution to Puzzle – REPLICATE over 8000 Characters

May 25, 2013
mycodetips-newlogo2

How to prevent SQL Injection in iOS apps?

October 7, 2013
visual studio purple

A New Approach to Scale .NET Applications

May 25, 2013
Next Post
Previous Post

Support us

mycodetips
mycodetips

Follow us @ LinkedIn 2850+

Subscribe for updates

Join 8,213 other subscribers

Latest Posts

  • YT-Featured-solidprinciples
    SOLID Principles of Software Design
  • IOS 16 Features
    Latest features in IOS 16
  • r-language
    How can R language be used for data analysis?
  • wordpress-coding-blog
    Guide To WordPress Coding Standards
  • YT-Featured-Algorithm
    What is Algorithm?
  • Frameworks of IOS
    Frameworks of IOS – Part ( I )
  • NSFileManager or NSPathUtilities
    NSFileManager or NSPathUtilities in Objective-C
  • Passing data between view controllers in Objective-C
    Passing data between view controllers in Objective-C
  • structures-classes-enum
    Structures and Classes in swift !
  • control-system-swift
    Control Flow in Swift
whiteboard

Whiteboard(PRO)

whiteboard

Whiteboard(lite)

alphabets

Kids Alphabet

techlynk

Techlynk

techbyte

Do2Day

techbyte

Techbyte

  • #about
  • #myapps
  • #contact
  • #privacy
  • #Advertise
  • #myQuestions

Android Android Studio API APP Programming Apps blogging CSS DATABASE dsa Features HTML HTML5 installation Interview Questions IOS iPhone javascript Mac objective-c OS Programming quicktips SDK SEO SQL swift Tips & Tricks Tools UI Web Wordpress Xcode

  • SOLID Principles of Software Design
  • Latest features in IOS 16
  • How can R language be used for data analysis?
  • Guide To WordPress Coding Standards
  • What is Algorithm?

©mycodetips.com