SQL SERVER – Cases When Stored Procedure RECOMPILE – Quiz – Puzzle – 14 of 31

SQL Server Interview Questions and Answers
Print Book Available (207 Pages) | Sample Chapters

Year 2011 was a year of learning and opportunity for me. My recent book, SQL Server Interview Questions and Answers, has received such overwhelming love and support from all of you. While writing the book, I had two simple goals: (1) Master the Basics and (2) Ignite Learning. There was a constant request from the Community to take the learning of these books to the next level. Here is an article which discusses the Author’s Perspective.

Beyond Relational has come up with a very interesting concept – they have converted a few of the questions from my book into the SQL Quiz. The quiz is indeed focused on my two goals. In addition, it’s going to put the learning of the book to a higher stage. Looking at this novel concept, Vinod Kumar (Co-author) and I have decided to help every participant and reader by giving a few hints and suggestions to solve the quiz.

SQL Quiz

14 SQL SERVER   Cases When Stored Procedure RECOMPILE   Quiz   Puzzle   14 of 31

Compile and Recompile have very interesting meanings when learning Machine learning. However, the same things have similar but very interesting effects in SQL Server. Many related compilations of the code with performance are correct but have nothing to do with each other at some degree. When do your stored Procedures RECOMPILE inside SQL Server?

Link to participate in SQL Quiz

Notes of Vinod Kumar

Compilation of any code is inevitable, but why should SQL Server recompile in first place? Well, this is really a fundamental question. Read this article which talks extensively about the usages and when-where these recompile happen. What are the various pitfalls and improvements made with SQL Server 2008 R2 edition?

Notes of Pinal Dave

Compilation and Recompilation are different concepts. I remember my college when I had to take a Compiler course as part of my study. I found myself in really hard nailed shoes during the course. Now, Stored Procedure compilation is an equally difficult concept. You might have found yourself in the situation when compilation of the stored procedure sometimes gives optimal performance, while at other times it reduces the overall performance of the server. One has to really master the similar various aspects of the SQL Server, if they aspire an advanced career with SQL Server technology.

Additional Notes

SQL Server Interview Questions and Answers ISBN: 1466405643 Page#99
Stored Procedure are Compiled on First Run – SP taking Longer to Run First Time
Recompile Stored Procedure at Run Time
Encrypted Stored Procedure and Activity Monitor
Stored Procedure and Transactions


There are exciting prizes awaiting the winners. Click here for Prizes and Frequently Asked Questions.

Link to participate in SQL Quiz

Note: The SQL Quiz, winners and prizes are administrated by Beyond Relational. The goal of this blog post is to provide additional learning pointers only.

Reference: Pinal Dave (http://blog.sqlauthority.com)

One thought on “SQL SERVER – Cases When Stored Procedure RECOMPILE – Quiz – Puzzle – 14 of 31

  1. Pingback: SQL SERVER – Weekly Series – Memory Lane – #012 « SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s