Category: Performance Troubleshooting

  • Script: Identify Long-Running Queries

    This script returns currently executing queries ordered by elapsed time. It helps identify long-running requests that may be causing blocking, resource pressure, or user-facing delays. This is typically used during live performance incidents when users report slowness or when the instance appears busy. The Script Example Output The result set shows currently executing sessions ordered…

  • Script: Identify Missing Indexes

    This script returns the highest-impact missing index recommendations based on cumulative workload statistics. It helps identify tables where SQL Server believes additional indexes could significantly improve query performance. This is useful during performance tuning, workload reviews, and when investigating high read activity or inefficient query plans. The Script Example Output The result set shows the…

  • Script: I/O Usage by Database

    This script returns total read and write activity per database based on file-level I/O statistics. It helps identify which databases are generating the most disk activity on the instance. This is commonly used during performance troubleshooting, storage pressure investigations, and workload analysis. The Script Example Output The result set shows one row per database, including…

  • Script: Top Wait Statistics

    This script returns the top wait types since the last SQL Server restart or wait statistics clear, excluding common idle and background waits. It provides a quick high-level view of where the instance is spending time. This is often the first script run during performance investigations to identify whether the primary pressure is related to…

  • Script: Check Worker Threads and Active Sessions

    This script shows the current SQL Server worker thread count along with all active sessions and requests. It provides a live view of CPU usage, blocking, waits, running commands, and transaction activity. This is a practical script for performance troubleshooting when an instance appears busy, unresponsive, or under pressure. The Script Example Output The first…

Blog Categories

Latest Posts

Blog Tags

Always On Availability Groups (4) Database Backups & Recovery (10) Database Maintenance (7) DBA Tools (29) Performance Troubleshooting (9) PowerShell (4) Query Behaviour (4) Schema Changes (1) SQL Agent (1) SQL Interview Questions (1) SQL Scripts (25) SQL Server Administration (9) SQL Server Configuration (7) SQL Server Errors (5) SQL Server High Availability (4) SQL Server Internals (4) SQL Server Management Studio (SSMS) (7) SQL Server Monitoring (10) SQL Server Networking (9) SQL Server Replication (1) SQL Server Security (8) SQL Server Versions (2) Storage and Capacity (14) T-SQL (9) Transaction Logs (8) Troubleshooting (14) Windows Server (4)