TempDB is a shared system database used by every session in SQL Server. When TempDB space grows or performance slows, it can be difficult to know which sessions are responsible. This script queries sys.dm_db_session_space_usage to show a clear breakdown of TempDB consumption per session, including the current SQL …

Tables without primary keys are heap structures with no guaranteed row ordering and no unique row identifier. They cause full table scans in queries that could use seeks, slow down DELETE and UPDATE operations, and are incompatible with SQL Server replication and Change Data Capture. This script finds every user table …

Find Active Long-Running Queries in SQL Server This script queries sys.dm_exec_requests and sys.dm_exec_sql_text to show all currently executing queries on the SQL Server instance, sorted by how long they have been running, along with CPU time, I/O activity, blocking status, and wait type information. Purpose and …

Find the Top CPU and IO Consuming Queries in SQL Server This script queries sys.dm_exec_query_stats to identify the most resource-intensive queries currently cached in the plan cache, showing logical reads, writes, CPU time, and elapsed time — with the full query text and execution plan for each. Purpose and Overview …

Report Index Fragmentation Across All Tables in SQL Server This script queries sys.dm_db_index_physical_stats to report external and internal fragmentation for every index in the current database, filtering to indexes with significant fragmentation and enough pages to make maintenance worthwhile. Purpose and Overview …

Find Unused Nonclustered Indexes in SQL Server This script queries sys.dm_db_index_usage_stats to identify nonclustered indexes that have never been used by any read operation since the last SQL Server restart, yet still carry write overhead with every INSERT, UPDATE, and DELETE on the table. Purpose and Overview Every …

Blocking in SQL Server occurs when one session holds a lock that another session needs, causing the second session to wait. This T-SQL script queries the dynamic management views to show all currently blocked sessions, the blocking session, wait type, wait duration, and the SQL text involved. Purpose and Overview When …

SQL Server tracks index recommendations automatically as queries execute, storing them in the missing index DMVs. This T-SQL script queries those views to produce a ranked report of missing indexes, ordered by the estimated improvement they would provide, along with a ready-to-run CREATE INDEX statement for each …

Wait statistics are one of the most reliable ways to identify performance bottlenecks in SQL Server. This T-SQL script queries sys.dm_os_wait_stats, filters out background and idle wait types that do not indicate real problems, and ranks the remaining waits by their percentage of total wait time. Purpose and Overview …

Before sys.dm_db_index_physical_stats existed, checking fragmentation across a database meant looking up every user table's object ID and running DBCC SHOWCONTIG against each one individually. This script closes that gap for the sysobjects-based era of SQL Server administration: it queries sysobjects for every table of …