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…
Written by
-
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…
Written by
-
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…
Written by
-
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…
Written by
-
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…
Written by
Blog Categories
Latest Posts
- Script: Get SQL Server CPU Topology and Core Counts
- Script: Generate Test Databases
- Troubleshooting Database Mirroring Issues in SQL Server
- Get Current Date & Time in SQL Server
- Creating SQL Logins on an Availability Group (AG) Environment
- Grant VIEW SERVER STATE in SQL Server
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)