To retrieve the HammerDB samples SQL transaction rates, run the following query:
SELECT @cntr_value = cntr_value,
FROM sys.dm_os_performance_counters WITH (NOLOCK)
WHERE counter_name = 'Batch Requests/sec'
The same views and predicates are used to capture and aggregate the TPM values of all the SQL MI instances using the internal test suite developed for these tests. The goal is to monitor the full multi-instance Azure Arc-enabled data services SQL MI deployment – capturing TPM results for each instance with the same workload as more SQL MI instances were added. The monitoring approach used leveraged Grafana to analyze performance at the individual SQL MI layer and Azure Monitor Container Insights to analyze performance at the Kubernetes layer.
At the same time, the aggregated HammerDB virtual user count was captured from all the running instances. The system session SPIDs were removed from the total user count along with removing the running reporting SPIDs.
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
OPTION (RECOMPILE);
