SQL Server Performance Forum – Threads Archive
SQL Server using 2 processors out of 4
We have a Compaq DL580 with 4 processors. For licensing cost reasons, we have started using the machine with SQL Server using only 2 of the 4 processors. Hyperthreading is in use. So, the logical processor numbers are 0,1,2,3,4,5,6,7. We have SQL Server configured to use logical processors 2,3,6,7. Those 4 logicals make up 2 entire physical CPUs. Anyway, in monitoring the individual processors, it looks as though logical CPUs 2 and 6 are working twice as hard as processors 3 and 7. Procesors 2 and 6 are averaging about 50% with periods of pegging at 100%. Processor queue length goes up at these times, but not to dangerous levels (2 – 5). Processors 3 and 7 average approx the 25%. It seems to be running ok right now. But if usage increases and the processors continue this same trend, Logical CPUs 2 and 6 will be maxed out before CPUs 3 and 7. And I’m afraid processor queue length wil start to degrade performance. We thought the individual processor usage would be more equitable. Does anyone have input on how to address this? ThanksWhat OS and SQL version do you have installed?
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
Windows 2003 Enterprise. SQL 2000 Enterprise with SP3a.
Capture the PERFMON (SYSMON) with the following counters: • Processor –> % Processor Time
• Process –> % Processor Time (all processes)
• Thread –> % Processor Time (all threads of the SQL Server Instance)
• SQL Server –> SQL Statistics/SQL Compilations/sec
• SQL Server –> SQL Statistics/SQL Re-Compilations/sec
• Server Work Queues –> Queue Length The next step is to verify if the SQL Server process is the main consumer of this CPU utilization. Check the Process –> % Processor Time counter of the SQL Server instance. If you have high CPU values for the system and low values for the SQL Server process, this indicates that some other process is causing the high CPU utilization. Although the System Monitor will give a good idea where the problem lies, to pinpoint exactly what queries are CPU bound or what type of queries are getting compiled/recompiled often, you usually need profiler traces to investigate further. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
When you say "For licensing cost reasons" do you mean you only want to pay for 2 sql processor licenses? As far as I know you still have to pay for 4 even if you limit SQL Server to 2 since there are many functions within sql server that are OS dependent and will still utilize all 4 processors. To only pay for 2 you would have to physically remove 2 processors.
Argyle is right. You have to pay for all processors physically located on the server, or you have to go with a CAL licensing model. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
Satya,
We’re quite sure that SQL Server is the main consumer of CPU. There are no other significant applications on the server. And we have verified that the insignificant apps (BMC Patrol, Veritas NetBackup) do not consume much CPU at all. Server Work Queues –> Queue Length is 0 for all CPUs that we have SQL Server designated for. We have looked at recompiles in the past, and they have not been significant (much less than 1 per sec). But we cannot see recompile figures currently because the SQL Server performance counters have disappeared (which is another topic altogether – uuuugggghhhhh!) The issue is the inequity with which SQL Server is using the processors is still the main concern. And now at issue is that we did not realize that what argyle and derrick are saying is the case about licensing. Thanks
For the missing performance counters just search msdn.microsoft.com for "missing performance counters". There’s a fix for it. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
]]>