Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase ««12

SQL Server Puzzling Performance Expand / Collapse
Posted Friday, October 25, 2013 6:58 AM



Group: General Forum Members
Last Login: Tuesday, July 19, 2016 4:34 AM
Points: 2,850, Visits: 4,076
Worth reading here

I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1508444
Posted Monday, November 4, 2013 1:48 AM


Group: General Forum Members
Last Login: Saturday, March 12, 2016 5:01 AM
Points: 13, Visits: 59
Hi Guys,

I can say that after trying to resolve this issue by optimizing my code and checking my server configuration i ended up refering the issue to MS support and they found the solution in a couple of days. See below.

Thank you guys for the time you put in to help out. Your the best


Hi Francis,

I did some research on this issue and found that it’s not a very common behavior of optimizer but we see this often with specific queries.

When optimizer compiles for a plan, various factors such as memory, CPU and cardinality are used to determine the most effective plan.
There have been a few cases where inefficient query plan results from large amount of memory, though it is very rare.
In these cases, what we experience is that if we reduce max server memory setting, the query runs faster.
So, the issue faced by you is very much similar and the point where its suddenly changing the plan is something we refer as “Tipping point”.

You can use trace flag 2335 to see if the problem is resolved.
This trace flag 2335 assumes SQL Server only has 1GB of memory available for query execution, and uses this memory setting to generate the query plan.
This will results in less memory intensive query plans such as nested loops.

Note: Setting this trace flag only affects optimizer for plan generation. It does not limit SQL Server from using all available memory for data buffers, query executions, etc.

We have a KB article documenting this.

Please let me know if this helps in fixing the issue.
Waiting for your findings so that we can move to next level of troubleshooting.

This did not work until I added this other trace flag as per their email below.

There is one more trace flag 4199 which is for implementing all optimizer fixes introduced so far.
Could you please implement both these trace flags in your SQL server startup parameter and then restart the SQL server services.

Please ensure that the trace flags are enabled from SQL server errorlog.
Let me know how this go.
Post #1511004
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse