Strange behavior for stored proc

Question

Post reply

Strange behavior for stored proc

Roy Ernest

SSC-Dedicated

Points: 38780
More actions
November 21, 2008 at 12:44 pm

#203124

HI All,
I have a stored that has been behaving very very badly from an application. If I just copy the stored proc from the profiler and run it in SSMS, the proc behaves normally like it should. Has anyone seen this strange behaviour?
I even tried logged on to SSMS using the same user the apllication uses and still it is fast from SSMS. But from teh website, the number of reads increases. (From 1200 to 11,000 page reads)
Please adviuce if anyone has any idea.
Thanks
Roy
-Roy

Viewing 8 posts - 1 through 7 (of 7 total)

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

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

Parameter sniffing?

Did you run the proc from management studio, or did you run the contents?

Can you post the proc?

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

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 2

Could be parameter sniffing. I am not sure how I can fix parameter sniffing.

I ran both the contents and the Stored proc from SSMS. Both has the same number of reads. 1300 page reads. When running from app, it is 11K page reads.

The SP has 4 parameters. Three Chars , One INT. No IF Statements. The only thing I really dont like is the SP is reading from 10 tables.

I wish I could post the SP here, but Management does not want any of the code to be published anywhere.. 🙁

-Roy

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

There are articles on my blog on parameter sniffing.

Were all the parameter values the same? All the options (ansi_nulls, etc) the same?

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

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 4

ANSI_PADDING False in SSMS and the DSN Audit Login shows ANSI_PADDING ON

Same with ANSI_NULLS, ANSI Warnings

-Roy

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

The different settings may cause problems. Make the settings in management studio the same as in the app and see what behaviour you get.

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

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 6

Hi GilaMonster,

I set it up the same in SSMS and tried running the Same Stored proc and it ran fine. First run was 2900 Page reads and the next time it was 878 page reads. But still from the app it is around 10000 Page reads... 🙁

-Roy

matt stockham SSCrazy Eights Points: 9942 More actions · Answer 7

Are you comfortable enough with Profiler to capture the XML execution plans for the proc (and any statements within it)? They would help you determine which statement was causing the problem, and comparing actual and estimated counts on the plan iterators may give more detail.

Using OPTION(RECOMPILE) on the problem statement may well resolve the issue without having to worry about changing the result set, but there are side effects and you should really understand why the problem is occurring before trying any quick fixes.