DBLense Query Analyzer — Complete User Guide

Your lightweight, zero-install SQL Server performance companion. Instantly analyze query execution patterns, detect anomalies, uncover missing indexes, and get AI-powered tuning recommendations — all from a single .exe.

Getting Started

Launch & Connect

Double-click DBLenseQueryAnalyzer.exe — no installation required. A connection dialog appears with four authentication options:

Enter your server name (e.g., localhost, myserver.database.windows.net), choose your auth method, and click Connect. DBLense automatically discovers all databases that have Query Store enabled — no manual configuration needed.

License & Trial

On first launch, you can start a 14-day free trial with full access. After the trial, click the license badge in the header to enter your license key. The badge shows your current status at a glance:

• Amber badge = Trial mode (with days remaining)
• Green badge = Licensed (Standard or Enterprise)

The Three Main Tabs

DBLense is organized into three tabs: Summary → Database → Query

Think of it as: "Which databases are busiest?" → "Which queries need attention?" → "What's wrong with this query and how do I fix it?"

Every tab has a time range selector — choose a quick preset (1 Hour to 1 Week) or pick custom dates. Data loads on-demand when you click the Load button, so it never runs background queries against your server.

Tab 1: Summary — Server-Wide Overview

The Summary tab gives you a bird's-eye view of all databases on the server. This is where you start every investigation.

Database Execution Summary

A table showing every Query Store-enabled database with:

• Total Executions — How many queries ran in the selected time range
• Successful vs. Aborted/Timed Out — Quickly spot databases with failures
• Success Rate % — Databases below 95% need immediate attention

Tip: Click any database name to jump directly to the Database tab with that database pre-selected.

Database Execution Compare

Compares execution counts across three days: Today vs. Yesterday vs. Day Before.

• Today vs Yesterday % — Positive = more load, negative = lighter load
• Yesterday vs Day Before % — Establishes the trend

Invaluable for Monday morning checks, post-deployment monitoring, or investigating slowness complaints.

Anomaly / Root Cause Detector Enterprise

DBLense's most powerful feature. Select a symptom and let DBLense trace it back to the exact queries causing the problem.

Nine Built-in Observations

Observation	What It Detects
High CPU Usage	Queries consuming the most CPU cycles
Low Page Life Expectancy	Memory pressure — which queries read the most data
Blocking & Lock Contention	Head blockers, blocked sessions, and lock-holding queries
High Session Count	Sessions piling up — identifies what they’re running
High Disk I/O	Queries generating the heaviest read/write workloads
Memory Grant Pressure	Queries requesting excessive memory grants
TempDB Contention	Queries spilling to TempDB or creating excessive temp objects
Long Running Queries	Active queries running beyond normal thresholds
General Health Check	Broad scan across all categories

Two Detection Modes

• Current Issue (Live DMVs) — Analyzes what's happening right now
• Past / Historical Issue (Query Store) — Investigate a past incident by selecting a date range

For each finding, you get: severity (Critical / Warning / Info) with impact score, the exact root cause SQL (with Copy button), session details, and a specific recommendation.

Tab 2: Database — Query-Level Performance

Query Execution Summary

Every query in the selected database, ranked by activity:

• Total Executions, Avg Duration (ms), Avg CPU (ms), Avg Reads
• Successful vs. Aborted — Spot queries that frequently fail

Click any query hash to jump to the Query tab for deep analysis.

Query Execution Compare

Day-over-day comparison for each query. Use this to find regressions after deployments — sort by "Δ Today" to find queries whose duration suddenly changed.

Missing Indexes

SQL Server tracks when a query would have benefited from a missing index. This tab surfaces those recommendations:

• Equality / Inequality / Included columns — Exactly what the index should contain
• User Seeks — How many times this index would have been used
• Avg Impact % — Estimated performance improvement
• Ready-to-run CREATE INDEX statement — Copy and execute in SSMS

Focus on indexes with high impact % AND high user seeks. An index with 95% impact but only 2 seeks isn't worth it.

Unused Indexes

Indexes that exist but aren't being used:

• Seeks / Scans / Lookups — How often the index is actually used
• Updates — How much overhead it creates
• Status: Red = Unused, Orange = Rarely Used, Green = Active

Unused indexes slow down writes, waste disk space, and increase backup times. Dropping them is one of the easiest performance wins.

Statistics Status

Outdated statistics cause the query optimizer to choose bad execution plans:

• Last Updated — When statistics were last refreshed
• Modification % — High = statistics are stale
• Status: Red = Very Stale, Orange = Outdated, Green = Up to Date

Quick fix: Run UPDATE STATISTICS [TableName] on tables flagged as Very Stale.

Tab 3: Query — Deep Dive

This is where you understand why a query is slow and how to fix it. Select a database and query hash, then click Load Data.

Execution Summary

Time-bucketed breakdown of the query's performance (e.g., hourly intervals):

• Executions per interval — When does this query run most?
• Avg vs. Max Duration — A big gap indicates intermittent slowdowns (parameter sniffing)
• CPU, Reads, Writes per interval — Track resource consumption over time

Execution Plan Enterprise

A graphical execution plan rendered directly in DBLense. Top panel: visual tree showing operators connected by flow lines. Bottom panel: tabular breakdown of every operator with estimated rows, CPU, I/O cost, and subtree cost.

What to Look For

• Table Scan instead of Index Seek → Missing index
• Key Lookup → Add included columns to the index
• Sort with high cost → Add an index on ORDER BY columns
• Hash Match → Large data sets; review join conditions

Performance Tuning Tips Enterprise

Click Analyze Query to run an automated code review. DBLense scans both the SQL text and execution plan XML to identify 25+ performance anti-patterns:

Common SQL Anti-Patterns Detected

• SELECT * — Fetches unnecessary columns
• Functions on columns in WHERE — Prevents index usage
• IN (SELECT ...) — Often slower than EXISTS
• NOT IN (SELECT ...) — Dangerous with NULLs
• Leading wildcard in LIKE — Forces full table scan
• Unnecessary DISTINCT — Usually hides a join problem
• UNION where UNION ALL suffices
• Scalar UDFs in SELECT — Row-by-row execution
• NOLOCK / READ UNCOMMITTED hints
• Cursors — Row-by-row processing
• Non-SARGable predicates

Execution Plan Warnings Detected

• Table Scans, Key Lookups, Expensive Sorts, Hash Match joins
• Parallelism issues, Spill warnings, Implicit conversions
• Row estimate mismatches (stale statistics)

For each issue: severity, problem description, recommendation, detected snippet, and a suggested fix. An Enhanced Query is generated with automatic fixes applied — click Copy Enhanced Query to use it.

Typical Workflows

Daily Health Check (2 minutes)

1. Open DBLense → Connect → Summary tab
2. Set duration to "1 Day" → Click Load Summary
3. Check Database Execution Compare — any unusual spikes?
4. Run General Health Check in Anomaly Detector

Investigating a Slow Application (5 minutes)

1. Summary → Database Execution Compare → find the database with the biggest increase
2. Click the database name → Database tab loads
3. Sort Query Execution Summary by Avg Duration → find the slowest queries
4. Click the worst query hash → Query tab loads
5. Check Execution Plan for table scans or key lookups
6. Run Performance Tuning Tips → get specific fix recommendations
7. Copy the Enhanced Query → test in SSMS

Post-Deployment Validation (3 minutes)

1. Database tab → Query Execution Compare
2. Sort by "Δ Today" descending → queries running significantly more
3. Sort by "Today Avg ms" descending → queries that got slower
4. Investigate any regression → Query tab → Tuning Tips

Index Optimization (5 minutes)

1. Database tab → Missing Indexes → sort by Impact %
2. Review top suggestions → copy CREATE INDEX statements
3. Switch to Unused Indexes → find indexes consuming write overhead
4. Cross-reference: don't create an index if a similar unused one exists

Incident Root Cause Analysis (3 minutes)

1. Summary → Anomaly / Root Cause Detector
2. Select symptom → "Past / Historical Issue" → set time window
3. Click Examine & Detect Root Cause
4. Review findings sorted by impact → share with team

System Requirements