SQL Server Foreign Keys Without Indexes Script
Apr 18, 2026 / · 6 min read · sql server indexes foreign keys performance sql scripts database structure sys.foreign_keys query optimization ·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 …
Read MoreSQL Server MAXDOP Recommendation Script
Apr 17, 2026 / · 6 min read · sql server maxdop performance sp_configure numa sql scripts database administration cpu configuration ·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 …
Read MoreSQL Server TempDB Usage and Contention Report
Apr 17, 2026 / · 7 min read · sql server tempdb dm db session space usage dm exec sessions dm exec requests dm exec sql text monitoring dba scripts space usage contention performance ·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 …
Read MoreSQL Server: Find Tables Without Primary Keys
Apr 16, 2026 / · 7 min read · sql server primary keys database structure heap tables sql scripts schema audit performance sys.tables ·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 …
Read MoreSQL Server Long-Running Queries: Find Active Sessions
Apr 12, 2026 / · 6 min read · sql server performance monitoring sql scripts sys.dm_exec_requests sys.dm_exec_sql_text blocking wait types database administration active sessions ·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 …
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 Index Fragmentation: dm_db_index_physical_stats
Apr 3, 2026 / · 6 min read · sql server index maintenance sql scripts sys.dm_db_index_physical_stats fragmentation database administration performance maintenance scripts indexes rebuild reorganize ·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 …
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 Blocking Detection: Find Blocked Sessions
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 …
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 More