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
On this page
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
Target
- SQL Server instanceRead-only access to DMVs and Query Store
Collectors
- Active requests
- Blocking
- Waits
- Query stats
- Query Store
Snapshot
- Timestamped snapshot filesRaw results kept as evidence
Analysis
- Python analysisGroup, rank and compare snapshots
Output
- Console summary
- HTML report
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
- 1Connect read-onlyVIEW SERVER STATE, or VIEW SERVER PERFORMANCE STATE on SQL Server 2022 and later
- 2Capture snapshotRequests, sessions, waits, blocking, query stats
- 3Blocking firstHead blockers and the full chain
- 4Active workWhat is running, for how long, waiting on what
- 5Expensive queriesBy total CPU, reads, writes, duration and executions
- 6Group the workloadBy database, login, host and application
- 7ReportSummary first, raw evidence attached
Inputs and sources
Every collector is a read-only query against documented system views:
- Requests and sessions:
sys.dm_exec_requestsandsys.dm_exec_sessions. - Blocking:
blocking_session_idinsys.dm_exec_requests, withsys.dm_os_waiting_tasksfor 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_statswithsys.dm_exec_sql_text, andsys.dm_exec_query_planwhen plan capture is enabled. - Query Store:
sys.query_store_query,sys.query_store_planandsys.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_statsand 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:
- Collector scripts: the T-SQL queries, each documented with what it answers and its overhead.
- Python runner: connection handling, running collectors, writing snapshot files.
- Analysis: ranking, grouping and comparing two snapshots taken minutes apart.
- Report: a console summary and a self-contained HTML report.
- 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_statsonly 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.
Repository, demo and downloads
Nothing has been published yet. The repository, demo and downloads will be linked here when they exist.
Have feedback or want to collaborate? Contact me
