Optimization

Question

Post reply

Optimization

Paul Randal

One Orange Chip

Points: 29544
More actions
February 27, 2010 at 5:07 pm

#217124

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

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

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[/url]
Learn Extended Events

Paul Randal One Orange Chip Points: 29544 More actions · Answer 2

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

OCTom SSChampion Points: 11785 More actions · Answer 3

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?

Toby White SSCertifiable Points: 6187 More actions · Answer 4

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

Toby White SSCertifiable Points: 6187 More actions · Answer 5

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

Oleg Netchaev SSCertifiable Points: 5276 More actions · Answer 6

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

vk-kirov SSCertifiable Points: 7686 More actions · Answer 7

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 🙂

Oleg Netchaev SSCertifiable Points: 5276 More actions · Answer 8

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

vk-kirov SSCertifiable Points: 7686 More actions · Answer 9

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 🙂

Oleg Netchaev SSCertifiable Points: 5276 More actions · Answer 10

vk-kirov (3/1/2010)
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 🙂

Sorry, this is my bad, I forgot to mention that this is only applicable to the SQL Server 2000. I understand that the situation in 2005/2008 is different. The actual data is stored in the base system table named sys.sysschobjs. The direct access to this table is not available except through DAC, and therefore, the object related data is instead exposed through the system views like the ones you mention. The bottom line is the same: there are records inserted into the system tables the moment the proc is created. So, to access the text of the proc via sys.objects one can use something like this:

select

[object_id], object_name([object_id]) [object_name],

object_definition([object_id]) definition

from sys.objects

where [object_id] = object_id('[your_schema].[your_proc_name]');

and via sys.sql_modules like this:

select

[object_id], object_name([object_id]) [object_name], definition

from sys.sql_modules

where [object_id] = object_id('[your_schema].[your_proc_name]');

Oleg

Iggy-SQL SSCarpal Tunnel Points: 4169 More actions · Answer 11

I choose the right answer "False" but that's because of this statement:

Stored procedures are optimized at creation

Because I believe that stored procedures are only optimized when they are first run, not when they are created 😀

Now I also learn that it's not stored on disk. Thanks, good question! :smooooth:

Urbis, an urban transformation company

ziangij SSCrazy Eights Points: 8066 More actions · Answer 12

thanks for the question :). learned something new today

brian.c.vos Valued Member Points: 74 More actions · Answer 13

Actually, you can persist query plans to disk.

MS SQL allows you to export a query plan to an XML file. You can then alter the query plan as required because contrary to MS documentation, the query optimiser does NOT work out anywhere close to the best execution when multiple view each with many joins are involved.

You can then force the query to execute using your customised plan.

So technically, you can persist a query plan. it's probably not what the question actually meant which was does the query optimiser save plans by default, in which case the answer in no. But you can force a query plan to be used which has been saved to disk.

OCTom SSChampion Points: 11785 More actions · Answer 14

Thanks all for your explanations. It cleared it up for me.