Symptom: ======== You have several threads in your application running , but you observe that only One CPU Core is being utilized by all of your threads and you don't see any activity on the other CPU Cores. Questions: ========
Why do I don't see any activity on the other CPU cores? Why the other CPU Cores are sitting idle? Is this a problem? Is there a way to make my threads spread across all the CPU cores so that my application performs much better?
Generally, having an application interfere with the scheduler’s decisions by forcing threads onto different CPUs leads to worse overall behavior, not better. Generally, if the threads within a single process aren’t fully utilizing a processor, it’s best to keep them all on the same CPU because this increases the overall performance by reducing the overhead of address translation when threads touch memory. Basically, the CPUs have registers called Translation Lookaside Buffers (TLBs) which cache page tables for processes. When the threads from the same process run on the same CPU, then the TLB registers don’t have to be reloaded .This saves a lot of clock cycles. There is a threshold where below a certain CPU utilization, it’s faster to run the threads on the same core, and above which it’s better to run them on multiple cores. The scheduler tries to find this balance, but it does so taking into account all threads from all processes on the system.
Also Starting from Windows Server 2008 R2, systems which have low CPU utilization will use "core parking" to conserve energy. Basically, if the system has multiple cores, but only one is needed to handle the workload (as measured by utilization), the others can be powered down to save energy. They will then be powered up automatically when the workload increases so it’s very dynamic.
Some suggestions which may help here are:
a) Does your application faces any performance issue because of only one CPU being utilized heavily?
b) Is the processor completely saturated (i.e) Does the one processor always at nearly 100% utilization and still your Application is running slow?
If the answer to the above two questions are YES, then you need to isolate the specific component in your application [ Which API or a set of API's is causing this behavior?] and fix them.
Balajee P Windows SDK.
We have a box (8 cores) running Server 03 with SQL Server 2005 and I have noticed that SQL will camp out on one of the cores. I am surprised that SQL does not take advantage of all of the cores. For example, if there is a transaction running on a thread on one of the cores and a second transaction is started, I would think SQL could be more efficient if it spawned the new transaction on a core that is not busy. Am I off base in my thinking?
That is a curious finding. Internally SQL Server tracks the load factor on each "scheduler" and ensures that newer connections hit less loaded schedulers.
At a high level, all SQL Server does is expose as many threads as there are CPUs on the box, at a time, to the OS thread scheduling mechanism. There is no hard affinity unless you set the Affinity mask in the configuration options.
You may want to watch sql server threads to determine which thread is consuming processor time, determine its thread id from the sys.sysprocesses KPID column to determine which sql connections are causing this.
Developer Support - Database.
Thanks for the advice.