Monitoring with PRTG

A guide to configuring PRTG for SQL Server monitoring
Tags:
Categories:

PRTG Network Monitor is a commercial monitoring product from Paessler. This guide explains how to configure it to monitor SQL Server.

There does not appear to be a package or counter group tailored to SQL Server natively in PRTG. You have to add them by hand.

To do that, add a “Windows PerfCounter Custom” sensor, and register the Windows performance counters related to SQL Server.

The simplest approach is to look the counters up directly on the SQL Server machine to identify the ones you care about, then enter them by hand in PRTG.

Here is a list of the most important ones. Note that on a named instance the counter prefix is not SQLServer but MSSQL$<instance_name>. Check the actual counter names on the machine itself.

Performance counter syntax

Counters have to be added in PRTG with this syntax:

\Object(Instance)\Counter

For the SQLServer:Buffer Manager\Page life expectancy counter, for example, what you enter in PRTG is:

\SQLServer:Buffer Manager\Page life expectancy

Unit of measurement

You can attach a custom unit to each performance counter in PRTG, with this syntax:

\Object(Instance)\Counter::Unit

The unit is simply the text displayed after the double colon in each counter definition — %, ms, bytes, req/s and so on.

So for the PerfCounter Custom sensor, each line takes the form PerfCounterPath::UnitString, for example \\Processor(_Total)\\% Processor Time::%, where % is the unit. That unit string is entirely free-form: percentages, time units (ms, s), sizes (KB, MB, bytes), rates (/s), plain counts, or any custom label that makes sense for the metric. PRTG then uses the unit to render graphs, tables and gauges, and to stack the channels that share it.

Performance counters worth watching

Instance performance

  • SQLServer:Buffer Manager\Page life expectancy — how many seconds a data page stays in the buffer cache. A low value can point to memory problems.
  • SQLServer:SQL Statistics\Batch Requests/sec — the number of batches processed per second. A high value indicates a heavy load on the server.
  • SQLServer:General Statistics\User Connections — the number of active user connections. A high value indicates a heavy load on the server.

The special case of Buffer cache hit ratio

The \SQLServer:Buffer Manager\Buffer cache hit ratio counter measures the proportion of requests that found their data pages in SQL Server’s memory cache rather than having to read them back from disk. It is an indicator of cache effectiveness: the closer to 100 %, the more SQL Server is working in memory, which is generally what you want.

This counter is a ratio counter in the Windows performance counter model, and it relies on a second counter, \SQLServer:Buffer Manager\Buffer cache hit ratio base, which provides the denominator.

In the performance counter API, ratio counters are not exposed directly as a readable percentage. The system exposes the numerator, the main counter, and the denominator, the base, separately, and it is up to the consumer to do the division. In SQL Server, if you query sys.dm_os_performance_counters, you already have to apply a formula of the form (cntr_value / base_value) * 100 to get the real percentage. The same applies when a monitoring tool such as PRTG consumes these counters.

The difficulty with PRTG’s PerfCounter Custom sensor is that it only reads raw counter values. You can declare either counter as a channel, but the sensor offers no way to define an expression combining several counters into a final value. If you add the ratio counter alone, ignoring the base, you end up displaying a value that is not the real percentage, and that moves in unintuitive ways.

The way to handle it is to split the problem into two steps:

  1. Declare both counters as two separate channels in a PerfCounter Custom sensor:

    • \\SQLServer:Buffer Manager\\Buffer cache hit ratio::raw
    • \\SQLServer:Buffer Manager\\Buffer cache hit ratio base::base

    These channels hold the raw values exactly as Windows provides them.

  2. Create a calculated sensor, or a calculated channel depending on your PRTG version, applying the ratio formula to those two channels: (BufferCacheHitRatio / BufferCacheHitRatioBase) * 100

Display that calculated channel in your graphs, with its unit set to a percentage, and keep the two raw channels in the background or hide them to keep the interface clean. This respects the logic of the SQL Server performance counters while giving you a readable, usable indicator in PRTG.

Database monitoring

Replace db with the name of your database.

  • SQLServer:Databases(db)\Transactions/sec — the number of transactions processed per second. A high value indicates a heavy load on the database.
  • SQLServer:Databases(db)\Log Bytes Flushed/sec — the number of transaction log bytes written to disk per second. A high value indicates a heavy load on the database.
  • SQLServer:Databases(db)\Log Flushes/sec — the number of transaction log flushes per second.
  • SQLServer:Databases(db)\Log Flush Waits/sec — the number of commits per second waiting on a log flush.
  • SQLServer:Databases(db)\Log Flush Wait Time/sec — the time spent waiting on transaction log flushes. A high value points to performance problems on the transaction log.

Monitoring and alerting for Always On availability groups

Monitoring the WSFC cluster

There are sensor scripts for the WSFC cluster available on GitHub, though they may no longer work on recent PRTG versions:

Get-ClusterStatus.ps1

SQL Server Expertise Need help with SQL Server? Contact me