jump to navigation

What is WITH RECOMPILE option? May 16, 2008

Posted by msrviking in Performance tuning.
trackback

Hello Guys,

I had a question from one of the developers asking me "What is WITH RECOMPILE?" and "When should we use this option?".

Well, here is what I could tell the developer (An abridged version of the mail I had replied back)

Read this note before you read the reasons what  is recompile and when to use it.
 
·         Logical reads mentioned in here is measure of how your query is doing, and you could get it by running SET STATISTICS IO.
·         Reusing a cached plan is always good, but in exceptions you will want to use WITH RECOMPILE.

Let us take an example. Say you have a query

Select * from <tabname> where <col1> = @var1 (@var1 = 1000 being an input value from the application). When this query is executed, the execution plan shows up a seek, using a non clustered or clustered index on the column col1 and this is always good. And the results of STATISTICS IO is 2 logical reads.

And, when you have the query Select * from <tabname> where <col1> = @var1 (@var1 = 10 being an input value from the application) executed, the execution plan may still show a seek again using same clustered and non clustered index. Great, this sounds good (reuse of execution plan) but the STATISTICS IO results show 30 logical reads.

Now, let us clear up the the plan cache of SQL Server by using some system command (DBCC FREEPROCCACHE) and run the same bunch of queries in a reverse order.

Select * from <tabname> where <col1> = @var1 (@var1 = 10 being an input value from the application). When this query is executed, the execution plan shows up a table scan with 10 logical reads.

Alright, now we execute the other query Select * from <tabname> where <col1> = @var1 (@var1 = 1000 being an input value from the application) the execution plan shows up a table scan with 20 logical reads.

So what should alarm you, is that query with input value 1000 took 2 logical reads with a seek and 20 logical reads with a table scan. And the query with input value 10 shows up 30 logical reads in a seek operation and 10 logical reads in a table scan.

Ah, you see confusing figures of (2, 20) and (30, 10) and you could be wondering what do you do now and which plan is the best? Well, each plan is best for the respective input parameters.

So what will you do now, to let the best plan be in place based on input parameters? You will use WITH RECOMPILE.

What does WITH RECOMPILE do? Every time there is a new input value, the existing reusable plan is not utilized and a new plan is created. So you are safe with having 2 logical reads with seek operation when input value is 1000 and 10 logical reads with scan operation when input value is 10.

Now, one may appreciate this statement after reading the above example.

“WITH RECOMPILE option, tells SQL Server to rebuild the plan each time the procedure is called. Although this means you get a good plan for your input parameters, it also means you never get the benefit of reusing a saved plan. In SQL Server 2000 and 7.0, a recompile always means a recompile of the entire procedure, even if only one statement in the procedure is using a suboptimal execution plan.”

Why is your sp performance gone bad after adding WITH RECOMPILE? By using WITH RECOMPILE, you are making the sp recompile every time and for a complex stored procedures eliminating recompilation time and this can make a noticeable performance difference.

There is an excellent whitepaper from microsoft on recompilation. Here is the link for your further reading – Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005 http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx

Enjoy reading, and do let me know what do you all think.

Thanks!

PS.: The last part of the entry in quotes is by my favorite SQL Server Guru – Kalen Delaney.

Advertisements

Comments»

No comments yet — be the first.

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

%d bloggers like this: