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
| Name | Required? | Description |
|---|---|---|
| Host | Required | The Microsoft SQL Server to connect to. |
| Port | The port for communication to the Microsoft SQL Server. | |
| Username | Required | |
| Password | Required | |
| SSL Configuration | The 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). | |
| Instance | Required | The SQL Server Instance to connect to. |
| Use NTLM v2 | Set to true to send LMv2/NTLMv2 responses when using Windows authentication. | |
| Query Count | The number of queries to collect. | |
| Query Order By | ||
| Excluded Queries | ||
| Connection Pool Size | ||
| Thread Pool Size | Maximum number of threads used to perform collection. | |
| Thread Timeout | ||
| Exclude Events | ||
| Exclude Log Based Events | ||
| Event Cutoff Time | How long ago (in minutes) to collect log-based events. |
Metrics
Always On Availability Group
| Name | Description |
|---|---|
| Automated Backup Preference | |
| Group ID | |
| Primary Recovery Health Description | |
| Primary Replica | |
| Replica ID | |
| Replicas | List of replicas being used by availability group. |
| Resource Name | |
| Secondary Recovery Health Description | Description of recovery health of the secondary replica. |
| Synchronization Health | Number represenation of rollup of synchronization health of all availability replicas in the availability group. |
| Synchronization Health Description Property | Description of rollup of synchronization health of all availability replicas in the availability group. |
Cluster Node
| Name | Description |
|---|---|
| Host | |
| Instance | |
| Is Current Owner | This metric signals whether the current node is the owner of the cluster. |
| Node Name | This is the name of a single node of a cluster. |
| Status Text | This metric is the text representation of a node's status. |
| Status Value | This metric is the integer value that represents a node's status. |
Database
| Name | Description |
|---|---|
| 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 Reason | If the database is suspended, the reason for the suspended state. |
| Synchronization Health | |
| Synchronization State | Description 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
| Name | Description |
|---|---|
| Drive ID | |
| Free Space (Mebibytes) | |
| Host | |
| Instance | |
| Resource Name | |
| Size (Mebibytes) |
File
| Name | Description |
|---|---|
| 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
| Name | Description |
|---|---|
| Availability Group Name | |
| Availability Mode Description | |
| Failover Mode Description | |
| Group ID | |
| Server Name |
Instance
| Name | Description |
|---|---|
| Active Connections (Connections) | |
| Agent Running | Indicates 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 Owner | The 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 Nodes | The number of nodes in a cluster in the up state. |
| Number of Unhealthy Cluster Nodes | The 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
| Name | Description |
|---|---|
| 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
| Name | Description |
|---|---|
| Command | |
| Host | |
| Instance | |
| Job ID | |
| Last Run Duration | Duration (hhmmss) of the step the last time it ran |
| Last Run Outcome | Outcome of the previous execution. 0-Failed,1-Succeeded,2-Retry,3-Canceled,5-Unknown |
| Step ID | |
| Step Name | |
| Step UID | |
| Subsystem | Name of the subsystem used by SQL Server Agent to execute the job step |
Program
| Name | Description |
|---|---|
| CPU Time (Milliseconds) | Sum of CPU time used by sessions of this program. |
| Host | |
| Instance | |
| Logical Reads | Number 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 | |
| Reads | Number of reads performed by requests of sessions of this program. |
| Writes | Number of writes performed by requests of sessions of this program. |
Query
| Name | Description |
|---|---|
| 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
| Name | Description |
|---|---|
| Host | |
| Instance | |
| Wait Time (Milliseconds) | Total wait time for this wait type in milliseconds. |
| Wait Type | The name of the type of wait being reported |
| Waiting Tasks Count | Number of waits on this wait type. This counter is incremented at the start of each wait. |
Updated almost 5 years ago