How to Monitor Microsoft SQL Server

Overview

Microsoft SQL Server is a powerful relational database management system that supports transaction processing, business intelligence, and analytics applications. Xitoring's MS SQL Server integration provides real-time visibility into query performance, resource consumption, lock contention, and active sessions.

What Can It Monitor?

Buffer Cache Hit Ratio — Percentage of pages found in the buffer cache
Batch Requests per Second — Rate of T-SQL batch requests
SQL Compilations per Second — Rate of query compilations
Transactions per Second — Transaction throughput
User Connections — Active user connections
Lock Timeouts per Second — Lock timeout occurrences
Deadlocks per Second — Deadlock detection count
Latch Waits per Second — Internal latch contention rate
Page Lookups / Reads / Writes per Second — Buffer pool I/O operations
Inserts / Updates / Deletes / Selects per Second — DML operation rates
Memory Usage — RAM consumed by SQL Server

Prerequisites

1. Create a Monitoring User

Connect to SQL Server and create a dedicated login:

CREATE LOGIN xitoring WITH PASSWORD = 'YOUR_SECURE_PASSWORD';

Create a user for the login:

CREATE USER xitoring FOR LOGIN xitoring;

2. Grant Required Privileges

GRANT CONNECT ANY DATABASE TO xitoring;
GRANT VIEW SERVER STATE TO xitoring;
GRANT VIEW ANY DEFINITION TO xitoring;
GRANT SELECT TO xitoring;

Supported versions: MS SQL Server 2012 and later. The size_on_disk_bytes stat is only available in SQL Server 2022+.

How to Activate the Integration

Run the Xitogent CLI:

xitogent integrate

Select SQL Server from the list. When prompted, provide:

IP address (e.g., 127.0.0.1)
Port (default: 1433)
Username and password for the monitoring user

Xitogent tests the connection and completes setup automatically. Within moments, real-time graphs appear on your server page.

Setting Up Triggers

Available trigger parameters:

Buffer Cache Hit Ratio
Batch Requests / SQL Compilations / Transactions per Second
User Connections
Lock Timeouts / Deadlocks per Second
Latch Waits per Second
Page Lookups / Reads / Writes per Second
DML rates (Inserts, Updates, Deletes, Selects)
Memory Usage

Navigate to Triggers on your server page, select SQL Server, choose a metric, set your threshold, and configure notification channels.

Tips

Always create a dedicated monitoring user — never use sa or admin accounts
Monitor Buffer Cache Hit Ratio — it should stay above 95% for optimal performance
Set alerts on Deadlocks per Second to catch concurrency issues immediately
Track Batch Requests per Second to understand query load patterns
Watch User Connections to detect connection pool exhaustion
Works on both Linux and Windows servers