Stored procedure compilation, SQL plan generation and storage
-
Monday, October 08, 2012 10:22 PM
Hi All,
I am on SQL 2008.
I have a very basic question. I am compiling/creating a stored procedure. My understanding is, when the stored procedure is compiled, exection plans for all the SQLs in the procedure will be created and stored with the compiled stored proc. Is that correct?
If that is the case, the data in the tables and statistics on the tables/indices at the time of compilation will decide the plan for future compilations, is that correct?
What if the nature of the data in the tables change later on? what if statistics is updated/deleted on the table?
Please help clearing my concept on stored procedure creation and the plans of the SQLs involved.
Thanks in advance
All Replies
-
Monday, October 08, 2012 10:50 PM
I think you've got it.
On every invocation SQL Server first checks to *see* if any of the tables mentioned in the plan have had their statistics updated. This normally happens automatically at given percentage growth points. If anything has changed, the plan is recomputed.
HTH,
Josh
-
Tuesday, October 09, 2012 2:51 AMModerator
I have a very basic question. I am compiling/creating a stored procedure. My understanding is, when the stored procedure is compiled, exection plans for all the SQLs in the procedure will be created and stored with the compiled stored proc. Is that correct?
To add to Josh's response, the execution plan is not physically persisted; it exists only in memory. The stored procedure itself is stored in the system catalog.
Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
-
Tuesday, October 09, 2012 6:38 AM
Those were really nice points.
Lets say I have a procedure running every hour, in a batch process. Due to some business requirement, we did major changes to the data in couple of tables (used in the proc). I did "update statistics" on those tables.
How can I find out, if the plan was recomputed after me changing data (and stats) or not ?
I am on SQL server 2008.
-
Tuesday, October 09, 2012 6:41 AM
Hallo Joker,
a pretty fine vid on sqlskills.com from Bob Beauchemin gives you a deeper view into optimizing procs. He explains the reasons for recompile, too.
http://technet.microsoft.com/en-us/sqlserver/gg545010.aspx
Uwe Ricken
MCITP Database Administrator 2005
MCITP Database Administrator 2008
MCITP Microsoft SQL Server 2008, Database Development
db Berater GmbH
http://www-db-berater.de -
Tuesday, October 09, 2012 6:45 AM
When you create and compile the stored Procedure they are just stored in sys catalog files/tables, they are not compiled till this time.
Actually compile is an incorrect word, because Stored Procedures are not pre-compiled. They are compiled only when they are executed for the first time. This time only the execution plans are created and stored in memory and are not persisted. Thus when you restart SQL Server or flush/clean buffers SP is re-compiled and plans are recreated.
~manoj | email: http://scr.im/m22g
http://sqlwithmanoj.wordpress.com
MCCA 2011 -
Tuesday, October 09, 2012 6:56 AMModerator
When you create a procedure it will create a best plan for that procedure. SP will recompile based on so many conditions, ex.) with recompile opition, temp table or table variable usage, dynamic queries etc. In addition to this each time when the query optimizer executes it will check whether the stats for that table is outdated? If its outdated then it will update (based on auto_update_stats and async_stats_update db option) the stats (where you done the changes to tables) and then execute the procedure. So to conclude this query optimzer will check for stats, if its outdated then it will udpate the stats and then will check whether the existing plan will be efficient if not it will generate a new plan for that procedure.
Mark as ANSWER if I helped you today :-) www.sql-articles.com
-
Tuesday, October 09, 2012 6:59 AM
As I said earlier Stored Procedures creates plans on first execution and they re-use the same plan for further execution. Thus you may get bad performance when the volume of data you are querying changes in future.
But to overcome this problem SQL Server engine uses a threshold method and formula to recompile SPs and Queries when the records count increases to a certain limit and changes the Recompilation Threshold (RT).
For Permanent tables:
If n <= 500, RT = 500.
If n > 500, RT = 500 + 0.20 * n.
For Temporary tables:
If n < 6, RT = 6.
If 6 <= n <= 500, RT = 500.
If n > 500, RT = 500 + 0.20 * n.
For Table variables:
RT does not exist. Therefore, recompilations do not happen because of changes in cardinalities of table variables.Check following links for more info:
Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005: http://technet.microsoft.com/en-us/library/cc966425.aspx
Statistical maintenance functionality (autostats) in SQL Server: http://support.microsoft.com/kb/195565
~manoj | email: http://scr.im/m22g
http://sqlwithmanoj.wordpress.com
MCCA 2011 -
Tuesday, October 09, 2012 10:01 AM
Hi,
"Automatic recompiling occurs whenever SQL Server is restarted. It also occurs if an underlying table referenced by the procedure has undergone physical design changes".
Hope below links will help you more
http://msdn.microsoft.com/en-us/library/ee343986(v=sql.100).aspx
http://msdn.microsoft.com/en-us/library/ms190439.aspx
Thanks & Regards,
Sathya
sathyas
- Marked As Answer by sqljoker Wednesday, October 10, 2012 2:23 PM
.png)
