Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Does a SP get precompiled? Expand / Collapse
Author
Message
Posted Tuesday, March 5, 2013 3:09 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 7:02 AM
Points: 111, Visits: 308
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
Post #1426680
Posted Tuesday, March 5, 2013 4:02 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 11:59 PM
Points: 406, Visits: 780
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''%'

Post #1426717
Posted Tuesday, March 5, 2013 4:22 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:36 AM
Points: 40,615, Visits: 37,080
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 2008, MVP
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

Post #1426735
Posted Tuesday, March 5, 2013 5:53 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 11:59 PM
Points: 406, Visits: 780
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.
Post #1426774
Posted Tuesday, March 5, 2013 6:00 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:36 AM
Points: 40,615, Visits: 37,080
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 2008, MVP
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

Post #1426779
Posted Tuesday, March 5, 2013 8:44 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 11:59 PM
Points: 406, Visits: 780
Thanks Gail.
Post #1426870
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse