How to Set Up SQL Server Monitoring on Windows Server 2012 R2

November 19, 2025
Tutorials
Comment off

How to Set Up SQL Server Monitoring on Windows Server 2012 R2

Monitoring SQL Server on Windows Server 2012 R2 requires a multi-layered approach that covers both OS-level performance (CPU, memory, disk I/O) and SQL Server-specific metrics (wait statistics, query performance, blocking, index fragmentation, database file sizes, and backup status). This guide covers setting up comprehensive SQL Server monitoring using built-in tools (Performance Monitor, SQL Server DMVs, SQL Server Agent), the free SQL Server Management Studio (SSMS) reports, and PowerShell scripting for automated health checks and alerting.

Prerequisites

SQL Server 2012 or SQL Server 2014 running on Windows Server 2012 R2 is assumed. SQL Server Management Studio must be installed. The monitoring user account needs VIEW SERVER STATE permission for DMV queries. SQL Server Agent must be running for automated job-based monitoring. Administrator or sysadmin rights are required for some configuration steps.

Step 1: Configure SQL Server Performance Counters in PerfMon

SQL Server registers performance counter objects in Windows that can be monitored via Performance Monitor alongside OS counters. Add these critical SQL Server counters to a Data Collector Set:

SQLServer:Buffer ManagerBuffer cache hit ratio
SQLServer:Buffer ManagerPage life expectancy
SQLServer:Memory ManagerMemory Grants Pending
SQLServer:Memory ManagerTotal Server Memory (KB)
SQLServer:General StatisticsUser Connections
SQLServer:General StatisticsLogins/sec
SQLServer:SQL StatisticsBatch Requests/sec
SQLServer:SQL StatisticsSQL Compilations/sec
SQLServer:SQL StatisticsSQL Re-Compilations/sec
SQLServer:Locks(_Total)Lock Waits/sec
SQLServer:Locks(_Total)Average Wait Time (ms)
SQLServer:Access MethodsFull Scans/sec
SQLServer:Access MethodsIndex Searches/sec

Create the DCS via logman:

logman create counter "SQL Server Monitoring" -cf "C:PerfDataSQLCounters.txt" -si 30 -f bincirc -max 500 -o "C:PerfDataSQLServer" -b 22:00 -e 06:00
logman start "SQL Server Monitoring"

Step 2: Monitor SQL Server Wait Statistics

Wait statistics reveal what SQL Server is waiting for and are the most reliable indicator of performance bottlenecks. Query the sys.dm_os_wait_stats DMV to identify top waits:

-- Top 15 wait types by total wait time (run in SSMS or via PowerShell)
SELECT TOP 15
    wait_type,
    waiting_tasks_count,
    wait_time_ms / 1000.0 AS wait_time_sec,
    max_wait_time_ms / 1000.0 AS max_wait_sec,
    (wait_time_ms - signal_wait_time_ms) / 1000.0 AS resource_wait_sec
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
    'SLEEP_TASK','BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_AUTO_EVENT',
    'DISPATCHER_QUEUE_SEMAPHORE','FT_IFTS_SCHEDULER_IDLE_WAIT',
    'HADR_FILESTREAM_IOMGR_IOCOMPLETION','HADR_WORK_QUEUE',
    'SQLTRACE_INCREMENTAL_FLUSH_SLEEP','WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
    'XE_DISPATCHER_WAIT','XE_TIMER_EVENT','SLEEP_DBSTARTUP',
    'SLEEP_MASTERDBREADY','SLEEP_MASTERMDREADY','SLEEP_MASTERUPGRADED'
)
ORDER BY wait_time_ms DESC;

Step 3: Monitor Running Queries and Blocking

Identify currently running queries and blocking chains:

-- Find blocking chains
SELECT
    blocking.spid AS BlockingID,
    blocked.spid AS BlockedID,
    blocked.wait_time / 1000 AS WaitSeconds,
    blocked.loginame AS BlockedUser,
    blocking.loginame AS BlockingUser,
    SUBSTRING(bt.text, 1, 500) AS BlockingQuery,
    SUBSTRING(bdt.text, 1, 500) AS BlockedQuery
FROM sys.sysprocesses AS blocked
INNER JOIN sys.sysprocesses AS blocking ON blocked.blocked = blocking.spid
CROSS APPLY sys.dm_exec_sql_text(blocking.sql_handle) AS bt
CROSS APPLY sys.dm_exec_sql_text(blocked.sql_handle) AS bdt
WHERE blocked.blocked > 0;

-- Find long-running queries (over 60 seconds)
SELECT
    r.session_id,
    r.status,
    r.start_time,
    DATEDIFF(SECOND, r.start_time, GETDATE()) AS ElapsedSeconds,
    r.cpu_time,
    r.logical_reads,
    SUBSTRING(t.text, 1, 500) AS QueryText,
    r.wait_type,
    r.blocking_session_id
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE DATEDIFF(SECOND, r.start_time, GETDATE()) > 60
ORDER BY ElapsedSeconds DESC;

Step 4: Monitor Database File Sizes and Growth

Track database file growth to proactively identify capacity issues before disks run out of space:

-- Database sizes and free space
SELECT
    DB_NAME(database_id) AS DatabaseName,
    type_desc AS FileType,
    name AS LogicalName,
    physical_name AS PhysicalPath,
    size * 8 / 1024 AS SizeMB,
    FILEPROPERTY(name, 'SpaceUsed') * 8 / 1024 AS UsedMB,
    (size - FILEPROPERTY(name, 'SpaceUsed')) * 8 / 1024 AS FreeMB
FROM sys.master_files
ORDER BY DatabaseName, FileType;

Step 5: Monitor SQL Server Backup Status

Verify all databases are being backed up regularly. Missing backups represent a critical data protection gap:

-- Databases with no backup in the last 24 hours
SELECT
    d.name AS DatabaseName,
    d.state_desc AS State,
    COALESCE(CONVERT(VARCHAR, MAX(b.backup_finish_date), 120), 'NEVER') AS LastFullBackup,
    COALESCE(DATEDIFF(HOUR, MAX(b.backup_finish_date), GETDATE()), 9999) AS HoursSinceBackup
FROM sys.databases d
LEFT JOIN msdb.dbo.backupset b ON d.name = b.database_name AND b.type = 'D'
WHERE d.state_desc = 'ONLINE'
    AND d.name NOT IN ('tempdb')
GROUP BY d.name, d.state_desc
HAVING COALESCE(DATEDIFF(HOUR, MAX(b.backup_finish_date), GETDATE()), 9999) > 25
ORDER BY HoursSinceBackup DESC;

Step 6: Create SQL Server Agent Alerts

SQL Server Agent supports automated alerts based on SQL Server error numbers, severity levels, and performance counter thresholds:

-- Create an operator (alert recipient)
EXEC msdb.dbo.sp_add_operator
    @name = 'DBAdmins',
    @email_address = '[email protected]',
    @enabled = 1;

-- Create alert for severity 17+ errors (insufficient resources)
EXEC msdb.dbo.sp_add_alert
    @name = 'Severity 17+ Error',
    @message_id = 0,
    @severity = 17,
    @enabled = 1,
    @delay_between_responses = 300,
    @notification_message = 'SQL Server severity 17 or higher error occurred';

-- Assign operator to receive the alert
EXEC msdb.dbo.sp_add_notification
    @alert_name = 'Severity 17+ Error',
    @operator_name = 'DBAdmins',
    @notification_method = 1;  -- Email

Step 7: Automate Health Checks with PowerShell

Create a daily PowerShell health check script that queries key SQL metrics and emails a summary report:

Import-Module SqlServer -ErrorAction SilentlyContinue
$sqlServer = "localhost"
$cred = [System.Data.SqlClient.SqlConnection]::new("Server=$sqlServer;Integrated Security=True")

# Check SQL Server service status
$sqlService = Get-Service -Name "MSSQLSERVER" -ErrorAction SilentlyContinue
$agentService = Get-Service -Name "SQLSERVERAGENT" -ErrorAction SilentlyContinue

$report = "SQL Server Daily Health Check`n"
$report += "Date: $(Get-Date)`n"
$report += "SQL Service: $($sqlService.Status)`n"
$report += "SQL Agent: $($agentService.Status)`n"

# Check for failed SQL Agent jobs in last 24 hours
$failedJobs = Invoke-Sqlcmd -ServerInstance $sqlServer -Query @"
SELECT j.name, jh.run_date, jh.run_time, jh.message
FROM msdb.dbo.sysjobhistory jh
INNER JOIN msdb.dbo.sysjobs j ON jh.job_id = j.job_id
WHERE jh.run_status = 0
  AND jh.step_id = 0
  AND jh.run_date >= CONVERT(INT, CONVERT(VARCHAR, GETDATE()-1, 112))
"@

if ($failedJobs) {
    $report += "`nFAILED SQL AGENT JOBS:`n"
    $report += ($failedJobs | Format-Table | Out-String)
} else {
    $report += "`nAll SQL Agent jobs succeeded in last 24 hours.`n"
}

Send-MailMessage -SmtpServer "smtp.yourdomain.com" -From "[email protected]" -To "[email protected]" -Subject "SQL Health Check - $env:COMPUTERNAME" -Body $report

Summary

Comprehensive SQL Server monitoring on Windows Server 2012 R2 requires combining OS-level performance counter monitoring with SQL Server-specific DMV queries, backup status verification, and automated alerting through SQL Server Agent. By establishing wait statistics baselines, monitoring for blocking, tracking file growth trends, and automating daily health checks, database administrators can proactively identify performance degradation and capacity issues before they cause service disruptions or data loss.

How to Set Up SQL Server Monitoring on Windows Server 2012 R2