<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by Paul Randal
»
Optimization
18 posts, Page 1 of 2
1
2
»»
Optimization
Rate Topic
Display Mode
Topic Options
Author
Message
Paul Randal
Paul Randal
Posted Saturday, February 27, 2010 5:07 PM
SSCommitted
Group: General Forum Members
Last Login: Tuesday, May 14, 2013 1:23 PM
Points: 1,905,
Visits: 1,601
Comments posted to this topic are about the item
Optimization
Paul Randal
CEO,
SQLskills.com
:
Check out SQLskills online training!
Blog:
www.SQLskills.com/blogs/paul
Twitter:
@PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of
TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
Post #874083
SQLRNNR
SQLRNNR
Posted Saturday, February 27, 2010 5:08 PM
SSCoach
Group: General Forum Members
Last Login: Thursday, May 16, 2013 1:46 PM
Points: 18,732,
Visits: 12,329
Once again, a very good question.
Thanks Paul.
Jason
AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008
SQL RNNR
Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #874084
Paul Randal
Paul Randal
Posted Sunday, February 28, 2010 8:22 AM
SSCommitted
Group: General Forum Members
Last Login: Tuesday, May 14, 2013 1:23 PM
Points: 1,905,
Visits: 1,601
Note: This question was written by Kimberly.
Paul Randal
CEO,
SQLskills.com
:
Check out SQLskills online training!
Blog:
www.SQLskills.com/blogs/paul
Twitter:
@PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of
TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
Post #874180
OCTom
OCTom
Posted Monday, March 01, 2010 8:17 AM
SSCrazy
Group: General Forum Members
Last Login: 2 days ago @ 1:42 PM
Points: 2,012,
Visits: 2,839
If stored procedures are never saved to disk, why do you have to bother compiling them? This is a misconception (or misunderstanding by me) on my part. I'm still learning, so, it's probably me.
Doesn't some part of a SP need to be stored somewhere? Like maybe just the source code or byte code? Otherwise, how would SQL Server ever find it when called?
Post #874439
Toby White
Toby White
Posted Monday, March 01, 2010 8:59 AM
SSC-Addicted
Group: General Forum Members
Last Login: Friday, November 09, 2012 2:48 PM
Points: 493,
Visits: 636
I thought this was a no brainer until I saw that 37% got it wrong. It always use to bug me when one of my previous team leaders would talk about "compiling a stored procedure" when he would issue a create/alter. Performance tuning is a related, but descrete discipline unto itself.
Anyway, the only thing I would add to the explanation on your post is that the optimizer may decide to recompile the plan on subsequent runs if the statistics on the underlying tables change significantly. Other possibilites that could cause the plan to be recompiled upon execution are temp tables and the with recompile option.
Regards,
Toby
Post #874473
Toby White
Toby White
Posted Monday, March 01, 2010 9:01 AM
SSC-Addicted
Group: General Forum Members
Last Login: Friday, November 09, 2012 2:48 PM
Points: 493,
Visits: 636
The definition of the object is stored to disk. However, the "english definition" has little to do with the compiled plan that the query optimizer submits upon execution.
Regards,
Toby
Post #874477
Oleg Netchaev
Oleg Netchaev
Posted Monday, March 01, 2010 9:07 AM
SSCommitted
Group: General Forum Members
Last Login: Thursday, May 16, 2013 12:39 PM
Points: 1,662,
Visits: 1,708
skjoldtc (3/1/2010)
If stored procedures are never saved to disk, why do you have to bother compiling them? This is a misconception (or misunderstanding by me) on my part. I'm still learning, so, it's probably me.
Doesn't some part of a SP need to be stored somewhere? Like maybe just the source code or byte code? Otherwise, how would SQL Server ever find it when called?
The answer to QoD does not mention anything about the storage of the proc on disk. It simply states that the
execution plan
of the procedure is never stored on disk. The text of the procedure is stored on disk the moment the procedure is created of course. When the procedure is executed first time after creation then the execution plan is created and stored in memory. It is assigned what is called age of the plan at this time. The execution plan will be stored in memory while the value of the age did not go down to 0 yet. The algorithm assigning and modifying the value of the execution plan age depends on the complexity of the plan as well as on frequency of procedure execution requests.
The fact that the execution plan of the stored procedure is not even created, much less stored, at create procedure time explains, for example, why it is possible to reference a table which does not even exist and still allow the procedure to be created successfully. At run time (because there is no plan stored in memory yet) the engine will attempt to generate one and break at this point because the proc is referencing a table which does not yet exist.
This is an easy to answer but excellent, clean a whistle question.
Oleg
Post #874479
vk-kirov
vk-kirov
Posted Monday, March 01, 2010 9:10 AM
Hall of Fame
Group: General Forum Members
Last Login: 2 days ago @ 1:32 AM
Points: 3,187,
Visits: 4,140
skjoldtc (3/1/2010)
If stored procedures are never saved to disk
The explanation says that stored procedure plans are never saved to disk. Of course, stored procedures themselves are saved to disk, otherwise SQL Server will not find any stored procedure after restart
Post #874481
Oleg Netchaev
Oleg Netchaev
Posted Monday, March 01, 2010 9:39 AM
SSCommitted
Group: General Forum Members
Last Login: Thursday, May 16, 2013 12:39 PM
Points: 1,662,
Visits: 1,708
vk-kirov (3/1/2010)
skjoldtc (3/1/2010)
If stored procedures are never saved to disk
The explanation says that stored procedure plans are never saved to disk. Of course, stored procedures themselves are saved to disk, otherwise SQL Server will not find any stored procedure after restart
To be exact, there are a couple of records inserted into the system tables when the procedure is created. The procedure name is inserted into sysobjects and the text of the stored procedure is inserted into syscomments. The
sp_helptext
which queries the data from syscomments will display the text of the newly created proc if called.
Oleg
Post #874507
vk-kirov
vk-kirov
Posted Monday, March 01, 2010 12:51 PM
Hall of Fame
Group: General Forum Members
Last Login: 2 days ago @ 1:32 AM
Points: 3,187,
Visits: 4,140
Oleg Netchaev (3/1/2010)
there are a couple of records inserted into the system tables when the procedure is created. The procedure name is inserted into sysobjects and the text of the stored procedure is inserted into syscomments.
Syscomments and sysobjects are not system tables anymore (in MSSQL 2005 and higher). They are views included for backward compatibility only (BOL:
http://msdn.microsoft.com/en-us/library/ms177596.aspx
,
http://msdn.microsoft.com/en-us/library/ms186293.aspx
). The 'sys.syscomments' view is quite complicated, and it queries data from several system tables.
Beware of using those views in new projects, use sys.objects and sys.sql_modules instead
Post #874649
« Prev Topic
|
Next Topic »
18 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
