tyring to take some of the heat off the sql box

Question

tyring to take some of the heat off the sql box

BaldingLoopMan

SSCertifiable

Points: 6378
More actions
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?

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

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

Scott D. Jacobson SSCertifiable Points: 6149 More actions · Answer 1

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Scott D. Jacobson (7/23/2012)

Not a direct answer but a similar discussion was had here: http://www.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://www.sqlservercentral.com/Forums/Topic1262979-146-1.aspx

Scott D. Jacobson SSCertifiable Points: 6149 More actions · Answer 3

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

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