After researching HammerDB internals and documentation, we confirmed that the HammerDB application samples SQL transaction rates with this counter:
SELECT @cntr_value = cntr_value,
FROM sys.dm_os_performance_counters WITH (NOLOCK)
WHERE counter_name = 'Batch Requests/sec'
We used the same DMV and WHERE predicates to capture TPM with our T-SQL logging solution. Our goal was to monitor the full multi-instance Azure Arc-enabled SQL MI deployment – capturing TPM results for each instance with the same workload – as more SQL MI instances were added. We used Grafana as our monitoring approach to analyze performance at the individual SQL MI layer and Azure Monitor Container Insights to analyze performance at the AKS-HCI layer.
We captured the user count at the same time as the batch/request/sec capture. We also removed system session SPIDs from our total user count along with removing the executing reporting SPID.
SELECT @UserCount = COUNT(es.session_id)
FROM sys.dm_exec_sessions AS es WITH (NOLOCK)
CROSS APPLY sys.dm_exec_input_buffer(es.session_id, NULL) AS ib
WHERE es.database_id = DB_ID(@DatabaseName)
AND es.session_id > 50
AND es.session_id <> @@SPID