Just Learned - Script to find stored procedures created with Recompile Option

Getting Started with Adobe After Effects - Part 6: Motion Blur


	Just Learned

A collection of quick technology learning tips from what people around you learn every day


	Submit a tip


	Subscribe..

Home · Most Liked · Most Learned · Most Discussed · Silver Rated · Sections

Script to find stored procedures created with Recompile Option

Sep 12 2011 12:05AM by Mahadevan N

I just learned this from MSDN. Below script is used to find what are the stored procs are created with "Recomplie" option.

SELECT 
     OBJECT_NAME(sm.object_id) AS ProcedureName,
     definition as SQLText 
FROM sys.sql_modules SM
INNER JOIN sys.procedures SP ON  SM.object_id=SP.object_id
WHERE is_recompiled=1

5 Comments

Good one mahadevan...learned that sys.sqlmodules has information of isrecompiled of stored procs...

commented on Sep 12 2011 1:57AM

Ramireddy

2 · 41% · 12972

So simple, yet so wonderful. Thank-you for sharing, Mahadevan.

commented on Sep 13 2011 10:58AM

Nakul Vachhrajani

4 · 33% · 10690

Wecome RR and Nakul. I would like to discuss about recompile option here. What are the reasons you have used Recompile option in your work? One might be when procedure affected heavily by parameter sniffing. Any other ?

commented on Sep 14 2011 6:44AM

Mahadevan N

94 · 2% · 589

whenever using Recompile option in existing SQL statement, that is new execution plan will be created at sql buffer cache, Recomplie option always fetching data form in data file not in buffer cache.

Recomple option execution always in statement level not in hole procedure and other batch...

could anyone clarify - if using recomplie option in exsiting SP, does degrade performance or not?

commented on Sep 15 2011 12:50AM

Ananda

1496 · 0% · 13

Anand, Below Are the some points about Recompile in SQL :

1) If With Recompile clause is added with Stored procedure while its created it will recompile fully at every time its executing

 EX :
   CREATE PROCEDURE <ProcName> WITH RECOMPILE
   AS
   Select Column1, column2 from Table

2 ) If you need to recompile only subset of stored procedure that is statement then you need to use Recompile Query hint inside the proc (statement).

 EX  : 
      select * from table OPTION(recompile)

3 ) Another one method you can force the recompilation while executing procedure.

  EX :
        EXEC <ProcName> 'Parameter1', 'Parameter2' WITH RECOMPILE

Pros : When stored procedures take parameters whose values differ widely between the executions then Recompile option will help here to Avoid Parameter Sniffing.

Cons: Every time plan needs to be created while executing ( CPU bound Process ). Query Elapse time will increase, since new plan creation time also included.

commented on Sep 16 2011 3:47PM

Mahadevan N

94 · 2% · 589

Your Comment

Sign Up or Login to post a comment.

"Script to find stored procedures created with Recompile Option " rated 5 out of 5 by 10 readers