• TheSQLGuru (12/25/2012)


    opc.three (12/24/2012)


    It should say "physical cores" instead of "physical processors." I would set it to 6 for the number of physical cores per NUMA node. If you're still seeing too much parallelism for your taste look into increasing the Cost Threshold for Parallelism.

    1) physical cores is the MAX you should EVER set MAXDOP on NUMA hardware. Don't leave it at zero either.

    I agree 100%, the documentation should be updated to say:

    MS article http://support.microsoft.com/kb/329204 says

    •For servers that use more than eight processors, use the following configuration: MAXDOP=8.

    •For servers that have eight or less processors, use the following configuration where N equals the number of processors: MAXDOP=0 to N.

    •For servers that have NUMA configured, MAXDOP should not exceed the number of CPUs physical cores that are assigned to each NUMA node.

    •For servers that have hyper-threading enabled, the MAXDOP value should not exceed the number of physical processors cores

    2) if you can, test your server with Hyperthreading disabled, especially it if is a data warehouse box (or possibly mixed-use server).

    I have had varied results with disabling Hyperthreading. One experiment with a DW workload on Server 2003 running SQL 2005 did not make one bit of difference. IN other cases it helped performance, in some it hurt. Specific cases are anecdotal at best so it is important to test with your workload. General consensus seems to be to disable it by default if you do not have the time to invest. If you want to know for sure the right way to go test, test, test.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato