locked
SQL server Procedure stores plan RRS feed

  • Question

  • Hi ,

      While reading "benefits of stored procedure" I found term  in case of stored procedure sql server stores plan and reuse it that's why procedure are more faster than ad-hoc query .

    My question is  sql server stores procedure execution plan permanently into database or just Cache the plan till catch memory not face space problem?

     If it stores permanently! where we can see that.  

     


    Regards Vikas Pathak


    Sunday, December 15, 2013 1:11 PM

Answers

  • My question is  sql server stores procedure execution plan permanently into database or just Cache the plan till catch memory not face space problem?

    SQL Server stores execution plans only in memory.  The sys.dm_exec_cached_plans can be queried to see cached plans.  See http://technet.microsoft.com/en-us/library/ms187404.aspx for more details and query examples.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Proposed as answer by Shanky_621MVP Sunday, December 15, 2013 3:46 PM
    • Marked as answer by Sofiya Li Monday, December 23, 2013 1:39 PM
    Sunday, December 15, 2013 1:41 PM
  • Hello,

    Its not permanent by virtue of fact that Cache is area present in memory and you must be aware RAM is volatile and moment SQL server or windowx box is started every thing present in RAM is flushed out.


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    • Proposed as answer by Sofiya Li Wednesday, December 18, 2013 6:42 AM
    • Marked as answer by Sofiya Li Monday, December 23, 2013 1:39 PM
    Sunday, December 15, 2013 3:47 PM

All replies

  • My question is  sql server stores procedure execution plan permanently into database or just Cache the plan till catch memory not face space problem?

    SQL Server stores execution plans only in memory.  The sys.dm_exec_cached_plans can be queried to see cached plans.  See http://technet.microsoft.com/en-us/library/ms187404.aspx for more details and query examples.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Proposed as answer by Shanky_621MVP Sunday, December 15, 2013 3:46 PM
    • Marked as answer by Sofiya Li Monday, December 23, 2013 1:39 PM
    Sunday, December 15, 2013 1:41 PM
  • Hello,

    Its not permanent by virtue of fact that Cache is area present in memory and you must be aware RAM is volatile and moment SQL server or windowx box is started every thing present in RAM is flushed out.


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    • Proposed as answer by Sofiya Li Wednesday, December 18, 2013 6:42 AM
    • Marked as answer by Sofiya Li Monday, December 23, 2013 1:39 PM
    Sunday, December 15, 2013 3:47 PM
  • As others have said, plans are cached in memory. And this applies both to plans to stored procedures and ad-hoc queries. Thus, it is not true that stored procedures are faster than ad-hoc queries due to plan caching per se. However, to achieve this with ad-hoc queries there are two criterias that must be fulfilled:

    1) Queries are parameterised; that is parameter values must not be inlined to the query string.
    2) All table references should use two-part notation, that is both schema and tables.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, December 15, 2013 4:14 PM
  • @Erland : You mean ad-hoc query and stored procedure both have same performance, their is no performance difference. Or if it has performance difference then what is the main reason.

    Thanks for suggestion


    Regards Vikas Pathak

    Friday, June 6, 2014 12:27 PM
  • << You mean ad-hoc query and stored procedure both have same performance, their is no performance difference. >>

    Not necessarily. It all boils down to the plan you get in the end. Procedures give you greater control over recompile, so in this regard procedures can work for you. And for procedures, SQL Server will sniff parameter values, whcih when they are "typical" will be better than using hard-wired selecivity values as it does for variables, it can be less optimal compared to constants. OTOH, if your app send your queries as text (compared to parameterized statements), then you will end up with separate plans for each value combination you have - lots and lots of plan - and this can also work both for you and against you.


    Tibor Karaszi, SQL Server MVP | web | blog

    Friday, June 6, 2014 12:54 PM
  • @Erland : You mean ad-hoc query and stored procedure both have same performance, their is no performance difference. Or if it has performance difference then what is the main reason.

    Exactly what I said: just because you run a query inside a stored procedure it does not magically run faster than if you had run it ad hoc.

    Consider these three cases:

    CREATE PROCEDURE dbo.some_sp @parameter int AS
      SELECT col2, col3 FROM tbl WHERE col = @parameter
    go
    EXEC dbo.some_sp 43

    .......................................

    EXEC sp_executesql N'SELECT col2, col3 FROM dbl.tbl WHERE col = @parameter'
                       N'@parameter int', 43

    .......................................................................

    SELECT col2, col3 FROM tbl WHERE col = 43

    The first two will have the same performance. The last one may perform differently, and if it does it is likely to perform faster, because 43 is now a constant which SQL Server can exploit for a more accurate plan. However, for this simple query it may not be likely.

    What may matter more is that in the first two cases, the plan will be cached and the plan will be reused by anyone who calls the procedure or runs the parameterised query, no matter the value of the parameter. But for the last query there will be a new compilation for every new value and also if users have a different default schema. If the query is invoked with high frequency, this can add a significant load to the server.

    (Actually, what I've said is not true for this particularly simple example, as it is likely that SQL Server will auto-parameterise the query, but imagine that the query is more complex.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, June 6, 2014 1:39 PM