Monitoring SQL Server Query Performance in Grafana Using Windows Exporter, Prometheus

[infofcc3]

Introduction

Monitoring SQL Server query performance is a critical requirement for database administrators and operations teams. Identifying slow queries, high CPU usage, and excessive logical reads in real time can help optimize database performance, prevent bottlenecks, and ensure application reliability.

In this article, we will build a real-time SQL Server Query Performance Dashboard in Grafana using:

• SQL Server

• PowerShell

• Windows Exporter

• Prometheus

• Grafana

The final dashboard will display:

• Query Text

• Database Name

• Execution Count

• Elapsed Time (ms)

• CPU Time (ms)

• Logical Reads

Solution Architecture

Step 1: Verify Windows Exporter Installation

Verify that Windows Exporter is running:

cmd

sc query windows_exporter

Expected:

STATE : 4 RUNNING

Step 2: Verify Textfile Collector Configuration

Run:

cmd

sc qc windows_exporter

Example Output:

BINARY_PATH_NAME : “C:\Program Files\windows_exporter\windows_exporter.exe” --config.file=“C:\Program Files\windows_exporter\config.yaml”

Verify that config.yaml contains the textfile collector configuration:

yaml

collectors:
  enabled: cpu,memory,logical_disk,physical_disk,net,os,service,system,textfile

---

Step 3: Verify SQL Server is Running

Verify SQL Server is running on port 1433:

cmd

docker ps

Expected:

CONTAINER ID PORTS

xxxxxxxxxxxx 0.0.0.0:1433->1433/tcp

---

Step 4: Create Test Database and Sample Data

Connect to SQL Server and create a test database:

cmd

docker exec -it sqlserver2 /opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -P "YourPassword" -C -Q "CREATE DATABASE TestDB"

Create a sample table with data:

cmd

docker exec -it sqlserver2 /opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -P "YourPassword" -C -d TestDB -Q "CREATE TABLE Orders (OrderID INT PRIMARY KEY, CustomerName NVARCHAR(100), Amount DECIMAL(10,2), OrderDate DATETIME DEFAULT GETDATE()); INSERT INTO Orders VALUES (1,'Alice',500.00,GETDATE()),(2,'Bob',1200.00,GETDATE()),(3,'Charlie',300.00,GETDATE()),(4,'Diana',850.00,GETDATE()),(5,'Eve',2000.00,GETDATE())"

---

Step 5: Create Textfile Collector Directory

C:\Program Files\windows_exporter\textfile_inputs

This directory will contain Prometheus metrics.

---

Step 6: Create PowerShell Script

Create the file:

C:\Program Files\windows_exporter\textfile_inputs\sql_queries.ps1

Paste the following script:

powershell

$server = "localhost,1433"
$user = "sa"
$password = "YourPassword"
$query = @"
SELECT TOP 10
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(st.text)
            ELSE qs.statement_end_offset
        END - qs.statement_start_offset)/2)+1) AS query_text,
    qs.execution_count,
    qs.total_elapsed_time / 1000 AS total_elapsed_ms,
    qs.total_worker_time / 1000 AS total_cpu_ms,
    qs.total_logical_reads,
    ISNULL(DB_NAME(st.dbid), 'internal') AS database_name

FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY qs.total_elapsed_time DESC
"@
try {
    $connection = New-Object System.Data.SqlClient.SqlConnection
    $connection.ConnectionString = "Server=$server;Database=master;User Id=$user;Password=$password;TrustServerCertificate=True;"
    $connection.Open()
    $cmd = $connection.CreateCommand()
    $cmd.CommandText = $query
    $reader = $cmd.ExecuteReader()
    $metrics = @()
    $metrics += "# HELP sqlserver_query_info SQL Server query performance metrics"
    $metrics += "# TYPE sqlserver_query_info gauge"
    while ($reader.Read()) {
        $queryText = $reader\["query_text"\].ToString().Trim()
        $queryText = $queryText -replace '"', '\\"'
        $queryText = $queryText -replace '\\n', ' '
        $queryText = $queryText -replace '\\r', ' '
        $queryText = ($queryText -replace '\\s+', ' ').Substring(0, \[Math\]::Min(100, $queryText.Length))
        $execCount    = $reader\["execution_count"\]
        $elapsedMs    = $reader\["total_elapsed_ms"\]
        $cpuMs        = $reader\["total_cpu_ms"\]
        $logicalReads = $reader\["total_logical_reads"\]
        $dbName       = $reader\["database_name"\].ToString()
        $label = "query=""$queryText"",database=""$dbName"",execution_count=""$execCount"",elapsed_ms=""$elapsedMs"",cpu_ms=""$cpuMs"",logical_reads=""$logicalReads"""
        $metrics += "sqlserver_query_info{$label} 1"
    }
    $reader.Close()
    $connection.Close()
    $metrics | Out-File "C:\\Program Files\\windows_exporter\\textfile_inputs\\sql_queries.prom" -Encoding ascii
    Write-Host "Metrics written successfully"
}
catch {
    Write-Host "Error: $\_"
}

---

Step 7: Execute the Script

Run:

powershell

powershell.exe -ExecutionPolicy Bypass -File "C:\\Program Files\\windows_exporter\\textfile_inputs\\sql_queries.ps1"

Expected output:

Metrics written successfully

---

Step 8: Verify Generated Metric File

Open:

C:\Program Files\windows_exporter\textfile_inputs\sql_queries.prom

Example:

sqlserver_query_info{query=“SELECT * FROM Orders”,database=“TestDB”,execution_count=“1”,elapsed_ms=“301”,cpu_ms=“301”,logical_reads=“0”} 1

---

Step 9: Verify Windows Exporter Metrics

Open:

localhost:9182/metrics

Search for sqlserver_query_info

Expected:

sqlserver_query_info{cpu_ms=“301”,database=“internal”,elapsed_ms=“301”,…}

---

Step 10: Configure Task Scheduler

To update the metric automatically every minute:

General Tab

• Name: sqlserver_metrics

• Run only when user is logged on

• Run with highest privileges

Trigger Tab

• Daily

• Repeat task every: 1 minute

• Duration: Indefinitely

Action Tab

• Program: powershell.exe

• Arguments: -ExecutionPolicy Bypass -File “C:\Program Files\windows_exporter\textfile_inputs\sql_queries.ps1”

Conditions Tab

• Uncheck all power conditions

Settings Tab

• Enable: Allow task to be run on demand

• Enable: Run task as soon as possible after a scheduled start is missed

Save the task.

---

Step 11: Verify Prometheus

Open Prometheus:

localhost:9091

Execute:

sqlserver_query_info

Expected result:

sqlserver_query_info{cpu_ms=“301”,database=“internal”,…}

---

Step 12: Create Grafana Dashboard

Create a new panel in Grafana with these settings:

• Visualization: Table

• Data source: Prometheus

• Query: sqlserver_query_info

• Format: Table

Add Organize Fields transformation and configure:
Field → Action
database Rename → Database
query Rename → Query
execution_count Rename → Execution Count
elapsed_ms Rename → Elapsed Time (ms)
cpu_ms Rename → CPU Time (ms)
logical_reads Rename → Logical Reads
Time, _name_, instance, job, Value → Hide

---

Dashboard Output

The dashboard successfully displays:
Database
Query
Execution Count
Elapsed Time (ms)
CPU Time (ms)
Logical Reads

DBAs and operations teams can instantly identify the slowest queries, most executed queries, and highest resource consuming queries in real time.

---

Benefits

• Real-time SQL Server query performance monitoring

• No additional agents required

• Uses existing Prometheus and Windows Exporter infrastructure

• Identifies slow queries, high CPU queries, and excessive logical reads

• Easily scalable across multiple SQL Server instances

• Customizable Grafana dashboards

• Real-time auto-updating metrics via Task Scheduler → refreshes every minute automatically

---

Conclusion

This solution reads from sys.dm_exec_query_stats which reflects the current SQL Server plan cache. Metrics will reset if SQL Server restarts or the plan cache is cleared. For historical query performance tracking, consider enabling SQL Server Query Store as a complementary solution

By combining PowerShell, Windows Exporter Textfile Collector, Prometheus, and Grafana, we created a lightweight and efficient solution for monitoring SQL Server query performance. This approach provides real-time visibility into query execution and can be extended to include additional metrics such as wait statistics, index usage, and connection counts.