none
Trace Flag 2440

    Question

  • Does anyone have any additional information on Trace Flag 2440 which is supposed to allow for more than one thread per partition when pulling from larger partitioned tables.

    I have done some testing with this on larger tables, trace flag on and off, to see if there is any notable difference in execution plan and / or performance but have not been able to see anything. Granted I may not executing something that the engine would deem appropriate to use multiple threads for a partition but I am pulling from multiple partitions with the query that I am executing.

    So, I'm hoping that someone has some more information on this; specifically details of when this would be invoked by the engine and also is there any way of seeing that it is being used, i.e. some reference in the execution plan which would denote this.

    Thanks in advance for any information that you can provide.


    David
    Wednesday, March 2, 2011 3:26 PM

Answers

  • -T2440 is not available for use on current builds. In case you are facing a performance issue with partitioning and think that parallelism would help, then please feel free to engage CSS at this point. The optimizer is designed to pick a query plan which is optimal in nature. If the query plan is not parallel in nature, then it doesn't mean that this is not the optimal plan. It is not necessary that the functionality mentioned for the trace flag would improve performance.

    HTH


    This posting is provided "AS IS" with no warranties, and confers no rights.
    My Blog: http://www.troubleshootingsql.com
    Twitter: @banerjeeamit
    MSDN Blog: http://blogs.msdn.com/sqlserverfaq
    Tuesday, March 8, 2011 7:32 PM
  • Which version of SQL Server are you using? 2440 was a temporary flag; it's not available anymore.

    • Marked as answer by DavidBenoit Tuesday, March 8, 2011 7:39 PM
    Wednesday, March 2, 2011 6:00 PM

All replies

  • Which version of SQL Server are you using? 2440 was a temporary flag; it's not available anymore.

    • Marked as answer by DavidBenoit Tuesday, March 8, 2011 7:39 PM
    Wednesday, March 2, 2011 6:00 PM
  • Running on SQL Server 2008 SP2. Where did you see that this was temporary? Is it actually incorporated into the engine by default then?

    Thanks for your help and I look forward to your feedback.


    David
    Wednesday, March 2, 2011 7:01 PM
  • Hi,

    According to the following documentation, "Multiple-thread-per-partition strategy" could be enabled by using trace
    flag 2440.

    UPDATING YOUR SQL SERVER 2005 SKILLS TO SQL SERVER 2008
    http://download.microsoft.com/download/F/8/8/F88E663F-618F-42EB-9A23-60D675FC110E/Nov18/Academy_Lecture3_081118.pdf

    Hope this helps.


    Best Regards,
    Chunsong Feng

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Thursday, March 3, 2011 10:36 AM
    Moderator
  • Thank you Chunsong. I had found the trace flag in the documentation which is why I am looking at it as a potential performance boost. Based on my testing though I am not able to see any difference in optimizer behavior between when I have the trace flag enabled or disabled. Can you answer any of the questions that I had in the original post which I am quoting below;

    "I have done some testing with this on larger tables, trace flag on and off, to see if there is any notable difference in execution plan and / or performance but have not been able to see anything. Granted I may not executing something that the engine would deem appropriate to use multiple threads for a partition but I am pulling from multiple partitions with the query that I am executing.

    So, I'm hoping that someone has some more information on this; specifically details of when this would be invoked by the engine and also is there any way of seeing that it is being used, i.e. some reference in the execution plan which would denote this."

    Thanks in advance for any help you can provide.


    David
    Thursday, March 3, 2011 2:02 PM
  • Hello David,

     

    Trace flags are for specific scenario, if your test shows it didn't get better performance then it may not be applicable for the query which we are running. unfortunately I am unable to find any public document where the trace flag is documented. Troubleshooting this issue  requires a more in-depth level of support.  You can also visit the below link to see the various paid support options that are available to better meet your needs. http://support.microsoft.com/default.aspx?id=fh;en-us;offerprophone

     

    Thank You

     

    Jayaprakash JO - MSFT

    Friday, March 4, 2011 7:20 PM
    Answerer
  • Thanks Jayaprakash - Appreciate the feedback. We have a support agreement which I may exercise at this point. Just hoping that someone had some experience with this as well.

    Thanks again to all that replied.


    David
    Friday, March 4, 2011 7:58 PM
  • -T2440 is not available for use on current builds. In case you are facing a performance issue with partitioning and think that parallelism would help, then please feel free to engage CSS at this point. The optimizer is designed to pick a query plan which is optimal in nature. If the query plan is not parallel in nature, then it doesn't mean that this is not the optimal plan. It is not necessary that the functionality mentioned for the trace flag would improve performance.

    HTH


    This posting is provided "AS IS" with no warranties, and confers no rights.
    My Blog: http://www.troubleshootingsql.com
    Twitter: @banerjeeamit
    MSDN Blog: http://blogs.msdn.com/sqlserverfaq
    Tuesday, March 8, 2011 7:32 PM
  • Thank you Amit for the feedback. We are not necessarily suffering performance issues but were looking to see if there was an opportunity to further the performance we are getting through the use of this trace flag so, we don't have a need to escalate this any further.

    Thanks again.


    David
    Tuesday, March 8, 2011 7:37 PM