Microsoft SQL Server

Monitor Microsoft SQL Server

Data Collection Setup

Metrics are collected via JDBC from Microsoft SQL Server tables and views.

High Availability, Failover and Always On clusters are supported

For Standalone Configurations:

Hostname (or IPv4 address) of SQL Server instance is required

For Clustered Configurations:

Virtual IP (VIP), or hostname for the VIP, of SQL Server instance is required

🚧

CPU Usage Metric and SQL Server Editions

The CPU Usage metric for Microsoft SQL Server Instances is not available on MSSQL Server Standard or Express editions.

Network Requirements

Port: 1433 (TCP) Default to SQL Server Instance

🚧

If Using Dynamic Ports

If using dynamic ports, you will want to change the default port to SQL Server Browser Service UDP port 1434. For more information, see Microsoft's Configure the Windows Firewall to Allow SQL Server Access documentation topic.

Least Privileged User

Run the following script to automate creation of a local MS SQL Server LPU user

use master;
 
CREATE LOGIN bluemedora WITH PASSWORD = 'P@ssw0rd1';
CREATE USER bluemedora FOR LOGIN bluemedora;
GRANT CONNECT SQL to bluemedora;
GRANT VIEW SERVER STATE to bluemedora;
GRANT VIEW DATABASE STATE TO bluemedora;
GRANT VIEW ANY DEFINITION TO bluemedora;
GO 

GRANT EXEC on [xp_readerrorlog] to bluemedora
GO
 
use model;
CREATE USER bluemedora FOR LOGIN bluemedora;
GO
 
use msdb;
CREATE USER bluemedora FOR LOGIN bluemedora;
GRANT SELECT ON [dbo].[syssessions] TO bluemedora
GRANT SELECT ON [dbo].[sysjobhistory] to bluemedora
GRANT SELECT ON [dbo].[sysjobsteps] TO bluemedora
GRANT SELECT ON [dbo].[sysjobs_view] TO bluemedora
GRANT SELECT ON [dbo].[sysjobactivity] TO bluemedora
ALTER ROLE [SQLAgentReaderRole] ADD MEMBER [bluemedora] 
-- For SQL Server 2008 R2, use: sp_addrolemember [SQLAgentReaderRole], [bluemedora]


-- Goes through each user database and adds public permissions
DECLARE @name NVARCHAR(MAX)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
    EXECUTE('USE ' + @name + '; CREATE USER bluemedora FOR LOGIN bluemedora;' );
    FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor

Supported Versions

SQL Server:

  • 2008 R2
  • 2012
  • 2012 R2
  • 2014
  • 2016
  • 2019

Connection Parameters

NameRequired?Description
HostRequiredThe Microsoft SQL Server to connect to.
PortThe port for communication to the Microsoft SQL Server.
UsernameRequired
PasswordRequired
SSL ConfigurationThe SSL mode to use when connecting to the target. Can be configured to not use SSL (No SSL), use SSL but do not verify the target's certificate (No Verify), and use SSL and verify the target's certificate (Verify).
InstanceRequiredThe SQL Server Instance to connect to.
Use NTLM v2Set to true to send LMv2/NTLMv2 responses when using Windows authentication.
Query CountThe number of queries to collect.
Query Order By
Excluded Queries
Connection Pool Size
Thread Pool SizeMaximum number of threads used to perform collection.
Thread Timeout
Exclude Events
Exclude Log Based Events
Event Cutoff TimeHow long ago (in minutes) to collect log-based events.

Metrics

Always On Availability Group

NameDescription
Automated Backup Preference
Group ID
Primary Recovery Health Description
Primary Replica
Replica ID
ReplicasList of replicas being used by availability group.
Resource Name
Secondary Recovery Health DescriptionDescription of recovery health of the secondary replica.
Synchronization HealthNumber represenation of rollup of synchronization health of all availability replicas in the availability group.
Synchronization Health Description PropertyDescription of rollup of synchronization health of all availability replicas in the availability group.

Cluster Node

NameDescription
Host
Instance
Is Current OwnerThis metric signals whether the current node is the owner of the cluster.
Node NameThis is the name of a single node of a cluster.
Status TextThis metric is the text representation of a node's status.
Status ValueThis metric is the integer value that represents a node's status.

Database

NameDescription
Active Connections
Active Transactions
Auto Close
Auto Close Enabled
Auto Create Statistics
Auto Shrink
Auto Shrink Enabled
Auto Update Statistics
Availability Group Name
Buffer Pool Size (Mebibytes)
Creation Date
Data File Size (Kibibytes)
Data Space Usage (Mebibytes)
Disk Space Available (Mebibytes)
Effective Free Space (Mebibytes)The amount of space on disk that is usable by a database, this includes the free space of the entire disk, and any space being used by the database.
Effective Memory Pages Used (Pages)
Effective Memory Server Total (Mebibytes)
Effective Memory Used Total (Mebibytes)The amount of effective server memory in MB used by the database.
Host
Index Space Usage (Mebibytes)
Instance
IOPS (Operations)
Log Cache Hit Ratio
Log Cache Hit Ratio Base
Log Cache Reads (per Second)
Log File Size (Kibibytes)
Log File Usage (%)
Log File Used Size (Kibibytes)
Log Pool Cache Misses (per Second)Database log cache misses over time.
Log Pool Disk Reads (per Second)Number of disk reads due to log cache misses for a database.
Log Pool Requests (per Second)Database log cache requests over time.
Log Size (Mebibytes)
Name
Read Delay (Milliseconds)
Read Only
Read Operations (Operations)
Read Total (Bytes)
Recovery Model
Reserved Space (Mebibytes)
Reserved Space Unused (Mebibytes)
Resource Name
Row Size (Mebibytes)
Single User Mode
Size Used (Mebibytes)
Smallest Fixed File Growth Increment (Mebibytes)
Smallest Percent File Growth Increment (Mebibytes)
Space Available (%)
Space Used (%)
Status
Suspended ReasonIf the database is suspended, the reason for the suspended state.
Synchronization Health
Synchronization StateDescription of the data-movement state.
Time Since Creation (Days)
Total IO Data (Bytes)
Total Space (Mebibytes)
Total Used Disk Size (Mebibytes)Total database file size on disk, this includes allocated but unused.
Tracked Transactions (per Second)
Transactions (per Second)
User Lookups
User Scans
User Seeks
User Updates
Write Delay (Milliseconds)
Write Operations (Operations)
Write Total (Bytes)
Write Transactions (per Second)

Disk

NameDescription
Drive ID
Free Space (Mebibytes)
Host
Instance
Resource Name
Size (Mebibytes)

File

NameDescription
Allocated Free Space (Mebibytes)
Autogrow Amount (Mebibytes)
Autogrow Fixed (Mebibytes)
Autogrow Rate (%)
Autogrow Type
Database Name
Free Space (Mebibytes)
Host
ID
Instance
Maximum Size (Mebibytes)
Name
Physical Name
Size (Mebibytes)
Space Utilization (%)
State Description
Type Description
Used Space (Mebibytes)

HADR Replica Server

NameDescription
Availability Group Name
Availability Mode Description
Failover Mode Description
Group ID
Server Name

Instance

NameDescription
Active Connections (Connections)
Agent RunningIndicates whether or not the agent is running. Agent must run to retrieve MAC Address.
Available Physical Memory (Mebibytes)
Average Query CPU Time (Milliseconds)
Average Query Executions
Average Wait Time (Milliseconds)
Average Wait Time Base (Milliseconds)
AWE Enabled
Background Processes
Batch Compilation Ratio
Batch Requests
Blocked Processes
Buffer Cache Hit Ratio
Buffer Ideal Page Life Expectancy (Seconds)
Buffer Page Life Expectancy (Seconds)
Buffer Pool Hit Ratio
Buffer Pool Size (Mebibytes)
Checkpoint Pages
Cluster Owner Status
Cluster Status
Connection Memory (Kibibytes)
Connections (Connections)
Connections Reset
CPU Count
CPU Usage (%)
Creation Date
Current Cluster OwnerThe name of the node in the cluster that is the current owner.
Data File Size (Kibibytes)
Database Cache Memory (Kibibytes)
Database Pages (Pages)
Dead Locked Queries
Deadlocks
Dormant Processes
Effective Total Memory (Mebibytes)The total amount of memory available between SQL Server and unused memory on the host.
Failed Jobs
Forced Parameterizations
Free List Stalls
Free Memory (Kibibytes)
Granted Workspace Memory (Kibibytes)
High Query CPU Time (Milliseconds)
High Query Executions
Host
Instance
IOPS (Operations)
Lazy Writes
Lock Memory (Kibibytes)
Lock Requests
Lock Timeouts
Lock Wait Time (Milliseconds)
Lock Waits
Log Cache Hit Ratio
Log Cache Hit Ratio Base
Log Cache Reads
Log Cache Requests (Requests)
Log File Size (Kibibytes)
Log File Usage (%)
Log File Used Size (Kibibytes)
Log Pool Cache Misses
Log Pool Disk Reads
Log Pool Memory (Kibibytes)
Logins
Logouts
MAC Addresses
Maximum Server Memory (Mebibytes)
Maximum Thread Count
Maximum Workspace Memory (Kibibytes)
Memory Usage (%)The percentage of memory that is being utilized by SQL Server.
Most Recent Start Date
MSSQL Architecture Type
Number of Healthy Cluster NodesThe number of nodes in a cluster in the up state.
Number of Unhealthy Cluster NodesThe number of nodes in a cluster in a down, paused, or unknown state.
Optimizer Memory (Kibibytes)
OS Architecture Type
OS Memory (Gibibytes)
Page Lookups
Page Reads
Page Splits
Page Writes
Physical Memory Maximum Limit (Mebibytes)
Physical Memory Recommended (Mebibytes)
Physical Memory Used (Mebibytes)
Possible Connections (Connections)
Preconnect Processes
Procedure Cached Pages (Pages)
Product Version
Read Aheads
Read Operations (Operations)This metric represents the amount of disk read operations over time.
Reserved Server Memory (Kibibytes)
Resource Name
Runnable Processes
Runnable Tasks Count
Running Processes
Server Name
Service Name
Sleeping Processes
SQL Cache Memory (Kibibytes)
SQL Compilations
SQL Recompilation Ratio
SQL Recompilations
SQL Version
Stolen Server Memory (Mebibytes)
Successful Jobs
Suspended Processes
Target Pages (Pages)
Thread Count
Thread Size (Mebibytes)
Time Since Creation (Days)
Time Since Last Start (Days)
Total Disk Space (Mebibytes)
Total IO Data (Bytes)
Total Physical Memory (Mebibytes)
Total Query CPU Time (Milliseconds)
Total Query Executions
Total Read (Bytes)
Total Server Memory (Mebibytes)
Total Written (Bytes)
Tracked Transactions
Transactions
Type
Used Disk Space (Mebibytes)
User Connections (Connections)
Wait Time (Milliseconds)
Windows Version
Write Operations (Operations)This metrice represents the amount of disk write operations over time.
Write Transactions

Job

NameDescription
Description
Host
ID
Instance
Last Message
Last Run Duration (Seconds)
Last Run Status
Last Run Time
Last Step
Name
Next Scheduled Run
Raw Last Run Status

Job Step

NameDescription
Command
Host
Instance
Job ID
Last Run DurationDuration (hhmmss) of the step the last time it ran
Last Run OutcomeOutcome of the previous execution. 0-Failed,1-Succeeded,2-Retry,3-Canceled,5-Unknown
Step ID
Step Name
Step UID
SubsystemName of the subsystem used by SQL Server Agent to execute the job step

Program

NameDescription
CPU Time (Milliseconds)Sum of CPU time used by sessions of this program.
Host
Instance
Logical ReadsNumber of logical reads that have been performed by sessions of this program.
Maximum Elapsed Time (Milliseconds)Elapsed time of longest running session.
Memory Usage (Pages)Number of 8-KB pages of memory used by this program.
Name
Open Sessions
ReadsNumber of reads performed by requests of sessions of this program.
WritesNumber of writes performed by requests of sessions of this program.

Query

NameDescription
Average Execution Time (Microseconds)
Execution Count
Execution Time (Microseconds)
Last Execution (Microseconds)
Name
SQL Handle
Statement End Offset
Statement Start Offset
Text
Total Elapsed Time (Microseconds)
Total Logical Reads
Total Logical Writes
Total Physical Reads
Unique Query Plans

Wait Type

NameDescription
Host
Instance
Wait Time (Milliseconds)Total wait time for this wait type in milliseconds.
Wait TypeThe name of the type of wait being reported
Waiting Tasks CountNumber of waits on this wait type. This counter is incremented at the start of each wait.