SP Recompile

Question

Post reply

SP Recompile

ramana3327

SSCoach

Points: 19572
More actions
March 23, 2014 at 6:09 pm

#283750

Hi,
I want to know, Is it possible to check a particular stored procedure is recompiling or not?

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

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

free_mascot One Orange Chip Points: 27168 More actions · Answer 1

One way to track is to create trace with SQL profiler.

HTH

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 2

You can use something like this and check the usage count, it will reset if recompiled. I forget but there may be another dmv with the compile time

SELECT usecounts

,object_name(qp.objectid)

, cacheobjtype

, objtype

, [text]

,query_plan

, db_name(qp.dbid)

--,*

FROM sys.dm_exec_cached_plans P

CROSS APPLY sys.dm_exec_sql_text(plan_handle)

CROSS APPLY sys.dm_exec_query_plan (plan_handle) qp

WHERE cacheobjtype = 'Compiled Plan'

and text like '%%'

and objtype = 'proc'

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 3

MysteryJimbo (3/24/2014)
You can use something like this and check the usage count, it will reset if recompiled.

It'll reset if the plan is removed from cache and compiled again from scratch, but in my tests recompiles didn't reset the usage count.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 4

GilaMonster (3/24/2014)
MysteryJimbo (3/24/2014)
You can use something like this and check the usage count, it will reset if recompiled.
It'll reset if the plan is removed from cache and compiled again from scratch, but in my tests recompiles didn't reset the usage count.

I guess this would depend on the circumstances and the code as whenever I have done this it has always reset the counter.

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 5

MysteryJimbo (3/24/2014)
GilaMonster (3/24/2014)
MysteryJimbo (3/24/2014)
You can use something like this and check the usage count, it will reset if recompiled.
It'll reset if the plan is removed from cache and compiled again from scratch, but in my tests recompiles didn't reset the usage count.
I guess this would depend on the circumstances and the code as whenever I have done this it has always reset the counter.

Just to confirm this was my experience on 2005-2008R2 and I just tested sp_recompile on a couple of non critical plans to confirm for myself.

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 6

Because sp_recompile does not cause a recompile (when run against a procedure). It removes the plan entirely from cache so that it has to be compiled from scratch on the next execution.

Try sp_recompile on a table, the behavior will be different.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 7

I'd suggest using extended events. You can capture the recompiles and you can filter the event so you're only capturing information for the query or stored procedure in question. It's much more efficient than using trace events (and we never use the Profiler GUI against production servers).

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software