Index Tuning Report – RockyPC / PhotoCleanup – 2026-07-30

Tuning goal: Index Tuning

Server: RockyPC

Database: PhotoCleanup

Version: SQL Server 2022 (16.0.1190.2), RTM-GDR

Edition: Developer Edition / Enterprise engine features available

Executive summary

Drop clearly unused nonclustered indexes on dbo.Files and dbo.Actions before adding anything new. There are multiple indexes with 0 reads but meaningful write cost, especially on the write-heavy dbo.Files table. Best immediate candidates are IX_Files_IsImage_IsQuarantined, IX_Files_IsVideo_IsQuarantined, and IX_Actions_FileId_ActionType.
Rebuild the badly fragmented, low-density indexes that remain in use. The largest win is on PK_Files and IX_Files_ScanState_IsQuarantined, where page density is very poor and fragmentation is extreme. Current scan-heavy workload is paying unnecessary I/O.
Do not add new indexes from the overlap signals. The missing-index section is empty, and most candidate keys are low-cardinality Boolean columns on a table with heavy update activity. Adding more Boolean-heavy indexes would likely worsen DML cost without enough selectivity benefit.
Enable Query Store automatic plan correction. FORCE_LAST_GOOD_PLAN is currently OFF. On SQL Server 2022 this is a safe, useful protection against future regressions.
Address expensive repeated aggregate scans with code changes rather than indexes. Repeated COUNT(*) and SUM(SizeBytes) queries over dbo.Files each read about 43,070 pages. Because IsMissing has estimated distinct values of 1, indexing that flag will not be selective enough to justify more write overhead.

Overall confidence: Medium-High

Priority	Recommendation	Expected benefit	Risk	Confidence
P1	Drop unused write-heavy indexes on `dbo.Files` and `dbo.Actions`	Lower insert/update cost, less maintenance, less fragmentation churn	Low-Medium	High
P1	Rebuild fragmented indexes that are still useful, especially `PK_Files` and `IX_Files_ScanState_IsQuarantined`	Lower read I/O and shorter scan time for common queries	Low	High
P2	Enable `FORCE_LAST_GOOD_PLAN`	Protection from future regressions	Low	High
P2	Update statistics after index cleanup/rebuild	Better cardinality estimates, better plan quality	Low	Medium-High
P3	Prefer query rewrites/materialized summaries over new Boolean indexes	Avoids write amplification on `dbo.Files`	Low-Medium	Medium

Detailed prioritized recommendations

P1Drop unused write-heavy indexes

Recommendation: Drop the following indexes:

dbo.Files.IX_Files_IsImage_IsQuarantined
dbo.Files.IX_Files_IsVideo_IsQuarantined
dbo.Actions.IX_Actions_FileId_ActionType

Why:

IX_Files_IsImage_IsQuarantined: 0 reads, 30,193 updates, size 17.2 MB.
IX_Files_IsVideo_IsQuarantined: 2 seeks, 30,193 updates, size 17.3 MB.
IX_Actions_FileId_ActionType: 0 reads, 30,021 updates, size 1.6 MB.

These are poor write-to-read tradeoffs. On dbo.Files, the two Boolean-led indexes are especially weak because the leading keys are low-cardinality. The cardinality data shows:

IsImage: estimated distinct values = 2
IsVideo: estimated distinct values = 2
IsQuarantined: estimated distinct values = 2
IsMissing: estimated distinct values = 1

That combination explains why these indexes are not helping the repeated aggregate queries; they are not selective enough, and the optimizer is ignoring them.

Not recommended to drop now:

IX_Files_NameKey, IX_Files_QuickHash, and IX_Files_Sha256 also show no direct reads in the sampled usage stats, but they align with application semantics and dedupe/hash workflows. Because workload windows can miss occasional but important usage, they should be retained unless repeated observation confirms persistent zero usage.
IX_Actions_PerformedUtc has only scan usage, but it is small and plausibly supports date-based review/audit access. Keep it for now.

Expected impact: lower DML overhead, less index maintenance, less fragmentation pressure, and better cache efficiency.

Risk: Low-Medium. Validate application features that may occasionally search by these columns before and after deployment.

Confidence: High

P1Rebuild the fragmented indexes that remain useful

Recommendation: Rebuild the heavily fragmented, low-density indexes below. Use online operations since Enterprise features are available.

Index	Reads	Writes	Avg page density	Fragmentation	Action
`dbo.Files.PK_Files`	76,516 seeks, 324 scans	30,521 updates	60.53%	98.89%	Rebuild
`dbo.Files.IX_Files_ScanState_IsQuarantined`	38 seeks, 34 scans	30,508 updates	43.24%	82.50%	Rebuild
`dbo.DupMembers.IX_DupMembers_FileId`	3 seeks, 12 scans	6,494 updates	41.23%	97.21%	Rebuild
`dbo.DupGroups.IX_DupGroups_WastedBytes`	52 scans	2,522 updates	28.58%	98.72%	Rebuild
`dbo.ImageMeta.PK_ImageMeta`	792 seeks, 26 scans	8,596 updates	67.48%	98.82%	Rebuild

Why: These indexes are being used, but page density is far below the desired range. That means more pages, more I/O, and longer scans than necessary. This aligns directly with the expensive workload patterns seen in Query Store and plan cache.

Examples that should benefit:

Query hash A562216AF17D4D82: SELECT COUNT(*) FROM [Files] ... WHERE [IsMissing] = 0 AND [IsPlaceholder] = 1 — avg logical reads 43,070.
Query hash E800BF84B1A28F51: SELECT COUNT(*) FROM [Files] ... WHERE [IsMissing] = 0 AND [IsImage] = 1 — avg logical reads 43,070.
Query hash 040E968994F6D988: SELECT COUNT(*) FROM [Files] ... WHERE [IsMissing] = 0 AND [IsVideo] = 1 — avg logical reads 43,070.
Query hash 5F0938E24B13D9A2: SELECT SUM([WastedBytes]) FROM [DupGroups] WHERE [GroupType] = @_type AND [ResolvedUtc] IS NULL.

Fill factor: For this run, a conservative rebuild target of FILLFACTOR = 90 on the most churn-heavy dbo.Files indexes is reasonable. It will not fix poor selectivity, but it should reduce rapid page split recurrence on wide, frequently updated rows. For the smaller supporting indexes, use 95.

Compression: Compression is available, but I am not recommending an immediate compression change on dbo.Files. That table has a substantial write workload, and the current evidence favors first removing dead indexes and repairing physical structure. Revisit compression only after observing post-cleanup read/write behavior.

Risk: Low when executed online during a quieter window.

Confidence: High

P2Enable automatic plan correction

Recommendation: Enable FORCE_LAST_GOOD_PLAN for PhotoCleanup.

Current state: Desired state DEFAULT, actual state OFF, reason AUTO_CONFIGURED.

Why: SQL Server 2022 supports automatic tuning well, Query Store is active, and the workload shows multiple statements with more than one distinct plan plus parameter sensitivity patterns. Even though there are no current forced-plan failures, enabling this option improves resilience against future regressions.

Risk: Low

Confidence: High

P2Refresh statistics after index cleanup and rebuild

Recommendation: Run targeted statistics updates on the main tables after the index drops/rebuilds.

Why:

dbo.Files has very heavy update activity.
Query Store shows parameter sensitivity on several statements with multiple plans.
Auto-update statistics is enabled, but manual post-maintenance refresh will help the optimizer immediately benefit from the new index layouts.

Priority targets: dbo.Files, dbo.DupGroups, dbo.DupMembers, dbo.ImageMeta, dbo.Actions.

Risk: Low

Confidence: Medium-High

P3Use query/code changes instead of adding more Boolean indexes

Recommendation: Do not add new indexes for the overlap signals involving IsMissing, IsPlaceholder, IsImage, IsVideo, or IsQuarantined at this time.

Why not:

No missing-index recommendations were found.
dbo.Files is write-heavy.
The proposed candidate keys are mostly low-cardinality flags.
IsMissing currently has estimated distinct values of 1, so indexing it is not selective.

Code changes with better payoff:

Replace repeated dashboard-style aggregate queries with one combined aggregate query or a cached summary table refreshed by the scan pipeline.
For the expensive QuickHash duplicate-check query, pre-aggregate candidate hashes into a temp table rather than using a correlated COUNT(*) subquery.
For large OPENJSON(@ids) lookups, load IDs into a temp table with a primary key and join to it, instead of parsing long JSON lists inline.

Examples where rewrite is preferable to indexing:

SELECT COUNT(*) FROM [Files] WHERE [IsMissing] = 0
SELECT COUNT(*) FROM [Files] WHERE [IsMissing] = 0 AND [IsPlaceholder] = 1
SELECT COALESCE(SUM([SizeBytes]),0) FROM [Files] WHERE [IsMissing] = 0
SELECT ... FROM [Files] WHERE [ScanState] = 2 ... AND (SELECT COUNT(*) FROM [Files] AS [f0] WHERE [f0].[QuickHash] = [f].[QuickHash] ... ) > 1

Reasoning: these are better treated as workload/query-shape problems than index design problems.

Risk: Low-Medium

Confidence: Medium

P3Findings that do not require action now

No duplicate or left-prefix overlapping indexes were detected in scope, so no consolidation drops are recommended beyond the clearly unused indexes above.
No large heaps were found.
No untrusted or disabled constraints were found; foreign keys and checks are trusted, which is good for optimization.
No forced plan failures were found in Query Store.
No columnstore recommendation: the largest table shown is roughly dbo.Files at ~550k rows, below the >1,000,000-row threshold requested for columnstore consideration.

Confidence: High

Workload evidence

Area	Evidence	Tuning implication
`dbo.Files` aggregate reads	Repeated `COUNT`/`SUM` queries read ~43,070 logical pages on average	Rebuild physical structures first; do not add nonselective Boolean indexes
Write overhead	`dbo.Files` indexes with ~30k updates and zero/tiny reads	Drop dead indexes before considering any new index
Physical health	`PK_Files` density 60.53%, fragmentation 98.89%	High-impact rebuild candidate
Plan stability	Multiple statements with 2-4 distinct plans and parameter sensitivity telemetry	Enable automatic tuning and refresh stats
DupGroups indexing	`IX_DupGroups_GroupType_ResolvedUtc` already matches overlap candidate with redundancy score 100	No new index needed there

Index structure visual aid

This visual summarizes the physical-design actions with the best risk/reward balance from the supplied data.

Forced plan review

No forced plan failures detected.

Because there are no affected query_id/plan_id pairs, no forced-plan remediation playbook is required for this run.

Scripts

Drop unused high-write indexes identified in Recommendation 1

USE [PhotoCleanup];
GO

IF EXISTS (
    SELECT 1
    FROM sys.indexes
    WHERE object_id = OBJECT_ID(N'dbo.Files')
      AND name = N'IX_Files_IsImage_IsQuarantined'
)
BEGIN
    DROP INDEX [IX_Files_IsImage_IsQuarantined] ON [dbo].[Files];
END
GO

IF EXISTS (
    SELECT 1
    FROM sys.indexes
    WHERE object_id = OBJECT_ID(N'dbo.Files')
      AND name = N'IX_Files_IsVideo_IsQuarantined'
)
BEGIN
    DROP INDEX [IX_Files_IsVideo_IsQuarantined] ON [dbo].[Files];
END
GO

IF EXISTS (
    SELECT 1
    FROM sys.indexes
    WHERE object_id = OBJECT_ID(N'dbo.Actions')
      AND name = N'IX_Actions_FileId_ActionType'
)
BEGIN
    DROP INDEX [IX_Actions_FileId_ActionType] ON [dbo].[Actions];
END
GO

Rebuild heavily fragmented indexes that are still useful, implementing Recommendation 2

USE [PhotoCleanup];
GO

ALTER INDEX [PK_Files]
ON [dbo].[Files]
REBUILD WITH (
    ONLINE = ON,
    SORT_IN_TEMPDB = ON,
    FILLFACTOR = 90,
    MAXDOP = 0
);
GO

ALTER INDEX [IX_Files_ScanState_IsQuarantined]
ON [dbo].[Files]
REBUILD WITH (
    ONLINE = ON,
    SORT_IN_TEMPDB = ON,
    FILLFACTOR = 90,
    MAXDOP = 0
);
GO

ALTER INDEX [IX_DupMembers_FileId]
ON [dbo].[DupMembers]
REBUILD WITH (
    ONLINE = ON,
    SORT_IN_TEMPDB = ON,
    FILLFACTOR = 95,
    MAXDOP = 0
);
GO

ALTER INDEX [IX_DupGroups_WastedBytes]
ON [dbo].[DupGroups]
REBUILD WITH (
    ONLINE = ON,
    SORT_IN_TEMPDB = ON,
    FILLFACTOR = 95,
    MAXDOP = 0
);
GO

ALTER INDEX [PK_ImageMeta]
ON [dbo].[ImageMeta]
REBUILD WITH (
    ONLINE = ON,
    SORT_IN_TEMPDB = ON,
    FILLFACTOR = 95,
    MAXDOP = 0
);
GO

Enable Query Store automatic plan correction, implementing Recommendation 3

USE [master];
GO

ALTER DATABASE [PhotoCleanup]
SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON );
GO

Refresh optimizer statistics after index changes, implementing Recommendation 4

USE [PhotoCleanup];
GO

UPDATE STATISTICS [dbo].[Files] WITH FULLSCAN;
GO
UPDATE STATISTICS [dbo].[DupGroups] WITH FULLSCAN;
GO
UPDATE STATISTICS [dbo].[DupMembers] WITH FULLSCAN;
GO
UPDATE STATISTICS [dbo].[ImageMeta] WITH FULLSCAN;
GO
UPDATE STATISTICS [dbo].[Actions] WITH FULLSCAN;
GO

Post-change validation script for reads, writes, fragmentation, and automatic tuning status

USE [PhotoCleanup];
GO

SELECT
    OBJECT_SCHEMA_NAME(i.object_id) AS schema_name,
    OBJECT_NAME(i.object_id) AS table_name,
    i.name AS index_name,
    us.user_seeks,
    us.user_scans,
    us.user_lookups,
    us.user_updates
FROM sys.indexes AS i
LEFT JOIN sys.dm_db_index_usage_stats AS us
    ON us.database_id = DB_ID()
   AND us.object_id = i.object_id
   AND us.index_id = i.index_id
WHERE i.object_id IN (
    OBJECT_ID(N'dbo.Files'),
    OBJECT_ID(N'dbo.Actions'),
    OBJECT_ID(N'dbo.DupGroups'),
    OBJECT_ID(N'dbo.DupMembers'),
    OBJECT_ID(N'dbo.ImageMeta')
)
AND i.index_id > 0
ORDER BY table_name, index_name;
GO

SELECT
    OBJECT_SCHEMA_NAME(ips.object_id) AS schema_name,
    OBJECT_NAME(ips.object_id) AS table_name,
    i.name AS index_name,
    ips.avg_fragmentation_in_percent,
    ips.avg_page_space_used_in_percent,
    ips.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') AS ips
JOIN sys.indexes AS i
    ON i.object_id = ips.object_id
   AND i.index_id = ips.index_id
WHERE ips.object_id IN (
    OBJECT_ID(N'dbo.Files'),
    OBJECT_ID(N'dbo.DupGroups'),
    OBJECT_ID(N'dbo.DupMembers'),
    OBJECT_ID(N'dbo.ImageMeta')
)
AND ips.index_id > 0
ORDER BY table_name, index_name;
GO

SELECT
    name,
    desired_state_desc,
    actual_state_desc,
    reason_desc
FROM sys.database_automatic_tuning_options
WHERE name = 'FORCE_LAST_GOOD_PLAN';
GO