Visualizing performance with RHEL 8.4 Grafana Dashboard for Microsoft SQL Server

Tracking CPU Usage and Memory Utilization

After you bring up the Dashboard, what can you do with it? 

For starters, we recommend you start by looking at CPU and memory metrics. Looking at the Linux Run Queue, System: % CPU and CPU Utilization metrics can help you identify runaway processes and unanticipated system activity. If your CPUs are highly saturated, it’s time to start looking at the processes on the system using command-line tools like pcp atop to identify processes hogging CPU or memory on the system. 

From a SQL Server perspective, the SQL wait statistics are pretty important to help you track down CPU and memory utilization issues. A high number of SOS_SCHEDULAR_YIELD or THREADPOOL might indicate that your workload needs additional processing resources. 

Similarly, SQL Server: Memory Manager metrics can help you identify how much memory SQL Server is working with overall, as well as how much memory is being "stolen" for purposes other than the SQL page cache. An usually high number for stolen server memory indicates memory pressure in the database system, and fixing it might require a change either to how SQL Server is utilizing its memory or increasing the amount of memory provided by the underlying OS platform. 

Tracking Disk Utilization

Memory problems can trigger disk problems, so we need to investigate this area next. Without enough memory, disk activity can rapidly increase because we’re paging memory out to disk (swapping).

You’ll have already determined whether you’re swapping if you’ve gone to use the pcp atop command we described above. 

Perhaps the most interesting thing you can look at with Disk Utilization is whether you are running out of throughput or IOPS. 

You can do some basic testing to confirm the maximum capability of your devices. For this, fio is a great tool for generating synthetic workloads. If you know the max throughput and max input/output operations per second (IOPS) you can then use that knowledge to determine when to add more storage via RAID, move to faster storage, add caching to fast storage tiers such as those using Intel’s Optane DC Persistent Memory, or when to split up your data stores. 

SQL statistics can also help you understand storage contention issues. For example, you can look at the SQL OS Wait Stats and examine the PAGE_I/O_LATCH metric. Some number of waits for SQL Server is typically acceptable but an order of magnitude difference in this statistic versus other wait types might indicate a problem that needs fixing by moving to faster storage. 

That said, there is also the possibility that you have a database tuning problem such as a poorly designed index that is spiking up disk I/O. 

We have now covered some of the key metrics. In this table we include several more useful metrics listed in the order of their appearance in the dashboard panels: 

Data annotations and sharing

Annotations in Grafana provide a way to mark points on the graph, linking data metrics with stories that you want to tell with your data. You can also easily export and share your PCP metrics using Grafana with other administrators so that they can use the same dashboard to view and troubleshoot performance problems.