I have a SQL Server 2005 instance running on a production server. CPU load is relatively low in part thanks to some recent improvements in the code. I'm trying to work out if I could safely reduce the number of CPUs in the machine and would like to test that out before actually getting them removed.

Is it possible to tell SQL Server 2005 to just use say 4 of the 8 CPUs in the machine to test this out?

Thanks, Robin


Yes - just set the processor affinity accordingly. The net has a lot of fancy coloured walkthroughs. Note that the Express Edition only ever uses a single thread, so it is hard-limited to using 1 CPU already.

  • thanks, so unchecking processor affinity basically stops sql server using that processor? This was something a few sites seemed to be alluding to but wasn't made completely clear (to me anyway) – Robin Feb 17 '12 at 13:11
  • Yes. The idea behind all this is to prevent SQL server thread execution on CPUs where other loads greatly benefit from CPU caching and thus reduce performance-impairing cache thrashing. – the-wabbit Feb 17 '12 at 13:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.