SQL Server 2005 preferring a single core per cpu?

Question

HI folks,

I've a SQL Server 2005 system, with 4 cpus in it each with 4 cores. It's currently mainly utilising just 4 cores, ie 4 hover around 90% utilisation at peak and the others closer to 30%. I'm assuming the 4 cores are one from each cpu although I'm not certain. Is this the expected behaviour does anyone know? Will increased load be distributed onto the additional cores rather than further loading onto the current 4. Is this indicative of some other issue/s I should investigate?

At peak load I'd expect in excess of 40 connections simultaneously at least, and most if not all of them will be set to allow dirty reads. For this reason I don't think it's anything to do with the work load arriving at SQL Server rather that it's how it chooses to use the cpu resources it has available.

thanks,

Robin

Answer 1

SQL automatically manages the CPU usage as it sees fit, depending on maximum degree of parallelism, and parallelism cost threshold, both available in the advanced options for sp_configure (see Books Online).

You might want to look at those settings, and possibly review the SQL scripts themselves.

Answer 2

SQL does not have the means to manage the thread allocation to physical cores. That is a priviledge only the OS has. SQL does not enforce affinity masks (short of the global configurable SQL wide affinity mask to limit the number of CPUs SQL sees). The behavior you see is ultimately driven by the motherboard drivers, CPU drivers and the core OS. I have seen similar behavior either associated with multi-threading or associated with driver problems.

The ideal behavior is to have all cores share an equal CPU load, perhaps one having more kernel load (red line) due to handling certain hardware interupts (network).

Of course I assume your SQL load can be spread out accross CPUs, there are some very unusual loads like having very few requests but very intense in computation that cnanot be spread out, but, again, those are highly unusual in SQL context.