June 17, 2005 at 7:27 am
Hi,
I have a proc that calls nested procs. If I drop and recreate the "parent" proc, does it result in the recompilation of the nested procs?
Is there any difference if I use ALTER PROC with regard to the compilation of nested procs? It's my understanding that a proc is compiled when it's executed the first time or when it's plan is not longer in cache for some reason or else when the WITH RECOMPILE is used.
My concern is how/when nested procs are compiled or recompiled.
I'll appreciate any feedback. Thanks!
June 20, 2005 at 6:30 am
I don't know the answer to this question, but it intrigued me so I did a quick experiment. I ran this query:
select
o.name, s.*
from master..syscacheobjects s
inner join sysobjects o
on s.objid = o.id
where s.dbid = 21
order by o.name
against a database that I knew had a some nested stored procedures. I verified that one of the parent procedures was in place, along with the children. I dropped & recreated the parent procedure and then reran the above query. The parent proc was gone, but the nested procedures were still there. I then ran the parent proc and reran the query above. The parent was there as were the children. From this I couldn't tell what had been recompiled, so I fired up SQL Profiler and get the SP:Recompile event on it. I followed the same process, verify the procedure is in place, then drop & recompile the parent, then verify it's existence in the cache. No recompile events fired.
So, based on that, my best guess is that a nested procedure call is no different than a client procedure call in terms of sparking a recompile. If the recompile events are achieved (go here for lots of info on that) then a recompile occurs, but evidently simplying nesting a procedure isn't one of the causes. It's what I suspected, but I was interested in proving it true.
Thanks for the question. That was fun. I just hope I got the answer right.
----------------------------------------------------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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply