Wednesday, March 7, 2012

Only one CPU is used by the server

How can I utilize all four CPU′s by the SQL server?


HPDL385 2 x Intel Xenon 5160 3 GHz Dual Core, 4 GB Ram

Windows Server 2005

SQL Server 2005 Standard Edition

When running heavy SQL SP′s, the CPU load is only 25% nicely distributed over the four CPU′s as if only 1 CPU was present.

Is it the operating system that creates this problem or the SQL server?

How does the SQL server react if the license somehow is configured wrong - is this the problem?


Is because use a CPU per query.

How do you test? with a one query or several paralell queries?


I test with two paralell queries.

When the second query is fired, the load remains the same.


This is the normal behavoir, where SQL Server 2005 will try to spread the load evenly among the four processors. You can experiment by appending OPTION MAXDOP(1) to the end of a query, then running it in a query window in SSMS and turning on the graphical execution plan. Then appending OPTION MAXDOP(4) to the end of a query, then running it to see if the query uses parallism.

Quite often, for OLTP workloads, you will be better off with MAXDOP(1).

|||You could set the processor affinity so sql server uses only one cpu and watch what happens, then allow it to use more and see the difference.

(i think multi cores are handled as physical cpu's but prob best to check it out)


No comments:

Post a Comment