November 13, 2007 at 4:50 am
We already have WITH RECOMPILE in the procs and it seemed to make no difference. That's why I made the other changes.
Our processing sometimes involves a few thousand rows and sometimes half a million rows depending on which platform we are processing hence the problems with sniffing.
With the revised procs I have noticed that for some platforms the performace is slightly worse than before (2 min 20 sec vs 1 min 30 sec) but we are comfortable with this compromise. We will leave WITH RECOMPILE in the procs for belt 'n braces.
Jez
November 13, 2007 at 4:57 am
I know its a stupid question but have you checked the database/instance/field collations to ensure they are the same?
November 13, 2007 at 5:14 am
Why would the collations be different given that the database on the test server is a restore of the live database?
Jez
November 13, 2007 at 5:17 am
If the default instance collation is different this can cause issues in execution plans. As the database will be restored in the collation is was backed up in.
November 13, 2007 at 11:16 pm
Jez (11/13/2007)
We already have WITH RECOMPILE in the procs and it seemed to make no difference. That's why I made the other changes.Our processing sometimes involves a few thousand rows and sometimes half a million rows depending on which platform we are processing hence the problems with sniffing.
Jez
Odd. Parameter sniffing comes from reusing a plan when its not appropriate any longer. If all your procs are already marked WITH RECOMPILE, then they will never reuse a plan (they'll never even cache the plan) and hence parameter sniffing shouldn't be possible.
Can you post one of the procs you're having the most trouble with please?
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
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply