Interface & Features — Activity Monitor

Walk through the main screens, tabs, and controls you will use day to day.

UI Tour

The app has three primary navigation areas: the left sidebar, the Servers home page, and the per-instance dashboard tabs.

Sidebar

Title bar controls

Dashboard tabs (when an instance is open)

Dashboard & KPI

When an instance is open, the top of the main view summarizes key counters so you can spot pressure before drilling into tabs:

• CPU % and related process utilization
• Parallelism context — CPU count, MAXDOP, cost threshold for parallelism
• Memory — target / total / clerk pressure signals at a glance
• Batch requests / sec, connections, and related workload counters
• Open alert count badge when history/alerting is deployed

KPI values refresh with Auto-Refresh / Refresh Now. They are live DMV samples unless a card explicitly shows a historic sparkline backed by collectors.

Performance Tab

The Performance tab is the main place to investigate “what ran during that spike?” It combines historic trend charts with a Top Queries pane correlated to the selected time range.

Source selector

When history collection is deployed, use Source to switch backends:

How to use charts and Top Queries

1. Pick a Source that has data (empty grid usually means that collector is off or has not run yet).
2. Brush or select a time range on the historic chart — Top Queries aggregates matching rows for that window.
3. Toggle Grid vs Chart for Top Queries. Grid shows full SQL text (one line), executions, duration, CPU, waits, DOP, IO, TempDB, database, and query hash. Chart ranks the same set visually.
4. Open Plan on a row for the graphical execution plan viewer.
5. Copy Query Hash and use the Query tab to dig into Query Store history for that hash / query id.
6. Right-click charts for Reset time selection or Jump to Session History when those actions apply.

Live CPU-by-query on other surfaces still reflects the current plan-cache snapshot; historic Top Queries always follow the selected Source + time brush.

Query Performance History

Historic query analysis uses Activity Monitor’s own collectors and tables in the dblense schema. This is separate from DBLense SQL Monitor enterprise repository and does not use the DBLenseMonitor XE session.

Phase 1 — Plan cache interval deltas

Instead of storing cumulative sys.dm_exec_query_stats values (which grow forever and misalign with chart intervals), collectors now:

1. Maintain a query_stats_snapshot table with the latest cumulative counters per plan.
2. On each Standard Collection run, compute the delta since the last snapshot and insert into query_stats_history.
3. The first run after deploy/upgrade only seeds the snapshot — expect meaningful Top Queries history after the second Standard Collection cycle.

Phase 2 — Individual query tracking (Extended Events)

Optional per-execution capture for statements that complete above a minimum duration (default 100 ms). Enable in the instance registration dialog:

• Detailed query tracking (XE) — creates Activity Monitor’s own XE session (DBLense_ActivityQueryMonitor), not SQL Monitor’s DBLenseMonitor.
• Min duration (ms) — filters short statements to reduce overhead.
• Quick Collection job runs usp_collect_xe_query_completed to land rows in query_completed_history.
• Select Individual (XE) on the Performance tab to chart and rank these executions.

Not available on Azure SQL Database (no custom XE on many SKUs). On-prem and Azure MI supported when XE can be created.

Phase 3 — Query Store intervals

For databases with Query Store enabled, Standard Collection runs usp_collect_query_store and stores interval stats in query_store_history. This source is often more trustworthy than plan cache when:

• Plans are evicted from cache frequently
• Ad-hoc workloads cause many plan handles
• You need consistent per-query-id tracking across recompiles

Collect Query Store is on by default when registering/editing an instance. Individual databases must still have ALTER DATABASE … SET QUERY_STORE = ON.

Key database objects (query performance)

Sessions & Blocking

The Active Requests tab is the primary triage surface during an incident. It shows who is connected, what they are running, wait types, DOP, memory grants, and who is blocked by whom.

Reading the grid

• SPID / Status / Database / Host / Login / Program — session identity.
• Command / SQL Text — current statement; open full text from the cell actions.
• Plan — graphical plan for the request when available.
• Waits — wait details for the session.
• Elapsed / CPU / Wait — time accounting; CPU/core normalizes parallel CPU.
• Blocked — blocking SPID (red highlight when blocked).
• Logical / Physical Reads, Writes, Granted / Used Memory — resource footprint.
• Kill — terminates the session when your login has sufficient rights. Confirm before using on production.

A companion connections summary can show connection counts by database / host / login / program.

Show blocking only

Use the Show blocking only checkbox above the Active Requests grid to filter down to sessions involved in active blocking chains:

• Blocked sessions — highlighted with a light red row background.
• Root blocker — the session at the head of the chain (not blocked itself, but blocking others) — highlighted in amber with semi-bold text.
• Intermediate blockers (blocked and blocking others) appear as victims in the chain walk.
• The session count in the header reflects the filtered count when the option is enabled.

The Blocked column shows the blocking SPID. Follow chains visually or enable the filter during incident triage. Data is live-only — for historic blocking after the fact, deploy collectors with Blocking enabled and use Metrics / Alerts history.

Waits & Metrics

Metrics tab

Time-series view of CPU, Page Life Expectancy, batch requests, user connections, and related counters. With history deployed, you can scroll back through Quick Collection samples; without it, you only see values sampled while the dashboard has been open this session.

Use Metrics when you need to correlate a user complaint (“it was slow at 10:15”) with resource pressure before opening Performance Top Queries for that same window.

Waits tab

Shows wait categories and types so you can separate IO waits (PAGEIOLATCH_*, WRITELOG), CPU (SOS_SCHEDULER_YIELD), locking (LCK_*), and network / parallelism waits. Prefer delta or interval views when available so cumulative waits since startup do not dominate the picture. Combined with Active Requests wait types, this answers whether the bottleneck is storage, CPU, or blocking.

Memory Tab

Inspect memory clerks, buffer pool usage, and grant-related pressure:

• Identify large consumers (e.g. CACHESTORE, USERSTORE, MEMORYCLERK_SQLQERESERVATIONS).
• Cross-check granted / used memory on Active Requests when queries spill or request large grants.
• Low PLE on Metrics plus growing buffer / stolen memory often points to memory pressure rather than pure CPU.

Memory views are live DMV samples refreshed with the dashboard timer.

Storage Tab

Volume and database size visibility for capacity planning:

• Volumes / drives — total, used, free, used % (on-prem / MI where available).
• Database sizes — data, log, internal free space, file/table/index counts.
• Context menu actions such as Show Table Sizes and Show File Sizes for deeper drill-down.
• Growth charts — when Storage Sizes collection is enabled, history retention (default ~3 years) supports trend and forecast views.

Tune Storage Snapshot (Daily vs Weekly) and Storage Retention in instance Edit. Weekly includes top table sizes and is heavier.

Databases Tab

Inventory of databases on the instance: state, recovery model, compatibility level, collation, owner, access mode, auto-close/shrink, page verify, log reuse wait, encryption, RCSI / snapshot isolation, and size columns. Use it as a quick configuration audit without SSMS Object Explorer.

Indexes Tab

Heavier live queries — open this tab when you are actively tuning, not necessarily on every 5-second refresh if the instance is under extreme load. Sub-tabs:

• Summary — high-level database index footprint when present.
• Index Fragmentation — frag %, pages, size; prioritize large, highly fragmented indexes for rebuild/reorganize.
• Index Usage Stats — seeks, scans, lookups, updates; find unused or write-heavy indexes.
• Missing Indexes — engine recommendations with impact; generate create scripts from the UI where offered.
• Statistics Updates — stale stats that may hurt plans.
• Database File IO Stats — latency and stall time by file to spot hot data/log files.

Some Azure SQL / contained environments restrict index DMVs; results may be partial.

Query Tab

Deep dive into a single query identity using Query Store (and related plan summary):

1. Choose a Database (Query Store must be ON for that database).
2. Pick a Query Hash from the list, or paste a hash / Query ID and click Go.
3. Review Query Text (Copy available) and the Plan Summary chart of plan points over time.
4. Use sub-tabs such as Execution Summary for interval metrics, then open plans for regressions.

Pair with Performance Top Queries: copy a hash from a costly row, then analyze it here across Query Store intervals.

Errors Tab

Reads SQL Server ERRORLOG files for the connected instance — a searchable feed of errors, warnings, and informational events.

• Filter by Duration (lookback window) and Severity.
• Use the Error Log chart to spot bursts, then select rows in the grid for message text.
• Refresh / Cancel control long reads on large logs.

Does not require history collectors. Permissions must allow reading the error log (typically VIEW SERVER STATE / security admin equivalents depending on edition).

Connection Flow

From the dashboard (link near connection/session areas), open Show Connection Flow to visualize how sessions relate — hosts, logins, databases, and blocking relationships — in a slide-out panel. Use it during blocking storms when a grid alone is hard to parse. Close with the panel’s window close control.

UI Health

The title-bar UI Health icon probes registered instances while the app is open:

• Instance reachability / login success
• SQL Agent running (where applicable)
• Stale Quick / Standard collection (last run older than expected interval)

Open the panel for per-instance detail. UI Health is separate from the Agent alert inbox — it is a desktop-side watchdog for your monitoring estate, not a replacement for threshold alerts.

History Collection

Deploy from a registered instance context menu when you need continuous history, query-performance collectors, and Agent alerts:

1. Connect with a login that can create objects in the chosen history database (and create Agent jobs when Agent is available).
2. Right-click the instance and choose Deploy History Collection, or enable monitoring options when adding a new instance.
3. Configure collection options: intervals, retention, Query Store, Detailed query tracking (XE), blocking/deadlock/wait stats, storage sizes (see Controlling the Tool).
4. Confirm Quick and Standard collector jobs are enabled in SQL Agent (SSMS → SQL Server Agent → Jobs).
5. Wait for at least two Standard Collection cycles before expecting Plan cache Top Queries history.
6. Use Remove History Collection only when you intentionally want to drop dblense objects and related jobs.

Custom history database names are supported as long as the login can read dblense.monitoring_config so the app can auto-discover the store on connect.

Re-deploy is rarely needed — connecting to an instance after an app upgrade automatically adds missing tables and redeploys procedures. Use manual Deploy only for a fresh install or if auto-upgrade fails.

Recommended starting profiles

Alerting

Activity Monitor can raise threshold alerts from existing dblense history plus a few live checks (storage and SQL Agent job failures). Evaluation runs in SQL Server via an Agent job, so alerts continue even when the desktop app is closed. Email uses Database Mail when configured; if mail is not set up, alerts still appear in the UI.

Alert architecture

History collectors feed metrics into dblense tables. Every minute the DBLense - Alert Evaluation job runs usp_evaluate_alerts, which reads history and live DMVs, writes open alerts, and optionally sends Database Mail. The dashboard reads the same tables for the Alerts inbox, rules, and mail settings. Sidebar ALERTS is for rule/mail configuration; the instance Alerts tab is the inbox (with open-alert count).

Alert types

Default rules are seeded when history / alerting objects are deployed (existing databases pick up new types on reconnect / re-deploy via MERGE):

Database objects

Deployed into the dblense schema (with monitoring setup / upgrade on connect):

• alert_rules — one row per alert type (enabled, threshold, operator, window, severity, cooldown, email on/off)
• alerts — raised alert log with status (Open / Acknowledged / Cleared), metric values, and fingerprint for cooldown dedupe
• alert_settings — Database Mail profile, recipients, and global enable flag
• usp_evaluate_alerts — evaluates enabled rules, inserts alerts, optionally calls sp_send_dbmail
• Agent job DBLense - Alert Evaluation — runs every 1 minute
• Agent job DBLense - Purge History — daily retention purge

Dashboard experience

• Sidebar ALERTS — per-instance rule and Database Mail configuration (see Alert Configuration).
• Instance Alerts tab — inbox of open / acknowledged alerts with Acknowledge and Acknowledge All; open count on the tab.
• Servers home — Latest Alerts rail — grouped summary across registered instances.
• Title-bar UI Health — instance reachability / Agent / collection issues (separate from Agent alert inbox).
• Evaluate Now — run usp_evaluate_alerts immediately from the Alerts UI.
• Email settings — Database Mail profile and recipients, plus a Test send.

Open-alert count refreshes with normal dashboard polling. The full inbox loads when the Alerts tab is active.

Alert Configuration (per instance)

Alert rules and notification settings are stored on each SQL Server in dblense.alert_rules and dblense.alert_settings. Each instance can have different thresholds, severities, cooldowns, and email recipients.

Configure rules for one instance

1. Open sidebar ALERTS (Alert Configuration page).
2. Select an instance from the Instance dropdown (shows display name and server name).
3. Edit the grid: On, operator, Threshold, Window, Cooldown, Severity, Email.
4. Select the rule row and click Save Selected Rule.
5. Configure Database Mail profile/recipients below and click Save or Test Email.
6. Switch the dropdown to another instance to configure it independently.

Apply same config to all instances

Apply All Rules To All Instances pushes the currently loaded rule set and notification settings to every configured instance. Use this when you want a standard baseline across the estate — otherwise configure each server separately.

Alert evaluation still runs via SQL Agent on each server (DBLense - Alert Evaluation), so alerts and email continue even when the desktop app is closed.

Platform notes

Auto-Refresh & Theme

Toggle auto-refresh in the title bar and choose 5, 10, or 30 second intervals. Disable auto-refresh when investigating a frozen moment, then use Refresh Now.

• 5 sec — incident response; higher load on the monitored instance.
• 10 sec — balanced default for interactive monitoring.
• 30 sec — lighter touch for shared / busy production boxes.

Theme toggle (sun/moon) switches light and dark UI. Preference is stored for the Windows user and does not affect collectors.

Registered Instances & Servers Home

The Servers home page lists all registered instances as health tiles with edition, platform, status, and open alert badges. A Latest Alerts rail on the right summarizes recent Agent alerts across the estate.

Select vs open

Tooltip on each tile: Click to select · Double-click to open dashboard.

Register frequently used servers for one-click access. Optional deploy/remove monitoring helpers are available from the instance context menu in the sidebar. Collapsed sidebar shows SERVERS / ALERTS / Connections icons only; expand to see individual connections with platform icons.

Instance registration options (collection)

Editing instance settings saves locally; server-side XE/proc updates apply on next connect (or Deploy).

Execution Plans

Open the graphical plan viewer from Active Requests, Performance Top Queries, Indexes/Missing Index script helpers, or the Query tab. Inspect operators, estimated costs, and warnings without switching to SSMS. Plans are fetched live from the plan cache / Query Store as applicable for that row.