Open chadbaldwin opened 3 weeks ago
UPDATE: I guess I've got blinders on and I have been looking at this problem for too long. I don't think there is any need to look at process_physical_affinity
. As it seems when virtual_machine_type_desc
is NONE
then the number of sections in process_physical_affinity
is equal to the socket count.
Here from SQL Server Community Slack chat: https://app.slack.com/client/T1LTZ0BQV/C1MS1RA4B
Note: I am not currently a SQLWATCH user, but while doing research I found the query being used by SQLWATCH. Leaving this issue here by request of @marcingminski
I came across a bug regarding this commonly used query: https://github.com/marcingminski/sqlwatch/blob/1b3543c290f327a7a918aab8c60a8826ec371cba/SqlWatch.Monitor/Project.SqlWatch.Database/dbo/Procedures/usp_sqlwatch_logger_performance.sql#L62-L79
(Also used in dbadash and other tools/scripts).
It seems there is an issue when running this query against non-hypervisor instances. The
ProcessUtilization
value does not appear to be 100-based and I have not yet determined what it actually is based on. Just working on theory at this point. The value starts at 0, like normal, but as SQL load increases, the value quickly surpasses where you would expect it to be.For example, I have an instance with the following stats:
When I run the linked query above, I get:
My theory is that it might be multiplied by the number of sections in
process_physical_affinity
, but that is a complete guess for now.One solution I have considered is to just make the query "less bad". For example, if
virtual_machine_type_desc
isNONE
then do not useProcessUtilization
, instead use100-SystemIdle
, asSystemIdle
does seem to reliably be 100-based. Unfortunately this lumps OS load into there, but that seems better than having nothing or something completely wrong.