Optimization

  • 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

  • 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

  • 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

  • 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?

  • 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

  • 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

  • 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

  • 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 🙂

  • 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

  • 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 🙂

  • 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

  • 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

  • thanks for the question :). learned something new today

  • 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.

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

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

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