dblens 0.0.0-alpha.0.3

db-lens

A read-only command-line analyzer for Microsoft SQL Server. It ranks queries by what they cost the server, shows the execution plan behind any of them, inventories indexes with their real usage, and reports unused, duplicate, redundant and missing indexes — as structured JSON or as a table. Beyond queries and indexes it reads the server's own diagnostics: wait statistics, live blocking chains, statistics freshness, and the deadlock history SQL Server keeps on its own.

db-lens never writes to your database. Every statement it runs is a hard-coded SELECT over system views; you cannot pass it SQL to execute. Index changes it suggests are printed as T-SQL for you to review and run yourself.

There is no LLM inside db-lens. It collects and reasons deterministically, and emits a stable JSON contract — the analysis on top of that is yours to do, by hand or by pointing an agent at the output.

Install

dotnet tool install --global dblens
dblens --help

That needs the .NET 10 SDK; one package covers macOS, Linux and Windows. Later: dotnet tool update --global dblens.

On a machine with no .NET, take the self-contained binary for your platform from the latest release — one file, nothing to install:

curl -sSL https://github.com/devsmart-pl/db-lens/releases/latest/download/dblens-osx-arm64.tar.gz | tar -xz
./dblens --help

Builds are published for linux-x64, linux-arm64, osx-x64, osx-arm64, win-x64 and win-arm64.

The binaries are unsigned, so macOS quarantines a downloaded one until you clear the flag: xattr -d com.apple.quarantine ./dblens.

Quick start

export DBLENS_CONNECTION_STRING='Server=localhost,1433;Database=AppDb;User Id=reader;Password=...;TrustServerCertificate=true'

dblens config test                       # what can this instance actually answer?
dblens queries --top 20 --order-by cpu   # the costliest queries
dblens plan 4711                         # the plan behind one of them
dblens indexes suggest                   # every index finding at once
dblens waits                             # what the server has been waiting on
dblens blocking                          # who is blocking whom, right now
dblens deadlocks                         # recent deadlocks, parsed from system_health
dblens report --format markdown -o report.md

Or save a profile instead of exporting a variable each time:

dblens config set prod 'Server=sql-01;Database=AppDb;Integrated Security=true;TrustServerCertificate=true' --default
dblens queries --profile prod

Requirements

• .NET 10 SDK to install as a global tool — or none at all with a self-contained build.

• SQL Server 2016 or later, Azure SQL Database, or Azure SQL Managed Instance.

• A login with three read permissions:

  GRANT VIEW SERVER STATE TO [db-lens-reader];   -- plan cache, index usage, missing-index DMVs, uptime
  USE [AppDb];
  GRANT VIEW DATABASE STATE TO [db-lens-reader]; -- Query Store and database-scoped DMVs
  GRANT VIEW DEFINITION TO [db-lens-reader];     -- index, column and foreign key definitions
  

  VIEW DEFINITION is easy to overlook and matters more than it looks: the catalog views only expose objects the login holds some permission on, so without it dblens indexes returns an empty list rather than an error. db-lens detects that case and says so instead of letting an empty result read as "this database has no indexes".

  No db_owner, no db_ddladmin, no write permission of any kind is needed. Each command degrades to what it can still answer — without VIEW SERVER STATE you keep index definitions and duplicate detection, losing only the usage-dependent findings — and names the missing permission and its GRANT rather than surfacing a raw SQL error.

Commands

Run dblens <command> --help for the full filter list — it is the reference, and it is generated from the same definitions the commands use.

Where the numbers come from

Query statistics have two possible sources, and which one answered changes what the numbers mean:

• Query Store (sys.query_store_*) — survives restarts and recompiles, keeps history, and gives stable query_id and plan_id values that stay valid between runs. Requires Query Store to be enabled on the database.
• Plan cache (sys.dm_exec_query_stats) — available on every instance with VIEW SERVER STATE and needs no per-database setup, but covers only what is still cached: it resets on restart, on recompile, and under memory pressure.

db-lens picks Query Store when every target database has it enabled, and the plan cache otherwise — never a mix, because the two are not comparable in one ranking. The source field of every result says which was used, and --source query-store|plan-cache overrides the choice.

The diagnostic commands have their own sources and scopes:

• waits reads sys.dm_os_wait_stats (instance-wide, since startup). On Azure SQL Database it reads sys.dm_db_wait_stats instead, which sees only that database's waits — the result says so.
• blocking joins sys.dm_exec_sessions, sys.dm_exec_requests and the SQL text DMVs into one snapshot. It starts from sessions rather than requests, so an idle session holding an open transaction — a head blocker with no running request — stays visible. Works everywhere.
• indexes stats reads sys.stats through sys.dm_db_stats_properties per database, 2016+ everywhere including Azure SQL Database.
• deadlocks reads the system_health Extended Events file target, which records every deadlock since 2012 with no setup. Box installs and Managed Instance only; Azure SQL Database exposes no file target and gets an explicit warning instead of an empty-looking result.

Reading the output

Every command returns the same envelope:

{
  "tool": "db-lens",
  "version": "0.1.0",
  "command": "indexes unused",
  "collectedAt": "2026-03-14T09:12:00Z",
  "server": { "name": "sql-01", "edition": "...", "uptimeDays": 22.4 },
  "source": "QueryStore",
  "warnings": ["..."],
  "data": { }
}

warnings is the field to read first. It carries the things that limit how far the data can be trusted — and acting on index findings without reading it is how a working index gets dropped:

• Usage counters reset when the instance restarts. Below --min-uptime-days (7 by default), "never read" only means "not read since Tuesday", and unused-index findings drop to Info.
• An index with no row in sys.dm_db_index_usage_stats has not been observed at all. That is absence of data, not a measurement of zero. db-lens reports it as no data, never as 0, and will not raise such a finding above Info.
• Missing-index recommendations ignore write cost. The optimizer's estimate says what a query would have saved, not what the index costs on every insert, update and delete.

Duplicate and redundant findings do not depend on usage counters at all — an index that duplicates another is redundant by its definition — so those stand regardless of uptime.

Using db-lens from a script or an agent

Output defaults to a table on a terminal and JSON when stdout is redirected, so a piped call needs no flags:

dblens indexes suggest | jq '.data.findings[] | select(.severity == "High")'

Points worth knowing when something else consumes the output:

• Field names are stable camelCase, with units in the name (totalCpuMs, sizeMb, totalLogicalReads).
• ruleId values are stable across versions, so findings can be referenced, suppressed, or diffed between runs. The families: IDX001–IDX007 (indexes), QRY001–QRY013 (queries and plans), WAIT001–WAIT006 (wait profile), BLK001–BLK003 (blocking), STAT001–STAT004 (statistics freshness), DLK001–DLK002 (deadlocks).
• Errors come back in the same format as results, with a stable error.code and a non-zero exit code — never a raw SQL exception or a stack trace.
• Volume is bounded: every command takes --top (-t/-n), with a default suited to the data — 25 for queries and waits, 100 for the index inventory, 50 for statistics findings, 20 for deadlocks; the per-command --help states it. Query text is truncated at --max-text-length (4000) unless you pass --full-text, and plan XML is not included unless you ask for it. One call will not flood a caller's context.
• dblens schema prints the JSON Schema for every shape, generated from the serializing types.

A reasonable path through a problem: report --format json to see everything, then queries --analyze to rank and diagnose, then plan <id> on whatever stands out. When the complaint is "the server is slow" rather than "this query is slow", start from waits instead — it says which resource to suspect — and blocking when things are stuck right now. Findings cross-reference each other: heavy lock waits point at blocking, estimate skew (QRY006) points at indexes stats.

Building

dotnet build
dotnet test
dotnet run --project src/DbLens.Cli -- --help

A single self-contained binary, no runtime needed on the target machine:

dotnet publish src/DbLens.Cli -c Release -r linux-x64 --self-contained -p:PublishSingleFile=true
dotnet publish src/DbLens.Cli -c Release -r win-x64   --self-contained -p:PublishSingleFile=true

Developing against a real server

docker-compose.yml brings up SQL Server 2022, and the seed script creates a database whose problems are deliberate — duplicate indexes, a redundant one, an index nothing reads, a foreign key with no index, a heap that needs one, and a varchar column compared against nvarchar parameters:

docker compose up -d
docker exec -i db-lens-mssql /opt/mssql-tools18/bin/sqlcmd \
  -S localhost -U sa -P 'DbLens!Local1' -C -b -i /dev/stdin < tests/sql/seed-testdb.sql
docker exec -i db-lens-mssql /opt/mssql-tools18/bin/sqlcmd \
  -S localhost -U sa -P 'DbLens!Local1' -C -b -i /dev/stdin < tests/sql/workload.sql

export DBLENS_CONNECTION_STRING='Server=localhost,14330;Database=DbLensDemo;User Id=sa;Password=DbLens!Local1;TrustServerCertificate=true'
dotnet run --project src/DbLens.Cli -- indexes suggest

Each seeded problem should produce exactly one finding, which is what the analyzer tests assert against — those run on plans captured from this database and need no server.

How it is put together

src/DbLens.Core/
  Connection/   opening connections, resolving profiles, probing what the instance supports
  Sql/          every statement db-lens runs, as embedded .sql files
  Sources/      reading the DMVs into models
  Showplan/     parsing ShowplanXML into an operator tree
  Analysis/     the rules, as pure functions over collected models
  Output/       the JSON envelope and the table, CSV and Markdown renderers
src/DbLens.Cli/ commands, options, and the Spectre.Console table renderer

Two properties are worth preserving when changing it. Every statement lives in Sql/ as a plain file, so the full set of what db-lens touches can be reviewed by reading one directory. And the analyzers take collected models and return findings with no I/O in between, so every rule is testable without a server.