Post reply

MAXDOP and Tempdb datafiles

  • August 21, 2012 at 11:23 am

    #265641

    I'm using SQL Server 2008R2 Enterprise with Sharepoint Server 2010. Adding tempdb data files is one of the suggested optimizations to make for the DB. This is done by dividing the number of processor cores by 4. At least that's one way of doing it. Another suggested setting, if SQL is dedicated to running only SharePoint server, is to set max degree of parallelism to 1. So I'm wondering how the maxdop setting affects the formula for determining the number of tempdb data files? Or am I on yet another fool's errand 🙂

    Thanks!

    Even as a mother protects with her life
    Her child, her only child,
    So with a boundless heart
    Should one cherish all living beings;

  • August 21, 2012 at 11:28 am

    #1528274

    It doesn't.

    Maxdop 1 is a sparepoint specific requirement, not something that should be done in other environments, and the number of TempDB files is not always 1 per 4 cores

    http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-%281230%29-tempdb-should-always-have-one-data-file-per-processor-core.aspx

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 2 posts - 1 through 1 (of 1 total)

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