Hi,
I want to report a bug I discovered. Some of the metrics in metricbeat.mssql.performance module did not return any values for me and I tried to debug this but did not find any useful logs from metricbeat itself. I turned on sql profile and managed to find that this is the sql statement it runs to collect the performance metrics:
SELECT object_name,
counter_name,
instance_name,
cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name = 'SQL Compilations/sec'
OR counter_name = 'SQL Re-Compilations/sec'
OR counter_name = 'User Connections'
OR counter_name = 'Page splits/sec'
OR ( counter_name = 'Lock Waits/sec'
AND instance_name = '_Total' )
OR counter_name = 'Page splits/sec'
OR ( object_name = 'SQLServer:Buffer Manager'
AND counter_name = 'Page life expectancy' )
OR counter_name = 'Batch Requests/sec'
OR ( counter_name = 'Buffer cache hit ratio'
AND object_name = 'SQLServer:Buffer Manager' )
OR ( counter_name = 'Target pages'
AND object_name = 'SQLServer:Buffer Manager' )
OR ( counter_name = 'Database pages'
AND object_name = 'SQLServer:Buffer Manager' )
OR ( counter_name = 'Checkpoint pages/sec'
AND object_name = 'SQLServer:Buffer Manager' )
OR ( counter_name = 'Lock Waits/sec'
AND instance_name = '_Total' )
OR ( counter_name = 'Transactions'
AND object_name = 'SQLServer:General Statistics' )
OR ( counter_name = 'Logins/sec'
AND object_name = 'SQLServer:General Statistics' )
OR ( counter_name = 'Logouts/sec'
AND object_name = 'SQLServer:General Statistics' )
OR ( counter_name = 'Connection Reset/sec'
AND object_name = 'SQLServer:General Statistics' )
OR ( counter_name = 'Active Temp Tables'
AND object_name = 'SQLServer:General Statistics' )
I noticed that for me, this statement did not return any results for the object_names that was prefixed with "SQLServer:". On my sql server all these counters have object_names beginning with "MSSQL$INSTANCE1:" instead of "SQLServer:". I have also checked on my other box where I only have one instance, the counter_name begins with "SQLServer:" though.
I didn't see any settings to configure this in the yml file instead so this might a bug/something hard coded that needs to be patched as other people might have the same issue.
FYI, my @@SERVICENAME is just "INSTANCE1" so the "MSSQL$" in the counter_name could be just some additional prefix from SQL Server that needs to be taken into account too. Not sure if it is the same in other editions of SQL Server but I am running this version where I had issue:
Microsoft SQL Server 2016 (SP2-CU5) (KB4475776) - 13.0.5264.1 (X64) .
Thanks.