Execution plans

  • Comments posted to this topic are about the item Execution plans

  • Thank you Steve

    M&M

  • 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

    -- Gianluca Sartori

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

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

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

    -- Gianluca Sartori

  • Great explanation:

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

  • Hello,

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

  • I think Steve tricked most of us here.

  • 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

  • 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

  • Have you been drinking Steve? :hehe:

  • Carlton Leach (6/28/2011)


    ...

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

    Care to rephrase?

    -- Gianluca Sartori

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

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

    -- Gianluca Sartori

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

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