Tag: Performance Troubleshooting

  • How to Kill a SPID in SQL Server

    In SQL Server, every connection to the database engine is assigned a Session Process ID, commonly known as a SPID. There are situations where you may need to kill a SPID in SQL Server. This typically occurs when a session is blocking other queries, running indefinitely, holding locks during maintenance, or preventing a database from…

  • How to Check Blocking SPIDs in SQL Server

    Blocking is one of the most common causes of performance issues in SQL Server. When one session holds a lock on a resource and another session needs that same resource, the second session waits. If that wait persists, users experience slowness. Understanding how to quickly identify blocking SPIDs is a core DBA skill. This guide…

  • 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…

  • Deleting Rows in Batches in SQL Server

    Deleting large volumes of data from a SQL Server table looks simple, but it can cause real problems if done carelessly. A single large DELETE can: When you’re cleaning up historical data or running routine maintenance, deleting rows in batches is usually the safest and most predictable approach. This post shows a simple, production-safe pattern…

  • 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…

  • Show Database Growth Events in SQL Server

    Database file growth events are easy to ignore until they become a problem. When a data or log file autogrows, SQL Server must extend the physical file on disk before continuing. During this operation, activity against that file can stall. If this happens frequently, it can introduce latency, increase I/O pressure, and in some cases…

  • 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…

  • Script: Check MAXDOP Configuration

    This script shows the current MAXDOP setting and helps you quickly validate whether it’s likely to be sensible for the server you’re connected to. It includes the configured MAXDOP value and core visibility information so you can make an informed call without digging through multiple places. The Script Example Output The first result set shows…

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)