SQL Server Counters

0. Overview MetricsSQL Server

UID: sql_server_countersVersion: 1.3.1 Panels: ~72 Datasources: MSSQL, InfluxDB

Purpose

This dashboard allows reviewing the global status of SQL Server instances, including critical aspects such as:

• Database size and log files
• Memory usage and buffer pool status
• Performance counters internal to SQL Server
• Page Life Expectancy and Buffer Cache Hit Ratio
• I/O metrics and disk latency
• High availability status (Availability Groups)

Variables

Variable	Description
$Instance	SQL Server instance to monitor

---

Sections

Instance Info

General information about the selected instance, including uptime, memory configuration, and key counters.

---

Up Time stat

Time elapsed since the last SQL Server instance startup.

Importance: A recent restart may indicate planned maintenance, patch updates, or an unexpected problem such as a crash or out of memory.

Thresholds:

Value	Color	Status
< 1 day	Magenta	Review - recent restart
>= 1 day	Green	Normal

Practical recommendation

After a restart, SQL Server needs time to "warm up" its cache. Buffer Cache Hit Ratio and PLE will be low initially. Monitor these values during the first hours to ensure they stabilize.

---

Max SQL Server Memory stat

Maximum amount of memory SQL Server can use (Target Server Memory).

Importance: According to official Microsoft documentation, correctly configuring maximum memory is critical to prevent SQL Server from consuming all the server's RAM, leaving the operating system without resources.

Formula recommended by Microsoft:

• Dedicated servers: Total RAM - 4 GB (for the OS) - RAM for other services
• Servers with 16+ GB: Leave 4 GB for the first 16 GB, plus 1 GB for each additional 8 GB

Best practice

Never leave this value at the default (2,147,483,647 MB). A server with 32 GB of RAM should have Max Server Memory configured at approximately 26-28 GB.

---

Used SQL Server Memory stat

Amount of memory SQL Server is currently using (Total Server Memory).

Importance: The difference between Target and Total Server Memory indicates if SQL Server is under memory pressure. If Total is significantly less than Target, it may indicate the system doesn't have enough load or there are configuration issues.

---

Free Space in Tempdb stat

Free space within tempdb files. Indicates the difference between allocated space and space used by objects.

Importance: TempDB is used by SQL Server for sorts, hash joins, temporary tables, row versioning (RCSI/Snapshot), and internal operations. It is one of the most critical databases for performance.

Thresholds:

Value	Color	Action
< 512 MB	Red	Critical - free up space
512 MB - 1 GB	Magenta	Review
> 1 GB	Green	Normal

TempDB Best Practices (Microsoft)

According to Microsoft recommendations:

1. Multiple data files: Create one file per logical core (up to 8 files)
2. Equal size: All files should have the same initial size and growth
3. Dedicated disk: Place TempDB on fast disks (SSD/NVMe) separate from user data
4. Pre-size: Configure an appropriate initial size to avoid frequent auto-growth

---

Page Life Expectancy (PLE) stat

Time in seconds that a data page remains in the buffer pool before being discarded. It is the most important indicator of memory pressure in SQL Server.

Importance: PLE is one of the most critical counters according to Microsoft documentation on Buffer Manager. A low PLE means pages are being evicted quickly from the buffer pool, forcing constant reads from disk.

Interpretation:

Value	Status	Action
< 300 sec	Red - Critical	Investigate immediately
300 - 1000 sec	Magenta - Review	Monitor trend
1000 - 3000 sec	Yellow	Acceptable but improvable
> 3000 sec	Green - Normal	Healthy system

Common causes of low PLE

1. Insufficient memory: Max Server Memory configured too low
2. Table scans: Queries scanning entire tables instead of using indexes
3. Missing indexes: Lack of appropriate indexes for executed queries
4. Queries with large results: SELECT * from large tables without filters
5. Peak workload: Temporary increase in activity

Practical recommendation

Modern PLE formula: Microsoft no longer recommends a fixed value of 300. The updated formula is:

Minimum PLE = (Max Server Memory in GB / 4) x 300

Example: Server with 64 GB -> Minimum PLE = (64/4) x 300 = 4,800 seconds

Remediation actions:

1. Review queries with high logical reads using sys.dm_exec_query_stats
2. Identify missing indexes with sys.dm_db_missing_index_details
3. Evaluate increasing Max Server Memory
4. Analyze if there are unnecessary table scans

---

Buffer Cache Hit Ratio stat

Percentage of data pages found in memory cache without needing to read from disk.

Importance: This counter measures buffer pool efficiency. According to Microsoft, a high value indicates most data requests are satisfied from memory.

Interpretation:

Value	Status	Action
< 95%	Red	Critical - serious memory problem
95% - 98%	Magenta	Review memory configuration
98% - 99%	Yellow	Acceptable
> 99%	Green	Optimal

Important note

For OLTP systems, this value should always be above 99%. Lower values are acceptable only for:

• Data Warehouse systems with ad-hoc queries
• Immediately after a restart (warm-up period)
• Workloads reading rarely accessed historical data

Difference between PLE and Buffer Cache Hit Ratio:

• Buffer Cache Hit Ratio: Measures hit percentage (instantaneous)
• PLE: Measures how long pages remain (stability)

A system can have good Hit Ratio but poor PLE if pages are constantly rotating.

---

Backup/Restore Throughput stat

Speed of backup and restore operations in bytes per second.

Importance: Allows evaluating if backups are operating at optimal speed or if there are I/O bottlenecks.

Backup optimization

To improve backup throughput:

1. Use backup compression (typical reduction of 60-80%)
2. Perform backups to multiple files in parallel
3. Configure appropriate BUFFERCOUNT and MAXTRANSFERSIZE
4. Use fast storage for backup destination

---

CPU Count stat

Number of CPU cores available to SQL Server.

Importance: This value directly affects MAXDOP configuration and licensing. SQL Server Standard Edition is limited to 24 cores.

---

Memory Grants Pending stat

Number of processes waiting to be allocated workspace memory to execute operations.

Importance: According to Microsoft, this counter indicates if there are queries waiting for memory to perform sorts, hash joins, or other operations that require workspace memory.

Interpretation:

Value	Status	Action
0	Green	Normal - no waits
1-5	Magenta	Review queries with high memory grants
> 5	Red	Critical - investigate immediately

Impact of Memory Grants Pending > 0

When there are queries waiting for memory grants:

1. Queries queue up and wait, increasing latency
2. Overall system performance degrades
3. Users experience timeouts

The value should ALWAYS be zero on a healthy system.

Remediation actions:

1. Identify queries with excessive memory grants using sys.dm_exec_query_memory_grants
2. Review queries with incorrect cardinality estimates
3. Update statistics with UPDATE STATISTICS
4. Consider Resource Governor to limit memory grants per query

---

Active Temp Tables stat

Number of temporary tables (#temp) currently existing on the server.

Importance: A very high number may indicate applications are creating many temporary tables without releasing them, or that there are long-running sessions accumulating temporary objects.

---

Version Store Size stat

Size of the version store in TempDB used for Snapshot Isolation and RCSI.

Importance: The Version Store is used by:

• Read Committed Snapshot Isolation (RCSI): Allows consistent reads without locks
• Snapshot Isolation: Transaction-level isolation
• Online Index Operations: Index rebuilds without blocking reads
• Always On Readable Secondaries: Reads on secondary replicas

According to Microsoft

A very large Version Store may indicate:

• Long-running transactions maintaining old versions
• High write load with RCSI enabled
• Long-running queries on Always On secondary replicas

Monitor with: sys.dm_tran_version_store_space_usage

---

Checkpoint pages/sec stat

Modified pages (dirty pages) written to disk during checkpoint operations.

Importance: Checkpoints write modified pages from the buffer pool to disk to guarantee transaction durability. A sustained very high value may indicate:

• High write load
• Very frequent checkpoints
• Possible I/O bottleneck

Indirect Checkpoints (SQL Server 2016+)

Microsoft recommends using Indirect Checkpoints with TARGET_RECOVERY_TIME set to 60 seconds. This provides more predictable recovery times and reduces I/O spikes.

---

Page lookups/sec stat

Number of requests per second to locate a page in the buffer pool.

Importance: Indicates the level of logical read activity. Very high values may indicate inefficient queries reading many pages.

---

Average User Connections stat

Average simultaneous user connections. SQL Server supports a maximum of 32,767 connections.

Thresholds:

Value	Status	Action
< 25,000	Green	Normal
>= 25,000	Magenta	Review - near limit

Connection Pooling

A very high number of connections may indicate connection pooling problems in applications. Each connection consumes memory (~1.5 MB per connection). Verify:

1. That applications use connection pooling
2. That connections are closed correctly
3. That there are no orphaned connections

---

Databases Info

Status and health of databases in the instance.

---

Databases Health stat

Aggregated health indicator. Sum of databases in problematic states.

Interpretation:

Value	Text	Status
0	HEALTHY	Green - all databases operational
>= 1	NOT HEALTHY	Magenta - investigate immediately

---

Database States stat

Individual counters by database state.

Database states according to Microsoft:

State	Description	Action
ONLINE	Database accessible	Normal state
OFFLINE	Manually disconnected	Verify if intentional
SUSPECT	Possible corruption detected	Run DBCC CHECKDB, restore if necessary
RECOVERING	In recovery process	Wait for completion
PENDING	Resource error during recovery	Review error logs
RESTORING	Restore in progress	Normal transient state

SUSPECT State

A SUSPECT database indicates the primary filegroup may be damaged. Immediate actions:

1. Review the SQL Server Error Log
2. Run DBCC CHECKDB('database_name') WITH NO_INFOMSGS
3. If corruption exists, evaluate repair or restore options
4. Never use DBCC CHECKDB WITH REPAIR_ALLOW_DATA_LOSS without understanding the consequences

---

Database Size

Visualization of individual database sizes and temporal evolution.

---

Data File Size / Log File Size piechart

Visual distribution of data and log file sizes by database.

File management best practices

1. Pre-size files: Avoid frequent auto-growth
2. Growth in MB, not %: Use fixed increments (512 MB - 1 GB)
3. Separate data and log: Place on different disks if possible
4. Monitor growth: Detect abnormal growth early

---

Performance Counters

Temporal evolution charts of the main performance counters.

---

SQL Stats timeseries

SQL activity metrics: batch requests, compilations, logins, and blocks.

Included metrics and their meaning:

Metric	Description	Ideal Value
Batch Requests/sec	Number of SQL batches received	Depends on workload
SQL Compilations/sec	New execution plans created	< 10% of Batch Requests
SQL Re-Compilations/sec	Plans recompiled	< 1% of Compilations
Processes blocked	Processes waiting for locks	0

Compilation Ratio

If SQL Compilations/sec is high relative to Batch Requests/sec, it indicates plans are not being reused. Common causes:

• Ad-hoc queries without parameterization
• Excessive use of dynamic SQL
• Unnecessary OPTION (RECOMPILE)
• Outdated statistics

Solution: Enable "Optimize for Ad Hoc Workloads" and parameterize queries.

---

Access Methods timeseries

Counters showing how SQL Server accesses data: scans, seeks, lookups.

Key Access Methods metrics:

Metric	Meaning	Target
Index Searches/sec	Index search operations (seeks)	High = good
Full Scans/sec	Full table/index scans	Low = good
Range Scans/sec	Range scans on indexes	Depends
Page Splits/sec	Page splits from insertions	Low = good

Scans vs Seeks Ratio

A high ratio of Full Scans/sec relative to Index Searches/sec indicates possible missing indexes or poorly optimized queries. Investigate with the DMV sys.dm_db_missing_index_details.

---

Interpretation Guide

Critical Indicators

Metric	Critical Value	Impact	Recommended Action
Page Life Expectancy	< 300 sec	Degraded performance	Increase memory, optimize queries
Buffer Cache Hit Ratio	< 95%	Excessive disk reads	Increase RAM, review indexes
Memory Grants Pending	> 0	Queries in queue	Update statistics, review queries
Databases SUSPECT	>= 1	Possible data loss	DBCC CHECKDB, restore if necessary

Metric Correlation

To diagnose performance problems, correlate these metrics:

Symptom	Metrics to Review	Probable Cause
Slow queries	PLE, Buffer Cache, Memory Grants	Memory pressure
Timeouts	Processes Blocked, User Connections	Blocks or connection limit
High I/O	Checkpoint pages/sec, Full Scans/sec	Missing indexes or checkpoints
High CPU	Batch Requests, Compilations	High load or recompilations

Review Frequency

Task	Frequency	Tool
Review PLE and Buffer Cache	Daily	This dashboard
Verify database status	Daily	This dashboard
Analyze growth trends	Weekly	Database Growth dashboard
Review Memory Grants and blocks	Weekly	Process Status dashboard
Full performance audit	Monthly	All dashboards

---

Microsoft References

Topic	Link
Buffer Manager Object	learn.microsoft.com
Memory Manager Object	learn.microsoft.com
Server Memory Options	learn.microsoft.com
TempDB Best Practices	learn.microsoft.com
Database States	learn.microsoft.com
Database Checkpoints	learn.microsoft.com

---

Stored Procedures Used

This dashboard uses stored procedures from the [Counters] schema:

Procedure	Description
spUpTimeDatabase	Calculates uptime
spLastPerformanceCounters	Gets the last value of a counter
spAVGPerformanceCountersGroupByInterval	Counter average grouped by interval
spMaxPerformanceCountersGroupByInterval	Counter maximum grouped by interval

---

Views Used

View	Description
[Counters].[vwDatabaseStatus]	Database status by server