List stored procedures that are marked for recompile?

Question

Post reply

List stored procedures that are marked for recompile?

tew

Ten Centuries

Points: 1249
More actions
January 12, 2010 at 2:26 pm

#140705

Hi folks,
Does anyone know a way to list all of the stored procedures that have been marked for recompile?
We need to manually mark a bunch of procedures for recompile that are used mainly for batch processing. For reporting purposes, I'd like a way to list all of the procedures that have been marked for recompile before and after I run sp_recompile.
thanks for your help,
Tim

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150467 More actions · Answer 1

The columns base_schema_ver and schema_ver in sysobjects change when an object is marked for recompilation.

Compare the values before and after you run your process.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

tew Ten Centuries Points: 1249 More actions · Answer 2

Thank you for the response Paul. However, results from sys.sysobjects aren't as one would hope.

Running the following query pre and post DBCC FLUSHPROCINDB (<id>) yields the same results:

select base_schema_ver, schema_ver from sys.sysobjects where xtype ='P'

group by base_schema_ver, schema_ver

base_schema_ver schema_ver

--------------- -----------

0 0

From BOL:

base_schema_ver : Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

schema_ver: Version number that is incremented every time the schema for a table changes. Always returns 0.

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 3

Hi

Could you please check whether the below script can help you..

SELECT OBJECT_NAME(ID)AS SP_NAME,* FROM SYSCOMMENTS

WHERE TEXT LIKE '%WITH RECOMPILE%'

Regards,

MC

Thanks & Regards,
MC

tew Ten Centuries Points: 1249 More actions · Answer 4

Hi MC,

I believe the query will reveal stored procedures that have been created with a "WITH RECOMPILE" definition, not those that have been flagged for recompile, regardless of their definition.

Tim

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 5

Ok, so meane to say flagged for recompile is different from this? I thought both are same, my be my lack o knowledge.....

If you dont mind can I know what does it mean flagged as recompile?

Regards,

MC

Thanks & Regards,
MC

tew Ten Centuries Points: 1249 More actions · Answer 6

Hi MC,

A stored procedure, amongst other stored objects, may be manually recompiled. With stored procedures, this may be accomplished by running sp_recompile <proc_name>. After running this command, the stored procedure is flagged for recompile, but the actual recompile occurs upon the procedures next execution.

When a stored procedures is written with the 'WITH RECOMPILE' option, the procedure is recompiled every time it is executed.

I hope my explanation makes sense.

Paul White SSC Guru Points: 150467 More actions · Answer 7

tew (1/13/2010)
Thank you for the response Paul. However, results from sys.sysobjects aren't as one would hope.

Well it was always undocumented, so I suppose I shouldn't be surprised that it has changed.

Inconvenient though.

Taking a quick look around, it seems that modify_date on sys.procedures also changes when it is marked for recompilation. It isn't documented as doing so (BOL just has that column changing when the procedure is changed by an ALTER statement) so bear that in mind.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi