In order to answer this question you have to find out if your load is really CPU bounded. Your load is really CPU bounded if a number of runnable tasks per each scheduler always greater than 1 and all of your queries have correct plan. The latter statement is very important, your load can be CPU bounded due to the fact that somehow optimizer generated bad plan – it can happen if your statistics out of date or you tried to perform handcrafted optimization. In this case you don’t want to run to Circuit City to buy more CPUs right a way – you want to fix the plan. Here is the query to find out average length of a runable queue on the system:
status = 'VISIBLE ONLINE'
Buying more CPUs has also to do with capacity planning. You have to be very careful when performing capacity planning on hardware with HT enabled – remember you don’t have extra physical CPUs. Keep in mind that if your load runs at 60% CPU utilization - it doesn’t mean that you have 40% of extra CPU capacity. You will be very surprise how fast CPU load will jump from 60% to 80% and then even faster to 100% once you apply more and more load.
CAST (cpu_id as varbinary) AS scheduler_affinity_mask
CASE count( DISTINCT parent_node_id)
WHEN 1 THEN 'NUMA disabled'
ELSE 'NUMA enabled'
where parent_node_id <> 32
You can answer this question by looking at the work queue length for each scheduler. If on average such value is above 1 then you might benefit from adding more threads to the system but only if
A. Your load currently is not CPU bounded (See info above on how to find out if your load is CPU bound)
B. Your load currently doesn’t experience any other heavy waits (If you add more threads in this case they will just end up waiting as everyone else)
You can answer this question by monitoring length of I/O queues.
If over time they keep on growing or you are seeing periodic jumps or numbers stay relatively high most likely your system is I/O bound. In order to identify the cause you will have to dive further.