none
is only one plan is kept for one query in plan cache?

    Question

  • hi,

    Is only one plan is kept for one query in plan cache?

    i heard generaly hash is created for a query and plan is search with this hash, how a store proc haning many queires plan is kept ?

    yours sincerley

    Saturday, March 14, 2015 11:20 AM

Answers

  • No, there can be multiple plans for the same query in the plan cache, because users may be running with different SET options or another differences which makes it impossible for them to share the same plan.

    I discuss this in an article on my web site, and this link takes you directly to the section in question:
    http://www.sommarskog.se/query-plan-mysteries.html#cachekeys
    But you may be better off reading from the beginning.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, March 14, 2015 3:10 PM
  • You can have multiple plans for the same query as Erland said the plan generation can be different with different SET options. Either you need to set the options on the connection client side or you can simply set the options on or off inside the stored procedure.

    One other connection issue can affect whether a plan can be reused. If an owner name must be resolved implicitly, a plan cannot be reused. Check below link:

    Multiple Plans in Cache

    Best is that objects are always qualified with their containing schema name, so that you never need to rely on implicit resolutions, and the reuse of plan cache can be more effective.

    Saturday, March 14, 2015 7:02 PM

All replies

  • Engine chooses optimal plan for the parameter passed with SP. This means that it can choose different execution plan if the current parameter has another optimal execution plan than the last one.

    http://blogs.technet.com/b/beatrice_popa/archive/2013/05/10/execution-plan-for-stored-procedures-chosen-by-sql-server-database-engine.aspx

    Saturday, March 14, 2015 11:33 AM
  • artical talks about compilation and recompilation cases.

    what i have asked is, if query has a plan in plan cache , can there be two plans for one query

    in plan cache.

    yours sincerly

    Saturday, March 14, 2015 11:58 AM
  • No, there can be multiple plans for the same query in the plan cache, because users may be running with different SET options or another differences which makes it impossible for them to share the same plan.

    I discuss this in an article on my web site, and this link takes you directly to the section in question:
    http://www.sommarskog.se/query-plan-mysteries.html#cachekeys
    But you may be better off reading from the beginning.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, March 14, 2015 3:10 PM
  • You can have multiple plans for the same query as Erland said the plan generation can be different with different SET options. Either you need to set the options on the connection client side or you can simply set the options on or off inside the stored procedure.

    One other connection issue can affect whether a plan can be reused. If an owner name must be resolved implicitly, a plan cannot be reused. Check below link:

    Multiple Plans in Cache

    Best is that objects are always qualified with their containing schema name, so that you never need to rely on implicit resolutions, and the reuse of plan cache can be more effective.

    Saturday, March 14, 2015 7:02 PM