November 1, 2008 at 1:20 am
I would like to know what is the difference between setting affinity mask and configuring max degree of parallelism on sql server.. Please advice on the scenarios in which these 2 become handy.
Thanks in advance!!!!
November 1, 2008 at 12:34 pm
Hi,
With affinity mask you can specify that a SQL Server instance should only use a subset of your processors. Let's say you have 8, you could set an affinity mask so that a particular SQL Server instance would use only 0-3. This is useful if you need to balance your resources between SQL Server and something else, or two SQL Server instances on the same server.
Max Degree of Parallelism (server side setting) and MAXDOP (query hint) stipulates how many processors a single query can run on. So again, let's say you have 8 processors and a query that the optimizer parallelizes on something like 4 to 12 threads. If your MAXDOP is set to default (meaning 8) those threads can run on all processors, if 2 only on 2 processors.
Max Degree of Parallelism should be set equal to number of physical processors; see http://support.microsoft.com/kb/329204, "General guidelines to use to configure the MAXDOP option" (and anything other than 1 might not work well in a pure OLTP scenario)
HTH!
/Elisabeth
elisabeth@sqlserverland.com
MCITP | MCT
http://sqlblog.com/blogs/elisabeth_redei/
http://linkedin.com/in/elisabethredei
November 1, 2008 at 12:39 pm
Thanks a lot.. that was really informative.. 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply