SQL Server Identify Heap Tables Without Clustered Indexes
May 15, 2026 / · 9 min read · sql server heap tables sys.tables sys.indexes sys.dm_db_index_physical_stats sys.allocation_units sys.partitions performance database administration sql scripts ·A heap table — a table with no clustered index — stores rows in no particular order, forces full table scans for most non-indexed queries, and accumulates forwarded records every time an UPDATE causes a row to outgrow its page. This script queries sys.tables, sys.indexes, sys.partitions, sys.allocation_units, and …
Read MoreSQL Server CPU Utilization History Report
SQL Server records CPU utilization data internally every minute and stores the last 256 readings in a ring buffer. This T-SQL script reads that buffer to produce a CPU history report showing SQL Server CPU usage, other process CPU usage, and total server CPU load — without requiring any external monitoring tool. …
Read MoreSQL Server Top Queries by CPU and IO: dm_exec_query_stats
Apr 4, 2026 / · 6 min read · sql server performance sql scripts sys.dm_exec_query_stats sys.dm_exec_sql_text sys.dm_exec_query_plan query tuning database administration monitoring execution plan ·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 …
Read MoreSQL Server Unused Indexes: sys.dm_db_index_usage_stats
Apr 1, 2026 / · 7 min read · sql server performance sql scripts sys.dm_db_index_usage_stats index maintenance database administration nonclustered indexes query tuning maintenance scripts indexes ·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 …
Read MoreSQL Server Missing Indexes Report: dm_db_missing_index
Feb 28, 2026 / · 6 min read · sql server missing indexes performance dm db missing index details dm db missing index group stats index tuning dba scripts query optimization sys.dm_db_index_usage_stats sys.dm_db_missing_index_details sys.dm_db_missing_index_group_stats sys.dm_db_missing_index_groups sys.index_columns sys.indexes ·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 …
Read MoreSQL Server Wait Statistics Report: dm_os_wait_stats
Feb 27, 2026 / · 6 min read · sql server wait statistics performance dm os wait stats wait types bottleneck analysis dba scripts performance tuning sys.dm_os_wait_stats sys.dm_os_waiting_tasks ·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 …
Read MoreSQL Server UPDATE STATISTICS and SELECT Dynamic Scripts
Aug 6, 2025 / · 4 min read · sql server database administration t-sql performance tuning dynamic sql database maintenance statistics automation sysobjects update statistics sys.tables ·SQL Server Dynamic Update Statistics and Select Scripts for Database Maintenance Purpose These SQL Server scripts are designed to automatically generate database maintenance commands by dynamically creating UPDATE STATISTICS statements and SELECT queries for all user tables in a database. This approach is particularly …
Read More