none
what are the major differene between affinity mask and max degree of parallelism in 2005 sql server properties

    Question

  • hi every one let me know the major difference between affinity mask and maxdop in server properties in sql 2005.i m un able to get a clarity on this topics

    so please make me clarified in these topics

    i hope someone will respond for this small topic. every respond will appreciable 

    thanking you all

     

    Friday, July 08, 2011 6:09 AM

Answers

  • Affinity mask is instance level configuration

    The affinity mask configuration option defined in the sp_configure stored procedure allows you to specify which CPUs on a multiprocessor computer are to be used to run threads from an instance of SQL Server.

    Degree of Parallelism is Query level configuration
    The Max Degree of Parallelism or MAXDOP is a configuration indicating how the SQL Server optimizer will use the CPUs.  This is a server wide configuration that by default uses all of the CPUs to have the available portions of the query executed in parallel.

    hopes this helps you


    http://uk.linkedin.com/in/ramjaddu
    • Proposed as answer by Siva1983 Friday, July 08, 2011 9:42 AM
    • Marked as answer by chetan.kt Friday, July 08, 2011 7:10 PM
    Friday, July 08, 2011 8:05 AM
  • Hi Chetan,

    If you have Multiple physical processors or logical Cores in your machine .It is very easy to understand the benefit of Afinitymask.For example u have 4 cores and 2 instances in your machine then ,u can share processors for each instance like 2 cores per instance.(for better resource balance).

     

    Maxdop is for query execution purpose. By default it will be on for all processors.(All processors will work on one  query execution).You can change this behavior for particular query like alter index .for ex if u give maxdop =2 while executing query ,2 processors will  dedicated for this query execution where as other processors  can handle server work load.

    Hope u got the idea.

    Refer below links for details.

    http://msdn.microsoft.com/en-us/library/ms187104%28v=SQL.90%29.aspx

    http://msdn.microsoft.com/en-us/library/ms188611%28v=SQL.90%29.aspx


    Sivaprasad.L Together We can Achieve
    • Proposed as answer by Satya SKJMVP Friday, July 08, 2011 12:14 PM
    • Marked as answer by chetan.kt Friday, July 08, 2011 7:10 PM
    Friday, July 08, 2011 9:41 AM

All replies

  • Affinity mask is instance level configuration

    The affinity mask configuration option defined in the sp_configure stored procedure allows you to specify which CPUs on a multiprocessor computer are to be used to run threads from an instance of SQL Server.

    Degree of Parallelism is Query level configuration
    The Max Degree of Parallelism or MAXDOP is a configuration indicating how the SQL Server optimizer will use the CPUs.  This is a server wide configuration that by default uses all of the CPUs to have the available portions of the query executed in parallel.

    hopes this helps you


    http://uk.linkedin.com/in/ramjaddu
    • Proposed as answer by Siva1983 Friday, July 08, 2011 9:42 AM
    • Marked as answer by chetan.kt Friday, July 08, 2011 7:10 PM
    Friday, July 08, 2011 8:05 AM
  • Hi Chetan,

    If you have Multiple physical processors or logical Cores in your machine .It is very easy to understand the benefit of Afinitymask.For example u have 4 cores and 2 instances in your machine then ,u can share processors for each instance like 2 cores per instance.(for better resource balance).

     

    Maxdop is for query execution purpose. By default it will be on for all processors.(All processors will work on one  query execution).You can change this behavior for particular query like alter index .for ex if u give maxdop =2 while executing query ,2 processors will  dedicated for this query execution where as other processors  can handle server work load.

    Hope u got the idea.

    Refer below links for details.

    http://msdn.microsoft.com/en-us/library/ms187104%28v=SQL.90%29.aspx

    http://msdn.microsoft.com/en-us/library/ms188611%28v=SQL.90%29.aspx


    Sivaprasad.L Together We can Achieve
    • Proposed as answer by Satya SKJMVP Friday, July 08, 2011 12:14 PM
    • Marked as answer by chetan.kt Friday, July 08, 2011 7:10 PM
    Friday, July 08, 2011 9:41 AM
  • Just to add what Siva referred:

    Affinity MASK option is continued since SQL 2000 and BOL refers:  To carry out multitasking, Microsoft Windows 2000 and Windows Server 2003 sometimes move process threads among different processors. Although efficient from an operating system point of view, this activity can reduce SQL Server performance under heavy system loads, as each processor cache is repeatedly reloaded with data. Assigning processors to specific threads can improve performance under these conditions by eliminating processor reloads and reducing thread migration across processors (thereby reducing context switching); such an association between a thread and a processor is called processor affinity.

    Also this option will be removed from the future version of SQL Server as the hardware and operating system are efficient enough to tackle the performance load.


    Satya SKJ, SQL Server MVP [Knowledge Sharing Network - www.sqlserver-qa.net]
    Author of SQL Server 2008 R2 Administration cookbook.
    Follow me @sqlmaster.
    Friday, July 08, 2011 12:16 PM