Author: Peter Whyte
-
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
-
How to Check Listening Ports on Windows
When troubleshooting connectivity issues, it’s often not enough to test whether a remote port is reachable. You also need to know what is actually listening on a port locally, or whether anything is listening at all. This post focuses on the local side of the equation, what is listening on the machine itself and which…
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
-
Get Last Database Restore Date and Time in SQL Server
Knowing when a database was last restored is useful during incident response, audits, and general environment validation. This is not about backup schedules or restore progress.It is a simple historical check that answers one question: When was this database last restored? This post shows a reliable way to retrieve the most recent restore date and…
Written by
-
Script: Check Index Fragmentation Across All Databases
This script checks index fragmentation levels across all online databases on the instance. It helps identify heavily fragmented indexes that may require maintenance. This is useful when reviewing index health, validating maintenance jobs, or troubleshooting performance issues related to inefficient index access. The Script Example Output The result set shows indexes across all user databases,…
Written by
-
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…
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
-
Get Database Sizes and Free Space in SQL Server
Keeping track of database file sizes and available free space is a basic but essential part of SQL Server administration. If you don’t know how large your data and log files are, or how much free space they contain, capacity planning becomes guesswork. Problems tend to surface late, usually during growth events, backups, or unexpected…
Written by
-
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…
Written by
Blog Categories
Latest Posts
- 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 (6) DBA Tools (29) Performance Troubleshooting (9) PowerShell (4) Query Behaviour (4) Schema Changes (1) SQL Agent (1) SQL Interview Questions (1) SQL Scripts (23) SQL Server Administration (11) 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)