SQL Server Index Fragmentation and Statistics: Detection, Alerting, and Maintenance Without Assumptions
Index maintenance and statistics management are among the most misunderstood areas of SQL Server administration. The traditional guidance: rebuild above 30%, reorganize between 5% and 30%, update stats when they are stale, is repeated everywhere and has become disconnected from what Microsoft’s current documentation actually says. Blindly following fixed thresholds produces maintenance jobs that hammer the transaction log and I/O for gains that cannot be measured, while missing the actual problems driving slow queries.
This article builds a complete, fact-based approach: what the metrics actually measure, what Microsoft says about them, how to detect fragmentation and statistics staleness with scripts, how to prove a column’s statistics are genuinely being used by the optimizer before declaring them unimportant, and how to set up custom alerting for environments not running Ola Hallengren’s maintenance solution.
On Ola Hallengren’s maintenance solution: IndexOptimize from ola.hallengren.com remains the community gold standard for automated index and statistics maintenance. If it is already running in the environment, the investigation scripts in this article complement it. If it is not yet deployed, the custom scripts here provide a starting point until it can be implemented. Every SQL Server environment should eventually run Ola’s solution.
- What Microsoft Actually Says About Fragmentation
- Logical Fragmentation vs Page Density: Two Different Problems
- How Statistics Work and When They Go Stale
- The Leading Column Problem: Why “Not Used” Is Rarely True
- Script 1: Index Fragmentation and Page Density Analysis
- Script 2: Statistics Staleness Detection
- Script 3: Proving Column Usage from Plan Cache
- Script 4: Query Store Column Usage Investigation
- Setting Alert Thresholds: What the Evidence Supports
- Custom SQL Agent Alert Job: Fragmentation
- Custom SQL Agent Alert Job: Statistics Staleness
1 What Microsoft Actually Says About Fragmentation Beginner
Microsoft’s current documentation on index maintenance contains a statement that most SQL Server DBAs have not seen because it contradicts what has been taught for twenty years. The official Microsoft Learn page on reorganizing and rebuilding indexes states directly:
From Microsoft Learn (Maintain Indexes Optimally to Improve Performance): “For most workloads, a higher index fragmentation doesn’t affect query performance or resource consumption.” Microsoft further states: “Index maintenance decisions should be made after considering multiple factors in the specific context of each workload, including the resource cost of maintenance. They shouldn’t be based on fixed fragmentation or page density thresholds alone.”
This represents a documented shift in Microsoft’s position. The traditional 5% reorganize and 30% rebuild thresholds are community guidelines that originated from older SQL Server behavior with spinning disk storage, where out-of-order physical page reads had measurable I/O cost. On modern SSD and NVMe storage, and in environments where SQL Server’s buffer pool keeps hot data in memory, logical fragmentation has minimal impact on read performance because sequential physical I/O across pages is no longer the dominant access pattern.
What does matter according to current Microsoft guidance is page density: how full each page actually is. Low page density means SQL Server must read more pages to retrieve the same data, which increases I/O and memory pressure regardless of storage type. This distinction shapes the entire detection and alerting approach in this article.
2 Logical Fragmentation vs Page Density: Two Different Problems Beginner
sys.dm_db_index_physical_stats returns two metrics that are frequently confused. Understanding the difference is fundamental to making correct maintenance decisions.
| Metric | Column | What It Measures | Impact |
|---|---|---|---|
| Logical fragmentation | avg_fragmentation_in_percent |
Percentage of pages in the index where the logical order of pages does not match the physical order on disk | Significant on spinning disk with sequential I/O patterns. Low impact on SSD and in-memory workloads. Microsoft says this often does not affect performance. |
| Page density | avg_page_space_used_in_percent |
Average percentage of available data storage capacity that is used on all pages. Low density means pages are sparsely populated. | Low page density means more pages to read for the same data. More I/O, more buffer pool memory consumed per row retrieved. This is the metric that actually hurts performance. |
An index can have high logical fragmentation and excellent page density, in which case it reads fine. An index can have low logical fragmentation and terrible page density, in which case it is wasting significant I/O and memory. Rebuilding an index addresses both metrics. Reorganizing addresses logical fragmentation but is less effective at improving page density because it can only compact pages when it can reduce 8 pages to 7, which is frequently not possible.
Page density threshold guidance from Tim Radney (Microsoft MVP): Prioritize maintenance on indexes where avg_page_space_used_in_percent falls below 70 to 80 percent and the page count is high enough to matter. An index with 60% page density and high page count is wasting roughly 40% of its storage and forcing more I/O than necessary. This is more actionable than a fragmentation percentage alone.
3 How Statistics Work and When They Go Stale Beginner
Statistics objects contain histograms of value distribution for one or more columns. The query optimizer uses them to estimate how many rows a predicate will return, which drives every significant plan decision: join algorithm choice, seek versus scan, memory grant size, and parallelism eligibility. When statistics are stale, the optimizer is making decisions based on data distribution that no longer reflects reality, which produces suboptimal or catastrophically wrong execution plans.
The auto-update threshold: what it actually is
SQL Server automatically updates statistics when the modification counter on a statistics object’s leading column exceeds a threshold. The threshold formula changed significantly in SQL Server 2016 with compatibility level 130.
| SQL Server Version / Compat Level | Threshold Formula | Example: 2 Million Row Table |
|---|---|---|
| SQL Server 2014 and earlier, or compat level below 130 | 500 + (20% of rows at last update) | 400,500 modifications required. On a volatile large table this threshold may never be reached between manual maintenance cycles. |
| SQL Server 2016+ with compat level 130 or higher | MIN(500 + 0.20 * n, SQRT(1,000 * n)) where n = rows | MIN(400,500; 44,721) = 44,721 modifications. Statistics update more frequently as tables grow. |
According to Microsoft documentation, for a 2 million row table under the modern formula: the calculation is the minimum of 500 + (0.20 times 2,000,000) = 400,500 and SQRT(1,000 times 2,000,000) = 44,721. This means statistics update after 44,721 modifications, not 400,500. The modern formula is a major improvement for large tables but it is only active at compatibility level 130 or higher.
Check your database compatibility level before assuming the modern threshold is active. A database upgraded from SQL Server 2014 but not yet updated to compatibility level 130 or higher still uses the old 20% formula, meaning large tables can run for extended periods with statistics that have never fired an auto-update despite substantial data changes. Run SELECT name, compatibility_level FROM sys.databases to verify.
What REORGANIZE does and does not do to statistics
This is one of the most commonly misunderstood facts in SQL Server maintenance. According to Microsoft documentation, reorganizing an index does NOT update statistics. Only rebuilding an index updates statistics, and that update covers the key columns of the index with an equivalent of a full scan. Column-level statistics created by the optimizer on non-index columns are not updated by an index rebuild and require a separate UPDATE STATISTICS call.
4 The Leading Column Problem: Why “Not Used” Is Rarely True Intermediate
A critical fact about how SQL Server’s modification counter works: the modification_counter in sys.dm_db_stats_properties tracks changes to the leading column of a statistics object only. Not all columns. Not the columns referenced in the WHERE clause of the query in question. The leading column of the statistics object’s definition.
This creates a specific investigative problem. When someone claims a column is “not used” and therefore its statistics do not matter, that assertion needs to be tested against the plan cache and Query Store rather than accepted as fact. Experience repeatedly shows that a datetime column someone believes is a non-functional administrative field is actually referenced in join predicates, ORDER BY clauses, or range filters by application queries that the team is not aware of, and those references cause the optimizer to load those statistics during plan compilation.
SQL Server logs which statistics objects the optimizer loaded during plan compilation in the execution plan XML under the OptimizerStatsUsage element. Extracting this from the plan cache provides the definitive answer about whether a column’s statistics are being used, regardless of what anyone believes about the application’s query patterns.
5 Script 1: Index Fragmentation and Page Density Analysis Intermediate
This script combines both fragmentation and page density into a single result set with a recommended action for each index. Run it in SAMPLED mode for production use. Use DETAILED mode only for targeted investigation on specific tables.
-- Index Fragmentation and Page Density Analysis
-- Run in SAMPLED mode for production. DETAILED scans every page (slow on large tables).
-- Excludes indexes with page_count below 500 -- maintenance overhead exceeds benefit for small indexes.
SELECT
DB_NAME() AS DatabaseName,
SCHEMA_NAME(o.schema_id) AS SchemaName,
o.name AS TableName,
i.name AS IndexName,
i.type_desc AS IndexType,
ips.index_depth,
ips.page_count,
CAST(ips.avg_fragmentation_in_percent AS DECIMAL(6,2))
AS FragmentationPct,
CAST(ips.avg_page_space_used_in_percent AS DECIMAL(6,2))
AS PageDensityPct,
-- Combined recommendation based on both metrics
CASE
WHEN ips.avg_page_space_used_in_percent < 60
AND ips.page_count > 500
THEN 'REBUILD -- Low page density'
WHEN ips.avg_fragmentation_in_percent > 30
AND ips.page_count > 500
THEN 'REBUILD -- High fragmentation'
WHEN ips.avg_fragmentation_in_percent BETWEEN 10 AND 30
AND ips.page_count > 500
THEN 'REORGANIZE -- Moderate fragmentation'
WHEN ips.avg_page_space_used_in_percent BETWEEN 60 AND 75
AND ips.page_count > 500
THEN 'MONITOR -- Page density approaching threshold'
ELSE 'OK'
END AS RecommendedAction,
-- Generate the maintenance statement
CASE
WHEN (ips.avg_page_space_used_in_percent < 60 OR ips.avg_fragmentation_in_percent > 30)
AND ips.page_count > 500
THEN 'ALTER INDEX ' + QUOTENAME(i.name) + ' ON '
+ QUOTENAME(SCHEMA_NAME(o.schema_id)) + '.' + QUOTENAME(o.name)
+ ' REBUILD WITH (ONLINE = ON, SORT_IN_TEMPDB = ON);'
WHEN ips.avg_fragmentation_in_percent BETWEEN 10 AND 30
AND ips.page_count > 500
THEN 'ALTER INDEX ' + QUOTENAME(i.name) + ' ON '
+ QUOTENAME(SCHEMA_NAME(o.schema_id)) + '.' + QUOTENAME(o.name)
+ ' REORGANIZE;'
ELSE NULL
END AS MaintenanceStatement
FROM sys.dm_db_index_physical_stats(
DB_ID(), -- current database
NULL, -- all tables
NULL, -- all indexes
NULL, -- all partitions
'SAMPLED' -- SAMPLED for production, DETAILED for investigation
) ips
JOIN sys.objects o
ON ips.object_id = o.object_id
JOIN sys.indexes i
ON ips.object_id = i.object_id
AND ips.index_id = i.index_id
WHERE o.is_ms_shipped = 0 -- user objects only
AND i.index_id > 0 -- exclude heaps (index_id = 0)
AND ips.page_count > 500 -- exclude small indexes
AND ips.index_type_desc NOT IN (
'XML INDEX', 'SPATIAL INDEX') -- exclude unsupported types for REBUILD
ORDER BY
-- Sort to show worst page density first, then fragmentation
ips.avg_page_space_used_in_percent ASC,
ips.avg_fragmentation_in_percent DESC;Why page_count > 500 as the exclusion threshold. Indexes with fewer than 500 to 1,000 pages are small enough that their pages often reside on mixed extents shared between objects. Microsoft documentation notes that fragmentation in small indexes may not reduce after reorganizing or rebuilding because of this mixed extent behavior. The maintenance overhead typically exceeds any benefit for these objects.
6 Script 2: Statistics Staleness Detection Intermediate
This script identifies statistics objects where the modification counter indicates data has changed significantly since the last update. The modification percentage column shows how far the data has drifted from what the optimizer’s histogram reflects.
-- Statistics Staleness Analysis
-- Shows all user table statistics with their modification counters
-- and estimated drift from the histogram's last known state
SELECT
SCHEMA_NAME(o.schema_id) AS SchemaName,
o.name AS TableName,
s.name AS StatsName,
s.auto_created AS IsAutoCreated,
s.user_created AS IsUserCreated,
sp.last_updated AS LastUpdated,
sp.rows AS RowsAtLastUpdate,
sp.rows_sampled AS RowsSampled,
CAST(100.0 * sp.rows_sampled
/ NULLIF(sp.rows, 0) AS DECIMAL(6,2)) AS SamplePct,
sp.modification_counter AS LeadingColumnModifications,
CAST(100.0 * sp.modification_counter
/ NULLIF(sp.rows, 0) AS DECIMAL(10,2)) AS ModificationPct,
-- Days since last update
DATEDIFF(DAY, sp.last_updated, GETDATE()) AS DaysSinceUpdate,
-- Auto-update threshold estimate (SQL 2016+ compat 130+ formula)
CAST(SQRT(1000.0 * sp.rows) AS BIGINT) AS AutoUpdateThreshold_Modern,
-- Is the modification counter approaching or past the modern threshold?
CASE
WHEN sp.modification_counter >= CAST(SQRT(1000.0 * sp.rows) AS BIGINT)
THEN 'PAST THRESHOLD -- Update needed'
WHEN sp.modification_counter >= CAST(SQRT(1000.0 * sp.rows) * 0.75 AS BIGINT)
THEN 'APPROACHING THRESHOLD -- Monitor'
WHEN CAST(100.0 * sp.modification_counter
/ NULLIF(sp.rows, 0) AS DECIMAL(10,2)) > 10
THEN 'OVER 10 PCT MODIFIED'
ELSE 'OK'
END AS Status,
-- Generate update statement
'UPDATE STATISTICS ' + QUOTENAME(SCHEMA_NAME(o.schema_id))
+ '.' + QUOTENAME(o.name)
+ ' ' + QUOTENAME(s.name)
+ CASE
WHEN CAST(100.0 * sp.modification_counter
/ NULLIF(sp.rows, 0) AS DECIMAL(10,2)) > 10
THEN ' WITH FULLSCAN;'
ELSE ' WITH SAMPLE 30 PERCENT;'
END AS UpdateStatement
FROM sys.stats s
JOIN sys.objects o
ON s.object_id = o.object_id
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE o.is_ms_shipped = 0
AND o.type = 'U' -- user tables only
AND sp.rows > 0 -- exclude empty tables
AND sp.modification_counter > 0 -- only stats with changes since last update
ORDER BY
sp.modification_counter DESC,
ModificationPct DESC;The modification_counter tracks the leading column only. If a statistics object covers columns (A, B, C), the counter increments only when column A changes. A table where column B and C change heavily but column A rarely changes will show a low modification_counter even if the histogram is no longer representative. This is why you cannot rely solely on modification_counter to declare statistics healthy. The plan cache investigation in Script 3 provides the deeper picture.
7 Script 3: Proving Column Usage from Plan Cache Advanced
This script answers the definitive question: is the optimizer actually loading a specific column’s statistics when compiling execution plans? It extracts the OptimizerStatsUsage element from cached execution plan XML, which lists every statistics object the optimizer referenced during plan compilation. This is the investigative tool to use when someone claims a column or table is “not used” and you need to prove or disprove it before deciding whether its statistics matter.
-- Extract statistics usage from plan cache
-- Shows which statistics objects the optimizer actually loaded for each cached plan
-- Requires VIEW SERVER STATE permission
-- Step 1: Find statistics objects used by cached plans
-- This parses the XML from sys.dm_exec_query_plan
-- Enable LAST_QUERY_PLAN_STATS at database level for better coverage:
-- ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON;
;WITH PlanXML AS
(
SELECT
qs.sql_handle,
qs.plan_handle,
qs.execution_count,
qs.total_elapsed_time,
qs.total_logical_reads,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qp.query_plan IS NOT NULL
),
StatsUsage AS
(
SELECT
px.sql_handle,
px.plan_handle,
px.execution_count,
px.total_elapsed_time / NULLIF(px.execution_count, 0) AS AvgElapsedUs,
px.total_logical_reads / NULLIF(px.execution_count, 0) AS AvgLogicalReads,
stat_node.value('@Database', 'NVARCHAR(256)') AS StatDatabase,
stat_node.value('@Schema', 'NVARCHAR(256)') AS StatSchema,
stat_node.value('@Table', 'NVARCHAR(256)') AS StatTable,
stat_node.value('@Statistics','NVARCHAR(256)') AS StatsObjectName,
stat_node.value('@ModificationCount', 'BIGINT') AS ModCountAtCompile,
stat_node.value('@SamplingPercent', 'FLOAT') AS SamplingPctAtCompile,
stat_node.value('@LastUpdate', 'NVARCHAR(50)') AS LastUpdatedAtCompile
FROM PlanXML px
CROSS APPLY px.query_plan.nodes(
'//p:OptimizerStatsUsage/p:StatisticsInfo',
'xmlns:p="http://schemas.microsoft.com/sqlserver/2004/07/showplan"'
) AS t(stat_node)
)
SELECT
su.StatDatabase,
su.StatSchema,
su.StatTable,
su.StatsObjectName,
COUNT(DISTINCT su.plan_handle) AS PlansUsingThisStats,
SUM(su.execution_count) AS TotalExecutions,
MAX(su.AvgElapsedUs) / 1000.0 AS MaxAvgElapsedMs,
MAX(su.AvgLogicalReads) AS MaxAvgLogicalReads,
MAX(su.ModCountAtCompile) AS MaxModCountAtCompile,
MIN(su.SamplingPctAtCompile) AS MinSamplingPctSeen,
MAX(su.LastUpdatedAtCompile) AS LatestUpdateAtCompile,
st.text AS SampleQueryText
FROM StatsUsage su
OUTER APPLY (
SELECT TOP 1 t.text
FROM StatsUsage su2
JOIN sys.dm_exec_query_stats qs2 ON su2.plan_handle = qs2.plan_handle
CROSS APPLY sys.dm_exec_sql_text(qs2.sql_handle) t
WHERE su2.StatsObjectName = su.StatsObjectName
AND su2.StatTable = su.StatTable
) st
GROUP BY
su.StatDatabase,
su.StatSchema,
su.StatTable,
su.StatsObjectName,
st.text
ORDER BY
TotalExecutions DESC,
PlansUsingThisStats DESC;-- Targeted lookup: prove whether a specific column's statistics are used
-- Replace 'YourTableName' and 'YourStatsName' with actual values
-- Stats names for auto-created column stats follow pattern: _WA_Sys_XXXXXXXX
;WITH PlanXML AS (
SELECT
qs.sql_handle,
qs.plan_handle,
qs.execution_count,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qp.query_plan IS NOT NULL
)
SELECT
stat_node.value('@Table', 'NVARCHAR(256)') AS StatTable,
stat_node.value('@Statistics', 'NVARCHAR(256)') AS StatsObjectName,
qs_text.text AS QueryText,
px.execution_count,
stat_node.value('@ModificationCount', 'BIGINT') AS ModCountAtCompile,
stat_node.value('@SamplingPercent', 'FLOAT') AS SamplingPct,
stat_node.value('@LastUpdate', 'NVARCHAR(50)') AS LastUpdated
FROM PlanXML px
CROSS APPLY px.query_plan.nodes(
'//p:OptimizerStatsUsage/p:StatisticsInfo',
'xmlns:p="http://schemas.microsoft.com/sqlserver/2004/07/showplan"'
) AS t(stat_node)
CROSS APPLY sys.dm_exec_sql_text(px.sql_handle) qs_text
WHERE stat_node.value('@Table', 'NVARCHAR(256)') LIKE '%YourTableName%'
OR stat_node.value('@Statistics', 'NVARCHAR(256)') LIKE '%YourStatsName%'
ORDER BY px.execution_count DESC;What this proves in practice: When someone claims a datetime column is never used in queries, run this script and filter on that table. If the column’s statistics object (look for the auto-created name like _WA_Sys_ or the index name containing the column) appears in the results with a non-zero execution count, the optimizer is loading those statistics and the column matters. The SamplingPct value shows how representative the histogram was at compile time. A low sampling percentage on a highly-used statistics object is itself a performance risk.
8 Script 4: Query Store Column Usage Investigation Advanced
The plan cache investigation in Script 3 only covers plans currently in cache. If plans have been evicted, the evidence disappears. Query Store persists plans and runtime statistics across restarts, providing a longer-term view of which statistics objects have been used by the optimizer.
-- Query Store investigation: find statistics referenced in persisted plans
-- Requires Query Store enabled in READ_WRITE mode
-- More comprehensive than plan cache because it survives restarts
SELECT
qsq.query_id,
qsp.plan_id,
qsrs.count_executions,
qsrs.avg_duration / 1000.0 AS AvgDurationMs,
qsrs.avg_logical_io_reads AS AvgLogicalReads,
LEFT(qsqt.query_sql_text, 300) AS QueryText,
stat_node.value('@Table', 'NVARCHAR(256)') AS StatTable,
stat_node.value('@Statistics', 'NVARCHAR(256)') AS StatsObjectName,
stat_node.value('@ModificationCount', 'BIGINT') AS ModCountAtCompile,
stat_node.value('@SamplingPercent', 'FLOAT') AS SamplingPct,
stat_node.value('@LastUpdate', 'NVARCHAR(50)') AS StatsLastUpdated
FROM sys.query_store_query qsq
JOIN sys.query_store_query_text qsqt
ON qsq.query_text_id = qsqt.query_text_id
JOIN sys.query_store_plan qsp
ON qsp.query_id = qsq.query_id
JOIN sys.query_store_runtime_stats qsrs
ON qsrs.plan_id = qsp.plan_id
CROSS APPLY (
SELECT TRY_CAST(qsp.query_plan AS XML)
) AS qp(plan_xml)
CROSS APPLY qp.plan_xml.nodes(
'//p:OptimizerStatsUsage/p:StatisticsInfo',
'xmlns:p="http://schemas.microsoft.com/sqlserver/2004/07/showplan"'
) AS t(stat_node)
WHERE qp.plan_xml IS NOT NULL
-- Filter to specific table or stats object name:
-- AND stat_node.value('@Table', 'NVARCHAR(256)') LIKE '%YourTableName%'
ORDER BY
qsrs.count_executions DESC,
qsrs.avg_duration DESC;9 Setting Alert Thresholds: What the Evidence Supports Intermediate
Fixed fragmentation thresholds are not supported by Microsoft’s current guidance as the primary trigger for alerts or maintenance. The appropriate thresholds for a SQL Server alerting system, based on what the evidence actually supports, are as follows.
| Condition | Threshold | Justification |
|---|---|---|
| Page density (alert) | Below 65% with page_count above 1,000 | Low page density consistently causes more I/O and memory pressure regardless of storage type. Microsoft documentation identifies this as a more meaningful metric than logical fragmentation. |
| Page density (investigate) | Below 75% with page_count above 1,000 | Indexes in this range are approaching the point where maintenance delivers measurable benefit. Track over time rather than immediate action. |
| Logical fragmentation (rebuild trigger) | Above 30% with page_count above 500 | The community standard, not an official Microsoft threshold. On SSD environments this threshold may be relaxed upward. On environments with heavy sequential scan workloads it remains relevant. |
| Statistics modification pct (immediate) | Leading column modifications above 20% of last known row count | At this level the histogram is likely materially stale for most workloads. Update with FULLSCAN for tables under 10GB, with SAMPLE 30 PERCENT for larger tables. |
| Statistics modification pct (monitor) | Leading column modifications between 10% and 20% of last known row count | Monitor and schedule update in next available maintenance window. Use sampled update. |
| Statistics not updated in N days | More than 7 days without update on tables with active modification | On high-volatility OLTP tables, weekly statistics updates are the minimum reasonable frequency regardless of whether the auto-update threshold has fired. |
10 Custom SQL Agent Alert Job: Fragmentation Advanced
This SQL Agent job runs a fragmentation check and writes results to a log table. A separate alert job reads the log table and sends a Database Mail notification when thresholds are breached. Run the detection job during off-peak hours as it uses SAMPLED mode which still adds I/O load.
-- Step 1: Create the log table in a DBA utility database
-- Run this once in your DBA utility database
USE [YourDBADatabase];
GO
IF OBJECT_ID('dba.IndexMaintenanceLog', 'U') IS NULL
CREATE TABLE dba.IndexMaintenanceLog
(
LogId INT IDENTITY(1,1) PRIMARY KEY,
CaptureTime DATETIME2(3) NOT NULL DEFAULT SYSUTCDATETIME(),
DatabaseName NVARCHAR(128) NOT NULL,
SchemaName NVARCHAR(128) NOT NULL,
TableName NVARCHAR(256) NOT NULL,
IndexName NVARCHAR(256) NOT NULL,
IndexType NVARCHAR(60) NOT NULL,
PageCount BIGINT NOT NULL,
FragmentationPct DECIMAL(6,2) NOT NULL,
PageDensityPct DECIMAL(6,2) NOT NULL,
RecommendedAction NVARCHAR(100) NOT NULL,
AlertSent BIT NOT NULL DEFAULT 0
);
GO
-- Step 2: SQL Agent job step script for fragmentation capture
-- Create a job that runs this on a schedule (e.g. every Sunday at 1 AM)
-- Adjust @threshold_page_density and @threshold_fragmentation as needed
DECLARE
@threshold_page_density DECIMAL(6,2) = 65.0, -- alert below this
@threshold_fragmentation DECIMAL(6,2) = 30.0; -- alert above this
INSERT INTO [YourDBADatabase].dba.IndexMaintenanceLog
(DatabaseName, SchemaName, TableName, IndexName, IndexType,
PageCount, FragmentationPct, PageDensityPct, RecommendedAction)
SELECT
DB_NAME(),
SCHEMA_NAME(o.schema_id),
o.name,
i.name,
i.type_desc,
ips.page_count,
CAST(ips.avg_fragmentation_in_percent AS DECIMAL(6,2)),
CAST(ips.avg_page_space_used_in_percent AS DECIMAL(6,2)),
CASE
WHEN ips.avg_page_space_used_in_percent < @threshold_page_density
THEN 'REBUILD -- Page density below ' + CAST(@threshold_page_density AS VARCHAR)
WHEN ips.avg_fragmentation_in_percent > @threshold_fragmentation
THEN 'REBUILD -- Fragmentation above ' + CAST(@threshold_fragmentation AS VARCHAR)
WHEN ips.avg_fragmentation_in_percent BETWEEN 10 AND @threshold_fragmentation
THEN 'REORGANIZE'
ELSE 'OK'
END
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ips
JOIN sys.objects o ON ips.object_id = o.object_id
JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE o.is_ms_shipped = 0
AND i.index_id > 0
AND ips.page_count > 500
AND i.type_desc NOT IN ('XML INDEX', 'SPATIAL INDEX')
AND (
ips.avg_page_space_used_in_percent < @threshold_page_density
OR ips.avg_fragmentation_in_percent > 10 -- capture everything above 10%
);
-- Step 3: Send alert email for indexes needing attention
-- Run this as a second job step or in a separate notification job
DECLARE @body NVARCHAR(MAX);
DECLARE @subject NVARCHAR(500);
DECLARE @count INT;
SELECT @count = COUNT(*)
FROM [YourDBADatabase].dba.IndexMaintenanceLog
WHERE CaptureTime >= DATEADD(HOUR, -25, SYSUTCDATETIME())
AND AlertSent = 0
AND RecommendedAction LIKE 'REBUILD%';
IF @count > 0
BEGIN
SET @subject = 'Index Maintenance Alert: '
+ CAST(@count AS NVARCHAR) + ' indexes on '
+ @@SERVERNAME + ' need rebuilding';
SET @body = 'Indexes requiring REBUILD on ' + @@SERVERNAME + ':' + CHAR(13) + CHAR(10)
+ CHAR(13) + CHAR(10);
SELECT @body = @body
+ SchemaName + '.' + TableName + '.' + IndexName
+ ' | Pages: ' + CAST(PageCount AS NVARCHAR)
+ ' | Fragmentation: ' + CAST(FragmentationPct AS NVARCHAR) + '%'
+ ' | Page Density: ' + CAST(PageDensityPct AS NVARCHAR) + '%'
+ ' | Action: ' + RecommendedAction
+ CHAR(13) + CHAR(10)
FROM [YourDBADatabase].dba.IndexMaintenanceLog
WHERE CaptureTime >= DATEADD(HOUR, -25, SYSUTCDATETIME())
AND AlertSent = 0
AND RecommendedAction LIKE 'REBUILD%'
ORDER BY PageDensityPct ASC, FragmentationPct DESC;
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DBA Alerts', -- update to your mail profile name
@recipients = 'dba@yourcompany.com', -- update to your DBA email
@subject = @subject,
@body = @body;
-- Mark alerts as sent
UPDATE [YourDBADatabase].dba.IndexMaintenanceLog
SET AlertSent = 1
WHERE CaptureTime >= DATEADD(HOUR, -25, SYSUTCDATETIME())
AND AlertSent = 0
AND RecommendedAction LIKE 'REBUILD%';
END11 Custom SQL Agent Alert Job: Statistics Staleness Advanced
-- Statistics staleness alert
-- Run as a SQL Agent job step, scheduled daily
USE [YourTargetDatabase]; -- run in each database being monitored
DECLARE
@pct_threshold_fullscan DECIMAL(10,2) = 20.0, -- trigger FULLSCAN update
@pct_threshold_sample DECIMAL(10,2) = 10.0, -- trigger sampled update
@days_since_update_max INT = 7; -- max days before mandatory update
DECLARE @StaleStats TABLE
(
SchemaName NVARCHAR(128),
TableName NVARCHAR(256),
StatsName NVARCHAR(256),
LastUpdated DATETIME,
Rows BIGINT,
ModificationPct DECIMAL(10,2),
DaysSinceUpdate INT,
Action NVARCHAR(50)
);
INSERT INTO @StaleStats
SELECT
SCHEMA_NAME(o.schema_id),
o.name,
s.name,
sp.last_updated,
sp.rows,
CAST(100.0 * sp.modification_counter / NULLIF(sp.rows, 0) AS DECIMAL(10,2)),
DATEDIFF(DAY, sp.last_updated, GETDATE()),
CASE
WHEN CAST(100.0 * sp.modification_counter / NULLIF(sp.rows, 0) AS DECIMAL(10,2))
>= @pct_threshold_fullscan
THEN 'UPDATE FULLSCAN'
WHEN CAST(100.0 * sp.modification_counter / NULLIF(sp.rows, 0) AS DECIMAL(10,2))
>= @pct_threshold_sample
THEN 'UPDATE SAMPLE 30%'
WHEN DATEDIFF(DAY, sp.last_updated, GETDATE()) >= @days_since_update_max
AND sp.modification_counter > 0
THEN 'UPDATE SAMPLE 30% -- Age threshold'
ELSE 'OK'
END
FROM sys.stats s
JOIN sys.objects o ON s.object_id = o.object_id
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE o.is_ms_shipped = 0
AND o.type = 'U'
AND sp.rows > 0;
-- Send alert if any statistics exceed thresholds
DECLARE @stale_count INT;
SELECT @stale_count = COUNT(*) FROM @StaleStats WHERE Action <> 'OK';
IF @stale_count > 0
BEGIN
DECLARE @body NVARCHAR(MAX) = 'Stale statistics on ' + DB_NAME()
+ ' (' + @@SERVERNAME + '):' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10);
SELECT @body = @body
+ SchemaName + '.' + TableName + ' | Stats: ' + StatsName
+ ' | Modified: ' + CAST(ModificationPct AS NVARCHAR) + '%'
+ ' | Last Updated: ' + COALESCE(CONVERT(NVARCHAR, LastUpdated, 120), 'Never')
+ ' | Action: ' + Action
+ CHAR(13) + CHAR(10)
FROM @StaleStats
WHERE Action <> 'OK'
ORDER BY ModificationPct DESC;
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DBA Alerts',
@recipients = 'dba@yourcompany.com',
@subject = 'Statistics Alert: ' + CAST(@stale_count AS NVARCHAR)
+ ' stale stats in ' + DB_NAME() + ' on ' + @@SERVERNAME,
@body = @body;
END12 Custom Maintenance Script Without Ola Hallengren Advanced
For environments that cannot immediately implement Ola Hallengren’s solution, this script performs targeted maintenance based on the thresholds established in Section 9. It covers both index rebuilds and statistics updates in a single pass.
-- Custom index and statistics maintenance
-- Adjust thresholds at the top to match environment requirements
-- Test in non-production before running in production
SET NOCOUNT ON;
DECLARE
@page_density_rebuild_threshold DECIMAL(6,2) = 65.0,
@fragmentation_rebuild_threshold DECIMAL(6,2) = 30.0,
@fragmentation_reorg_threshold DECIMAL(6,2) = 10.0,
@min_page_count INT = 500,
@stats_fullscan_threshold DECIMAL(10,2)= 20.0,
@stats_sample_threshold DECIMAL(10,2)= 10.0,
@online_rebuild NVARCHAR(3) = 'ON', -- change to OFF for Standard Edition
@sql NVARCHAR(MAX),
@start_time DATETIME = GETDATE();
PRINT 'Index and statistics maintenance started: ' + CONVERT(VARCHAR, @start_time, 120);
-- ============================================================
-- PHASE 1: Index maintenance based on fragmentation and page density
-- ============================================================
DECLARE @IndexWork TABLE
(
SchemaName NVARCHAR(128),
TableName NVARCHAR(256),
IndexName NVARCHAR(256),
IndexType NVARCHAR(60),
PageCount BIGINT,
FragPct DECIMAL(6,2),
DensityPct DECIMAL(6,2),
Action NVARCHAR(20)
);
INSERT INTO @IndexWork
SELECT
SCHEMA_NAME(o.schema_id),
o.name,
i.name,
i.type_desc,
ips.page_count,
CAST(ips.avg_fragmentation_in_percent AS DECIMAL(6,2)),
CAST(ips.avg_page_space_used_in_percent AS DECIMAL(6,2)),
CASE
WHEN ips.avg_page_space_used_in_percent < @page_density_rebuild_threshold
THEN 'REBUILD'
WHEN ips.avg_fragmentation_in_percent > @fragmentation_rebuild_threshold
THEN 'REBUILD'
WHEN ips.avg_fragmentation_in_percent >= @fragmentation_reorg_threshold
THEN 'REORGANIZE'
ELSE 'SKIP'
END
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ips
JOIN sys.objects o ON ips.object_id = o.object_id
JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE o.is_ms_shipped = 0
AND i.index_id > 0
AND ips.page_count >= @min_page_count
AND i.type_desc NOT IN ('XML INDEX', 'SPATIAL INDEX');
-- Execute index maintenance
DECLARE
@Schema NVARCHAR(128),
@Table NVARCHAR(256),
@Index NVARCHAR(256),
@Action NVARCHAR(20),
@FragPct DECIMAL(6,2),
@DensPct DECIMAL(6,2);
DECLARE idx_cursor CURSOR LOCAL FAST_FORWARD FOR
SELECT SchemaName, TableName, IndexName, Action, FragPct, DensityPct
FROM @IndexWork
WHERE Action <> 'SKIP'
ORDER BY
CASE Action WHEN 'REBUILD' THEN 1 ELSE 2 END,
DensityPct ASC,
FragPct DESC;
OPEN idx_cursor;
FETCH NEXT FROM idx_cursor INTO @Schema, @Table, @Index, @Action, @FragPct, @DensPct;
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
IF @Action = 'REBUILD'
BEGIN
SET @sql = N'ALTER INDEX ' + QUOTENAME(@Index) + N' ON '
+ QUOTENAME(@Schema) + N'.' + QUOTENAME(@Table)
+ N' REBUILD WITH (ONLINE = ' + @online_rebuild
+ N', SORT_IN_TEMPDB = ON);';
PRINT 'REBUILD: ' + @Schema + '.' + @Table + '.' + @Index
+ ' | Frag: ' + CAST(@FragPct AS VARCHAR) + '%'
+ ' | Density: ' + CAST(@DensPct AS VARCHAR) + '%';
END
ELSE
BEGIN
SET @sql = N'ALTER INDEX ' + QUOTENAME(@Index) + N' ON '
+ QUOTENAME(@Schema) + N'.' + QUOTENAME(@Table)
+ N' REORGANIZE;';
PRINT 'REORGANIZE: ' + @Schema + '.' + @Table + '.' + @Index
+ ' | Frag: ' + CAST(@FragPct AS VARCHAR) + '%';
END
EXEC sp_executesql @sql;
END TRY
BEGIN CATCH
PRINT 'ERROR on ' + @Schema + '.' + @Table + '.' + @Index
+ ': ' + ERROR_MESSAGE();
END CATCH;
FETCH NEXT FROM idx_cursor INTO @Schema, @Table, @Index, @Action, @FragPct, @DensPct;
END
CLOSE idx_cursor;
DEALLOCATE idx_cursor;
-- ============================================================
-- PHASE 2: Statistics update for non-index column stats
-- Index rebuild already updated index-key statistics above
-- This phase covers column statistics and reorganized indexes
-- ============================================================
DECLARE @StatsWork TABLE
(
SchemaName NVARCHAR(128),
TableName NVARCHAR(256),
StatsName NVARCHAR(256),
ModPct DECIMAL(10,2),
Action NVARCHAR(30)
);
INSERT INTO @StatsWork
SELECT
SCHEMA_NAME(o.schema_id),
o.name,
s.name,
CAST(100.0 * sp.modification_counter / NULLIF(sp.rows, 0) AS DECIMAL(10,2)),
CASE
WHEN CAST(100.0 * sp.modification_counter / NULLIF(sp.rows, 0) AS DECIMAL(10,2))
>= @stats_fullscan_threshold
THEN 'FULLSCAN'
WHEN CAST(100.0 * sp.modification_counter / NULLIF(sp.rows, 0) AS DECIMAL(10,2))
>= @stats_sample_threshold
THEN 'SAMPLE'
ELSE 'SKIP'
END
FROM sys.stats s
JOIN sys.objects o ON s.object_id = o.object_id
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE o.is_ms_shipped = 0
AND o.type = 'U'
AND sp.rows > 0
AND sp.modification_counter > 0;
DECLARE
@StSchema NVARCHAR(128),
@StTable NVARCHAR(256),
@StName NVARCHAR(256),
@StAction NVARCHAR(30);
DECLARE stats_cursor CURSOR LOCAL FAST_FORWARD FOR
SELECT SchemaName, TableName, StatsName, Action
FROM @StatsWork
WHERE Action <> 'SKIP'
ORDER BY ModPct DESC;
OPEN stats_cursor;
FETCH NEXT FROM stats_cursor INTO @StSchema, @StTable, @StName, @StAction;
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
SET @sql = N'UPDATE STATISTICS '
+ QUOTENAME(@StSchema) + N'.' + QUOTENAME(@StTable)
+ N' ' + QUOTENAME(@StName)
+ CASE @StAction
WHEN 'FULLSCAN' THEN N' WITH FULLSCAN;'
ELSE N' WITH SAMPLE 30 PERCENT;'
END;
PRINT 'UPDATE STATS (' + @StAction + '): '
+ @StSchema + '.' + @StTable + '.' + @StName;
EXEC sp_executesql @sql;
END TRY
BEGIN CATCH
PRINT 'ERROR on stats ' + @StSchema + '.' + @StTable + '.' + @StName
+ ': ' + ERROR_MESSAGE();
END CATCH;
FETCH NEXT FROM stats_cursor INTO @StSchema, @StTable, @StName, @StAction;
END
CLOSE stats_cursor;
DEALLOCATE stats_cursor;
PRINT 'Maintenance complete. Duration: '
+ CAST(DATEDIFF(MINUTE, @start_time, GETDATE()) AS VARCHAR) + ' minutes.';13 Workshop: Full Investigation from Alert to Maintenance Advanced
This workshop simulates the complete workflow from receiving an alert to executing maintenance. It is designed for use in a test environment.
- Set up the monitoring tables and alerting jobs from Sections 10 and 11 in a test database.
- Generate fragmentation artificially by inserting and deleting rows on a test table in a loop to create page splits. Run Script 1 to confirm fragmentation and page density are captured in the log table.
- Generate stale statistics by bulk-loading a large batch of rows into a test table. Run Script 2 to confirm the modification counter exceeds the threshold.
- Test the “is this column used?” investigation. Create a test query that references a datetime column in a WHERE clause. Run Script 3 against the plan cache. Confirm the statistics object for that column appears in the results. Then have someone claim “the datetime column is not used” and show them the plan cache output as evidence.
- Run the custom maintenance script from Section 12 and confirm it rebuilds the fragmented index and updates the stale statistics.
- Re-run Scripts 1 and 2 after maintenance to confirm fragmentation decreased, page density improved, and modification counter reset to zero after the statistics update.
What this workshop builds: The habit of investigating before acting. Fragmentation and statistics staleness numbers are starting points, not conclusions. The plan cache and Query Store tell you whether those numbers actually matter to the workload running on the server. The combination of detection, investigation, and targeted maintenance is more effective and less disruptive than blanket nightly rebuild jobs.
References
- Microsoft Docs: Maintain Indexes Optimally to Improve Performance and Reduce Resource Utilization
- Microsoft Docs: Statistics
- Microsoft Docs: UPDATE STATISTICS (Transact-SQL)
- Microsoft Docs: sys.dm_db_stats_properties
- Microsoft Docs: sys.dm_db_index_physical_stats
- Microsoft Docs: Guidelines for Online Index Operations
- Microsoft Tech Community: Default Auto Statistics Update Threshold Change for SQL Server 2016
- Ola Hallengren: SQL Server Index and Statistics Maintenance
- Tim Radney (Microsoft MVP): Rethinking Index Maintenance
Discover more from SQLYARD
Subscribe to get the latest posts sent to your email.
