March 29, 2017 at 1:57 am
Hi all
I've been doing a bit of a check on some our servers and found something odd in the logs.
I've come across this message on a few servers:-
SQL Server detected 16 sockets with 1 cores per socket and 1 logical processors per socket, 16 total logical processors; using 4 logical processors based on SQL Server licensing. This is an informational message; no user action is required.
Does this mean we're only using a quarter of what's available or am I reading it wrong?
If I am reading it correctly, how can I correct the issue so SQL uses everything available?
These machines are mostly VMs (with a few physical boxes thrown in for good measure) if that helps.
March 29, 2017 at 3:34 am
You're using 4 out of the 16 cores. Probably because that's SQL Server Express, which is limited to 4 cores.
What's the very first (oldest) entry in the error log?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 29, 2017 at 4:03 am
Hi Gail
Thanks for that.
Does that mean we have (potentially) 12 cores sitting idle?
One of the SQL instances showing that error is :-
Microsoft SQL Server 2012 (SP3) Business Intelligence Edition
The oldest entry with that message is the 4th of January this year (must have been after a server reboot).
It's in Archive #6 (I assume that's what you meant by oldest?).
I've exported that log to a text file in case you wanted anything else from it.
March 29, 2017 at 4:48 am
Yes, you have 12 cores idle.
The problem is in the number of sockets. For some reason, the VM host is exposing the cores to the VM as 16 single-core processors, and BI edition's CPU limit is:
"Limited to lesser of 4 Sockets or 16 cores" and the VM thinks (because of the settings) that it has 16 sockets, each with a single core, and hence the lesser of "4 Sockets or 16 cores" is 4 sockets of 1 core each, hence 4 cores.
Speak to the VM admin, see why there's such an odd setting. The cores per socket should match the physical hardware
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 29, 2017 at 5:06 am
Another thing I'd add is that's potentially a lot of cores for a VM. In a SQL Server VM environment you really, really want to avoid drastic over provisioning like the plague. What's your CPU use on this? If you have low use, over provisioning and many cores your CPU ready time and performance is really going to suck - maybe not now, but at some point.
Check your CPU use and get your net admin to check the CPU ready time.
Give me a shout if you don't understand any of these concepts and want me to drone on / provide links.
cheers
Andrew
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
March 29, 2017 at 5:09 am
andrew gothard - Wednesday, March 29, 2017 5:06 AMIf you have low use, over provisioning and many cores your CPU ready time and performance is really going to suck - maybe not now, but at some point.
Depends what else the VM is sharing the host with.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 29, 2017 at 5:12 am
The following query will give you a few hours worth of CPU utilization from the DM views
DECLARE @ts_now BIGINT
SELECT @ts_now = cpu_ticks / (cpu_ticks/ms_ticks)
FROM sys.dm_os_sys_info;
SELECT record_id,
DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS EventTime,
SQLProcessUtilization,
SystemIdle,
100 - SystemIdle - SQLProcessUtilization AS OtherProcessUtilization
FROM
(
SELECT
record.value('(./Record/@id)[1]', 'int') AS record_id,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS SystemIdle,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS SQLProcessUtilization,
TIMESTAMP
FROM (
SELECT TIMESTAMP, CONVERT(XML, record) AS record
FROM sys.dm_os_ring_buffers
WHERE
ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
AND
record LIKE '% %'
) AS x
) AS y
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
March 29, 2017 at 5:20 am
GilaMonster - Wednesday, March 29, 2017 5:09 AMandrew gothard - Wednesday, March 29, 2017 5:06 AMIf you have low use, over provisioning and many cores your CPU ready time and performance is really going to suck - maybe not now, but at some point.Depends what else the VM is sharing the host with.
Quite true, but if the host is at all busy it's going to get nasty. Highish utilization (as long as you're not running at 90 - maxed out all the time, obviously) is generally the way to go. Also, with VMware (no idea about HyperV, we don't use it on our SQL Server infrastructure) if you're under provisioned and need more cores, that's online. If over provisioning is killing your performance, removing cores requires a reboot to take effect.
In terms of what it's sharing with - I really like, and I mean really, really like if your infrastructure can work with it - SQL Server dedicated infrastructure where possible. SQL Server and other stuff (app / file server) doesn't play nicely in a lot of cases - although with some stuff you're going to find SQL Server and app server on the same VM is your best call.
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
March 29, 2017 at 6:03 am
We've got quite a bit of CPU idle at the moment as the server isn't in full use as yet.
We're expecting CPU usage to increase as more stuff is put onto the instance and more people start using it.
This still doesn't answer the question as to why the instance is only recognising 4 out of 16 cores.
March 29, 2017 at 6:05 am
richardmgreen1 - Wednesday, March 29, 2017 6:03 AMThis still doesn't answer the question as to why the instance is only recognising 4 out of 16 cores.
Um...
GilaMonster - Wednesday, March 29, 2017 4:48 AMThe problem is in the number of sockets. For some reason, the VM host is exposing the cores to the VM as 16 single-core processors, and BI edition's CPU limit is:
"Limited to lesser of 4 Sockets or 16 cores" and the VM thinks (because of the settings) that it has 16 sockets, each with a single core, and hence the lesser of "4 Sockets or 16 cores" is 4 sockets of 1 core each, hence 4 cores.Speak to the VM admin, see why there's such an odd setting. The cores per socket should match the physical hardware
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 29, 2017 at 6:18 am
Sorry Gail, missed your reply.:blush:
I'll speak to our server team to see if they can alter the settings.
April 25, 2017 at 2:11 am
Hi Gail
Have you got a link to where I can find this infor for other versions please?
April 25, 2017 at 2:22 am
I'm guessing you're after this: "Features supported by the Editions of SQL Server 2012" (I've linked to the server scale section which deals with your first query, but there's a lot of other useful stuff there.)
Thomas Rushton
blog: https://thelonedba.wordpress.com
April 25, 2017 at 2:22 am
Google for "SQL Server edition hardware limits" and you should get the comparison page (it's what I do any time I need it)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 25, 2017 at 2:29 am
Thanks both
Just one more question....
Will it be easier/better (in order to use all the cores) to:-
1) sort out the cores on the VM (and give each socket 4 cores)?
2) upgrde the licence to enterprise?
If it's (2), is it just a case of changing the licence key?
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply