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 12»»

Optimization Expand / Collapse
Author
Message
Posted Saturday, February 27, 2010 5:07 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, October 27, 2014 11:15 AM
Points: 2,041, Visits: 1,671
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
Posted Saturday, February 27, 2010 5:08 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 10:18 PM
Points: 17,964, Visits: 15,968
Once again, a very good question.

Thanks Paul.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #874084
Posted Sunday, February 28, 2010 8:22 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, October 27, 2014 11:15 AM
Points: 2,041, Visits: 1,671
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
Posted Monday, March 1, 2010 8:17 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 1:50 PM
Points: 2,630, Visits: 4,036
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
Posted Monday, March 1, 2010 8:59 AM
SSC-Addicted

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

Group: General Forum Members
Last Login: Friday, November 9, 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
Posted Monday, March 1, 2010 9:01 AM
SSC-Addicted

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

Group: General Forum Members
Last Login: Friday, November 9, 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
Posted Monday, March 1, 2010 9:07 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 8:33 AM
Points: 1,676, Visits: 1,760
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
Posted Monday, March 1, 2010 9:10 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, May 9, 2014 12:47 AM
Points: 3,448, Visits: 4,407
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
Posted Monday, March 1, 2010 9:39 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 8:33 AM
Points: 1,676, Visits: 1,760
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
Posted Monday, March 1, 2010 12:51 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, May 9, 2014 12:47 AM
Points: 3,448, Visits: 4,407
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 »

Add to briefcase 12»»

Permissions Expand / Collapse