All projects

SQL Server

SQL Performance Analyzer

Design-complete project for a read-only SQL Server diagnostics tool covering active sessions, blocking, waits and expensive queries. No runnable demo is published yet.

Status
Design complete
Level
Intermediate

Design complete: this page documents the architecture, specification and implementation plan. The reusable implementation has not been published yet; code, templates and demos will be linked here as each milestone ships.

Project overview

Who this is for

  • Engineers who want to see how SQL Server exposes what it is doing, through DMVs and Query Store
  • DBAs and engineers who need a repeatable first response to "the database is slow"
  • Teams that want consistent incident evidence instead of ad-hoc screenshots

What this project demonstrates

  • Which dynamic management views answer which performance question
  • How to read active requests, waits, blocking chains and resource usage
  • How to find the queries with the most total impact, not just the slowest one
  • How to group workload by database, login, host and application
  • How to collect evidence safely, with read-only permissions and low overhead
  • How to connect Python to SQL Server and turn result sets into a report

Tech stack

  • SQL Server
  • T-SQL
  • Dynamic Management Views
  • Query Store
  • Python
  • GitHub

SQL Performance Analyzer: status and purpose

Status: design complete. This page describes a proposed read-only SQL Server diagnostics tool; no runnable code, downloadable package, or hosted demo is published yet. Today, you can use the existing guides and run their diagnostic queries manually.

It is designed for DBAs and engineers who need a repeatable first response to a slow database. The planned snapshot groups active requests, blocking, waits, expensive queries and Query Store evidence into a console summary and HTML report.

For a working workflow now, start with SQL Server Performance Tuning, then inspect running queries, blocking, historical Query Store regressions or real-time monitoring. The SQL Server hub links to the wider production guide collection.

The problem

When someone reports that SQL Server is slow, the first minutes decide whether the investigation is evidence-based or guesswork. The information is all there, in dynamic management views (DMVs) and Query Store. But collecting it by hand is slow, inconsistent between engineers, and easy to get wrong under pressure. Evidence disappears too: a restart or a killed session removes the state that explained the problem.

The SQL Performance Analyzer is designed to make that first response repeatable. It takes a consistent, read-only snapshot of what the server is doing, and presents it in the order an experienced engineer would look at it. It follows the workflow in SQL Server Performance Tuning: A Practical Checklist.

Architecture

  1. Target

    • SQL Server instanceRead-only access to DMVs and Query Store
  2. Collectors

    • Active requests
    • Blocking
    • Waits
    • Query stats
    • Query Store
  3. Snapshot

    • Timestamped snapshot filesRaw results kept as evidence
  4. Analysis

    • Python analysisGroup, rank and compare snapshots
  5. Output

    • Console summary
    • HTML report
Intended architecture. The tool only reads from SQL Server; analysis and reporting happen on the machine running it.

Collectors are plain T-SQL files, so they can be reviewed, run by hand, and reused without the tool. The Python layer handles connection, scheduling, grouping and reporting. Snapshots are written to disk before analysis, so the evidence survives even if the analysis step fails.

How it works

  1. 1Connect read-onlyVIEW SERVER STATE, or VIEW SERVER PERFORMANCE STATE on SQL Server 2022 and later
  2. 2Capture snapshotRequests, sessions, waits, blocking, query stats
  3. 3Blocking firstHead blockers and the full chain
  4. 4Active workWhat is running, for how long, waiting on what
  5. 5Expensive queriesBy total CPU, reads, writes, duration and executions
  6. 6Group the workloadBy database, login, host and application
  7. 7ReportSummary first, raw evidence attached
Investigation order. The order the report follows, from scope to specific queries, before anything is changed.

Inputs and sources

Every collector is a read-only query against documented system views:

  • Requests and sessions: sys.dm_exec_requests and sys.dm_exec_sessions.
  • Blocking: blocking_session_id in sys.dm_exec_requests, with sys.dm_os_waiting_tasks for the wait each blocked task is in.
  • Waits: sys.dm_os_wait_stats, sampled at the start and end of the capture so the difference describes the interval.
  • Query statistics: sys.dm_exec_query_stats with sys.dm_exec_sql_text, and sys.dm_exec_query_plan when plan capture is enabled.
  • Query Store: sys.query_store_query, sys.query_store_plan and sys.query_store_runtime_stats, on databases where Query Store is enabled.

Analysis workflow

  • Active sessions and requests: status, command, elapsed time, wait type and wait time, CPU, reads and writes.
  • Blocking: head blockers, chain depth and the statements involved.
  • Waits: the top wait types for the capture interval, rather than since startup, so they describe the incident.
  • Expensive queries: ranked by total impact, with query text and plan handle, using sys.dm_exec_query_stats and Query Store where it is enabled.
  • Workload grouping: totals by login, database and host, which often shows that one application or job dominates.

Output

  • Snapshot files: the raw result of every collector, timestamped and written before analysis, kept as evidence.
  • Console summary: blocking, the top waits for the interval and the top queries by total impact.
  • HTML report: a self-contained file with the summary first and the raw evidence attached, with an option to omit query text.

Implementation milestones

Nothing is published yet. The tool will be built in these milestones:

  1. Collector scripts: the T-SQL queries, each documented with what it answers and its overhead.
  2. Python runner: connection handling, running collectors, writing snapshot files.
  3. Analysis: ranking, grouping and comparing two snapshots taken minutes apart.
  4. Report: a console summary and a self-contained HTML report.
  5. Plan information: capturing plan XML for the top queries and flagging common warnings.

Project deliverables

  • Documented T-SQL collector scripts that are useful on their own, without the tool
  • A Python runner that writes timestamped snapshots
  • Ranking, workload grouping and a two-snapshot comparison
  • A console summary and a self-contained HTML report
  • Opt-in plan capture with common plan warnings flagged

Intended usage

The workflow the tool is designed for: grant the account read access to server state, take a snapshot as soon as an incident is reported, take a second one a few minutes later, read the summary, and attach the report to the incident record. Each collector can also be run by hand in a query window, which is how the scripts should be reviewed before the tool is used against a production server.

Limitations

  • Snapshots describe a point in time. They are not a continuous history; trend analysis needs a monitoring tool or a table that stores snapshots over time.
  • sys.dm_exec_query_stats only covers plans still in cache and resets on restart. Query Store sections appear only where Query Store is enabled.
  • Deadlocks and long-term tracing need Extended Events, which are out of scope for the first version.
  • The first version targets SQL Server instances. Differences in Azure SQL Database and Azure SQL Managed Instance are not covered yet.

How to explore this project

Getting started

Prerequisites

  • A local SQL Server (Developer Edition) or a test instance you are allowed to query
  • Basic T-SQL; basic Python

Concepts to understand first

What a session, a request and a wait are. How to Find Running Queries in SQL Server shows sessions and requests in practice.

Working through the milestones

Start with the collector scripts: each one can be run by hand, and its output read, before any Python exists. Reproducing a blocking scenario with two query windows is the quickest way to confirm that the blocking collector finds the head blocker.

Acceptance checks

  • The top waits are reported for the capture interval, not since startup.
  • A blocking chain three sessions deep is resolved to its head blocker from the DMVs alone.
  • Top queries are ranked by both average duration and total CPU, and the report makes the difference visible.

Extending the design

Enable Query Store on a test database, run a workload, and compare what Query Store shows with the DMV snapshot.

Professional considerations

For professionals

Architecture decisions

T-SQL collectors keep the logic reviewable by DBAs and usable without Python. Snapshots to disk make the tool safe to use during incidents and provide evidence for later review.

Security

The tool needs only read permissions on server state, never sysadmin. Snapshots can contain query text and parameters, so they are treated as sensitive. The report has an option to omit query text.

Overhead and failure modes

Collectors are designed to be lightweight. Plan capture is opt-in because reading plan XML for many queries is not free. Every collector has a timeout, so the tool cannot become part of the problem it is investigating.

Repeatable incident workflow

The same snapshot format on every incident makes before-and-after comparisons and post-incident reviews straightforward. Two snapshots a few minutes apart are more useful than one.

Alternatives considered

  • Commercial monitoring tools: better for continuous history; this tool targets the first response and learning.
  • Community diagnostic scripts: excellent and widely used; the analyzer focuses on a smaller, guided workflow and a shareable report.
  • Extended Events sessions: the right tool for deadlocks and long-term tracing; out of scope for the first version.

Common mistakes

  • Reading cumulative waits since startup as if they described the current incident.
  • Ranking queries only by average duration and missing frequent, cheap-looking queries.
  • Killing the blocking session before capturing what it was doing.
  • Running diagnostic queries with more permissions than they need.

Next improvements

  • Publish the collector scripts with documentation.
  • Add a comparison mode for “before and after a change”.
  • Explore exporting snapshots to a table for trend analysis.

Tags

  • SQL Server
  • Performance
  • Query Store
  • Execution Plans
  • Concurrency
  • Debugging
  • Observability
  • Python