SQL Server Performance

Max degree of parallelism question

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by mkal, Sep 21, 2009.

  1. mkal New Member

    Config info:
    4CPU (quad core)
    SQL 2005 SP3 32GB RAM
    Max Degree of Parallelism set to 8
    OS Windows 2003 sp2 on a RAID 1, RAID 10 log files, DAS unit as a RAID 5 carved into three luns in which one of the luns holds the tempdb, the other two luns hold the data files. We also have another RAID 5 available for use but is currently not configured for anything.
    Does changing the MAXDOP to 8 effect how many files should be created for the tempdb. We originally created 16 files for the tempdb and I am wondering if we should back that down to 8?
    Thanks
  2. satya Moderator

    I believe I have referred this somewhere in Technet that, by default SQL Server creates a logical scheduler for each of the CPUs presented to it (logical or physical).
    There is a reason to have multi file for TEMPDB that will allow each of the logical schedulers to loosely align with a file. Since you can only have 1 active worker per scheduler this allows each worker to have its own tempdb file (at that instant) and avoid allocation contention. I also believe that 8 is not a limit for TEMPDB and it is a general recommendation for better optimization.
  3. rohit2900 Member

    I've a small confusion on this...
    Satya... Does creating more than one tempdb file on the same physical drive gives any performance improvement??
  4. Luis Martin Moderator

    [quote user="rohit2900"]
    I've a small confusion on this...
    Satya... Does creating more than one tempdb file on the same physical drive gives any performance improvement??
    [/quote]
    I'm more confused than you.
    MAXDOP: The max degree of parallelism option limits the number of processors to use in parallel plan execution.
    Also Rohit question is valid. More tempdb files in the same drive?. Why?
  5. ghemant Moderator

    You may refer this article for further reading http://blogs.msdn.com/sqlserverstorageengine/archive/2009/01/04/managing-tempdb-in-sql-server-tempdb-configuration.aspx
    IMO...The reason behind splitting tempdb into multiple files is to over come the allocation bottleneck issue, but yes, it would boost performance for tempdb if its on the same physical array... with Max Degree of Parallelism set to 8OP has forced engine to use max 8 cpus parallel.
    But let's wait for Satya's reply here
  6. ndinakar Member

    MAXDOP affects only queries.. not the files in TempDb. One of the best practice recommendations for tempdb is to create as many files as there are logical processors. I cant remember who, but someone did a study around this and measured numbers and concluded that beyong 8 files, there is a decrease in ROI..
  7. rohit2900 Member

    [quote user="ghemant"]
    http://blogs.msdn.com/sqlserverstor...empdb-in-sql-server-tempdb-configuration.aspx
    [/quote]
    In the above link it's referring about "contention on SGAM page". what exactly this mean??
    [quote user="ndinakar"]
    One of the best practice recommendations for tempdb is to create as many files as there are logical processors.
    [/quote]
    Is that so... I mean if the number of tempdb files are equel to the total no of processors one is seeing in the task manager. it will give performance improvement, irrespective of the fact whether it's on the same physical drive or not. Does it applies to SQL Server 2000 also???
    [quote user="ndinakar"]
    there is a decrease in ROI..
    [/quote]
    What is ROI stands for???
  8. rohit2900 Member

  9. ghemant Moderator

  10. satya Moderator

    Dinaker, IT was Bob Dorr.
    One of the MAXDOP index option will have affect on TEMPDB if you specify SORT_IN_TEMPDB and in any case SQL 2005 has heavy usage of TEMPDB...

Share This Page