Post reply

tyring to take some of the heat off the sql box

  • July 23, 2012 at 10:03 am

    #392805

    Looking around I noticed there is a particularly Long running query that takes over 40,000 ms and during the high heat time is running 1,000 times a minute. It’s execution plan looks decent. Mostly index seeks. I noticed however it coming in as dynamic sql and therefore the optimized exec plan is not using it's cached execution plan. What kind of performance boost would I be looking at if it was changed to proc and began utilize optimized exec plan caching.

    Long and short I suppose what I’m asking is how long does it take for sql server to generate the optimized execution plan as opposed to it using a cached exec plan?

  • July 23, 2012 at 10:41 am

    #1516344

    Not a direct answer but a similar discussion was had here: http://qa.sqlservercentral.com/Forums/Topic1262979-146-1.aspx

  • July 23, 2012 at 10:54 am

    #1516353

    Scott D. Jacobson (7/23/2012)

    Not a direct answer but a similar discussion was had here: http://qa.sqlservercentral.com/Forums/Topic1262979-146-1.aspx%5B/quote%5D

    Really helpful if you use the [ url ]/[ /url ] IFCode shortcuts (no spaces inside the square brackets) around urls when you post them.

    http://qa.sqlservercentral.com/Forums/Topic1262979-146-1.aspx

  • July 23, 2012 at 11:20 am

    #1516364

    Lynn Pettis (7/23/2012)

    Really helpful if you use the [ url ]/[ /url ] IFCode shortcuts (no spaces inside the square brackets) around urls when you post them.

    Hey, I searched for the article for him. I thought it was OK to make him work for it a little. 😛

  • July 23, 2012 at 11:30 am

    #1516372

    BaldingLoopMan (7/23/2012)

    I noticed however it coming in as dynamic sql and therefore the optimized exec plan is not using it's cached execution plan.

    Dynamic SQL caches and uses cached plans much as procedures do. The matching is on the statement text not the object ID, that's pretty much the only important difference

    Long and short I suppose what I’m asking is how long does it take for sql server to generate the optimized execution plan as opposed to it using a cached exec plan?

    It depends on the complexity of the query and how many phases of optimisation the optimiser has to go through.

    Are you seeing compile events and cache misses?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 23, 2012 at 11:47 am

    #1516383

    Scott D. Jacobson (7/23/2012)

    Lynn Pettis (7/23/2012)

    Really helpful if you use the [ url ]/[ /url ] IFCode shortcuts (no spaces inside the square brackets) around urls when you post them.

    Hey, I searched for the article for him. I thought it was OK to make him work for it a little. 😛

    But that makes everyone work if they want to see the content referenced. 😉

Viewing 6 posts - 1 through 5 (of 5 total)

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