Post reply

Trace Flag for execution plan with lot of memory?

  • August 17, 2011 at 8:51 am

    #241786

    Hi

    Please correct me if I am wrong.

    The execution plan created for any query will also depend on the amount of memory available.

    for example. if we have two servers one with 1 gb free memory and the other with 200gb free memory.

    same query executed against each server might have different execution plan. the plan created with having 200 gb will be bad compared to the one created with 1gb in mind.

    There is a trace flag which will make sql server think that there is only less memory resource available(even when we have abundant free memory). this way the execution plan will be optimal.

    can anyone please help me find this traceflag..

    thanks in advance

  • August 17, 2011 at 8:56 am

    #1369656

    Got it!!

    T2335- trace flag

    Thanks...

  • August 17, 2011 at 9:11 am

    #1369670

    I thought i had this one in my docs but it seems if I did I couldn't find it 🙂

    Thanks for the answer, I'll make sure I keep this one as we're moving servers to over 100GB of ram.

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • October 29, 2014 at 7:45 am

    #1755518

    Does anyone know if the "Using large amounts of memory can result in an inefficient plan in SQL Server" in SQL Server 2008 R2 was resolved in any of the service packs since SP2?

    On a more wild question, is there a trace flag to make sql server think is has more memory that is on the server? We don't have enough memory to test on a non production server what the "large" actually measures to and we would like to recreate the problem in order to see if SQL Server 2008 R2 SP3 resolves the issue and/or if T2335 causes any problems.

    Thanks!

Viewing 4 posts - 1 through 3 (of 3 total)

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