Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

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


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 5:03 AM
Points: 2,840, Visits: 3,975
Worth reading here http://www.dbsophic.com/SQL-Server-Articles/sql-server-logical-reads-what-they-really-tell.html

-------Bhuvnesh----------
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
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 17, 2014 2:56 PM
Points: 13, Visits: 45
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

Regards
FK

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”.

Recommendation:
=================
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 http://support.microsoft.com/kb/2413549 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