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 Monday, March 1, 2010 2:24 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, November 24, 2014 8:33 AM
Points: 1,676, Visits: 1,760
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






Post #874715
Posted Monday, March 1, 2010 2:56 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 19, 2014 2:26 AM
Points: 2,953, Visits: 439
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!



Urbis, an urban transformation company
Post #874746
Posted Monday, March 1, 2010 10:18 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, November 2, 2014 11:03 PM
Points: 2,132, Visits: 371
thanks for the question :). learned something new today
Post #874853
Posted Tuesday, March 2, 2010 4:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 4, 2010 1:03 AM
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.
Post #874992
Posted Tuesday, March 2, 2010 8:42 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 9:53 AM
Points: 2,667, Visits: 4,091
Thanks all for your explanations. It cleared it up for me.
Post #875226
Posted Tuesday, March 2, 2010 12:10 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:21 PM
Points: 7,928, Visits: 9,653
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
Post #875441
Posted Wednesday, March 3, 2010 8:54 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, June 13, 2014 3:03 PM
Points: 594, 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... :)


Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems
Post #876066
Posted Tuesday, March 30, 2010 8:27 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 4:18 PM
Points: 9,928, Visits: 11,206
Managed to get this wrong! Oh dear...



Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #892831
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse