Chapter 4: SQL Server Monitoring
(sqlserver) Metrics
Many probes ship with default QoS threshold values set. The default threshold values provide an idea of the type of values to be entered in the fields and are not necessarily recommended best practice values. To aid in tuning thresholds and reducing
false-positive alarms, this section describes the QoS metrics and provides the default QoS thresholds.
This section contains the following topics:
QoS Metrics (see page 65)
Alert Metrics (see page 68)
QoS Metrics
The following table describes the checkpoint metrics that can be configured using the probe.
Monitor Name Units Description Version
QOS_SQLSERVER_active_connec tion_ratio
Percent Active Connection Ratio v4.4
QOS_SQLSERVER_active_users Count Active Users v4.4 QOS_SQLSERVER_alloc_space Percent Free Allocated Space v4.4 QOS_SQLSERVER_av_fragmentat
ion
Percent Average Fragmentation v4.4
QOS_SQLSERVER_average_waitti me
ms Average Lock Wait Time v4.4
QOS_SQLSERVER_backup_status Minutes Minutes Since Last Backup v4.4 QOS_SQLSERVER_blocked_users Count Blocked Users v4.4 QOS_SQLSERVER_buf_cachehit_
ratio
Percent Buffer Cachehit Ratio v4.4
QOS_SQLSERVER_check_dbalive State Availability v4.4 QOS_SQLSERVER_connection_m
emory
66 sqlserver Guide
Monitor Name Units Description Version
QOS_SQLSERVER_database_cou nt
Count Database Count v4.4
QOS_SQLSERVER_database_size Megabytes Database Size v4.4 QOS_SQLSERVER_database_stat
e
State Database State v4.4
QOS_SQLSERVER_deadlocks Count/s Deadlocks v4.4 QOS_SQLSERVER_differential_ba
ckup_status
Minutes Minutes Since Last Differential Backup
v4.4
QOS_SQLSERVER_fg_free_space Percent Filegroup Free Space v4.7 QOS_SQLSERVER_free_connecti
ons
Percent Free Connections v4.4
QOS_SQLSERVER_free_space Percent Free Space v4.4 QOS_SQLSERVER_full_scans Count/sec. Full Scans v4.4 QOS_SQLSERVER_latch_waits Requests/Se
c
Latch Requests v4.4
QOS_SQLSERVER_lock_memory Kb Lock Memory Allocated v4.4 QOS_SQLSERVER_lock_requests Requests/Se
c
Lock Requests v4.4
QOS_SQLSERVER_lock_timeouts Count/sec Lock Timeouts v4.4 QOS_SQLSERVER_lock_waits Count/sec Lock Waits v4.4 QOS_SQLSERVER_locked_users Count Locked Users v4.4 QOS_SQLSERVER_locks_used Percent Percent Lock Blocks Used v4.4 QOS_SQLSERVER_log_cachehit_r
atio
Percent Log Cachehit Ratio v4.4
QOS_SQLSERVER_log_file_growt hs
Count Log File Growths v4.4
QOS_SQLSERVER_log_file_shrink s
Count Log File Shrinks v4.4
QOS_SQLSERVER_log_flush_wait s
Count/sec. Log Flush Waits v4.4
OQS_SQLSERVER_logfile_size Count Log-file size v4.4 QOS_SQLSERVER_logfile_usage Percent Log-file Usage v4.4 QOS_SQLSERVER_logic_fragmen
t
Chapter 4: SQL Server Monitoring (sqlserver) Metrics 67
Monitor Name Units Description Version
QOS_SQLSERVER_login_count Count Login Count v4.4 QOS_SQLSERVER_long_queries None Monitors long running queries
in seconds.
v4.4
QOS_SQLSERVER_mirror_sqlinst ance
State Mirror Sqlinstance v4.4
QOS_SQLSERVER_mirror_state State Mirror State v4.4 QOS_SQLSERVER_mirror_witnes
s_server
State Mirror Witness Server v4.4
QOS_SQLSERVER_optimizer_me mory
Kilobyte Optimizer Memory v4.4
QOS_SQLSERVER_page_reads Count/sec. Page Reads v4.4 QOS_SQLSERVER_page_writes Count/sec. Page Writes v4.4 QOS_SQLSERVER_scan_density Percent Object Density v4.4 QOS_SQLSERVER_server_cpu Percent CPU Usage v4.4 QOS_SQLSERVER_server_io Percent I/O busy v4.4 QOS_SQLSERVER_server_startup Days Server Uptime v4.4 QOS_SQLSERVER_sqlcache_mem
ory
Kb SQL Cache Memory Allocated v4.4
QOS_SQLSERVER_table_space Kb User Table Space Usage v4.4 QOS_SQLSERVER_total_memory Kb Total Dynamic Memory v4.4 QOS_SQLSERVER_transaction_ba
ckup_status
Minutes Minutes since last Transaction log backup
v4.4
QOS_SQLSERVER_transactions Transactions /sec
Transactions v4.4
QOS_SQLSERVER_user_cpu Percent CPU Usage v4.4 QOS_SQLSERVER_workspace_m
emory
Percent Workspace Memory Allocated v4.4
QOS_SQLServer_ls_primary_stat us
Status Primary Status v4.4
QOS_SQLServer_ls_time_since_l ast_backup
Minutes Minutes Since Last Backup v4.4
QOS_SQL_Server_ls_secondary_ status
68 sqlserver Guide
Monitor Name Units Description Version
QOS_SQL_Server_ls_time_since_ last_copy
Minutes Minutes Since Last Copy v4.4
QOS_SQL_Server_ls_time_since_ last_restore
Minutes Minutes Since Last Restore v4.4
QOS_SQL_Server_ls_last_restore d_latency
Minutes Minutes Last Restored Latency v4.4
QOS_SQL_Server_fg_freeSpace_ with_avail_disk
Percent SQL Server Filegroup Free Space considering Available Disk Size
v4.4
QOS_SQL_Server_logfile_usage_ with_avail_disk
Percent Monitors free space in the database log files after considering the available disk size.
v4.7
Alert Metrics
The following table describes the default settings for the alert metrics default threshold.
Alarm Metric Warning Threshol d Warnin g Severity Error Threshold Error Severit y Description Version
Active Connection Ratio - - 75 Major
Monitors ratio of active connections to total allowed connections. v4.4 Active Users - - 1 Inform ation This checkpoint monitors the number of users having an active transaction at the moment of snapshot. v4.4
Alloc Space - - 10 Major
Monitors free space in allocated data files regardless of
Chapter 4: SQL Server Monitoring (sqlserver) Metrics 69
Avg Fragmentation - - 25 Major
Monitors average fragmentation per allocation unit of an index or table. v4.4
Avg Wait Time - - 10
Inform ation
Monitors average
lock wait time in ms. v4.4
Backup Status - - 1440 Major
Monitors number of days since last
backup. v4.4 Blocked Users - - 1 Inform ation Monitors the number of user blocked. v4.4 Cachehit Ratio 95 Warnin g 75 Major
Monitors the buffer cache-hit ratio v4.4 Availability - - 1 Major Monitors connectivity to the database instance v4.4 Connection Memory - - 400 Inform ation Monitors amount of connection memory in Kb. v4.4 Db Count - - 4 Inform ation Monitors the number of existing databases. v4.4 Db Size - - 1 Inform ation
Monitors space size (in Kb) for each database, log and
data files together. v4.4
Db State - - 0 Major Monitors database state v4.4 Deadlocks - - 0.01 Major Monitors the number of deadlocks per second v4.4 Differential Backup Status - - 1440 Major Monitors number of days since last
differential backup v4.4
Fg Space - - 10 Major
Monitors free space in file groups v4.7
Free Connection - - 10 Major
Monitors % free connections to SQL Server instance. v4.4
70 sqlserver Guide
Free Space - - 10 Major
Monitors free space in database. v4.4
Full Scans - - 0.1
Inform ation
Monitors the number of full scans (table or index) per second v4.4 Latch Waits - - 0.01 Inform ation Monitors the number of latch
requests per second v4.4
Lock Memory - - 400 Inform ation Monitors amount of allocated lock memory in Kb v4.4
Lock Requests - - 0.1 Major
Monitors the number of lock
requests per second v4.4
Lock Timeouts - - 0.01 Major
Monitor the number of lock-timeouts per second v4.4
Lock Waits - - 0.1 Major
Monitor the number of lock waits per
second v4.4 Locked Users - - 1 Inform ation Monitors the number of users suspended by locks v4.4
Locks Used - - 80 Major
Monitors % of lock and lock owner
blocks used. v4.4
Log Cachehit Ratio - - 95 Major
Monitors the log
cache-hit ratio v4.4
Log File Growths - - 2
Inform ation
Monitors the usage (growth) of the
transaction logs v4.4
Log File Shrinks - - 2
Inform ation
Monitors the usage (shrinking) of the
transaction logs v4.4
Log Flush Waits - - 0.1
Inform ation
Monitors the number of log flush waits per second v4.4 Log File Size - - 100 Major Monitors log file size v4.4
Log File Usage - - 90 Major
Monitors free space in log files v4.4
Chapter 4: SQL Server Monitoring (sqlserver) Metrics 71
Logic Fragment - - 25 Major
SQL Server 2000 only! Monitors logical fragmentation of an index or table. v4.4 Login Count - - 1 Inform ation Monitors the number of users currently logged
onto the server v4.4
Long Jobs - - 10 Major
This checkpoint will find all jobs running longer then defined threshold in seconds v4.4
Long Queries - - 60 Major
Monitors long running queries (in seconds) v4.4 Optimizer Memory - - 200 Inform ation Monitors amount of optimizer memory in Kb. v4.4 Page Reads - - 0.1 Inform ation Monitors the number of database page reads per
second v4.4 Page Writes - - 0.5 inform ation Monitors the number of database page writes per
second. v4.4
Scan Density - - 90 Major
SQL Server 2000 only! Monitors table/index fragmentation (density) v4.4 Server CPU - - 80 inform ation Monitors % of CPU usage by SQL Server instance v4.4 Server IO - - 80 Inform ation Monitors % of I/O busy for SQL Server instance. v4.4
Startup - - 120
Inform ation
Monitors the uptime (in days) of the
72 sqlserver Guide Cache Memory - - 400 Inform ation Monitors amount of SQL cache memory in Kb v4.4 Table Space - - 2097152 Inform ation Monitors reserved
space in user tables. v4.4
Total Memory - - 15000 Inform ation Monitors total amount of dynamic server memory in Kb. v4.4 Transaction Backup Status - - 1440 Major Monitors number of days since last transaction log backup v4.4 Transactions - - 0.5 Major Monitors the number of transactions per second v4.4 User CPU - - 50 Inform ation Monitors % of CPU usage by user. v4.4 User Waits - - 0.1 Inform ation Monitors time in seconds, session spends waiting for a lock. v4.4
Workspace Memory - - 80 Major
Monitors % of workspace memory allocated. v4.4
Profile Timeout - - NA Major
Defines the
maximum processing time for all
checkpoints in the profile. If this timeout is reached, the interval
processing is finished and the probe waits for next heartbeat to evaluate any checkpoint schedules. Alarm message is issued. v4.4 SQL Error - - NA Major SQL Native client error. v4.4
Chapter 4: SQL Server Monitoring (sqlserver) Metrics 73
SQL Timeout - - NA Major
Every checkpoint query run
asynchronously. In case the query reaches the SQL timeout, the checkpoint processing will be terminated and the next checkpoint will be started. Alarm is issued. v4.4 Query Checksum - - NA Major Query manipulated. v4.4
Query Error - - NA Major
Returns data in
unsupported format. v4.4
Suspect Pages - - 1 Major
Checks if suspect pages are logged for databases. v4.4
Agent Job Failure - - 15 Major
Monitors agent jobs which failed during the defined
threshold interval (in minutes). v4.4
Primary Status - - 1 Major
Monitors collective status of agents for the primary log
shipping database. v4.4
Secondary Status 1 Major
Monitors collective status of agents for the secondary log
74 sqlserver Guide
Primary Time Since Last
Backup - - 60 Major
Monitors the duration(in minutes), since the last log backup was taken on primary database server in log shipping environment. v4.4
Secondary Time Since
Last Copy - - 60 Major
Monitors the duration(in minutes), since the last log backup was copied on secondary database server in log shipping
environment. v4.4
Secondary Time Since
Last Restore - - 60 Major
Monitors the duration(in minutes), since the last log backup was restored on secondary database server in log shipping
Chapter 4: SQL Server Monitoring (sqlserver) Metrics 75
Secondary Last Restored
Latency - - 60 Major
Monitors duration(in minutes) from the creation of the last backup to restore of the backup in log shipping
environment. v4.4
Free Space with Available
Disk - - 10 Major
Monitors free space in filegroups after considering the
available disk size. v4.4
Log File Usage with
Available Disk - - 25 Major
Monitors free space in the database log files after
considering the