Windows Counters

0. Overview MetricsWindows

UID: windows_countersVersion: 1.3.1 Panels: ~35 Datasource: InfluxDB (Telegraf)

Purpose

This dashboard shows key Windows performance counters for monitoring:

CPU: Processor usage and distribution
Memory: Available and used RAM
Disk: Free space and I/O operations
Network: Incoming and outgoing network traffic

This panel provides a real-time view of operating system resource consumption to correlate SQL Server performance with host-level activity.

Importance for SQL Server

SQL Server depends directly on the Windows operating system for access to CPU, memory, disk, and network. A problem at the Windows level always impacts SQL Server performance, making this dashboard essential for complete diagnostics.

Variables

Variable	Description	Query
`$hostname`	Hostname of the server to monitor	`SHOW TAG VALUES FROM "win_cpu" WITH KEY = "host"`

Color Codes

Color	Code	Meaning
Green	`#00db88`	Correct - Optimal state
Magenta	`#ad21d8e8`	To Check - Review
Red	`#F2495C`	Wrong - Critical state

Sections

Overview

General information about the selected server.

Telegraf Version stat

Telegraf version installed on the server.

Importance: Telegraf is the agent that collects operating system metrics. An outdated version may have known bugs, security issues, or lack important new metrics. Keeping Telegraf updated ensures the accuracy and availability of monitoring data.

Color mapping:

Version	Color	Note
1.30.0	Green	Updated version
1.25.0	Magenta	Update available
Others	-	Verify

Uptime stat

Time elapsed since the last server startup.

Importance: Low uptime indicates a recent server restart. This is critical for SQL Server because:

The buffer pool is completely emptied after a restart
Cached execution plans are lost
Performance statistics are reset
Active connections are interrupted

After a restart, SQL Server needs time to "warm up" its cache, which can cause temporary performance degradation.

Thresholds:

Value	Color	Status
< 2 days	Red	Recent restart
>= 2 days	Green	Normal

CPU Count stat

Number of CPU cores on the server.

Importance: The number of cores directly affects SQL Server's parallel processing capacity:

SQL Server uses schedulers (one per logical core by default)
Queries with parallel operators benefit from more cores
SQL Server licensing is core-based
The max degree of parallelism parameter should be configured based on available cores

CPU Usage gauge

Current CPU usage percentage.

Importance: CPU usage is one of the most critical indicators for SQL Server:

Sustained high CPU indicates inefficient queries or excessive load
SQL Server competes with other system processes for CPU
100% CPU causes SOS_SCHEDULER_YIELD wait types
Directly affects query response times

Correlation with SQL Server: When CPU is high, check in SQL Server:

Queries with high cpu_time in sys.dm_exec_requests
Inefficient execution plans (table scans, missing indexes)
Excessive plan recompilations

Thresholds:

Value	Color	Status
< 70%	Green	Normal
70% - 90%	Orange	Review load
> 90%	Red	Critical

Processes stat

Total number of processes running on the server.

Importance: An excessive number of processes may indicate:

Unnecessary software installed on the database server
Zombie or orphan processes consuming resources
Non-essential services that should be disabled

Microsoft recommends keeping SQL Server servers dedicated with minimal additional software to maximize available resources.

Memory

Free Memory stat

Remaining free memory on the server.

Importance: Windows free memory is critical for SQL Server:

SQL Server uses memory for the buffer pool (data cache)
Too little free memory can cause Windows to page to disk
Paging to disk severely degrades SQL Server performance
Windows needs memory for the operating system, services, and other applications

Correlation with SQL Server: If there is low free memory:

Verify the max server memory configuration in SQL Server
Review the Page Life Expectancy (PLE)
Look for excessive memory grants in queries
Evaluate if there is internal or external memory pressure

Thresholds:

Value	Color	Status
< 1 GB	Red	Critical
1 GB - 2 GB	Orange	Review
> 2 GB	Green	Normal

Critical Configuration

SQL Server should have max server memory configured to leave at least 4-6 GB free for Windows on dedicated servers. Without this configuration, SQL Server can consume all available memory.

Total Memory stat

Total memory assigned to the server.

Importance: Total memory determines the maximum size of the SQL Server buffer pool:

More memory = more cached data = fewer disk reads
SQL Server Standard Edition has a 128 GB buffer pool limit
Total memory should be sized according to active database sizes

Disk

Disk Free Space (%) bargauge

Percentage of free space on each server disk.

Importance: Disk free space is vital for SQL Server:

Data files (MDF/NDF) and logs (LDF) grow based on activity
TempDB can grow significantly with complex queries
Backups require temporary space
Without space, SQL Server cannot write and transactions fail

Correlation with SQL Server: Monitor especially:

Data disk: database growth
Log disk: large transactions, log shipping
TempDB disk: queries with sorts, hash joins, versioning
Backup disk: backup retention

Thresholds:

Value	Color	Status
< 10%	Red	Critical - very low space
10% - 20%	Orange	Review
> 20%	Green	Normal

Recommendation

It is recommended to maintain at least 20% free space on each disk to avoid performance issues and allow growth of data and log files. On SSD drives, free space also affects write performance.

Disk Free Space (MB) stat

Numeric value of free space on each disk in megabytes.

Importance: The absolute value in MB is important for planning:

How much time remains before running out of space
Whether there is enough space for maintenance operations (index rebuilds)
Database growth planning

Detailed CPU

CPU Usage Over Time timeseries

Graph showing CPU usage evolution over time.

Importance: CPU history allows identifying:

Usage patterns (peaks at specific times)
Load growth trends
Correlation with SQL Server events (backups, jobs, ETL)
Anomalies requiring investigation

Included metrics:

Percent Privileged Time: Time in kernel mode (system operations, I/O). High privileged time may indicate disk or network problems.
Percent User Time: Time executing application code (including SQL Server). This is the "useful" work.
Percent Interrupt Time: Time handling hardware interrupts. High interrupt time may indicate driver or hardware problems.

Correlation with SQL Server:

High Metric	Possible Cause in SQL Server
User Time	CPU-intensive queries, compilations
Privileged Time	I/O waits, disk reads
Interrupt Time	Network issues, storage drivers

Disk I/O

Disk I/O timeseries

Read and write operations per disk.

Importance: Disk I/O is frequently the main bottleneck for SQL Server:

High reads may indicate insufficient buffer pool (low PLE)
High writes may be checkpoints, logs, or TempDB
High disk latency causes PAGEIOLATCH_* wait types

Included metrics:

Disk Reads/sec: Read operations per second. Sustained high reads indicate memory pressure.
Disk Writes/sec: Write operations per second. High writes on log indicate high transactional activity.
Avg. Disk Queue Length: Average number of operations waiting. Values > 2 per physical disk indicate saturation.

Correlation with SQL Server:

I/O Symptom	Check in SQL Server
High Queue Length	Wait stats: PAGEIOLATCH_*, WRITELOG
High Reads	Page Life Expectancy, missing indexes
High Writes on log	Large transactions, log shipping lag
High Writes on tempdb	Spills, version store, temp tables

Network

Network Traffic timeseries

Incoming and outgoing network traffic.

Importance: Network traffic affects SQL Server in:

Response time to remote clients
Backup speed to network locations
Replication and Always On Availability Groups
Linked servers and distributed queries

Included metrics:

Bytes Received/sec: Incoming data. High incoming traffic may be bulk inserts or restores.
Bytes Sent/sec: Outgoing data. High outgoing traffic may be queries returning large amounts of data.

Correlation with SQL Server:

Network saturation causes ASYNC_NETWORK_IO wait types
Affects Always On synchronous commit latency
Impacts backups/restores to network locations

Interpretation Guide

Critical Indicators

Metric	Critical Value	Action
CPU Usage	> 90% sustained	Review queries, heavy processes
Free Memory	< 1 GB	Evaluate memory configuration
Disk Free Space	< 10%	Free up space urgently
Disk Queue	> 2	Possible I/O bottleneck

Correlation with SQL Server

This dashboard is fundamental for correlating SQL Server problems with the operating system:

Symptom in SQL Server	Check in Windows
Slow queries	High CPU, High Disk Queue
Low Page Life Expectancy	Low Free Memory
Connection timeouts	Network Traffic, CPU
Slow backups	Disk I/O, Disk Free Space

Combined Diagnostics: Windows + SQL Server

For effective diagnostics, it is necessary to correlate Windows metrics with SQL Server metrics. This section explains how to combine both sources of information.

Diagnostic Methodology

Identify the symptom in SQL Server (slow queries, timeouts, errors)
Review wait statistics in SQL Server to identify the wait type
Correlate with Windows metrics according to the following table
Take corrective actions based on the combined analysis

Detailed Correlation Matrix

Wait Type in SQL Server	Windows Metric	What to Look For	Recommended Action
`SOS_SCHEDULER_YIELD`	CPU Usage	CPU > 90% sustained	Optimize queries, add cores
`PAGEIOLATCH_*`	Disk Queue, Disk Reads	Queue > 2, High Reads	More memory, SSD, optimize queries
`WRITELOG`	Disk Writes (log disk)	High latency on log	SSD for logs, separate disks
`ASYNC_NETWORK_IO`	Network Traffic	Network saturation	Optimize result sets, check network
`RESOURCE_SEMAPHORE`	Free Memory	Free memory < 2 GB	Adjust max server memory
`CXPACKET/CXCONSUMER`	CPU Usage per core	Unbalanced cores	Adjust MAXDOP

Common Scenarios

Scenario 1: Slow Queries with High CPU

Windows shows CPU > 85%
SQL Server shows SOS_SCHEDULER_YIELD waits
Diagnosis: SQL Server is CPU-bound
Actions:
- Identify queries with high cpu_time in sys.dm_exec_requests
- Review execution plans looking for table scans
- Verify missing indexes
- Consider adding cores

Scenario 2: Slow Queries with High I/O

Windows shows Disk Queue > 2
SQL Server shows PAGEIOLATCH_SH or PAGEIOLATCH_EX waits
Diagnosis: SQL Server is I/O-bound
Actions:
- Verify Page Life Expectancy (if low, add RAM)
- Review missing indexes causing table scans
- Consider migrating to SSD
- Verify that data, log, and tempdb disks are separate

Scenario 3: Low Memory with Low PLE

Windows shows Free Memory < 2 GB
SQL Server shows PLE < 300 seconds
Diagnosis: External memory pressure or incorrect configuration
Actions:
- Verify that max server memory is configured
- Identify other processes consuming memory
- Consider adding more RAM
- Review excessive memory grants in queries

Scenario 4: Recent Restart (Low Uptime)

Windows shows Uptime < 2 days
SQL Server shows degraded performance
Diagnosis: Cold buffer pool post-restart
Actions:
- Wait for the buffer pool to "warm up" naturally
- Run frequent queries to pre-load data
- Investigate the cause of the restart
- Document the event for pattern analysis

Windows Best Practices for SQL Server

This section details the Windows configurations recommended by Microsoft for servers running SQL Server.

Memory Configuration

Recommendations:

Reserve memory for Windows: Configure max server memory in SQL Server leaving:
- 4 GB free for servers with 16 GB or less
- 6-8 GB free for servers with 32-64 GB
- 10% of total RAM for servers with more than 64 GB
Disable kernel paging:
- In system properties > Advanced > Performance > Advanced
- Configure virtual memory manually if necessary
Lock Pages in Memory: Grant this privilege to the SQL Server service account to prevent Windows from paging the buffer pool to disk.

Power Plan

Critical Configuration

The Windows Power Plan dramatically affects SQL Server performance.

Recommended configuration:

Always use the High Performance plan
Never use "Balanced" or "Power Saver" on production servers

How to verify and change:

Control Panel > Power Options
Select "High performance"
Verify in advanced settings that minimum processor state is 100%

Impact of incorrect Power Plan:

Inconsistent query latency
Spinlocks and CPU waits
Unpredictable performance

Antivirus Exclusions

Antivirus software can cause severe performance problems if it scans SQL Server files.

Files and folders to exclude:

Type	Extensions/Paths
Data files	`.mdf`, `.ndf`
Log files	`*.ldf`
Backups	`.bak`, `.trn`
Full-text	`*.ndf` in FT folders
Data folder	`C:\Program Files\Microsoft SQL Server\MSSQLXX.INSTANCE\MSSQL\DATA\`
Log folder	Transaction log location
Backup folder	Backup location
TempDB	TempDB file location

Processes to exclude:

sqlservr.exe
sqlagent.exe
sqlwriter.exe
msmdsrv.exe (if using Analysis Services)
ReportingServicesService.exe (if using SSRS)

Windows Update Scheduling

Recommendations:

Use WSUS or SCCM to control updates on production servers
Schedule updates outside of production hours
Test updates first in development/QA environments
Coordinate with maintenance windows for SQL Server
Avoid automatic updates that could restart the server without notice

Recommended configuration:

Configure active hours to avoid restarts during production
Use group policies to control Windows Update behavior
Document all applied updates

Other Important Configurations

Configuration	Recommendation	Reason
Windows Firewall	Allow SQL Server ports (1433, 1434)	Connectivity
Remote Desktop	Enable only for administration	Remote access
Windows Defender	Exclude SQL Server files	Performance
Unnecessary services	Disable	Reduce attack surface and resources
Instant File Initialization	Enable (SE_MANAGE_VOLUME_NAME)	Fast file creation
Perform Volume Maintenance Tasks	Grant to SQL Server account	Enables IFI

Telegraf Metrics

This dashboard uses metrics collected by Telegraf in InfluxDB:

Measurement	Description
`win_cpu`	CPU metrics
`win_mem`	Memory metrics
`win_disk`	Disk metrics
`win_system`	System metrics
`win_net`	Network metrics
`internal_gather`	Telegraf information
`sqlserver_server_properties`	SQL Server properties

Telegraf Configuration

For this dashboard to work correctly, Telegraf must be configured with the following inputs:

toml

[[inputs.win_perf_counters]]
  # CPU
  [[inputs.win_perf_counters.object]]
    ObjectName = "Processor"
    Counters = ["% Idle Time", "% Interrupt Time", "% Privileged Time", "% User Time"]
    Instances = ["_Total"]
    Measurement = "win_cpu"

  # Memory
  [[inputs.win_perf_counters.object]]
    ObjectName = "Memory"
    Counters = ["Available Bytes", "Committed Bytes"]
    Measurement = "win_mem"

  # Disk
  [[inputs.win_perf_counters.object]]
    ObjectName = "LogicalDisk"
    Counters = ["% Free Space", "Free Megabytes", "Disk Reads/sec", "Disk Writes/sec"]
    Instances = ["*"]
    Measurement = "win_disk"

Microsoft References

Official Documentation

Hardware and Software Requirements for SQL Server - Hardware and software requirements for installing SQL Server
Configure Windows Service Accounts and Permissions - Service account and permission configuration
Server Memory Configuration Options - Server memory configuration options
Lock Pages in Memory Option - Enable Lock Pages in Memory
Database Engine Instances - Instant File Initialization - Instant file initialization

Performance and Optimization

Performance Monitoring and Tuning Tools - Performance monitoring and tuning tools
Monitor Resource Usage with System Monitor - Monitor resource usage with System Monitor
SQL Server Best Practices - Performance - SQL Server Performance Center

Security and Antivirus

How to choose antivirus software on SQL Server - How to choose antivirus software for SQL Server
Antivirus software on servers running SQL Server - Antivirus software on servers running SQL Server

Power Management

Processor power management in Windows - Processor power management in Windows Server
Power Plan settings for SQL Server - Includes Power Plan recommendations

Windows Server for SQL Server

Windows Server documentation - General Windows Server documentation
Performance tuning for Windows Server - Windows Server performance optimization

Windows Counters ​

Purpose ​

Variables ​

Color Codes ​

Sections ​

Overview ​

Telegraf Version stat

Uptime stat

CPU Count stat

CPU Usage gauge

Processes stat

Memory ​

Free Memory stat

Total Memory stat

Disk ​

Disk Free Space (%) bargauge

Disk Free Space (MB) stat

Detailed CPU ​

CPU Usage Over Time timeseries

Disk I/O ​

Disk I/O timeseries

Network ​

Network Traffic timeseries

Interpretation Guide ​

Critical Indicators ​

Correlation with SQL Server ​

Combined Diagnostics: Windows + SQL Server ​

Diagnostic Methodology ​

Detailed Correlation Matrix ​

Common Scenarios ​

Scenario 1: Slow Queries with High CPU ​

Scenario 2: Slow Queries with High I/O ​

Scenario 3: Low Memory with Low PLE ​

Scenario 4: Recent Restart (Low Uptime) ​

Windows Best Practices for SQL Server ​

Memory Configuration ​

Power Plan ​

Antivirus Exclusions ​

Windows Update Scheduling ​

Other Important Configurations ​

Telegraf Metrics ​

Telegraf Configuration ​

Microsoft References ​

Official Documentation ​

Performance and Optimization ​

Security and Antivirus ​

Power Management ​

Windows Server for SQL Server ​

Windows Counters

Purpose

Variables

Color Codes

Sections

Overview

Memory

Disk

Detailed CPU

Disk I/O

Network

Interpretation Guide

Critical Indicators

Correlation with SQL Server

Combined Diagnostics: Windows + SQL Server

Diagnostic Methodology

Detailed Correlation Matrix

Common Scenarios

Scenario 1: Slow Queries with High CPU

Scenario 2: Slow Queries with High I/O

Scenario 3: Low Memory with Low PLE

Scenario 4: Recent Restart (Low Uptime)

Windows Best Practices for SQL Server

Memory Configuration

Power Plan

Antivirus Exclusions

Windows Update Scheduling

Other Important Configurations

Telegraf Metrics

Telegraf Configuration

Microsoft References

Official Documentation

Performance and Optimization

Security and Antivirus

Power Management

Windows Server for SQL Server