Tag: Performance

  • DBA Scripts: Get Lock Escalation, Contention Analysis, and Blocking Chains with Plan

    Part of the DBA-Tools Project. Three Different Views of the Same Underlying Problem Blocking Chains shows what’s blocked right now. These three scripts go wider and deeper: Get-LockEscalationStats finds which tables have converted row/page locks into full table locks (a common, self-inflicted cause of blocking), Get-ContentionAnalysis rolls up lock waits, latch waits, TempDB allocation contention,…

    Written by

    Peter Whyte

  • DBA Scripts: Blocking and Locking

    ,

    Part of the DBA-Tools Project. “The database is slow” and “the database is blocked” look identical from the outside, timeouts, a frozen application, users complaining, and the fix is completely different depending on which one it actually is. Four scripts on this site cover the full picture: finding the head blocker, tracing a multi-level chain,…

    Written by

    Peter Whyte

  • DBA Scripts: Get Open Transactions

    Part of the DBA-Tools Project. The Transaction Nobody Remembered to Close An open transaction that never commits or rolls back is one of the quietest ways SQL Server gets into trouble: it holds locks other sessions wait on, it stops the transaction log from truncating, and none of that shows up as an error anywhere,…

    Written by

    Peter Whyte

  • DBA Scripts: Get Blocking Sessions

    Part of the DBA-Tools Project. When Everything Looks Busy But Nothing Is Moving Blocking is one of the most common causes of a SQL Server that suddenly looks frozen. Applications start timing out, users report that everything has stopped responding, and the server itself looks fine, CPU is low, disk queues are calm, nothing is…

    Written by

    Peter Whyte

  • DBA Scripts: Get Table Sizes

    Part of the DBA-Tools Project. Walk onto a new SQL Server instance and the first honest question is usually “where’s all the space going.” Not at the database level, sys.master_files answers that in seconds, but at the table level, inside a database that’s grown for years without anyone tracking which objects are actually driving the…

    Written by

    Peter Whyte

  • DBA Scripts: Get Query Store Top Queries

    Part of the DBA-Tools Project. Query Store is SQL Server’s built-in flight recorder for query performance: every query’s plan, runtime stats, and history, retained and queryable without any extra tracing setup. Most instances have had it available since SQL Server 2016, and a surprising number still have it switched off, which means every “what changed”…

    Written by

    Peter Whyte

  • DBA Scripts: Get Index Design Issues

    Part of the DBA-Tools Project. Most index problems aren’t about a single bad index, they’re about a table’s index design as a whole drifting out of shape over time. A table that accumulates 25 indexes because nobody ever removed the old ones. A composite key that grew column by column until it’s wider than SQL…

    Written by

    Peter Whyte

  • DBA Scripts: Get Implicit Conversions

    Part of the DBA-Tools Project. An implicit conversion is SQL Server quietly changing a data type to compare two values that don’t match, and it’s one of the few performance problems that will not throw an error, will not show up in a slow-query complaint, and will not appear anywhere in the object definition. The…

    Written by

    Peter Whyte

  • DBA Scripts: Get Duplicate Indexes

    Part of the DBA-Tools Project. Indexes accumulate the same way clutter does: one developer adds an index to fix a slow query, a colleague adds a near-identical one three months later because the first one wasn’t obvious from the name, and nobody ever goes back to check whether both are pulling their weight. Two indexes…

    Written by

    Peter Whyte

  • DBA Scripts: Get Statistics Health

    Part of the DBA-Tools Project. Query plans are only as good as the statistics SQL Server uses to build them. When a table’s data distribution changes but its statistics don’t get refreshed to match, the optimizer keeps making decisions based on a picture of the data that’s no longer true, choosing a scan when a…

    Written by

    Peter Whyte

Blog Categories

Latest Posts

Blog Tags

Access (3) Always On Availability Groups (4) Auditing (7) Backups (4) Blocking Locking (6) Database Backups & Recovery (8) Database Backups and Recovery (5) Database Maintenance (7) DBA Tools (28) Disk Space (5) Error Log (2) Extended Events (7) Features (6) High Availability (6) Indexes (5) Instance (3) Inventory (6) Jobs (3) Migration (6) Monitoring (24) Performance (19) Performance Troubleshooting (9) PowerShell (4) Queries (3) Query Behaviour (5) Security (7) SQL Agent (3) SQL Scripts (21) SQL Server Administration (10) SQL Server Configuration (7) SQL Server Errors (5) SQL Server High Availability (4) SQL Server Internals (3) SQL Server Management Studio (SSMS) (6) SQL Server Monitoring (7) SQL Server Networking (9) SQL Server Security (9) SQL Server Versions (2) Storage and Capacity (9) T-SQL (9) Temp DB (3) Tracing (7) Transaction Logs (5) Troubleshooting (14) Windows Server (4)