Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Optimization


Optimization

Author
Message
Oleg Netchaev
Oleg Netchaev
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1693 Visits: 1807
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
Iggy-SQL
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2961 Visits: 440
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 :-D
Now I also learn that it's not stored on disk. Thanks, good question! Smooooth


Urbis, an urban transformation company
ziangij
ziangij
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2905 Visits: 374
thanks for the question Smile. learned something new today
brian.c.vos
brian.c.vos
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 8
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
OCTom
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2805 Visits: 4152
Thanks all for your explanations. It cleared it up for me.
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10638 Visits: 11980
Nice question but one niggle about the explanation: if the procedure option RECOMPILE is specified the same plan will NOT be used in subsequent executions. This can be useful if an SP's plan would be heavily influenced by one of its parameters or if some of the tables it accesses changed very rapidly (so that statistics are very different) compared to the frequency with which the proc is called.

Tom

Peter Trast
Peter Trast
SSChasing Mays
SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)

Group: General Forum Members
Points: 642 Visits: 655
Thanks for an easy one, I usually need to think alot more. Actually, I was surprised to see how many missed that one. I will be careful to cover that topic a little more carefully in class...

Now to go find another question that I can get wrong... Smile

Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems
Paul White
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10332 Visits: 11350
Managed to get this wrong! Oh dear...



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search