high reads

Question

high reads

Viewing 6 posts - 16 through 20 (of 20 total)

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

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

Gut feel from what you've said is a parameter sniffing problem. Seen that many, many times. Need the actual execution plan of a slow execution to tell for sure (so not a plan pulled from cache)

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

Jared SSC Guru Points: 61793 More actions · Answer 2

in my opinion if imp in ent mngr and physically click generate query exec plan it should damn well generate the plan. not pull the cached plan if it exists.

You have a button that says "generate execution plan?" As far as I knew, there was one for "display estimated execution plan" and "include actual execution plan."

Jared
CE - Microsoft

BaldingLoopMan SSCertifiable Points: 6378 More actions · Answer 3

GilaMonster. That’s the thing. the execution plan runs fine then all the sudden during one of the recompiles it starts doing 11 million reads. keeps doing that until whatever caused it goes away and it’s freed from cache and recompiled or the proc is dropped and recreated, so I don’t have an example bad plan. I thought perimeter sniffing only applied to local variables or input params getting defaulted. In this instance the only params are chars so I don’t think that can be it. I see the check to see if isInactiveDate is null. I know dates can do odd thinks in exec plan especially when they're not actually set to a date. if that’s what u mean by param sniffing then I agree.

if u meant param sniffing as in related to the local variable or input params then I would think the execution plan wouldn’t ever fix itself. I would think it would always stay at 11 million reads.

I will find what is causing this hopefully sooner than later. if I had to guess. I’d say and index or stat rebuild is happening. Either way I will be setting up a monitor to track logical reads every 5 mins and if the logical reads exceeds 100% of what it did on the last run then notify me. Then I can see what’s running and maybe determine the red herring

BaldingLoopMan SSCertifiable Points: 6378 More actions · Answer 4

oh. and thanks SQL Know It All. I retract my blasphemous statement at Microsoft. I wish I would have known that before

i'll keep u all posted on what the issue was when i find it

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

BaldingLoopMan (8/22/2012)
I thought perimeter sniffing only applied to local variables or input params getting defaulted.

No, not at all. Parameter sniffing problems occur when SQL reuses a plan compiled for one parameter value with another that produces a very different row count.

It's a problem that can happily come and go without recompiles, rebuilds or anything happening.

To say anything for sure, I need to see the plans, especially the actual plan for a bad execution (11 million reads)

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

BaldingLoopMan SSCertifiable Points: 6378 More actions · Answer 6

Noted. I will keep an eye out for that monday morning when the box starts flying.

thanks.