asked
SQL Server only detects 16 of 32 logical processors
My question for everyone is why does SQL Server 2008 on this new server we built only detect 16 of the 32 logical processors? We have an older 2 node cluster with 24 logical processors on Windows 2008 R2. That server has SQL Server 2008 SP4 Enterprise Edition and detects all 24 logical processors. Our server team built a 2 node cluster with physical servers (HP blades). We (DBA's) setup SQL Server and noticed the SQL Server 2008 instances only detect 16 of the 32 logical processors. The instances with SQL Server 2012 SP3 detect all 32 logical processors. The task manager shows 16 of the 32 processors with much more activity than the other 16. Rt-click the instance > properties > General > Processors: 16 Rt-click the instance > properties > Processors: All settings default on all instances. SELECT cpu_count FROM sys.dm_os_sys_info: 16 X:\MSSQL10.SQL4\MSSQL\Log\ERRORLOG shows: "2016-11-28 13:28:53.48 Server Detected 16 CPUs. This is an informational message; no user action is required." When I look at all the above mentioned items on any of the SQL 2012 instances it shows 32 processors. Physical server has 2 sockets/16 cores/32 logical processors 512 GB RAM O.S. Windows 2012 R2 5 instances of SQL Server 2008 SP4 Enterprise Edition. 3 instances of SQL Server 2012 SP3 Enterprise Edition. Any advice is greatly appreciated. If more info is need please let me know. Thanks!
I added an answer, which is awaiting approval. To add to that - ask your server team if they have changed the groupsize of the HP Machines (or if they come preconfigured with a groupsize value lower than 64).
Can you post the results of these - --Following query will confirm how many CPUs SQL Server is using - select scheduler_id,cpu_id, status, is_online from sys.dm_os_schedulers where status='VISIBLE ONLINE' --Following query lists how many CPUs a particular instance of SQL Server can see - select cpu_count from sys.dm_os_sys_info
Not sure why the results I pasted into the free text comment field keep disappearing... The results of the queries on one of the SQL Server 2008 SP4 instances show 16 CPU's 'VISIBLE ONLINE'. cpu_id values 0-15 The results of the queries on one of the SQL Server 2012 SP3 instances show 32 CPU's 'VISIBLE ONLINE'. cpu_id values 0-15 and 64 - 79. Maybe the cpu_id being higher than 63 has something to do with it? I read that SQL Server 2008 does not support more than 64 CPU's. There isn't more than 64 CPU's here, however maybe the cpu_id being higher than 63 is an issue?
From what I have read about how Windows addresses logical processors, it seems like the your CPUs somehow have come into different processor Groups. That's the only explaination I can find for the cpu_id having gaps between 15 and 64. But I can't for the world figure out why that would happen. Windows should fit all 32 logical processors into a single Processor Group. I read somewhere that Windows could leave room in the Processor Groups if the system has hot-add of CPUs enabled. But the same documentation states that Windows will try to *minimize* the number of Processor Groups. So I don't know which one takes precedence - leaving room for hot-added CPUs or minimizing number of Processor Groups. Not a complete answer to your question, but perhaps a push in a direction where you will be able to dig out more information from the Windows Server documentation. Oh, and yeah - SQL Server 2008 doesn't support Processor Groups and will therefore only be able to use one Processor Group, while SQL Server 2008R2 does have support for Processor Groups, which explains why SQL Server 2012 finds all logical processors and SQL Server 2008 doesn't.
Thanks for the input Magnus! That led us in the right direction. The processor groupsize/maxsize settings are all set to the default. We believed this was related to the NUMA configuration, and found a relevant BIOS setting that could be changed. We tested changing the BIOS setting from “Default – Clustered” to “Flat” on another server with matching hardware. This helped the 2008 instances detect all 32 logical processors. Although, HPE clearly indicates that systems will perform better with this setting set to Clustered, except for non-processor group aware multi-threaded applications, which usually perform better with the current setting. We have a mix of SQL 2008 and 2012 instances on this cluster. I don’t want sacrifice the performance on one version to help the other. We may look into upgrading the 2008 instances to 2008 R2. The app teams aren't ready for SQL 2012. For now we know what can been done if there are complaints about performance on SQL 2008 instances. That may happen as more databases are migrated there, we just don’t know yet. We aren't going to take any action at this time. Thanks for everyone's input!
I'm glad I was able to push your research in the right direction. As a bonus, I learned a lot about Processor Groups, as I couldn't let it go without reading some more about it :)
19 People are following this question.
