Execution plans

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 720447

    Comments posted to this topic are about the item Execution plans

  • M&M

    SSC-Insane

    Points: 21699

    Thank you Steve

    M&M

  • spaghettidba

    SSC Guru

    Points: 105673

    Steve, thanks for the good question. As usual, I got it wrong. ๐Ÿ˜›

    Let me ask one question: this page on MSDN states that:

    Server execution plans have the following main components:

    Query Plan

    The bulk of the execution plan is a re-entrant, read-only data structure used by any number of users. This is referred to as the query plan. No user context is stored in the query plan. There are never more than one or two copies of the query plan in memory: one copy for all serial executions and another for all parallel executions. The parallel copy covers all parallel executions, regardless of their degree of parallelism.

    Execution Context

    ...

    What's the difference in meaning with today's question?

    Thanks,

    Gianluca

  • Mighty

    SSCrazy Eights

    Points: 8815

    Gianluca Sartori (6/28/2011)


    Steve, thanks for the good question. As usual, I got it wrong. ๐Ÿ˜›

    Let me ask one question: this page on MSDN states that:

    Server execution plans have the following main components:

    Query Plan

    The bulk of the execution plan is a re-entrant, read-only data structure used by any number of users. This is referred to as the query plan. No user context is stored in the query plan. There are never more than one or two copies of the query plan in memory: one copy for all serial executions and another for all parallel executions. The parallel copy covers all parallel executions, regardless of their degree of parallelism.

    Execution Context

    ...

    What's the difference in meaning with today's question?

    Thanks,

    Gianluca

    I also think that re-entrant should be part of correct the answer.

  • dawryn

    SSCarpal Tunnel

    Points: 4692

    I have to disagree with explanation :crazy:

    I looked up before answering and I found:

    Query plans are read-only reentrant structures that are shared by multiple users.

    The explanation is about execution context (not the same as execution plan):

    From a query plan, an execution context is derived.

    Although execution contexts are reused, they are not reentrant (i.e., they are single-threaded).

    It is all at the same page that is given for explanation (see 'Query plans and execution contexts')

    [reused in not contested]

  • spaghettidba

    SSC Guru

    Points: 105673

    Uhmm... I'm finding more and more references that describe query plans as re-entrant.

    Also Grant in "SQL Server 2008 Query Performance Tuning Distilled" says so.

    I'm a bit confused.

  • dawryn

    SSCarpal Tunnel

    Points: 4692

    Great explanation:

    Execution context is another data structure that maintains the variable part of the query.

  • khelloufsofiane 6183

    SSC Eights!

    Points: 862

    Hello,

    I have found that the query plans are re-entrant ??????

  • paul s-306273

    SSChampion

    Points: 10615

    I think Steve tricked most of us here.

  • SQLWinther

    SSCertifiable

    Points: 5946

    Thanks for the Question! Unfortunately I think the answer is incorrect. The execution plan are reusable and re-entrant, but the execution context is not.

    Query plans and execution contexts

    When a cache-able batch is submitted to SQL Server for execution, it is compiled and a query plan for it is put in the plan cache. Query plans are read-only reentrant structures that are shared by multiple users. There are at most two instances of a query plan at any time in plan cache: one for all of the serial executions and one for all of the parallel executions. The copy for parallel executions is common for all of the degrees of parallelism. (Strictly speaking, if two identical queries posed by the same user using two different sessions with the same session options arrive at a SQL Server simultaneously, two query plans exists while they execute. However, at the end of their executions, plan for only one of them is retained in the plan cache.)

    From a query plan, an execution context is derived. Execution contexts hold the values needed for a specific execution of a query plan. Execution contexts are also cached and reused. Each user concurrently executing a batch will have an execution context that holds data (such as parameter values) specific to their execution. Although execution contexts are reused, they are not reentrant (i.e., they are single-threaded). That is, at any point of time, an execution context can be executing only one batch submitted by a session, and while the execution is happening, the context is not given to any other session or user.

    /Hรฅkan Winther
    MCITP:Database Developer 2008
    MCTS: SQL Server 2008, Implementation and Maintenance
    MCSE: Data Platform

  • Kwex

    Default port

    Points: 1407

    I also think reusable and re-entrant are both correct.

    Pls change my "wrong" to "right" so I can build up my points and rise up the Hall of Fame!

    Kwex

    Database Developer

  • Carlton Leach

    SSCarpal Tunnel

    Points: 4202

    Have you been drinking Steve? :hehe:

  • spaghettidba

    SSC Guru

    Points: 105673

    Carlton Leach (6/28/2011)


    ...

    Maybe I'm overly sensitive, but I don't like this kind of comment.

    Care to rephrase?

  • Carlton Leach

    SSCarpal Tunnel

    Points: 4202

    Gianluca Sartori (6/28/2011)


    Carlton Leach (6/28/2011)


    ...

    Maybe I'm overly sensitive, but I don't like this kind of comment.

    Care to rephrase?

    I believe you certainly are...it's a joke.

  • spaghettidba

    SSC Guru

    Points: 105673

    Carlton Leach (6/28/2011)


    Gianluca Sartori (6/28/2011)


    Carlton Leach (6/28/2011)


    ...

    Maybe I'm overly sensitive, but I don't like this kind of comment.

    Care to rephrase?

    I believe you certainly are...it's a joke.

    Yeah, it's a joke. I must have read it wrong, but I still don't like it.

    If it's ok for you, it's ok for me.

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

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