SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 affinity mask
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

CanadaDBA
Aged Yak Warrior

Canada
583 Posts

Posted - 01/20/2006 :  13:38:47  Show Profile  Reply with Quote
SQL Server 2k and Win2k3.

My production value for affinity mask is different than my test server.

        Min               Max               Config_Value          Run_Value
Test:       0          2147483647                 0                  0
Prod: -2147483648      2147483647                 0                  0

How can I set them identical? How can I set the Min value to -2147483648?

Thanks,

Canada DBA

tkizer
Almighty SQL Goddess

USA
37142 Posts

Posted - 01/20/2006 :  13:43:06  Show Profile  Visit tkizer's Homepage  Reply with Quote
You'll need to run sp_configure to modify the value. Check out sp_configure in BOL for details.

Tara Kizer
aka tduggan
Go to Top of Page

CanadaDBA
Aged Yak Warrior

Canada
583 Posts

Posted - 01/20/2006 :  14:13:01  Show Profile  Reply with Quote
It doesn't talk about how to set the value in Minimum column.

quote:
Originally posted by tkizer

You'll need to run sp_configure to modify the value. Check out sp_configure in BOL for details.

Tara Kizer
aka tduggan



Canada DBA
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37142 Posts

Posted - 01/20/2006 :  14:18:44  Show Profile  Visit tkizer's Homepage  Reply with Quote
Oops, you are right. I was thinking you were asking about changing the current value. I suspect that the minimum and maximum values can not be changed and that they are controlled by MS. The difference in your environments is probably due to an environmental difference, such as versions, hardware, etc...

Tara Kizer
aka tduggan
Go to Top of Page

CanadaDBA
Aged Yak Warrior

Canada
583 Posts

Posted - 01/20/2006 :  14:56:03  Show Profile  Reply with Quote
Good News!

The Minimum value is in spt_values in master database. I changed it and tested with sp_configure. Everything looks fine.

Canada DBA
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37142 Posts

Posted - 01/20/2006 :  15:06:42  Show Profile  Visit tkizer's Homepage  Reply with Quote
Wow, you've certainly got guts changing system data like that.

Tara Kizer
aka tduggan
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 01/20/2006 :  16:13:42  Show Profile  Reply with Quote
I guess you like doing things the hard way:

"How to configure the affinity mask (Enterprise Manager)
To configure the affinity mask
Expand a server group.
Right-click a server, and then click Properties.
Click the Processor tab.
Under Processor control, select one or more processors to assemble your affinity mask."


I hope your systems have identical hardware configurations. I would be inclined to follow the suggestion in BOL, and just leave it alone.

"Allocating Threads to a CPU
By default, each instance of Microsoft® SQL Server™ 2000 starts each thread, and then Microsoft Windows NT® or Windows® 2000 assigns each thread to a specific CPU. Windows NT or Windows 2000 distribute threads from instances of SQL Server evenly among the microprocessors, or CPUs on a computer. At times, Windows NT or Windows 2000 can also move a thread from one CPU with heavy usage to another CPU.

SQL Server administrators can use the affinity mask configuration option to exclude one or more CPUs from being eligible to run threads from a specific instance of SQL Server. The affinity mask value specifies a bit pattern that indicates the CPUs that are used to run threads from that instance of SQL Server. For example, the affinity mask value 13 represents the bit pattern 1101. On a computer with four CPUs, this indicates threads from that instance of SQL Server can be scheduled on CPUs 0, 2, and 3, but not on CPU 1. If affinity mask is specified, the instance of SQL Server allocates threads evenly among the CPUs that have not been masked off. Another effect of affinity mask is that Windows NT and Windows 2000 do not move threads from one CPU to another. affinity mask is rarely used; most systems get optimal performance by letting Windows NT or Windows 2000 schedule the threads among the available CPUs."





CODO ERGO SUM
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37142 Posts

Posted - 01/20/2006 :  16:17:03  Show Profile  Visit tkizer's Homepage  Reply with Quote
Michael,

He wasn't changing the current/running value of the affinity mask. He wanted the minimum to be the same on both systems. Changing the affinity mask from EM would change the current value, right?

Tara Kizer
aka tduggan
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000