Performance

Updated
  • Updated on Jan 14, 2026
  • Published on Jan 14, 2026
  • 14 minute(s) read
Prev Next

Performance Module Summary

The Performance module (formerly called Database in prior versions) provides comprehensive tools for analyzing SQL Server workload patterns, query performance, resource consumption, and troubleshooting performance issues. This module helps you identify bottlenecks, optimize queries, understand wait events, resolve blocking and deadlock issues, monitor SQL Agent jobs, track database connections, and manage Always On Availability Groups.

Available Performance Data and Sections

Workload Activity

The Workload Activity section provides real-time and historical analysis of SQL Server workload patterns, helping you understand what's happening on your instances and identify performance issues. Filter by instance, database, login, program, wait category, and tags to refine your analysis.

What Happened?

What it provides:

  • Focused troubleshooting tool showing minute-by-minute diagnostic information for a specific instance
  • sp_whoisactive data captured by WISdom in 1-minute collection intervals
  • Selectable time ranges: 15 minutes, 30 minutes, or 60 minutes
  • Same detailed data and flyouts as Activity Overview and Details

When to use it:

  • Troubleshoot specific performance incidents when you know the exact timeframe
  • Investigate user-reported slowness during specific windows
  • Analyze minute-by-minute patterns during short-term issues
  • Identify blocking chains, wait events, and resource contention at a precise point in time
  • Review query activity with granular 1-minute precision

Activity - Overview

What it provides:

  • Database Activity Histogram - Aggregated distribution of running processes and blocking events over time for up to three instances
  • Wait Activity Chart - Wait times by category (Backup, Blocking, CPU, Disk, Memory, Network, Parallelism, etc.) over time
  • Top Events Table - Grouped result sets showing Counts, Wait Time, CPU, and Logical Reads by Instance, Database, or SQL Statement

When to use it:

  • Identify performance trends and patterns across multiple instances
  • Spot blocking events and high wait times
  • Compare workload activity across instances
  • Find the most resource-intensive queries across your environment
  • Understand which wait categories are consuming the most time

Key capabilities:

  • Filter by date range, time interval, instance, database, login, program, wait category, and tags
  • Group Top Events by Instance, Database, or SQL Statement
  • Drill down from aggregated metrics to individual databases and queries
  • Toggle between Database Activity and Wait Activity views
  • Zoom into specific time blocks by clicking and dragging on the histogram

Activity - Details

What it provides:

  • Time-based Activity Data - Result counts and aggregate values at collection time for a single instance
  • Breakdown by Time Intervals - Data displayed in increments that are determined by the date and time range selected in the date picker; the larger the date range, the larger the interval
  • View Details Flyout - Three detailed views (Resources, Wait Statistics, Applications) for any time interval

When to use it:

  • Investigate performance issues on a specific instance
  • Analyze activity patterns at granular time intervals
  • Identify blocking chains, deadlocks, and high wait times during specific periods
  • Review batch requests, parallel threads, and session counts over time
  • Troubleshoot issues reported for specific timeframes

Key metrics displayed:

  • Batch Requests
  • Parallel Threads
  • Blocked Processes
  • Deadlocks
  • Wait Time
  • CPU
  • IO (Logical Reads)
  • Total Sessions
  • Observations (High CPU Usage, High IO Usage, High Session Count, High Blocking, High Wait Time)

View Details Flyout tabs:

Resources Tab:

  • Session IDs (SPIDs) and their status (Running, Runnable, Suspended, Rollback)
  • Runtime and CPU time per query
  • Databases, blocking relationships, parallel threads
  • SQL statements with copy/export capability
  • Group by Database, Status, or SQL Statement

Wait Statistics Tab:

  • Wait types and wait times per query
  • Runtime, database, and status context
  • The Wait Resources contested
  • SQL statements involved in waits
  • Group by Database, Status, Wait Type, Wait Resource, or SQL Statement

Applications Tab:

  • Host origins, logins, and programs executing queries
  • Session IDs, runtime, database, and status
  • Connection context information
  • Group by Database, Status, Host, Login, Program, or SQL Statement

Workload Compare

What it provides:

  • Side-by-side comparison of workload activity and configuration between one or two instances for two time periods
  • Comparison settings for time ranges (24, 12, 6, 3, or 1 hour windows)
  • Compare by Running Queries, CPU, Wait Time, or IO
  • Configurable workload variance threshold to identify anomalies
  • Configuration differences detection and display

When to use it:

  • Compare performance before and after changes (code deployments, configuration changes, index modifications)
  • Analyze the impact of optimization efforts
  • Understand performance differences between time periods (e.g., peak vs. off-peak hours)
  • Validate that performance improvements were effective
  • Identify configuration changes that may have caused performance shifts

Key capabilities:

  • The Instance Properties card shows configuration differences between time periods
  • Database Activity chart highlights anomaly periods in gray
  • The Top Events table displays SQL statements with variance percentages
  • Statement Variance setting filters results by variance threshold (100% to 10,000%)
  • The Anomalies button displays detailed variance information by time interval

Query Performance and Analysis

Query Analysis (Query Statistics)

What it provides:

  • Modern Interface - Completely redesigned with improved performance and enhanced visualization
  • Execution Metrics - CPU time, duration, logical reads (IO), execution counts, and costs
  • Activity Pattern Integration - Connects query performance data directly to activity patterns
  • Flexible Date Filtering - Analyze query performance over custom date ranges (typically weeks or months)
  • Cost Analysis - Financial impact of query resource consumption with FinOps metrics

When to use it:

  • Identify expensive queries consuming the most resources
  • Analyze query execution patterns and frequency over longer timeframes
  • Optimize queries based on CPU, IO, duration, or cost
  • Track query performance over time
  • Find queries contributing to high wait times or blocking
  • Justify optimization efforts with cost metrics
  • Proactive optimization to reduce cost and increase efficiency

Key components:

Workload Analytics Card:

  • Top Overall by Resources - Dollar, CPU, or IO cost for Top 10 SQL Statements vs. the rest of the workload
  • Top Instances by Resources - Cost distribution across instances (top 5 + others)
  • Top Databases by Resources - Cost distribution across databases (top 5 + others)
  • Top Instances by Efficiency - Five instances with the highest efficiency ratings
  • Operational Analytics - Summary information and 7-day change tracking

Top Statements Table:

  • Group by Instance, Database, Object, or SQL Statement
  • Sort by SQL Statement (default), Cost, Executions, CPU, IO, or Efficiency
  • Expandable rows showing metrics by execution context (Instance/Database)
  • View Details flyout with three tabs (Details, Query History, SQL Statement)

Query Details Flyout includes:

  • Properties (Instance, Database, Object, First/Last Execution)
  • SQL Statement with full-screen and copy options
  • Query Statistics (Executions, CPU, IO, Efficiency with change vs. prior period)
  • Projected Total Cost of Code (30-day, 1-year, 2-year, 3-year projections)
  • Query Information - CPU & Reads chart with customizable time intervals
  • Query History hourly deltas (Sample Time, Executions, Duration, CPU AVG/MAX, IO AVG/MAX)

Key metrics:

  • Cost - Financial impact (based on CPU, IO, and execution frequency)
  • Loaded Cost of Code - Cost if server ran at 90% capacity 100% of the time
  • CPU Time - Processor time consumed by query execution
  • Duration - Elapsed time from query start to completion
  • IO (Logical Reads) - Number of data pages read from memory or disk
  • Executions - How many times the query has run
  • Efficiency - Ratio of rows returned/inserted/updated/deleted to IO operations consumed

Index Analysis (Index Statistics)

What it provides:

  • Weekly delta data from sys.dm_db_index_usage_stats DMV
  • Index usage patterns (seeks, scans, lookups, updates per day)
  • Index size and yearly storage cost
  • Identification of unused or underutilized indexes
  • SQL statements to create and disable indexes

When to use it:

  • Find unused or underused indexes to reclaim storage and improve write performance
  • Analyze index usage patterns to validate index effectiveness
  • Identify indexes with high lookups indicating optimization opportunities
  • Calculate storage costs for indexes
  • Generate scripts to disable or recreate indexes

Key capabilities:

  • Filter by Instance, Database, Table, Compressed status, Unique status, and Tags
  • Sort by seeks/day, scans/day, lookups/day, updates/day, index size, or yearly cost
  • Index Details flyout with properties, SQL statements, statistics, and column information
  • Visual distinction between key columns (green) and included columns (orange)
  • Copy-to-clipboard functionality for the create and disable scripts

Important note: Data requires at least one week of monitoring in WISdom before appearing

Blocking Analysis

The Blocking section helps you identify and resolve blocking issues where one session holds a lock on a resource while other sessions wait for access to the same resource.

Blocking

What it provides:

  • Introduction to blocking concepts and navigation between Overview and Details screens
  • Two main screens: Overview (aggregated blocking analysis) and Details (single-instance drill-down)

Blocking - Overview

What it provides:

  • Blocking Events/Victims Chart - Visualization of blocking events or victims count over time for the top 3 instances
  • Blocks by Chain Table - Aggregated blocking chains grouped by Instance, Database, Lead Blocker, and Object
  • Blocks by Object Table - Alternative view grouping by Table, Instance, Database, Schema, or Index ID
  • Day of Week and Time of Day Filters - Pattern analysis for when blocking occurs

When to use it:

  • Identify patterns in blocking events across multiple instances
  • Find the most impactful blocking chains by cumulative wait time
  • Discover which objects are most frequently blocked
  • Understand when blocking typically occurs (specific days or time ranges)
  • Compare blocking activity across instances

Key capabilities:

  • Toggle between the Blocking Events and the Blocking Victims views
  • Filter by Instance, Database, Blocker ID, Weekday, Time of Day, and Tags
  • Group Blocks by Chain data by Blocker ID, Instance, Database, Object, or Lead Blocker
  • Group Blocks by Object data by Table (default), Instance, Database, Schema, or Index ID
  • Click and drag on chart to narrow time range
  • View Details flyout with Resources, Wait Statistics, and Applications tabs

Blocking metrics:

  • Blocker ID - Unique identifier for each blocking chain
  • Blocking Impact - Cumulative wait time across all blocked victims
  • Blocking Events - Count of times this blocking occurred
  • Blocked Victims - Total count of blocked SPIDs

Blocking - Details

What it provides:

  • Detailed blocking information for a single instance
  • Individual blocking event analysis with start/end times
  • Lead blocker SQL statements and blocking chain details
  • Session-level information for all participants in blocking chains

When to use it:

  • Investigate blocking issues on a specific instance
  • Analyze individual blocking events by time and duration
  • Review SQL statements causing blocks
  • Identify sleeping transactions causing blocking
  • Understand resource contention at the session level

Key capabilities:

  • Group by Blocker ID or Lead Blocker for different analysis perspectives
  • Blocking Details flyout with three tabs:
    • Resources tab - Session ID, CPU, IO, Parallel Threads, Granted Memory, Objects, SQL Statements
    • Wait Statistics tab - Session ID, Wait Type, Wait Resource, Wait Time, Objects, SQL Statements
    • Applications tab - Session ID, Host, Login, Program, Objects, SQL Statements
  • Copy SQL statements for analysis or optimization
  • Filter by Database, Blocker ID, Weekday, Time of Day, and Tags

Deadlock Analysis

The Deadlocks section helps you identify and analyze deadlock situations where two processes compete for a resource, and neither can obtain exclusive access, resulting in one process being terminated.

What it provides:

  • Overview screen with Events Count chart showing deadlock occurrences over time
  • Deadlock Groups table grouping deadlocks by Deadlock Group ID
  • Count of how many times specific deadlocks occurred
  • Details screen showing all deadlock events for a selected instance

When to use it:

  • Identify recurring deadlock patterns
  • Analyze frequency of specific deadlock scenarios
  • Troubleshoot application concurrency issues
  • Track deadlock occurrences over time
  • Find which queries and objects are involved in deadlocks

Key capabilities:

  • Filter by time range to focus on specific periods
  • Group deadlocks to identify recurring patterns
  • View Details from Overview to drill into specific deadlock events
  • Track deadlock resolution (which process was terminated)

SQL Agent Jobs

The Agent Jobs section provides monitoring and analysis of SQL Server Agent job executions across your environment.

What it provides:

  • Overview screen with job execution graph (Daily or Hourly Snapshot options)
  • Activity Details table showing job execution overview
  • Details screen with comprehensive job execution information for selected instance

When to use it:

  • Monitor SQL Agent job success and failure rates
  • Track job execution patterns and schedules
  • Identify job performance issues or long-running jobs
  • Troubleshoot failed job executions
  • Analyze job execution trends over time

Key capabilities:

  • Toggle between Daily and Hourly snapshot views
  • Filter by time range, instance, and other criteria
  • View job execution details and history
  • Track job status and duration

Database Connections

The DB Connections section displays information about user connections into databases, helping you understand connection patterns and security account usage.

What it provides:

  • Overview screen with User Connections Activity chart (top 3 instances by connections per second)
  • Connections Summary table with grouping options
  • Details screen for in-depth connection analysis
  • Filter by Environment, Instance, Host, Database, Login, Program, Weekday, and Time of Day

When to use it:

  • Monitor connection patterns across instances
  • Identify connection spikes or unusual activity
  • Analyze which applications and logins are connecting
  • Understand database connection distribution
  • Troubleshoot connection-related issues

Key capabilities:

  • Group Connections Summary by Instance, Host, or Database
  • View connections per second (CPS) trends over time
  • Filter by day of week and time blocks to identify patterns
  • Average CPS calculations for selected time ranges

Availability Groups

The Availability Groups section monitors Always On Availability Groups, providing visibility into replica health, synchronization status, and latency metrics.

What it provides:

  • Primary Replica Latency - Bytes sent to replica/sec and Log Send Queue metrics
  • Secondary Replica Latency - Redone bytes/sec and Redo Queue metrics
  • Primary Replicas Table - Health, availability mode, failovers, and data throughput
  • Secondary Replicas Table - Replica health status and synchronization information

When to use it:

  • Monitor Availability Group health and synchronization
  • Identify latency issues in data replication
  • Track failover events
  • Verify synchronization modes (Asynchronous vs. Synchronous)
  • Ensure database copies are synchronized properly

Key metrics:

  • Health State - General status of replicas
  • Availability Mode - Synchronous or asynchronous commit
  • Unhealthy Databases - Count of databases with issues
  • Avg. MB Sent to Replica - Data throughput rates
  • Failovers - Transitions of replicas between primary and secondary roles
  • Log Send Queue - Log records not yet sent to secondary (primary replica metric)
  • Redo Queue Latency - Log records not yet redone (secondary replica metric)

When to use it:

  • Ensure high availability configuration is healthy
  • Monitor disaster recovery readiness
  • Track replication performance
  • Identify synchronization lag issues
  • Verify failover readiness

How the Performance Module Fits Together

For Daily Monitoring:

  • Activity - Overview gives you the big picture across instances
  • Performance Scores on Home Page indicate overall health (links to Query Statistics)
  • Agent Jobs Overview monitors scheduled maintenance and ETL processes
  • Availability Groups ensures HA/DR replicas are synchronized

For Troubleshooting Incidents:

  • What Happened? provides minute-by-minute forensics for specific timeframes
  • Activity - Details shows time-interval breakdowns with detailed flyouts
  • Activity - Overview helps identify when problems occurred
  • Blocking - Details investigates locking issues
  • Deadlocks analyzes concurrency failures

For Query Optimization:

  • Query Statistics identifies expensive queries with cost and efficiency metrics
  • Activity - Overview Top Events shows which queries consume the most resources
  • Activity - Details Resources Tab reveals query execution patterns
  • Index Statistics identifies unused indexes and optimization opportunities

For Change Validation:

  • Workload Compare shows before/after performance differences
  • Query Statistics tracks query performance improvements over time
  • Activity - Compare validates configuration and workload changes

For Understanding Wait Events:

  • Activity - Overview Wait Activity charts wait categories over time
  • Activity - Details Wait Statistics Tab shows specific wait types and wait resources
  • Top Database Costs on Home Page identifies wait categories with highest costs

For Infrastructure Health:

  • Connections monitors database connection patterns
  • Availability Groups ensures HA/DR health
  • Agent Jobs validates scheduled tasks execute successfully
  • Blocking Analysis identifies resource contention

Common Use Cases

"The system was slow yesterday afternoon"
→ Use Activity - Overview to identify the timeframe, then What Happened? or Activity - Details to see minute-by-minute activity

"Which queries should I optimize first?"
→ Use Query Statistics sorted by Cost or CPU, and filter by high execution counts

"Did our index changes improve performance?"
→ Use Workload Compare to compare Baseline (before) vs. Comparison (after) periods

"What's causing blocking on this instance?"
→ Use Blocking - Overview to find patterns, then Blocking - Details for specific blocking chains

"Why are wait times so high?"
→ Use Activity - Overview Wait Activity to see which wait categories dominate, then Activity - Details Wait Statistics for specific wait types

"Is our optimization working?"
→ Compare Performance Score and Efficiency Score on the Home Page over time, and verify in Query Statistics

"Are there unused indexes consuming space?"
→ Use Index Statistics to find indexes with low seeks/scans and high storage costs

"Are our Availability Groups healthy?"
→ Check Availability Groups for replica health, synchronization status, and latency metrics

"Why do we have so many deadlocks?"
→ Use Deadlocks to identify recurring patterns and the queries involved

"Are backup jobs running successfully?"
→ Check Agent Jobs for job execution history and failure patterns

"What applications are connecting to this database?"
→ Use Connections filtered by database to see logins, programs, and hosts

Related Documentation

Workload Activity:

Query & Index Analysis:

Blocking & Deadlocks:

Infrastructure Monitoring:

Related Functionality: