SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Does a SP get precompiled?


Does a SP get precompiled?

Author
Message
Raymond van Laake
Raymond van Laake
SSC Eights!
SSC Eights! (989 reputation)SSC Eights! (989 reputation)SSC Eights! (989 reputation)SSC Eights! (989 reputation)SSC Eights! (989 reputation)SSC Eights! (989 reputation)SSC Eights! (989 reputation)SSC Eights! (989 reputation)

Group: General Forum Members
Points: 989 Visits: 390
Hi,

I have a large Stored Procedure. One tiny part of it is dynamic: SET @qry = 'SELECT something'; EXEC(@qry) .

Is the result of this that the SP will be compiled every time it is called? And if so, would it be better to move the dynamic part to a new, small SP, and call that one from the large SP? Or wouldn't that give any performance improvement?

Thanks,
Raymond
Arjun Sivadasan
Arjun Sivadasan
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1328 Visits: 976
Recompilation may not happen if you are writing a simple select as in - select 1,2. If you are selecting from a table or tables and there is filtering involved, the SP will get recompiled at run time. This is because the optimizer did not have a plan for the dynamic part of the SP and has to generate a plan at run time. When the query is like select 1, 2 the values are supplied inline.

Check this out with the following query. Check the last execution time column. My guess is that you will see multiple rows for a single execution of the SP, probably two rows.

SP recompilation will also happen if you use temp table in the SP and insert a certain number (6 IIRC) of records in the temp table. You may want to flush cache before running the script.

DBCC freeproccache
DBCC dropcleanbuffers



SELECT TOP 10 qs.*
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.sql_handle) qp
WHERE st.text LIKE '%select ''me''%'


GilaMonster
GilaMonster
SSC Guru
SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)

Group: General Forum Members
Points: 227675 Visits: 46339
A single piece of dynamic SQL will not cause the entire procedure to recompile. At worse, the dynamic section will compile each time, but that depends on a number of things.

Since SQL 2005, recompilation hasn't been at the procedure level, rather at the statement level so if a statement in a procedure triggers a recompile, it's just the statements that need recompiling that get it.

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


Arjun Sivadasan
Arjun Sivadasan
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1328 Visits: 976
Thanks Gail. For an SP, there can be multiple entries in the dm_exec_query_plan DMV even without SP recompilation is what this implies, right? Will insertion into temp table cause recompilation of SP or only the piece of code where it is queried?

PS: sorry for asking questions when I can find the answers myself. I don't have access to SQL Server right now.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)

Group: General Forum Members
Points: 227675 Visits: 46339
Arjun Sivadasan (3/5/2013)
Thanks Gail. For an SP, there can be multiple entries in the dm_exec_query_plan DMV even without SP recompilation is what this implies, right?


No. A procedure has a single plan in cache only (other than when there are set options different)

Will insertion into temp table cause recompilation of SP or only the piece of code where it is queried?


As I said, since SQL 2005, recompilation hasn't been at the procedure level, rather at the statement level so if a statement in a procedure triggers a recompile, it's just the statements that need recompiling that get it.

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


Arjun Sivadasan
Arjun Sivadasan
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1328 Visits: 976
Thanks Gail.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search