Tag: DBA Tools

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

  • Script: SQL Server User & Permissions Audit

    This script audits SQL Server users and permissions across all databases on an instance. It consolidates server-level logins, database users, Windows groups, and role memberships into a single result set. It is useful during security reviews, access audits, migrations, or when validating permissions after changes. The Script Example Output The result set shows consolidated role…

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

  • Script: Get SQL Server Agent Job Overview

    This script returns an overview of all SQL Server Agent jobs on an instance, including job category, owner, whether the job is enabled, the last run start time, last run duration, and the next scheduled run time. It’s a practical check when reviewing inherited environments, validating job ownership, identifying disabled jobs, or confirming schedules after…

  • Script: Get SQL Server Services Information

    This script returns details about the SQL Server and SQL Server Agent services running on an instance. It shows the service name, process ID, startup type, current status, service account, and whether instant file initialisation is enabled. This is a useful check when validating new builds, reviewing service accounts, or troubleshooting startup and permission-related issues.…

  • Script: Get Database Sizes and Free Space

    This script returns file-level size and free space information for all databases on a SQL Server instance. It shows how much space is allocated, how much is used, and how much free space remains inside each data and log file. This is a go-to check when reviewing storage usage, investigating unexpected growth, or validating capacity…

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)