Server-Wide Index Usage and Missing Index Logging in SQL Server
SQL Server 2019 through 2025 · Azure SQL Database · Azure SQL Managed Instance
SQL Server’s missing index and index usage DMVs reset on every restart, database detach, or availability group failover. The data they contain reflects only the activity since the last reset. If the server restarted at 3 AM, the DMVs at 9 AM show six hours of data. Decisions made on six hours of data instead of thirty days of data produce bad recommendations.
This guide builds a lightweight logging framework that snapshots index usage, unused indexes, and missing index recommendations on a schedule and stores them in history tables. After 30 to 90 days of snapshots, tuning decisions rest on workload evidence that survives restarts, failovers, and maintenance windows.
SQL Server 2022 and 2025 updates: Clustered and nonclustered columnstore indexes now appear in the usage DMVs. Ordered columnstore indexes added in SQL Server 2022 are tracked in the same DMVs. Azure SQL Database and Managed Instance behave identically to on-premises: counters reset at failover or maintenance. The missing index DMVs remain heuristics in all versions. They suggest potential indexes but do not account for write overhead, duplicate coverage, or full workload context. See the SQLYARD Index Tuning Guide for the full validation workflow.
Table of Contents
Setup
Scheduling
Reporting Queries
- Latest Index Usage
- Never-Used Indexes
- Not Used in Last 30 or 90 Days (Historical)
- Write-Heavy Read-Light Indexes
- Top Missing Indexes
- The Not-Used-30d View
Tuning Safely
1 Step 1: Create the DBA Schema and History Tables Beginner
Run this in a DBA utility database. The schema, helper function, and three history tables are the foundation. All subsequent steps write into these objects.
USE [YourDBADatabase];
GO
-- Create DBA schema if it does not exist
IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = 'dbadmin')
EXEC('CREATE SCHEMA dbadmin AUTHORIZATION dbo');
GO
-- Helper: returns key or include columns for an index as a comma-separated string
CREATE OR ALTER FUNCTION dbadmin.fn_IndexColumnsCsv
(
@object_id INT,
@index_id INT,
@include BIT -- 0 = key columns, 1 = included columns
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @csv NVARCHAR(MAX);
;WITH cols AS (
SELECT c.name AS colname, ic.key_ordinal, ic.is_included_column
FROM sys.index_columns ic
JOIN sys.columns c
ON c.object_id = ic.object_id
AND c.column_id = ic.column_id
WHERE ic.object_id = @object_id
AND ic.index_id = @index_id
AND ((@include = 0 AND ic.is_included_column = 0)
OR (@include = 1 AND ic.is_included_column = 1))
)
SELECT @csv = STRING_AGG(colname, ', ') FROM cols;
RETURN @csv;
END;
GO
-- Index usage history: one row per index per snapshot
IF OBJECT_ID('dbadmin.IndexUsageLog', 'U') IS NULL
CREATE TABLE dbadmin.IndexUsageLog
(
capture_id UNIQUEIDENTIFIER NOT NULL,
capture_time_utc DATETIME2(3) NOT NULL DEFAULT SYSUTCDATETIME(),
database_name SYSNAME NOT NULL,
schema_name SYSNAME NOT NULL,
object_name SYSNAME NOT NULL,
object_id INT NOT NULL,
index_name SYSNAME NULL,
index_id INT NOT NULL,
index_type_desc NVARCHAR(60) NOT NULL,
is_primary_key BIT NOT NULL,
is_unique BIT NOT NULL,
is_disabled BIT NOT NULL,
filter_definition NVARCHAR(MAX) NULL,
key_columns_csv NVARCHAR(MAX) NULL,
include_columns_csv NVARCHAR(MAX) NULL,
user_seeks BIGINT NULL,
user_scans BIGINT NULL,
user_lookups BIGINT NULL,
user_updates BIGINT NULL,
system_seeks BIGINT NULL,
system_scans BIGINT NULL,
system_lookups BIGINT NULL,
system_updates BIGINT NULL,
last_user_seek DATETIME NULL,
last_user_scan DATETIME NULL,
last_user_lookup DATETIME NULL,
last_user_update DATETIME NULL,
last_user_read DATETIME NULL,
use_flag BIT NULL
);
GO
-- Indexes that have never been used since the last DMV reset
IF OBJECT_ID('dbadmin.UnusedIndexLog', 'U') IS NULL
CREATE TABLE dbadmin.UnusedIndexLog
(
capture_id UNIQUEIDENTIFIER NOT NULL,
capture_time_utc DATETIME2(3) NOT NULL DEFAULT SYSUTCDATETIME(),
database_name SYSNAME NOT NULL,
schema_name SYSNAME NOT NULL,
object_name SYSNAME NOT NULL,
object_id INT NOT NULL,
index_name SYSNAME NOT NULL,
index_id INT NOT NULL,
index_type_desc NVARCHAR(20) NOT NULL,
last_user_read DATETIME NULL,
last_user_update DATETIME NULL,
columns_csv NVARCHAR(4000) NULL
);
GO
-- Missing index recommendations from the engine
IF OBJECT_ID('dbadmin.MissingIndexLog', 'U') IS NULL
CREATE TABLE dbadmin.MissingIndexLog
(
capture_id UNIQUEIDENTIFIER NOT NULL,
capture_time_utc DATETIME2(3) NOT NULL DEFAULT SYSUTCDATETIME(),
database_name SYSNAME NOT NULL,
schema_name SYSNAME NOT NULL,
table_name SYSNAME NOT NULL,
statement_text NVARCHAR(4000) NULL,
equality_columns NVARCHAR(4000) NULL,
inequality_columns NVARCHAR(4000) NULL,
included_columns NVARCHAR(4000) NULL,
user_seeks BIGINT NULL,
user_scans BIGINT NULL,
system_seeks BIGINT NULL,
system_scans BIGINT NULL,
avg_total_user_cost FLOAT NULL,
avg_user_impact FLOAT NULL,
impact_factor DECIMAL(20,3) NULL,
create_index_statement NVARCHAR(MAX) NULL
);
GO
-- Optional: audit table to record tuning decisions for future reference
IF OBJECT_ID('dbadmin.IndexTuningNotes', 'U') IS NULL
CREATE TABLE dbadmin.IndexTuningNotes
(
note_id BIGINT IDENTITY PRIMARY KEY,
noted_at_utc DATETIME2(3) NOT NULL DEFAULT SYSUTCDATETIME(),
database_name SYSNAME NOT NULL,
schema_name SYSNAME NOT NULL,
object_name SYSNAME NOT NULL,
index_name SYSNAME NULL,
action NVARCHAR(50) NOT NULL, -- DROP, CREATE, REBUILD, etc.
rationale NVARCHAR(MAX) NULL,
script_text NVARCHAR(MAX) NULL
);
GO2 Step 2: The Server-Wide Snapshot Procedure Intermediate
This procedure loops through all online, writable user databases and captures three things: index usage statistics, indexes that have not been used since the last DMV reset, and missing index recommendations. A single call covers the entire server.
CREATE OR ALTER PROCEDURE dbadmin.Snapshot_Indexes_AllDBs
AS
BEGIN
SET NOCOUNT ON;
DECLARE @capture_id UNIQUEIDENTIFIER = NEWID();
DECLARE @db SYSNAME;
DECLARE @dbq SYSNAME;
DECLARE @sql NVARCHAR(MAX);
DECLARE cur CURSOR LOCAL FAST_FORWARD FOR
SELECT name
FROM sys.databases
WHERE database_id > 4 -- exclude system databases
AND state_desc = 'ONLINE'
AND is_read_only = 0
AND source_database_id IS NULL; -- exclude database snapshots
OPEN cur;
FETCH NEXT FROM cur INTO @db;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @dbq = QUOTENAME(@db);
BEGIN TRY
-- --------------------------------------------------------
-- Part A: Index usage statistics
-- --------------------------------------------------------
SET @sql = N'
INSERT INTO YourDBADatabase.dbadmin.IndexUsageLog
(
capture_id, capture_time_utc, database_name, schema_name, object_name,
object_id, index_name, index_id, index_type_desc, is_primary_key,
is_unique, is_disabled, filter_definition, key_columns_csv,
include_columns_csv, user_seeks, user_scans, user_lookups, user_updates,
system_seeks, system_scans, system_lookups, system_updates, use_flag,
last_user_seek, last_user_scan, last_user_lookup, last_user_update,
last_user_read
)
SELECT
@capture_id, SYSUTCDATETIME(), N' + QUOTENAME(@db, '''') + N',
sc.name, so.name, si.object_id,
si.name, si.index_id, si.type_desc,
si.is_primary_key, si.is_unique, si.is_disabled, si.filter_definition,
YourDBADatabase.dbadmin.fn_IndexColumnsCsv(si.object_id, si.index_id, 0),
YourDBADatabase.dbadmin.fn_IndexColumnsCsv(si.object_id, si.index_id, 1),
us.user_seeks, us.user_scans, us.user_lookups, us.user_updates,
us.system_seeks, us.system_scans, us.system_lookups, us.system_updates,
CASE WHEN COALESCE(us.user_seeks,0)
+ COALESCE(us.user_scans,0)
+ COALESCE(us.user_lookups,0) > 0 THEN 1 ELSE 0 END,
us.last_user_seek, us.last_user_scan, us.last_user_lookup, us.last_user_update,
CASE
WHEN us.last_user_seek >= COALESCE(us.last_user_scan, ''19000101'')
AND us.last_user_seek >= COALESCE(us.last_user_lookup, ''19000101'')
THEN us.last_user_seek
WHEN us.last_user_scan >= COALESCE(us.last_user_seek, ''19000101'')
AND us.last_user_scan >= COALESCE(us.last_user_lookup, ''19000101'')
THEN us.last_user_scan
ELSE us.last_user_lookup
END
FROM ' + @dbq + N'.sys.indexes si
JOIN ' + @dbq + N'.sys.objects so ON so.object_id = si.object_id
AND so.type = ''U''
JOIN ' + @dbq + N'.sys.schemas sc ON sc.schema_id = so.schema_id
LEFT JOIN sys.dm_db_index_usage_stats us
ON us.database_id = DB_ID(N' + QUOTENAME(@db, '''') + N')
AND us.object_id = si.object_id
AND us.index_id = si.index_id;
';
EXEC sp_executesql @sql,
N'@capture_id UNIQUEIDENTIFIER',
@capture_id = @capture_id;
-- --------------------------------------------------------
-- Part B: Indexes unused since last DMV reset
-- --------------------------------------------------------
SET @sql = N'
;WITH idx AS (
SELECT i.object_id, i.index_id, i.name, i.type_desc,
o.name AS objname, sch.name AS schemaname
FROM ' + @dbq + N'.sys.indexes AS i
JOIN ' + @dbq + N'.sys.objects AS o ON o.object_id = i.object_id
JOIN ' + @dbq + N'.sys.schemas AS sch ON sch.schema_id = o.schema_id
WHERE OBJECTPROPERTY(o.object_id, ''IsUserTable'') = 1
AND i.type_desc NOT IN (''HEAP'', ''CLUSTERED'')
),
cols AS (
SELECT DISTINCT ic1.object_id, ic1.index_id,
STUFF((
SELECT '','' + c2.name
FROM ' + @dbq + N'.sys.index_columns AS ic2
JOIN ' + @dbq + N'.sys.columns AS c2
ON c2.object_id = ic2.object_id
AND c2.column_id = ic2.column_id
WHERE ic2.object_id = ic1.object_id
AND ic2.index_id = ic1.index_id
FOR XML PATH(''''), TYPE).value(''.'', ''nvarchar(max)'')
, 1, 1, '''') AS index_columns
FROM ' + @dbq + N'.sys.index_columns AS ic1
),
usg AS (
SELECT s.object_id, s.index_id
FROM sys.dm_db_index_usage_stats AS s
WHERE s.database_id = DB_ID(N' + QUOTENAME(@db, '''') + N')
)
INSERT INTO YourDBADatabase.dbadmin.UnusedIndexLog
(
capture_id, capture_time_utc, database_name, schema_name, object_name,
object_id, index_name, index_id, index_type_desc, columns_csv,
last_user_read, last_user_update
)
SELECT
@capture_id, SYSUTCDATETIME(), N' + QUOTENAME(@db, '''') + N',
x.schemaname, x.objname, x.object_id,
x.name, x.index_id, x.type_desc, c.index_columns,
NULL, NULL
FROM idx AS x
LEFT JOIN usg AS u ON u.object_id = x.object_id AND u.index_id = x.index_id
LEFT JOIN cols AS c ON c.object_id = x.object_id AND c.index_id = x.index_id
WHERE u.index_id IS NULL;
';
EXEC sp_executesql @sql,
N'@capture_id UNIQUEIDENTIFIER',
@capture_id = @capture_id;
-- Backfill historical last-used from IndexUsageLog for the unused rows
UPDATE u
SET u.last_user_read = lu.max_last_user_read,
u.last_user_update = lu.max_last_user_update
FROM YourDBADatabase.dbadmin.UnusedIndexLog AS u
JOIN (
SELECT database_name, object_id, index_id,
MAX(last_user_read) AS max_last_user_read,
MAX(last_user_update) AS max_last_user_update
FROM YourDBADatabase.dbadmin.IndexUsageLog
WHERE database_name = @db
GROUP BY database_name, object_id, index_id
) AS lu
ON lu.database_name = u.database_name
AND lu.object_id = u.object_id
AND lu.index_id = u.index_id
WHERE u.capture_id = @capture_id
AND (u.last_user_read IS NULL OR u.last_user_update IS NULL);
-- --------------------------------------------------------
-- Part C: Missing index recommendations
-- --------------------------------------------------------
SET @sql = N'
USE ' + @dbq + N';
;WITH x AS (
SELECT
DB_NAME() AS database_name,
OBJECT_SCHEMA_NAME(mid.object_id, DB_ID()) AS schema_name,
OBJECT_NAME(mid.object_id, DB_ID()) AS table_name,
mid.statement AS statement_text,
mid.equality_columns,
mid.inequality_columns,
mid.included_columns,
migs.user_seeks, migs.user_scans,
migs.system_seeks, migs.system_scans,
migs.avg_total_user_cost,
migs.avg_user_impact,
CAST((migs.avg_total_user_cost * migs.avg_user_impact)
* (migs.user_seeks + migs.user_scans) AS DECIMAL(20,3)) AS impact_factor,
N''CREATE NONCLUSTERED INDEX ix_'' + o.name COLLATE DATABASE_DEFAULT
+ N''_'' + REPLACE(REPLACE(REPLACE(
ISNULL(mid.equality_columns, '''') + ISNULL(mid.inequality_columns, ''''),
N''['', N''''), N'']'', N''''), N'', '', N''_'')
+ N'' ON '' + mid.[statement]
+ N'' ('' + ISNULL(mid.equality_columns, N'''')
+ CASE WHEN mid.inequality_columns IS NULL THEN N''''
ELSE CASE WHEN mid.equality_columns IS NULL THEN N''''
ELSE N'', '' END + mid.inequality_columns
END + N'')''
+ CASE WHEN mid.included_columns IS NULL THEN N''''
ELSE N'' INCLUDE ('' + mid.included_columns + N'')'' END
+ N'';'' AS create_index_statement
FROM sys.dm_db_missing_index_group_stats AS migs
JOIN sys.dm_db_missing_index_groups AS mig ON migs.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle
JOIN sys.objects AS o ON mid.object_id = o.object_id
WHERE OBJECTPROPERTY(o.object_id, ''IsUserTable'') = 1
)
INSERT INTO YourDBADatabase.dbadmin.MissingIndexLog
(
capture_id, capture_time_utc, database_name, schema_name, table_name,
statement_text, equality_columns, inequality_columns, included_columns,
user_seeks, user_scans, system_seeks, system_scans,
avg_total_user_cost, avg_user_impact, impact_factor, create_index_statement
)
SELECT
@capture_id, SYSUTCDATETIME(),
database_name, schema_name, table_name, statement_text,
equality_columns, inequality_columns, included_columns,
user_seeks, user_scans, system_seeks, system_scans,
avg_total_user_cost, avg_user_impact, impact_factor, create_index_statement
FROM x;
';
EXEC sp_executesql @sql,
N'@capture_id UNIQUEIDENTIFIER',
@capture_id = @capture_id;
END TRY
BEGIN CATCH
PRINT CONCAT('Snapshot error in ', @db, ': ', ERROR_MESSAGE());
END CATCH;
FETCH NEXT FROM cur INTO @db;
END
CLOSE cur;
DEALLOCATE cur;
END;
GOReplace YourDBADatabase throughout. The procedure uses three-part names to insert into the central DBA database. Find and replace every occurrence of YourDBADatabase with the actual name of the utility database before executing.
3 Option A: SQL Server Agent (Recommended) Beginner
SQL Server Agent is the preferred scheduling mechanism. The script below creates the snapshot job running every 15 minutes and a nightly retention job that purges data older than 180 days.
USE msdb;
GO
-- Job: run snapshot every 15 minutes
EXEC sp_add_job
@job_name = N'Index Snapshot (All DBs)',
@enabled = 1,
@description = N'Logs index usage, unused indexes, and missing indexes server-wide.';
EXEC sp_add_jobstep
@job_name = N'Index Snapshot (All DBs)',
@step_name = N'Run Snapshot',
@subsystem = N'TSQL',
@database_name = N'YourDBADatabase',
@command = N'EXEC dbadmin.Snapshot_Indexes_AllDBs;',
@retry_attempts = 3,
@retry_interval = 5;
EXEC sp_add_schedule
@schedule_name = N'Every 15 Minutes',
@freq_type = 4, -- daily
@freq_interval = 1,
@freq_subday_type = 4, -- minutes
@freq_subday_interval = 15,
@active_start_time = 000000;
EXEC sp_attach_schedule
@job_name = N'Index Snapshot (All DBs)',
@schedule_name = N'Every 15 Minutes';
EXEC sp_add_jobserver
@job_name = N'Index Snapshot (All DBs)',
@server_name = N'(LOCAL)';
GO
-- Job: nightly retention (keep 180 days)
EXEC sp_add_job
@job_name = N'Index Logs Retention (180d)',
@enabled = 1,
@description = N'Deletes old index log rows to cap table growth.';
EXEC sp_add_jobstep
@job_name = N'Index Logs Retention (180d)',
@step_name = N'Prune',
@subsystem = N'TSQL',
@database_name = N'YourDBADatabase',
@command = N'
DELETE FROM dbadmin.IndexUsageLog
WHERE capture_time_utc < DATEADD(DAY, -180, SYSUTCDATETIME());
DELETE FROM dbadmin.UnusedIndexLog
WHERE capture_time_utc < DATEADD(DAY, -180, SYSUTCDATETIME());
DELETE FROM dbadmin.MissingIndexLog
WHERE capture_time_utc < DATEADD(DAY, -180, SYSUTCDATETIME());
';
EXEC sp_add_schedule
@schedule_name = N'Midnight Daily',
@freq_type = 4,
@freq_interval = 1,
@active_start_time = 000000;
EXEC sp_attach_schedule
@job_name = N'Index Logs Retention (180d)',
@schedule_name = N'Midnight Daily';
EXEC sp_add_jobserver
@job_name = N'Index Logs Retention (180d)',
@server_name = N'(LOCAL)';
GO
-- Verify jobs ran and check recent history
SELECT TOP 50
j.name AS job_name,
h.run_date,
h.run_time,
h.run_status,
h.message
FROM msdb.dbo.sysjobhistory h
JOIN msdb.dbo.sysjobs j ON j.job_id = h.job_id
WHERE j.name IN (N'Index Snapshot (All DBs)', N'Index Logs Retention (180d)')
ORDER BY h.instance_id DESC;4 Option B: Windows Task Scheduler and sqlcmd Beginner
For environments where SQL Server Agent is not running, Windows Task Scheduler with sqlcmd provides the same scheduling capability.
Create C:\DBA\run_snapshot.sql:
USE [YourDBADatabase];
EXEC dbadmin.Snapshot_Indexes_AllDBs;Create C:\DBA\run_snapshot.bat:
@echo off
set datetime=%date% %time%
sqlcmd -S YourServerName -d YourDBADatabase -i "C:\DBA\run_snapshot.sql" ^
-b -o "C:\DBA\snapshot_log.txt"
if errorlevel 1 (
echo [%datetime%] Snapshot FAILED >> "C:\DBA\snapshot_errors.txt"
) else (
echo [%datetime%] Snapshot OK >> "C:\DBA\snapshot_log.txt"
)In Task Scheduler: Action runs C:\DBA\run_snapshot.bat, trigger is Daily with repeat every 15 minutes for a duration of one day, run whether user is logged on or not.
For nightly retention, create C:\DBA\prune_index_logs.sql:
USE [YourDBADatabase];
DELETE FROM dbadmin.IndexUsageLog
WHERE capture_time_utc < DATEADD(DAY, -180, SYSUTCDATETIME());
DELETE FROM dbadmin.UnusedIndexLog
WHERE capture_time_utc < DATEADD(DAY, -180, SYSUTCDATETIME());
DELETE FROM dbadmin.MissingIndexLog
WHERE capture_time_utc < DATEADD(DAY, -180, SYSUTCDATETIME());Azure SQL Database: SQL Agent is not available on Azure SQL Database single databases. Use Azure Automation, Elastic Jobs, or an external scheduler with a client that can reach the database. Azure SQL Managed Instance has SQL Agent available and Option A applies directly.
5 Latest Index Usage Beginner
-- Most recent snapshot: all indexes with use flag
SELECT TOP 100
capture_time_utc,
database_name,
schema_name,
object_name,
index_name,
user_seeks + user_scans + user_lookups AS reads_since_reset,
user_updates,
use_flag,
last_user_read
FROM dbadmin.IndexUsageLog
ORDER BY capture_time_utc DESC;
-- Verify the last run inserted rows
SELECT TOP 20 *
FROM dbadmin.IndexUsageLog
ORDER BY capture_time_utc DESC;6 Never-Used Indexes Beginner
-- Indexes with no recorded usage since the last DMV reset
SELECT TOP 100
capture_time_utc,
database_name,
schema_name,
object_name,
index_name,
columns_csv,
last_user_read, -- NULL = never seen in any snapshot
last_user_update
FROM dbadmin.UnusedIndexLog
ORDER BY capture_time_utc DESC;7 Not Used in Last 30 or 90 Days (Historical) Intermediate
This query uses the full history in IndexUsageLog to find indexes where the most recent recorded read across all snapshots falls outside the window. This survives restarts because the data is in the history tables, not the DMV.
DECLARE @window_days INT = 30; -- change to 90 for a 90-day window
;WITH last_used AS (
SELECT
database_name,
object_id,
index_id,
-- most recent read across all snapshots (survives DMV resets)
CASE
WHEN MAX(last_user_seek) >= COALESCE(MAX(last_user_scan), '19000101')
AND MAX(last_user_seek) >= COALESCE(MAX(last_user_lookup), '19000101')
THEN MAX(last_user_seek)
WHEN MAX(last_user_scan) >= COALESCE(MAX(last_user_seek), '19000101')
AND MAX(last_user_scan) >= COALESCE(MAX(last_user_lookup), '19000101')
THEN MAX(last_user_scan)
ELSE MAX(last_user_lookup)
END AS max_last_user_read
FROM dbadmin.IndexUsageLog
GROUP BY database_name, object_id, index_id
),
dim AS (
SELECT DISTINCT
database_name, schema_name, object_name, object_id,
index_name, index_id, index_type_desc, is_primary_key
FROM dbadmin.IndexUsageLog
)
SELECT
d.database_name,
d.schema_name,
d.object_name,
d.index_name,
d.index_type_desc,
lu.max_last_user_read
FROM dim AS d
LEFT JOIN last_used AS lu
ON lu.database_name = d.database_name
AND lu.object_id = d.object_id
AND lu.index_id = d.index_id
WHERE (lu.max_last_user_read IS NULL
OR lu.max_last_user_read < DATEADD(DAY, -@window_days, SYSUTCDATETIME()))
AND d.index_type_desc <> 'CLUSTERED' -- clustered indexes are always needed
AND d.is_primary_key = 0 -- primary keys enforce integrity
ORDER BY d.database_name, d.schema_name, d.object_name, d.index_name;8 Write-Heavy Read-Light Indexes Advanced
These are the expensive indexes: the ones that add write overhead on every INSERT, UPDATE, and DELETE but are rarely or never read. Finding them requires computing deltas between snapshots because the DMV counters are cumulative.
DECLARE @days INT = 30; -- look at last 30 days of snapshots
DECLARE @minWrites INT = 1000; -- minimum write activity to flag
DECLARE @maxReads INT = 50; -- maximum read activity to flag
;WITH win AS (
SELECT *
FROM dbadmin.IndexUsageLog
WHERE capture_time_utc >= DATEADD(DAY, -@days, SYSUTCDATETIME())
),
deltas AS (
SELECT
database_name, schema_name, object_name, object_id,
index_name, index_id, index_type_desc, capture_time_utc,
COALESCE(user_seeks, 0) AS seeks,
COALESCE(user_scans, 0) AS scans,
COALESCE(user_lookups, 0) AS lookups,
COALESCE(user_updates, 0) AS updates,
LAG(COALESCE(user_seeks, 0)) OVER (PARTITION BY database_name, object_id, index_id ORDER BY capture_time_utc) AS prev_seeks,
LAG(COALESCE(user_scans, 0)) OVER (PARTITION BY database_name, object_id, index_id ORDER BY capture_time_utc) AS prev_scans,
LAG(COALESCE(user_lookups, 0)) OVER (PARTITION BY database_name, object_id, index_id ORDER BY capture_time_utc) AS prev_lookups,
LAG(COALESCE(user_updates, 0)) OVER (PARTITION BY database_name, object_id, index_id ORDER BY capture_time_utc) AS prev_updates
FROM win
),
agg AS (
SELECT
database_name, schema_name, object_name, object_id,
index_name, index_id, index_type_desc,
-- positive deltas only: negative values indicate a DMV reset, ignore them
SUM(CASE WHEN seeks - ISNULL(prev_seeks, seeks) > 0 THEN seeks - ISNULL(prev_seeks, seeks) END) AS seeks_delta,
SUM(CASE WHEN scans - ISNULL(prev_scans, scans) > 0 THEN scans - ISNULL(prev_scans, scans) END) AS scans_delta,
SUM(CASE WHEN lookups - ISNULL(prev_lookups, lookups) > 0 THEN lookups - ISNULL(prev_lookups, lookups) END) AS lookups_delta,
SUM(CASE WHEN updates - ISNULL(prev_updates, updates) > 0 THEN updates - ISNULL(prev_updates, updates) END) AS updates_delta
FROM deltas
GROUP BY database_name, schema_name, object_name, object_id, index_name, index_id, index_type_desc
)
SELECT TOP 100
database_name, schema_name, object_name, index_name, index_type_desc,
COALESCE(seeks_delta, 0) + COALESCE(scans_delta, 0) + COALESCE(lookups_delta, 0) AS reads_delta,
COALESCE(updates_delta, 0) AS writes_delta
FROM agg
WHERE COALESCE(updates_delta, 0) >= @minWrites
AND COALESCE(seeks_delta, 0)
+ COALESCE(scans_delta, 0)
+ COALESCE(lookups_delta,0) <= @maxReads
AND index_type_desc <> 'CLUSTERED'
ORDER BY writes_delta DESC, reads_delta ASC;9 Top Missing Indexes Beginner
-- Top missing indexes by impact score from the last 7 days of snapshots
SELECT TOP 50
capture_time_utc,
database_name,
schema_name,
table_name,
impact_factor,
create_index_statement,
user_seeks,
user_scans,
avg_total_user_cost,
avg_user_impact
FROM dbadmin.MissingIndexLog
WHERE capture_time_utc >= DATEADD(DAY, -7, SYSUTCDATETIME())
ORDER BY impact_factor DESC, capture_time_utc DESC;Validate missing index suggestions before creating anything in production. The engine generates these from individual query compilations without considering the full workload. Two suggestions may be served by one index with an additional include column. Creating every suggestion as-is creates index sprawl that hurts write performance. Cross-reference each suggestion against Query Store and actual execution plans before acting.
10 The Not-Used-30d View Intermediate
For teams that prefer a view for recurring queries, this encapsulates the 30-day not-used logic. Pass a different date threshold in a wrapper query for 90-day analysis.
CREATE OR ALTER VIEW dbadmin.v_Indexes_NotUsed_30d
AS
WITH lu AS (
SELECT
database_name, object_id, index_id,
CASE
WHEN MAX(last_user_seek) >= COALESCE(MAX(last_user_scan), '19000101')
AND MAX(last_user_seek) >= COALESCE(MAX(last_user_lookup), '19000101')
THEN MAX(last_user_seek)
WHEN MAX(last_user_scan) >= COALESCE(MAX(last_user_seek), '19000101')
AND MAX(last_user_scan) >= COALESCE(MAX(last_user_lookup), '19000101')
THEN MAX(last_user_scan)
ELSE MAX(last_user_lookup)
END AS max_last_user_read
FROM dbadmin.IndexUsageLog
GROUP BY database_name, object_id, index_id
),
dim AS (
SELECT DISTINCT
database_name, schema_name, object_name, object_id,
index_name, index_id, index_type_desc, is_primary_key
FROM dbadmin.IndexUsageLog
)
SELECT
d.database_name, d.schema_name, d.object_name,
d.index_name, d.index_type_desc,
lu.max_last_user_read
FROM dim AS d
LEFT JOIN lu
ON lu.database_name = d.database_name
AND lu.object_id = d.object_id
AND lu.index_id = d.index_id
WHERE (lu.max_last_user_read IS NULL
OR lu.max_last_user_read < DATEADD(DAY, -30, SYSUTCDATETIME()))
AND d.index_type_desc <> 'CLUSTERED'
AND d.is_primary_key = 0;
GO
-- Use the view
SELECT * FROM dbadmin.v_Indexes_NotUsed_30d
ORDER BY database_name, schema_name, object_name;
-- For 90 days, filter the view further
SELECT *
FROM dbadmin.v_Indexes_NotUsed_30d
WHERE max_last_user_read IS NULL
OR max_last_user_read < DATEADD(DAY, -90, SYSUTCDATETIME());11 Safe Index Tuning Workflow Intermediate
The logging framework provides evidence. Acting on that evidence safely requires a structured process.
- Collect at least 30 to 90 days of history. A single workload cycle may miss month-end reporting, ETL windows, or seasonal workloads. An index that shows zero reads in a two-week window may be critical at quarter-end.
- Identify candidates from the three tables. UnusedIndexLog for indexes never seen in any snapshot. IndexUsageLog for high write and low read ratios. MissingIndexLog for high impact_factor candidates.
- Validate with Query Store and execution plans. Confirm unused indexes genuinely never appear in plans. Confirm missing index candidates are triggered by queries that actually run and matter. DMV data alone is not sufficient to make a production change.
- Exclude clustered indexes and primary keys. These are never candidates for removal regardless of usage statistics.
- Test in a non-production environment first. Apply changes in dev or staging, run regression tests and critical queries, confirm no unexpected slowdowns.
- Make changes one at a time. Drop one index, monitor for a week, then proceed. Keep scripts to recreate removed indexes immediately if needed.
- Record every decision in IndexTuningNotes. Future DBAs need to know why an index was removed. The audit table is the paper trail.
The goal is evidence-based decisions, not automatic cleanup. The logging framework removes the guesswork by providing history that survives restarts and failovers. The judgment calls: which indexes to drop, which suggestions to implement, which write-heavy indexes to keep despite low reads because they enforce integrity or support specific infrequent queries. Those still require DBA expertise. The framework gives better data to apply that expertise against.
12 Troubleshooting: Why Last-Used Columns Are NULL Intermediate
NULL values in last_user_seek, last_user_scan, last_user_lookup, and last_user_update are normal in certain situations and indicate a problem in others. Here is how to diagnose which situation applies.
Step 1: Check whether the DMV has timestamps at all
-- Run inside a busy user database
SELECT TOP 20
DB_NAME(database_id) AS DatabaseName,
object_id,
index_id,
user_seeks, user_scans, user_lookups, user_updates,
last_user_seek, last_user_scan, last_user_lookup, last_user_update
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID()
AND (last_user_seek IS NOT NULL
OR last_user_scan IS NOT NULL
OR last_user_lookup IS NOT NULL
OR last_user_update IS NOT NULL)
ORDER BY COALESCE(last_user_seek, last_user_scan, last_user_lookup, '19000101') DESC;
-- If this returns zero rows: the DMV has no usage recorded since the last reset.
-- Normal after a restart, on quiet servers, or on freshly attached databases.
-- Generate some activity (see Step 2) and re-check.Step 2: Generate test activity to confirm the pipeline works
-- Create a quick test table if one does not exist
IF OBJECT_ID('dbo.idx_demo', 'U') IS NULL
BEGIN
CREATE TABLE dbo.idx_demo (id INT IDENTITY PRIMARY KEY, c1 INT, c2 INT);
CREATE INDEX IX_idx_demo_c1 ON dbo.idx_demo (c1);
INSERT dbo.idx_demo (c1, c2)
SELECT TOP 5000 ABS(CHECKSUM(NEWID())) % 1000, 1 FROM sys.all_objects;
END;
-- Generate seeks, scans, and updates
SELECT COUNT(*) FROM dbo.idx_demo WHERE c1 = 42;
SELECT SUM(c2) FROM dbo.idx_demo WHERE c1 BETWEEN 1 AND 50;
UPDATE dbo.idx_demo SET c2 = c2 + 1 WHERE c1 = 42;
-- Re-run Step 1 and confirm last_user_seek and last_user_update now appearStep 3: Run a manual snapshot and verify the log
-- Run the procedure manually
EXEC dbadmin.Snapshot_Indexes_AllDBs;
-- Verify the snapshot captured the timestamps
SELECT TOP 50
capture_time_utc, database_name, schema_name, object_name, index_name,
user_seeks, user_scans, user_lookups, user_updates,
last_user_seek, last_user_scan, last_user_lookup, last_user_update,
last_user_read, use_flag
FROM dbadmin.IndexUsageLog
WHERE database_name = DB_NAME()
ORDER BY capture_time_utc DESC;Common causes of NULL last-used columns
| Cause | Explanation | Action |
|---|---|---|
| Server recently restarted | DMV reset on restart. No history until queries run. | Let workload run and re-snapshot. |
| AG failover | DMV resets on failover. Same as restart. | Let workload run and re-snapshot. |
| Index genuinely unused | No query has touched the index since reset. | Expected. Check historical log for prior reads. |
| Insufficient permissions | VIEW SERVER STATE missing from the procedure's execution context. | Grant VIEW SERVER STATE to the SQL Agent job account. |
| Quiet or test environment | No real workload generates usage. | Run test queries or wait for production-level traffic. |
References
Discover more from SQLYARD
Subscribe to get the latest posts sent to your email.
