I'm staggering about in the dark here, because i don't know what i'm doing......
We have a 7 year old SBS 2008 server running SQL Server 2008 and Exchange 2007. The server is dual Xeon quad core with 12G ram. So the server thinks it's got 16 cores. I've had a couple of previous questions in here trying to track down the cause of the poor server/network performance we have been seeing in the past few months. The only thing which has changed recently is that I have migrated an MsAccess database back-end into the SQL Server. We have 8 users of the database who now access the SQL Server.
Yesterday I rebooted the server and found that everything was much, much faster afterwards until someone opened their database application and started to untilize the SQL Server. At that point everything slowed down again. So I made the guess that maybe our problems were related to the SQL Server waking up and grabbing the server resources.
In the SQL Server Processor properties I saw that it was configured to automatically set the Processor Affinity Mask and I/O Affinity Mask for all processors. I changed it so that the Processor Affinity is ticked on 2 CPU's and the I/O affinity is ticked on 2 CPU's, leaving the other 12 CPU's available for other things.
This has speeded things up massively, and we don't notice our database application running any slower because of this change.
I will welcome any comments about this, but I have 2 specific questions.....
1. How can it be that by releasing 12 CPU cores, the server has speeded up 10 fold, when previously the System Idle Process was mostly at 98/99% anyway? I would have thought that with that tiny anount of CPU utilization, the other server processes should have been able to run seamlessly. We're only a small business. This server has a pretty quiet life.
2. Please look at the attached screenshot. Have I got the Affinity flags set correctly? Could it be done any better? For instance, would 3 x 1 or 1 x 3, be better than 2 x 2 that I currently have configured?