Excessive virtual log files (VLFs) inside the transaction log slow down database recovery, backup, and restore operations — often by minutes on large logs — and the problem compounds silently every time autogrowth fires with a small increment. This script queries DBCC LOGINFO across every online database on the …

Every SQL Server installation ships with max server memory set to its default value of 2,147,483,647 MB — effectively unlimited — which allows the Buffer Pool to consume all available RAM and starve the operating system of the memory it needs for network stack, disk I/O, and other critical processes. This script reads …

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 …

Foreign keys enforce referential integrity, but SQL Server does not automatically create indexes on the referencing (child) columns. An unindexed foreign key column forces a full table scan every time a join or a DELETE on the parent table triggers a referential check. This script identifies all foreign key columns in …

MAXDOP (max degree of parallelism) controls how many CPU threads SQL Server can use for a single parallel query. The default value of 0 allows SQL Server to use all available processors, which can cause large parallel queries to monopolise the server. This script reads the CPU topology from system DMVs and calculates …

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 …