Install the Prometheus Windows exporter from github the installer is in the Assets section near the bottom of the page
Install Prometheus scraper/database to your monitoring server/laptop via the appropriate installer
Point a browser to the prometheus server e.g. :9090
Add a new target, which will be the Windows exporter installed in step.
It will be something like <SQLSERVERIP>:9182/metrics
Ensure the Target shows “Green”
Check that we can scrape SQLserver tranactions. In the search/execute box enter something like this rate(windows_mssql_sqlstats_batch_requests[30s])*60
Put the SQLserver under load with something like HammerDB
Hit Execute on the Prometheus server search box and you should see a transaction rate similar to HammerDB
Install Grafana and Point it to the Prometheus server (See multiple examples of how to do this)
HammerDB on Linux driving load to Windows SQL Server
Often it’s nice to be able to drive Windows applications and databases from Linux, especially if you are more comfortable in a Unix environment. This post will show you how to drive a Microsoft SQL Server database running on a Windows server from a remote Linux machine. In this example I am using Ubuntu 22.04, SQLserver 2019, Windows 11 and HammerDB 4.4
In a recent experiment using Amazon RDS instance and a VM running in an on-prem Nutanix cluster, both using Skylake class processors with similar clock speeds and vCPU count. The SQLServer database on Nutanix delivered almost 2X the transaction rate as the same workload running on Amazon RDS.
It turns out that migrating an existing SQLServer VM to RDS using the same vCPU count as on-prem may yield only half the expected performance for CPU heavy database workloads. The root cause is how Amazon thinks about vCPU compared to on-prem.
A Nutanix cluster can persist a replicated write across two nodes in around 250 uSec which is critical for single-threaded DB write workloads. The performance compares very well with hosted cloud database instances using the same class of processor (db.r5.4xlarge in the figure below). The metrics below are for SQL insert transactions not the underlying IO.
Single threaded commit heavy insert rates. Latency as seen from SQL insert statement.
AOS 6.1 greatly improved database performance on Nutanix especially when the guest VM uses just a single disk for all the database files. The underlying change is known as vdisk sharding. Basically it allows the Nutanix CVM to scale up the number of threads used to service a single virtual disk under heavy load.
From the SQL Window of SQL*Server. Issue these commands to drop the tables and procedures created by HammerDB. This will allow you (for instance) to re-create the database, or create a new database with more warehouses (larger size) while retaining the same name/DB layout.
Some versions of HammerDB (e.g. 3.2) may induce imbalanced NUMA utilization with SQL Server.
This can easily be observed with Resource monitor. When NUMA imbalance occurs one of the NUMA nodes will show much larger utilization than the other. E.g.
Imbalanced NUMA usage by SQL Server.
The cause and fix is well documented on this blog. In short HammerDB issues a short lived connection, for every persistent connection. This causes the SQL Server Round-robin allocation to send all the persistent worker threads to a single NUMA Node! To resolve this issue, simply comment out line #212 in the driver script.
Comment out this line to work-around the HammerDB NUMA imbalance problem.
If successful you will immediately see that the NUMA nodes are more balanced. Whether this results in more/better performance will depend on exactly where the bottleneck is.
