search

microsoft / SqlNexus

SQL Nexus is a tool that helps you identify the root cause of SQL Server performance issues. It loads and analyzes performance data collected by SQL LogScout, SQLDiag or PSSDiag. It can dramatically reduce the amount of time you spend manually analyzing data.
MIT License
356 stars 101 forks source link

SQL Nexus - Bottleneck analysis showing 100% cpu usage when it's not the case #126

Closed olivieraupetit closed 1 year ago

olivieraupetit commented 2 years ago

In SQL Server Bottle neck analysis, the graph showing System CPU Utilization displays 100% CPU for sql server cpu utilisation (red bars)

nexusgraph

But checking BLG files we can see total CPU usage is far from 100% (40% on average)

perfmonview

and the same values are also reported in Perfmon Summary

nexusperfmonsummary

Note: we have 32 processors on the machine where PSSDIAG was run

PiJoCoder commented 2 years ago

Thanks @olivieraupetit . The issue is with how sys.dm_os_ring_buffers sometimes incorrectly reports the CPU usage coming from SQL Server. There are several issues with this DMV and it is why this DMV is no longer supported. In the future someone may be able to change this report to use the Perfmon counters instead of the SYS.DM_OS_RING_BUFFERS.

PiJoCoder commented 1 year ago

@olivieraupetit , this should be now addressed with the latest release 7.22.11.11. Fixed in https://github.com/microsoft/SqlNexus/issues/193