CPU Graph Not Showing

[AhmadMansourAwaed]

Hello David ,

Thanks for the wonderful tool , we are using it for almost 1 year .
I wanted to report something that :

CPU graph suddenly not showing for first node only however it's working fine with 2nd node

not working in SQL 1

working in SQL 2

we are using latest : DBADash_3.15.0

Both nodes have the same configuration : 25 Core , 50 Logical CPU

I am curious to investigate the issue for this strange behavior

[DavidWiseman]

Hi, I think this might be related to #1144. Can you run this query and let me know what it returns? Do you see values over 100?

Is the server physical or virtual? If it's virtual, what type of hypervisor or if it's running in the cloud, what cloud provider and instance type? How many physical CPUs?

Thanks

[AhmadMansourAwaed]

Hello David ,
Yes , it's VM on Oracle cloud

i got this while running the SQLCPU query

Thanks for your quick response as well :)

[DavidWiseman]

The application passes in the @TOP parameter - the script needs to be edited slightly. Can you run this:

DECLARE @TOP INT = 120
DECLARE @ts_now bigint 
SELECT @ts_now= cpu_ticks/(cpu_ticks/ms_ticks) 
FROM sys.dm_os_sys_info; 

SELECT TOP(@TOP) DATEADD(ms, -1 * (@ts_now - [timestamp]), GETUTCDATE()) AS [EventTime],
			SQLProcessUtilization AS [SQLProcessCPU], 
				 SystemIdle AS [SystemIdleProcess] 
FROM (SELECT record.value('(./Record/@id)[1]', 'int') AS record_id, 
		record.value('(./Record//SystemHealth/SystemIdle)[1]', 'int') 
		AS [SystemIdle], 
		record.value('(./Record//SystemHealth/ProcessUtilization)[1]', 'int') 
		AS [SQLProcessUtilization], [timestamp] 
		FROM (SELECT [timestamp], CONVERT(xml, record) AS [record] 
		FROM sys.dm_os_ring_buffers WITH (NOLOCK)
		WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' 
		AND record LIKE N'%<SystemHealth>%') AS x) AS y 
ORDER BY record_id DESC OPTION (RECOMPILE);

If you can take a screenshot of this information from the hardware tab in DBA Dash it might be useful

It looks like it might be a 2 socket system. It's possible that the CPU utilization for the server is reporting up to 200% (double counting for each socket) instead of 100% and DBA Dash currently excludes values over 100%. I'm not sure how to handle this as I've never seen the problem myself. It's not as simple as dividing by the number of sockets as I have some two socket systems that don't exhibit this behaviour.

The more information I have the better. The results from the query will confirm if the utilization is reporting over 100% or if this is a different problem.

Thanks

[AhmadMansourAwaed]

Hi David ,

SQLCPU.xlsx

Also i am upload a sheet for the output

[AhmadMansourAwaed]

Bear in mind that it's work for one node and not working for the other one however both of them are same configuration :)

node 1

node 2

[DavidWiseman]

It looks like DPA fixed the issue dividing the SQL process utilization by the number of CPUs where the idle and CPU process are adding up to more than 100%.

I can apply a similar fix. I think this will work:

DECLARE @TOP INT = 120

DECLARE @ts_now BIGINT 
DECLARE @PhysicalCPUs INT

SELECT	@ts_now= cpu_ticks/(cpu_ticks/ms_ticks),
		@PhysicalCPUs = cpu_count / hyperthread_ratio
FROM sys.dm_os_sys_info; 

SELECT TOP(@TOP) DATEADD(ms, -1 * (@ts_now - RB.timestamp), GETUTCDATE()) AS EventTime,
				CASE WHEN RB.SQLProcessUtilization+RB.SystemIdle>100 THEN RB.SQLProcessUtilization / @PhysicalCPUs ELSE RB.SQLProcessUtilization END AS SQLProcessCPU, 
				RB.SystemIdle AS [SystemIdleProcess] 
FROM (SELECT	record.value('(./Record/@id)[1]', 'int') AS record_id, 
				record.value('(./Record//SystemHealth/SystemIdle)[1]', 'int') AS SystemIdle, 
				record.value('(./Record//SystemHealth/ProcessUtilization)[1]', 'int') AS SQLProcessUtilization, 
				timestamp 
		FROM (SELECT [timestamp], CONVERT(xml, record) AS [record] 
		FROM sys.dm_os_ring_buffers WITH (NOLOCK)
		WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' 
		AND record LIKE N'%<SystemHealth>%') AS x) AS RB 
ORDER BY RB.record_id DESC 
OPTION (RECOMPILE);

If you can try it out and let me know if it appears to be working. The system idle and SQL process shouldn't exceed 100 with any remainder associated with non-SQL Server processes.

[AhmadMansourAwaed]

this is how it looks like with new query

[DavidWiseman]

Those numbers are looking about right now - adding up to 100 or almost 100, but never exceeding it. Most of the CPU consumption is SQL with a very small amount of CPU consumed by other processes.

[AhmadMansourAwaed]

yes the excel file which i uploaded earlier if you it was exceeding the 100 %

with the new script you provided to me it's equal to 100% which is more realistic

[chadbaldwin]

@AhmadMansourAwaed I'm just a curious bystander. What do you get for this query:

SELECT virtual_machine_type_desc FROM sys.dm_os_sys_info

??

[DavidWiseman]

In addition to the query above, it would also be useful to know what this returns on each of the instances.

/* Get number of numa nodes for the SQL instance */
SELECT COUNT(*) 
FROM sys.dm_os_memory_nodes 
WHERE memory_node_id <> 64

[AhmadMansourAwaed]

SELECT virtual_machine_type_desc FROM sys.dm_os_sys_info

[AhmadMansourAwaed]

/* Get number of numa nodes for the SQL instance /
SELECT COUNT()
FROM sys.dm_os_memory_nodes
WHERE memory_node_id <> 64