Parameter Sniffing

Question

Post reply

Parameter Sniffing

Attopeu

Ten Centuries

Points: 1308
More actions
January 21, 2010 at 1:01 pm

#156599

Hi Guru,
I have a customer search table which has about 4 millions unique records. We search a record by a hashed value of SSN. When I executed a proc by my login ID, the optimizer used non-clustered index seek and with only eight READS. However, when I ran a trace to capture the same proc proc and the same parameter value on Production server, there were more than 18 millions READs from my trace.
My question are:
How this could be a parameter sniffing if the values of this table is very unique?
There are total 6000176 8KB pages to store this table but how did I get more than 18 millions READs?
Please help!
Silaphet

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

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

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

Sounds more like stale statistics.

Does UPDATE STATISTICS <Table Name> WITH FULLSCAN help?

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

Attopeu Ten Centuries Points: 1308 More actions · Answer 2

Hi Gail,

That's my next plan to analyze distribution statistics and I might have to update statistics with full scan per your suggestion.

Also, I cloned this production database to our Test server two months ago and I had a user ran a few searches on our test box and the searches was instant (so fast).

The weird part is I did not even have update statistis job run on a test server.

Another option I have in mind is to recompile this proc.

Thoughts?

Thanks much,

Silaphet

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

Silaphet Mounkhaty (1/21/2010)
Also, I cloned this production database to our Test server two months ago and I had a user ran a few searches on our test box and the searches was instant (so fast).
The weird part is I did not even have update statistis job run on a test server.

Smaller data sets?

Try a recompile first, if that doesn't fix it then it's not parameter sniffing.

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

Attopeu Ten Centuries Points: 1308 More actions · Answer 4

Hi Gail,

After recompiling the proc last night, the READs reduced from 18 millions to only 54 and I just can't beleive it.

However, I'm still confused why this could be a parameter sniffing because every search is qualanteed to be unique.

Also, if I run into this issue again, I'll advise developers to modify the procedure to put WITH RECOMPILE option.

What do you think?

FYI... I visit your blog at least a few times a week:)

Thanks so much,

Silaphet

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

Silaphet Mounkhaty (1/22/2010)
Also, if I run into this issue again, I'll advise developers to modify the procedure to put WITH RECOMPILE option.
What do you think?

I think that's using a sledgehammer to kill a fly. Identify the cause of the problem (hint look at the exec plans for the good and bad queries), ask advice here if you need, implement an appropriate solution.

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