1. Introduction
Indexes make queries fast — until they don’t.
Over time, SQL Server indexes become bloated, fragmented, duplicated, unused, or simply outdated because the nature of your workload has changed. DBAs manually tune indexes, but in modern large-scale systems with hundreds of tables and thousands of daily queries, manual index management is no longer sustainable.
This article introduces the Continuous Index Tuning System (CITS) — an automated, intelligent module that continuously analyzes workload patterns, monitors index health, identifies missing/duplicate/unused indexes, and automatically generates safe index recommendations.
The system uses a mixture of SQL metadata, extended events, query store, heuristics, and optional machine-learning scoring.
2. What Problems CITS Solves
2.1 Fragmentation accumulation
As data is inserted, updated, and deleted, indexes become fragmented, which slows down scans and increases IO.
2.2 Stale/unused indexes
Many systems have dozens of indexes that are queried less than once a month but heavily slow down INSERT/UPDATE/DELETE.
2.3 Duplicate / overlapping indexes
These increase maintenance cost without adding value.
2.4 Missing indexes
Developers create new queries but forget to create supporting indexes.
2.5 No workload awareness
Traditional scripts run blindly without knowing actual query patterns.
3. CITS System Architecture
Below is a simplified architecture diagram.
+----------------------+ +---------------------------+
| SQL Workload | | Query Store + Statistics |
| (Applications) | ----> | Extended Events |
+----------------------+ +---------------------------+
\ /
\ /
\ /
+----------------------------------------------+
| CITS ENGINE |
| - Index Usage Analyzer |
| - Fragmentation Monitor |
| - Duplicate Index Detector |
| - Missing Index Advisor |
| - Recommendation Scoring Module |
| - Scheduler (Continuous or Nightly) |
+----------------------------------------------+
|
|
+-----------------------+
| Action Executor |
| (Manual or Auto Mode) |
+-----------------------+
4. ER Diagram (Internal Metadata Tables)
CITS uses internal metadata tables to store history and recommendations:
+----------------------+ +----------------------+
| CITS_IndexHealth | | CITS_Recommendations |
+----------------------+ +----------------------+
| IndexId (PK) | | RecId (PK) |
| TableName | | IndexId |
| IndexName | | RecType |
| FragPercent | | BenefitScore |
| PageCount | | Script |
| UsageReads | | CreatedOn |
| UsageWrites | +----------------------+
| LastAnalyzed |
+----------------------+
5. CITS Workflow (Sequence Diagram)
App Queries --> SQL Server --> Query Store --> CITS Engine
|
V
Index Usage Analyzer
|
V
Fragmentation + Duplicate Check
|
V
Missing Index Detection
|
V
Generate Recommendations
|
V
Output SQL Scripts
|
V
(Auto-Execute or Manual)
6. Core Components and Logic
6.1 Index Usage Analyzer
Uses
sys.dm_db_index_usage_stats
sys.dm_db_index_operational_stats
Query Store runtime stats
Query
SELECT
DB_NAME() AS DatabaseName,
t.name AS TableName,
ix.name AS IndexName,
us.user_seeks,
us.user_scans,
us.user_lookups,
us.user_updates,
us.last_user_seek,
us.last_user_scan
FROM sys.dm_db_index_usage_stats us
JOIN sys.indexes ix ON us.index_id = ix.index_id AND us.object_id = ix.object_id
JOIN sys.tables t ON t.object_id = ix.object_id
WHERE us.database_id = DB_ID();
Detects
6.2 Fragmentation Monitor
Uses: sys.dm_db_index_physical_stats
SELECT
object_name(object_id) AS TableName,
index_id,
avg_fragmentation_in_percent,
page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED');
Rules
>30% fragmentation + pages >1000 → REBUILD
10%–30% fragmentation → REORGANIZE
Table <1000 pages → ignore
6.3 Duplicate and Overlapping Index Detector
Example overlapping indexes:
The second covers all columns of the first → first is redundant.
Sample logic
;WITH idxCols AS (
SELECT
i.name AS IndexName,
c.name AS ColumnName,
ic.key_ordinal
FROM sys.indexes i
JOIN sys.index_columns ic
ON i.object_id = ic.object_id AND i.index_id = ic.index_id
JOIN sys.columns c
ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE i.is_hypothetical = 0
)
SELECT *
FROM idxCols a
JOIN idxCols b
ON a.ColumnName = b.ColumnName
AND a.IndexName <> b.IndexName
AND a.key_ordinal = b.key_ordinal;
6.4 Missing Index Advisor
Uses
Example query
SELECT
mid.database_id,
mid.object_id,
OBJECT_NAME(mid.object_id) AS TableName,
mid.equality_columns,
mid.inequality_columns,
mid.included_columns,
migs.unique_compiles,
migs.avg_total_user_cost * migs.avg_user_impact AS ImprovementScore
FROM sys.dm_db_missing_index_details mid
JOIN sys.dm_db_missing_index_groups mig
ON mid.index_handle = mig.index_handle
JOIN sys.dm_db_missing_index_group_stats migs
ON mig.index_group_handle = migs.group_handle
ORDER BY ImprovementScore DESC;
6.5 Recommendation Scoring System
Each potential index change receives a Benefit Score based on:
Score =
Reads * 3
+ Seek Improvements * 5
+ Predicted Cost Reduction
- Write Penalty
- Storage Penalty
This ML-friendly scoring allows ranking and auto-execution decisions.
7. Putting It All Together (Nightly Job)
A SQL Agent Job runs:
Gather index usage stats
Gather fragmentation levels
Gather missing indexes
Detect duplicates
Run scoring model
Write recommendations into CITS_Recommendations
Generate scripts:
Sample Auto-Generated Script
-- Missing Index
CREATE NONCLUSTERED INDEX IX_Orders_CustomerId_OrderDate
ON Orders(CustomerId, OrderDate)
INCLUDE (Amount, Status);
or:
-- Drop Duplicate
DROP INDEX IX_Orders_CustomerId ON Orders;
or:
-- Rebuild Fragmented Index
ALTER INDEX IX_Orders_OrderDate ON Orders REBUILD;
8. Optional: Machine Learning Enhancement
You can add ML scoring using:
Historical Query Store data
Regression model to predict query latency improvement
Classification model to detect “unsafe” indexes
Reinforcement learning to adjust recommendations based on outcomes
This makes CITS a self-learning system.
9. Deployment Strategy
9.1 Phase 1 — Monitoring Only
Log all recommendations into tables, but take no action.
9.2 Phase 2 — Manual Approval
DBA reviews and approves.
9.3 Phase 3 — Automated Execution (Low-Risk Only)
Drop unused indexes
Rebuild high-fragmentation indexes
Create missing indexes with high score
9.4 Phase 4 — Fully Autonomous
Self-optimizing database.
10. Example Dashboard (Optional)
Build using Angular + ASP.NET Core:
11. Conclusion
CITS transforms SQL Server index management from a manual DBA activity into a continuous, automated, intelligent optimization process.