The maximum average wait time in ms for trigger expression.

The minimum % buffer cache hit ratio for high trigger expression.

The minimum % buffer cache hit ratio for warning trigger expression.

This macro is used in database discovery. It can be overridden on the host or linked template level.

This macro is used in database discovery. It can be overridden on the host or linked template level.

The maximum deadlocks per second for trigger expression.

System data source name.

The maximum free list stalls per second for trigger expression.

The instance name for the default instance is SQLServer. For named instance set macro value as MSSQL$instance name.

The maximum lazy writes per second for trigger expression.

The maximum lock requests per second for trigger expression.

The maximum lock timeouts per second for trigger expression.

The maximum log flush waits per second for trigger expression.

The maximum log flush wait time in ms for trigger expression.

The minimum page life expectancy for trigger expression.

The maximum page reads per second for trigger expression.

The maximum page writes per second for trigger expression.

MSSQL user password.

The maximum percent of Transact-SQL compilations for trigger expression.

The maximum percent log used for trigger expression.

The maximum percent of pages read/sec in anticipation of use for trigger expression.

The maximum percent of Transact-SQL recompilations for trigger expression.

MSSQL TCP port.

MSSQL username.

The minimum % worktables from cache ratio for high trigger expression.

The maximum number of work files created per second for trigger expression.

The maximum number of work tables created per second for trigger expression.

Scanning databases in DBMS.

Preprocessing:

- DISCARD_UNCHANGED_HEARTBEAT: 1d

Filter:

- A: {#DBNAME} MATCHES_REGEX {$MSSQL.DBNAME.MATCHES}

- B: {#DBNAME} NOT_MATCHES_REGEX {$MSSQL.DBNAME.NOT_MATCHES}

Discovery the existed availability groups.

Preprocessing:

- DISCARD_UNCHANGED_HEARTBEAT: 1d

Discovery of the local availability databases.

Preprocessing:

- DISCARD_UNCHANGED_HEARTBEAT: 1d

Discovery of the non-local (not local to the SQL Server instance) availability databases.

Preprocessing:

- DISCARD_UNCHANGED_HEARTBEAT: 1d

Discovery of the database replicas.

Preprocessing:

- DISCARD_UNCHANGED_HEARTBEAT: 1d

To see the row for a database other than master or tempdb, you must

either be the database owner or have at least ALTER ANY DATABASE or VIEW ANY

DATABASE server-level permission or CREATE DATABASE permission in the master

database. To see non-NULL values on a mirror database, you must be a member

of the sysadmin fixed server role.

Preprocessing:

- DISCARD_UNCHANGED_HEARTBEAT: 1d

Test the availability of MS SQL Server on TCP port.

Preprocessing:

- DISCARD_UNCHANGED_HEARTBEAT: 10m

MS SQL Server version.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}' && @.counter_name=='Version')].instance_name.first()

- DISCARD_UNCHANGED_HEARTBEAT: 1d

MS SQL Server uptime in 'N days, hh:mm:ss' format.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}' && @.counter_name=='Uptime')].cntr_value.first()

Number of records per second fetched through forwarded record pointers.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}:Access Methods' && @.counter_name=='Forwarded Records/sec')].cntr_value.first()

- CHANGE_PER_SECOND

Number of unrestricted full scans per second. These can be either base-table or full-index scans. Values greater than 1 or 2 indicate that we are having table / Index page scans. If we see high CPU then we need to investigate this counter, otherwise if the full scans are on small tables we can ignore this counter.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}:Access Methods' && @.counter_name=='Full Scans/sec')].cntr_value.first()

- CHANGE_PER_SECOND

Number of index searches per second. These are used to start a range scan, reposition a range scan, revalidate a scan point, fetch a single index record, and search down the index to locate where to insert a new row.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}:Access Methods' && @.counter_name=='Index Searches/sec')].cntr_value.first()

- CHANGE_PER_SECOND

Number of page splits per second that occur as the result of overflowing index pages.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}:Access Methods' && @.counter_name=='Page Splits/sec')].cntr_value.first()

- CHANGE_PER_SECOND

Number of work files created per second. For example, work files could be used to store temporary results for hash joins and hash aggregates.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}:Access Methods' && @.counter_name=='Workfiles Created/sec')].cntr_value.first()

- CHANGE_PER_SECOND

Number of work tables created per second. For example, work tables could be used to store temporary results for query spool, lob variables, XML variables, and cursors.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}:Access Methods' && @.counter_name=='Worktables Created/sec')].cntr_value.first()

- CHANGE_PER_SECOND

Number of times locks on a table were escalated to the TABLE or HoBT granularity.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}:Access Methods' && @.counter_name=='Table Lock Escalations/sec')].cntr_value.first()

- CHANGE_PER_SECOND

Percentage of work tables created where the initial two pages of the work table were not allocated but were immediately available from the work table cache.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}:Access Methods' && @.counter_name=='WorktablesFromCacheRatio')].cntr_value.first()

Indicates the percentage of pages found in the buffer cache without having to read from disk. The ratio is the total number of cache hits divided by the total number of cache lookups over the last few thousand page accesses. After a long period of time, the ratio moves very little. Because reading from the cache is much less expensive than reading from disk, you want this ratio to be high. Generally, you can increase the buffer cache hit ratio by increasing the amount of memory available to SQL Server or by using the buffer pool extension feature.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}:Buffer Manager' && @.counter_name=='BufferCacheHitRatio')].cntr_value.first()

Indicates the number of pages flushed to disk per second by a checkpoint or other operation that require all dirty pages to be flushed.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}:Buffer Manager' && @.counter_name=='Checkpoint pages/sec')].cntr_value.first()

- CHANGE_PER_SECOND

Indicates the number of pages in the buffer pool with database content.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}:Buffer Manager' && @.counter_name=='Database pages')].cntr_value.first()

Indicates the number of requests per second that had to wait for a free page.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}:Buffer Manager' && @.counter_name=='Free list stalls/sec')].cntr_value.first()

- CHANGE_PER_SECOND

Indicates the number of buffers written per second by the buffer manager's lazy writer. The lazy writer is a system process that flushes out batches of dirty, aged buffers (buffers that contain changes that must be written back to disk before the buffer can be reused for a different page) and makes them available to user processes. The lazy writer eliminates the need to perform frequent checkpoints in order to create available buffers.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}:Buffer Manager' && @.counter_name=='Lazy writes/sec')].cntr_value.first()

- CHANGE_PER_SECOND

Indicates the number of seconds a page will stay in the buffer pool without references.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}:Buffer Manager' && @.counter_name=='Page life expectancy')].cntr_value.first()

Indicates the number of requests per second to find a page in the buffer pool.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}:Buffer Manager' && @.counter_name=='Page lookups/sec')].cntr_value.first()

- CHANGE_PER_SECOND

Indicates the number of physical database page reads that are issued per second. This statistic displays the total number of physical page reads across all databases. Because physical I/O is expensive, you may be able to minimize the cost, either by using a larger data cache, intelligent indexes, and more efficient queries, or by changing the database design.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}:Buffer Manager' && @.counter_name=='Page reads/sec')].cntr_value.first()

- CHANGE_PER_SECOND

Indicates the number of physical database page writes that are issued per second.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}:Buffer Manager' && @.counter_name=='Page writes/sec')].cntr_value.first()

- CHANGE_PER_SECOND

Indicates the number of pages read per second in anticipation of use.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}:Buffer Manager' && @.counter_name=='Readahead pages/sec')].cntr_value.first()

- CHANGE_PER_SECOND

The optimum number of pages in the buffer pool.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}:Buffer Manager' && @.counter_name=='Target pages')].cntr_value.first()

Total size of all the data files.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}:Databases' && @.counter_name=='Data File(s) Size (KB)' && @.instance_name=='_Total')].cntr_value.first()

- MULTIPLIER: 1024

Total size of all the transaction log files.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}:Databases' && @.counter_name=='Log File(s) Size (KB)' && @.instance_name=='_Total')].cntr_value.first()

- MULTIPLIER: 1024

The cumulative used size of all the log files in the database.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}:Databases' && @.counter_name=='Log File(s) Used Size (KB)' && @.instance_name=='_Total')].cntr_value.first()

- MULTIPLIER: 1024

Total number of transactions started for all databases per second.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}:Databases' && @.counter_name=='Transactions/sec' && @.instance_name=='_Total')].cntr_value.first()

- CHANGE_PER_SECOND

Total number of logins started per second. This does not include pooled connections. Any value over 2 may indicate insufcient connection pooling.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}:General Statistics' && @.counter_name=='Logins/sec')].cntr_value.first()

- CHANGE_PER_SECOND

Total number of logout operations started per second. Any value over 2 may indicate insufcient connection pooling.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}:General Statistics' && @.counter_name=='Logouts/sec')].cntr_value.first()

- CHANGE_PER_SECOND

Number of currently blocked processes.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}:General Statistics' && @.counter_name=='Processes blocked')].cntr_value.first()

Number of users connected to MS SQL Server.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}:General Statistics' && @.counter_name=='User Connections')].cntr_value.first()

Average latch wait time (in milliseconds) for latch requests that had to wait.

Expression:

The number of latch requests that could not be granted immediately. Latches are lightweight means of holding a very transient server resource, such as an address in memory.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}:Latches' && @.counter_name=='Latch Waits/sec')].cntr_value.first()

- CHANGE_PER_SECOND

Total latch wait time (in milliseconds) for latch requests in the last second. This value should stay stable compared to the number of latch waits per second.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}:Latches' && @.counter_name=='Total Latch Wait Time (ms)')].cntr_value.first()

- CHANGE_PER_SECOND

The average wait time, in milliseconds, for each lock request that had to wait.

Expression:

Number of new locks and lock conversions per second requested from the lock manager.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}:Locks' && @.counter_name=='Lock Requests/sec' && @.instance_name=='_Total')].cntr_value.first()

- CHANGE_PER_SECOND

Number of lock requests per second that timed out, including requests for NOWAIT locks.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}:Locks' && @.counter_name=='Lock Timeouts/sec' && @.instance_name=='_Total')].cntr_value.first()

- CHANGE_PER_SECOND

Number of lock requests per second that required the caller to wait.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}:Locks' && @.counter_name=='Lock Waits/sec' && @.instance_name=='_Total')].cntr_value.first()

- CHANGE_PER_SECOND

Average of total wait time (in milliseconds) for locks in the last second.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}:Locks' && @.counter_name=='Lock Wait Time (ms)' && @.instance_name=='_Total')].cntr_value.first()

- CHANGE_PER_SECOND

Number of lock requests per second that resulted in a deadlock.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}:Locks' && @.counter_name=='Number of Deadlocks/sec' && @.instance_name=='_Total')].cntr_value.first()

- CHANGE_PER_SECOND

Specifies the total amount of memory currently granted to executing processes, such as hash, sort, bulk copy, and index creation operations.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}:Memory Manager' && @.counter_name=='Granted Workspace Memory (KB)')].cntr_value.first()

- MULTIPLIER: 1024

Indicates the maximum amount of memory available for executing processes, such as hash, sort, bulk copy, and index creation operations.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}:Memory Manager' && @.counter_name=='Maximum Workspace Memory (KB)')].cntr_value.first()

- MULTIPLIER: 1024

Specifies the total number of processes that have successfully acquired a workspace memory grant.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}:Memory Manager' && @.counter_name=='Memory Grants Outstanding')].cntr_value.first()

Specifies the total number of processes waiting for a workspace memory grant.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}:Memory Manager' && @.counter_name=='Memory Grants Pending')].cntr_value.first()

Indicates the ideal amount of memory the server can consume.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}:Memory Manager' && @.counter_name=='Target Server Memory (KB)')].cntr_value.first()

- MULTIPLIER: 1024

Specifies the amount of memory the server has committed using the memory manager.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}:Memory Manager' && @.counter_name=='Total Server Memory (KB)')].cntr_value.first()

- MULTIPLIER: 1024

Ratio between cache hits and lookups.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}:Plan Cache' && @.counter_name=='CacheHitRatio' && @.instance_name=='_Total')].cntr_value.first()

Number of cache objects in the cache.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}:Plan Cache' && @.counter_name=='Cache Object Counts' && @.instance_name=='_Total')].cntr_value.first()

Number of cache objects in use.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}:Plan Cache' && @.counter_name=='Cache Objects in use' && @.instance_name=='_Total')].cntr_value.first()

Number of 8-kilobyte (KB) pages used by cache objects.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}:Plan Cache' && @.counter_name=='Cache Pages' && @.instance_name=='_Total')].cntr_value.first()

Number of errors per second.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}:SQL Errors' && @.counter_name=='Errors/sec' && @.instance_name=='DB Offline Errors')].cntr_value.first()

- CHANGE_PER_SECOND

Number of errors per second.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}:SQL Errors' && @.counter_name=='Errors/sec' && @.instance_name=='Info Errors')].cntr_value.first()

- CHANGE_PER_SECOND

Number of errors per second.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}:SQL Errors' && @.counter_name=='Errors/sec' && @.instance_name=='Kill Connection Errors')].cntr_value.first()

- CHANGE_PER_SECOND

Number of errors per second.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}:SQL Errors' && @.counter_name=='Errors/sec' && @.instance_name=='User Errors')].cntr_value.first()

- CHANGE_PER_SECOND

Number of errors per second.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}:SQL Errors' && @.counter_name=='Errors/sec' && @.instance_name=='_Total')].cntr_value.first()

- CHANGE_PER_SECOND

Number of auto-parameterization attempts per second. Total should be the sum of the failed, safe, and unsafe auto-parameterizations. Auto-parameterization occurs when an instance of SQL Server tries to parameterize a Transact-SQL request by replacing some literals with parameters so that reuse of the resulting cached execution plan across multiple similar-looking requests is possible. Note that auto-parameterizations are also known as simple parameterizations in newer versions of SQL Server. This counter does not include forced parameterizations.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}:SQL Statistics' && @.counter_name=='Auto-Param Attempts/sec')].cntr_value.first()

- CHANGE_PER_SECOND

Number of Transact-SQL command batches received per second. This statistic is affected by all constraints (such as I/O, number of users, cache size, complexity of requests, and so on). High batch requests mean good throughput.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}:SQL Statistics' && @.counter_name=='Batch Requests/sec')].cntr_value.first()

- CHANGE_PER_SECOND

The ratio of SQL compilations per second to Batch requests per second in percent.

Expression:

The ratio of SQL re-compilations per second to SQL compilations per second in percent.

Expression:

The ratio of Full scans per second to Index searches per second. The threshold recommendation is strictly for OLTP workloads.

Expression:

Number of failed auto-parameterization attempts per second. This should be small. Note that auto-parameterizations are also known as simple parameterizations in later versions of SQL Server.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}:SQL Statistics' && @.counter_name=='Failed Auto-Params/sec')].cntr_value.first()

- CHANGE_PER_SECOND

Number of safe auto-parameterization attempts per second. Safe refers to a determination that a cached execution plan can be shared between different similar-looking Transact-SQL statements. SQL Server makes many auto-parameterization attempts some of which turn out to be safe and others fail. Note that auto-parameterizations are also known as simple parameterizations in later versions of SQL Server. This does not include forced parameterizations.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}:SQL Statistics' && @.counter_name=='Safe Auto-Params/sec')].cntr_value.first()

- CHANGE_PER_SECOND

Number of SQL compilations per second. Indicates the number of times the compile code path is entered. Includes compiles caused by statement-level recompilations in SQL Server. After SQL Server user activity is stable, this value reaches a steady state.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}:SQL Statistics' && @.counter_name=='SQL Compilations/sec')].cntr_value.first()

- CHANGE_PER_SECOND

Number of statement recompiles per second. Counts the number of times statement recompiles are triggered. Generally, you want the recompiles to be low.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}:SQL Statistics' && @.counter_name=='SQL Re-Compilations/sec')].cntr_value.first()

- CHANGE_PER_SECOND

Number of unsafe auto-parameterization attempts per second. For example, the query has some characteristics that prevent the cached plan from being shared. These are designated as unsafe. This does not count the number of forced parameterizations.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}:SQL Statistics' && @.counter_name=='Unsafe Auto-Params/sec')].cntr_value.first()

- CHANGE_PER_SECOND

The number of currently active transactions of all types.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}:Transactions' && @.counter_name=='Transactions')].cntr_value.first()

0 = ONLINE

1 = RESTORING

2 = RECOVERING

3 = RECOVERY_PENDING

4 = SUSPECT

5 = EMERGENCY

6 = OFFLINE

7 = COPYING

10 = OFFLINE_SECONDARY

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}:Databases' && @.counter_name=='State' && @.instance_name=='{#DBNAME}')].cntr_value.first()

- DISCARD_UNCHANGED_HEARTBEAT: 15m

Number of active transactions for the database.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}:Databases' && @.counter_name=='Active Transactions' && @.instance_name=='{#DBNAME}')].cntr_value.first()

Cumulative size of all the data files in the database including any automatic growth. Monitoring this counter is useful, for example, for determining the correct size of tempdb.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}:Databases' && @.counter_name=='Data File(s) Size (KB)' && @.instance_name=='{#DBNAME}')].cntr_value.first()

- MULTIPLIER: 1024

Total number of log bytes flushed per second. Useful for determining trends and utilization of the transaction log.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}:Databases' && @.counter_name=='Log Bytes Flushed/sec' && @.instance_name=='{#DBNAME}')].cntr_value.first()

- CHANGE_PER_SECOND

Cumulative size of all the transaction log files in the database.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}:Databases' && @.counter_name=='Log File(s) Size (KB)' && @.instance_name=='{#DBNAME}')].cntr_value.first()

- MULTIPLIER: 1024

The cumulative used size of all the log files in the database.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}:Databases' && @.counter_name=='Log File(s) Used Size (KB)' && @.instance_name=='{#DBNAME}')].cntr_value.first()

- MULTIPLIER: 1024

Number of log flushes per second.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}:Databases' && @.counter_name=='Log Flushes/sec' && @.instance_name=='{#DBNAME}')].cntr_value.first()

- CHANGE_PER_SECOND

Number of commits per second waiting for the log flush.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}:Databases' && @.counter_name=='Log Flush Waits/sec' && @.instance_name=='{#DBNAME}')].cntr_value.first()

- CHANGE_PER_SECOND

Total wait time (in milliseconds) to flush the log. On an AlwaysOn secondary database, this value indicates the wait time for log records to be hardened to disk.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}:Databases' && @.counter_name=='Log Flush Wait Time' && @.instance_name=='{#DBNAME}')].cntr_value.first()

- CHANGE_PER_SECOND

Total number of times the transaction log for the database has been expanded.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}:Databases' && @.counter_name=='Log Growths' && @.instance_name=='{#DBNAME}')].cntr_value.first()

Total number of times the transaction log for the database has been shrunk.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}:Databases' && @.counter_name=='Log Shrinks' && @.instance_name=='{#DBNAME}')].cntr_value.first()

The number of times the transaction log has been shrunk.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}:Databases' && @.counter_name=='Log Truncations' && @.instance_name=='{#DBNAME}')].cntr_value.first()

Percentage of space in the log that is in use.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}:Databases' && @.counter_name=='Percent Log Used' && @.instance_name=='{#DBNAME}')].cntr_value.first()

Number of transactions started for the database per second.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}:Databases' && @.counter_name=='Transactions/sec' && @.instance_name=='{#DBNAME}')].cntr_value.first()

- CHANGE_PER_SECOND

Indicates the recovery health of the primary replica:

0 = In progress

1 = Online

2 = Unavailable

Preprocessing:

- JSONPATH: $[?(@.group_name=='{#GROUP_NAME}')].primary_recovery_health.first()

- DISCARD_UNCHANGED_HEARTBEAT: 1h

Name of the server instance that is hosting the current primary replica.

Preprocessing:

- JSONPATH: $[?(@.group_name=='{#GROUP_NAME}')].primary_replica.first()

- DISCARD_UNCHANGED_HEARTBEAT: 3h

Indicates the recovery health of a secondary replica replica:

0 = In progress

1 = Online

2 = Unavailable

Preprocessing:

- JSONPATH: $[?(@.group_name=='{#GROUP_NAME}')].secondary_recovery_health.first()

- DISCARD_UNCHANGED_HEARTBEAT: 1h

Reflects a rollup of the synchronization_health of all availability replicas in the availability group:

0: Not healthy. None of the availability replicas have a healthy.

1: Partially healthy. The synchronization health of some, but not all, availability replicas is healthy.

2: Healthy. The synchronization health of every availability replica is healthy.

Preprocessing:

- JSONPATH: $[?(@.group_name=='{#GROUP_NAME}')].synchronization_health.first()

- DISCARD_UNCHANGED_HEARTBEAT: 1h

0 = Online

1 = Restoring

2 = Recovering

3 = Recovery pending

4 = Suspect

5 = Emergency

6 = Offline

Preprocessing:

- JSONPATH: $[?(@.group_name=='{#GROUP_NAME}' && @.replica_name=='{#REPLICA_NAME}' && @.dbname=='{#DBNAME}')].database_state.first()

- DISCARD_UNCHANGED_HEARTBEAT: 1h

Database state:

0 = Resumed

1 = Suspended

Preprocessing:

- JSONPATH: $[?(@.group_name=='{#GROUP_NAME}' && @.replica_name=='{#REPLICA_NAME}' && @.dbname=='{#DBNAME}')].is_suspended.first()

- DISCARD_UNCHANGED_HEARTBEAT: 1h

Reflects the intersection of the synchronization state of a database that is joined to the availability group on the availability replica and the availability mode of the availability replica (synchronous-commit or asynchronous-commit mode):

0 = Not healthy. The synchronization_state of the database is 0 (NOT SYNCHRONIZING).

1 = Partially healthy. A database on a synchronous-commit availability replica is considered

partially healthy if synchronization_state is 1 (SYNCHRONIZING).

2 = Healthy. A database on an synchronous-commit availability replica is considered healthy if synchronization_state is 2 (SYNCHRONIZED), and a database on an asynchronous-commit availability replica is considered healthy if synchronization_state is 1 (SYNCHRONIZING).

Preprocessing:

- JSONPATH: $[?(@.group_name=='{#GROUP_NAME}' && @.replica_name=='{#REPLICA_NAME}' && @.dbname=='{#DBNAME}')].synchronization_health.first()

- DISCARD_UNCHANGED_HEARTBEAT: 1h

Amount of log records of the primary database that has not been sent to the secondary databases.

Preprocessing:

- JSONPATH: $[?(@.group_name=='{#GROUP_NAME}' && @.replica_name=='{#REPLICA_NAME}' && @.dbname=='{#DBNAME}')].log_send_queue_size.first()

- MULTIPLIER: 1024

- DISCARD_UNCHANGED_HEARTBEAT: 1h

Amount of log records in the log files of the secondary replica that has not yet been redone.

Preprocessing:

- JSONPATH: $[?(@.group_name=='{#GROUP_NAME}' && @.replica_name=='{#REPLICA_NAME}' && @.dbname=='{#DBNAME}')].redo_queue_size.first()

- MULTIPLIER: 1024

- DISCARD_UNCHANGED_HEARTBEAT: 1h

Whether a secondary replica is currently connected to the primary replica:

0 : Disconnected. The response of an availability replica to the DISCONNECTED state depends on its role:

On the primary replica, if a secondary replica is disconnected, its secondary databases are marked as NOT SYNCHRONIZED on the primary replica, which waits for the secondary to reconnect;

On a secondary replica, upon detecting that it is disconnected, the secondary replica attempts to reconnect to the primary replica.

1 : Connected. Each primary replica tracks the connection state for every secondary replica in the same availability group. Secondary replicas track the connection state of only the primary replica.

Preprocessing:

- JSONPATH: $[?(@.group_name=='{#GROUP_NAME}' && @.replica_name=='{#REPLICA_NAME}')].connected_state.first()

- DISCARD_UNCHANGED_HEARTBEAT: 1h

Whether the replica is local:

0 = Indicates a remote secondary replica in an availability group whose primary replica is hosted by the local server instance. This value occurs only on the primary replica location.

1 = Indicates a local replica. On secondary replicas, this is the only available value for the availability group to which the replica belongs.

Preprocessing:

- JSONPATH: $[?(@.group_name=='{#GROUP_NAME}' && @.replica_name=='{#REPLICA_NAME}')].is_local.first()

- DISCARD_UNCHANGED_HEARTBEAT: 1h

0 = Not joined

1 = Joined, standalone instance

2 = Joined, failover cluster instance

Preprocessing:

- JSONPATH: $[?(@.group_name=='{#GROUP_NAME}' && @.replica_name=='{#REPLICA_NAME}')].join_state.first()

- DISCARD_UNCHANGED_HEARTBEAT: 1h

Current operational state of the replica:

0 = Pending failover

1 = Pending

2 = Online

3 = Offline

4 = Failed

5 = Failed, no quorum

6 = Not local

Preprocessing:

- JSONPATH: $[?(@.group_name=='{#GROUP_NAME}' && @.replica_name=='{#REPLICA_NAME}')].operational_state.first()

- DISCARD_UNCHANGED_HEARTBEAT: 1h

Rollup of the database_state column of the sys.dm_hadr_database_replica_states dynamic management view:

0 : In progress. At least one joined database has a database state other than ONLINE

(database_state is not 0).

1 : Online. All the joined databases have a database state of ONLINE (database_state is 0).

Preprocessing:

- JSONPATH: $[?(@.group_name=='{#GROUP_NAME}' && @.replica_name=='{#REPLICA_NAME}')].recovery_health.first()

- DISCARD_UNCHANGED_HEARTBEAT: 1h

Current Always On availability groups role of a local replica or a connected remote replica:

0 = Resolving

1 = Primary

2 = Secondary

Preprocessing:

- JSONPATH: $[?(@.group_name=='{#GROUP_NAME}' && @.replica_name=='{#REPLICA_NAME}')].role.first()

- DISCARD_UNCHANGED_HEARTBEAT: 1h

Reflects a rollup of the database synchronization state (synchronization_state)of all joined availability databases (also known as replicas) and the availability mode of the replica (synchronous-commit or asynchronous-commit mode). The rollup will reflect the least healthy accumulated state the databases on the replica:

0 : Not healthy. At least one joined database is in the NOT SYNCHRONIZING state.

1 : Partially healthy. Some replicas are not in the target synchronization state: synchronous-commit replicas should be synchronized, and asynchronous-commit replicas should be synchronizing.

2 : Healthy. All replicas are in the target synchronization state: synchronous-commit replicas are synchronized, and asynchronous-commit replicas are synchronizing.

Preprocessing:

- JSONPATH: $[?(@.group_name=='{#GROUP_NAME}' && @.replica_name=='{#REPLICA_NAME}')].synchronization_health.first()

- DISCARD_UNCHANGED_HEARTBEAT: 1h

Current role of the local database plays in the database mirroring session.

1 = Principal

2 = Mirror

Preprocessing:

- JSONPATH: $[?(@.dbname=='{#DBNAME}')].mirroring_role.first()

- DISCARD_UNCHANGED_HEARTBEAT: 1h

The number of times that mirroring partners have switched the principal and mirror roles due to a failover or forced service.

Preprocessing:

- JSONPATH: $[?(@.dbname=='{#DBNAME}')].mirroring_role_sequence.first()

- SIMPLE_CHANGE

State of the mirror database and of the database mirroring session.

0 = Suspended

1 = Disconnected from the other partner

2 = Synchronizing

3 = Pending Failover

4 = Synchronized

5 = The partners are not synchronized. Failover is not possible now.

6 = The partners are synchronized. Failover is potentially possible. For information about the requirements for failover see, Database Mirroring Operating Modes.

Preprocessing:

- JSONPATH: $[?(@.dbname=='{#DBNAME}')].mirroring_state.first()

- DISCARD_UNCHANGED_HEARTBEAT: 1h

State of the witness in the database mirroring session of the database:

0 = Unknown

1 = Connected

2 = Disconnected

Preprocessing:

- JSONPATH: $[?(@.dbname=='{#DBNAME}')].mirroring_witness_state.first()

- DISCARD_UNCHANGED_HEARTBEAT: 1h

Safety setting for updates on the mirror database:

0 = Unknown state

1 = Off [asynchronous]

2 = Full [synchronous]

Preprocessing:

- JSONPATH: $[?(@.dbname=='{#DBNAME}')].mirroring_safety_level.first()

- DISCARD_UNCHANGED_HEARTBEAT: 1h

The item gets server global status information.

Expression:

Average latch wait time (in milliseconds) for latch requests that had to wait.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}:Latches' && @.counter_name=='Average Latch Wait Time (ms)')].cntr_value.first()

For internal use only.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}:Latches' && @.counter_name=='Average Latch Wait Time Base')].cntr_value.first()

Average amount of wait time (in milliseconds) for each lock request that resulted in a wait. Information for all locks.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}:Locks' && @.counter_name=='Average Wait Time (ms)' && @.instance_name=='_Total')].cntr_value.first()

For internal use only.

Preprocessing:

- JSONPATH: $[?(@.object_name=='{$MSSQL.INSTANCE}:Locks' && @.counter_name=='Average Wait Time Base' && @.instance_name=='_Total')].cntr_value.first()

Getting replica states - name, primary and secondary health, synchronization health.

Expression:

Getting the states of the local availability database.

Expression:

Getting the states of the non-local availability database.

Expression:

Getting the database replica states.

Expression:

Getting mirrors state

Expression:

The TCP port of the MS SQL Server service is currently unavailable.

MSSQL version has changed. Ack to close.

Manual close: YES

Uptime is less than 10 minutes

Manual close: YES

Zabbix has not received data for items for the last 30 minutes.

Depends on:

- MSSQL: Service is unavailable

Rows with varchar columns can experience expansion when varchar values are updated with a longer string. In the case where the row cannot fit in the existing page, the row migrates and access to the row will traverse a pointer. This only happens on heaps (tables without clustered indexes). Evaluate clustered index for heap tables. In cases where clustered indexes cannot be used, drop non-clustered indexes, build a clustered index to reorg pages and rows, drop the clustered index, then recreate non-clustered indexes.

Too many work files created per second to store temporary results for hash joins and hash aggregates.

Too many work tables created per second to store temporary results for query spool, lob variables, XML variables, and cursors.

A value less than 90% may indicate insufcient memory, since execution plans are being dropped, or may indicate, on 32-bit systems, the need for an upgrade to a 64-bit system

Too low buffer cache hit ratio.

Low buffer cache hit ratio.

Depends on:

- MSSQL: Percentage of the buffer cache efficiency is low (below {$MSSQL.BUFFER_CACHE_RATIO.MIN.CRIT}% for 5m)

Some requests have to wait for a free page.

The number of buffers written per second by the buffer manager's lazy writer exceeds the threshold.

The page stays in the buffer pool without references of less time than the threshold value.

The physical database page reads are issued too frequently.

The physical database page writes are issued too frequently.

If this value is makes up even a sizeable minority of total Page Reads/sec (say, greater than 20% of total page reads), you may have too many physical reads occurring.

An average wait time longer than 500ms may indicate excessive blocking. This value should generally correlate to 'Lock Waits/sec' and move up or down with it accordingly.

The number of new locks and lock conversions per second requested from the lock manager is high.

The total number of lock requests per second that timed out, including requests for NOWAIT locks is high.

Values greater than zero indicate at least some blocking is occurring, while a value of zero can quickly eliminate blocking as a potential root-cause problem.

Too many deadlocks are occurring currently.

The lower this value is the better. High values often indicate excessive adhoc querying and should be as low as possible. If excessive adhoc querying is happening, try rewriting the queries as procedures or invoke the queries using sp_executeSQL. When rewriting isn’t possible, consider using a plan guide or setting the database to parameterization forced mode.

This number should be at or near zero, since recompiles can cause deadlocks and exclusive compile locks. This counter’s value should follow in proportion to “Batch Requests/sec” and “SQL Compilations/sec”. This needs to be nil in your system as much as possible.

Index searches are preferable to index and table scans. For OLTP applications, optimize for more index searches and less scans (preferably, 1 full scan for every 1000 index searches). Index and table scans are expensive I/O operations.

The DB has a non-working state.

Too many commits are waiting for the log flush.

The wait time to flush the log is too long.

There's not enough space left in the log.

The primary replica is in the synchronization process.

The secondary replica is in the synchronization process.

None of the availability replicas have a healthy.

The synchronization health of some, but not all, availability replicas is healthy.

The local availability database has a non-working state.

The synchronization state of the local availability database is NOT SYNCHRONIZING.

A database on a synchronous-commit availability replica is considered partially healthy if synchronization state is SYNCHRONIZING.

The log records of the primary database do not send to the secondary databases.

The log records in the log files of the secondary replica have not yet been redone.

The response of an availability replica to the DISCONNECTED state depends on its role:

On the primary replica, if a secondary replica is disconnected, its secondary databases are marked as NOT SYNCHRONIZED on the primary replica, which waits for the secondary to reconnect; On a secondary replica, upon detecting that it is disconnected, the secondary replica attempts to reconnect to the primary replica.

The operational state of the replica in a given availability group is "Pending" or "Offline".

The operational state of the replica in a given availability group is "Failed".

The operational state of the replica in a given availability group is "Failed, no quorum".

At least one joined database has a database state other than ONLINE.

At least one joined database is in the NOT SYNCHRONIZING state.

Some replicas are not in the target synchronization state: synchronous-commit replicas should be synchronized, and asynchronous-commit replicas should be synchronizing.

The state of the mirror database and of the database mirroring session is "Suspended", "Disconnected from the other partner", or "Synchronizing".

The state of the mirror database and of the database mirroring session is "Pending Failover".

The state of the mirror database and of the database mirroring session is "Not synchronized". The partners are not synchronized. A failover is not possible now.

The state of the witness in the database mirroring session of the database is "Disconnected".