September 9, 2011 at 6:37 am
Hello,
is it possible to configure MSSQL to ignore the value in parameterised statements and set to estimat the executino plan a mean value?
We have more often the problem that for imbalanced data the parameter value of the first cached exection results an suboptimal execution plan for a second exection with another value in the parameter. At the moment we working with hitns for such known exections. But I know from colleagues, Oracle will to the estimation of the exection plan without the values in the prameters and estimate a mean value.
September 9, 2011 at 7:54 am
Maybe you could check for the option "With recompile"
September 9, 2011 at 7:56 am
Thx for your hint, but we like to set up a global option; not to hint any executions.
September 9, 2011 at 9:56 am
There's a traceflag to turn parameter-sniffing off server-wide, but that will affect every single query and probably will be detrimental to most. I would not recommend using that, rather targetted fixes (option optimise for unknown) with them queries that are affected.
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
September 9, 2011 at 9:58 am
I know, but we would like to try and to compare with oracle the SAP performance. Could you please post the correct trace flag?
September 9, 2011 at 10:03 am
To be honest, no. I really, really, really don't ever recommend anyone use this, and posting it could give the impression that I do. Since this is a SAP system, unless SAP advises using it you probably shouldn't consider it.
Google - parameter sniffing trace flag SQL 2008 and you should find it.
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 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply