Index Tuning Report – RockyPC – 2026-09-22 09:24:55 UTC

Executive summary

Environment and evidence

• SQL Server 2022, compatibility and feature set capable of online operations, compression, columnstore, PSP optimization, and automatic plan correction.
• The largest reported table is dbo.Files with 567,884 rows; it is large enough for selective rowstore indexes but below the supplied columnstore threshold.
• Automatic create and update statistics are enabled; asynchronous statistics updates are disabled.
• All supplied foreign keys and check constraints are trusted and enabled. No constraint repair script is required.
• No deadlock, blocking-chain, Query Store forced-plan failure, disabled index, heap, or duplicate-index evidence was supplied.

Detailed prioritized recommendations

1. 1. Create three selective filtered indexes on dbo.Files

   Recommendation: create:

   • IX_Files_LiveImage_PHashPending on (PHash), filtered to IsImage = 1 AND IsMissing = 0 AND IsQuarantined = 0 AND IsPlaceholder = 0 AND PHash IS NULL, including FileId, RootId, RelativePath, SizeBytes, Extension.
   • IX_Files_LiveVideo_FingerprintPending on (VideoFingerprint), filtered to IsVideo = 1 AND IsMissing = 0 AND IsQuarantined = 0 AND IsPlaceholder = 0 AND VideoFingerprint IS NULL, including FileId, RootId, RelativePath, SizeBytes, Extension.
   • IX_Files_Live_SizeBytes on (SizeBytes), filtered to IsMissing = 0, including FileId, IsPlaceholder, IsImage, IsVideo, IsQuarantined, ScanState.

   Evidence: Query Store reports 623 seconds for the PHash-pending query with 17,367 actual rows and 41,814 reads, 199 seconds for the video-fingerprint query with 235 rows and 4,801 reads, and repeated live-file count/sum queries with approximately 43,716 reads per execution. The supplied missing-index overlap data also reports a 43.3 MB candidate matching the PHash workload and a 13.0 MB candidate for IsMissing with SizeBytes.

   Estimated size: PHash index 43.3 MB using the supplied candidate estimate for 567,884 rows; video index 20–45 MB because no candidate size or complete column widths were supplied; live aggregate index 13.0 MB using the supplied candidate estimate. Assumptions are filtered-row counts comparable to the workload, existing clustering key propagation, rowstore PAGE compression, and normal page overhead.

   Net storage impact: approximately 76.3–101.3 MB added before drops and rebuild effects.

   Risk: Medium. Every insert or update affecting filter columns can maintain these indexes. The filters are selective and the workload evidence is strong.

   Confidence: High for the PHash and live aggregate indexes; Medium-High for the video index.

2. 2. Rebuild heavily fragmented, high-value indexes

   Rebuild PK_Files, IX_Files_RootId_PathHash, IX_Files_ScanState_IsQuarantined, and IX_Files_SizeBytes online with PAGE compression and fill factor 95. These structures have reported page densities of 60.43%, 51.66%, 43.66%, and 60.31%, respectively, with fragmentation from 82.17% to 98.90%.

   Index	Rows / before size	After estimate	Net change	Rationale
   PK_Files	570,600 / 329.5 MB	280–360 MB	-49.5 to +30.5 MB	High seeks and 99% fragmentation; clustered access path for all dependent indexes.
   IX_Files_RootId_PathHash	567,884 / 72.1 MB	55–75 MB	-17.1 to +2.9 MB	Critical synchronization join key; used by INSERT and UPDATE workloads.
   IX_Files_ScanState_IsQuarantined	567,884 / 37.6 MB	28–40 MB	-9.6 to +2.4 MB	14 seeks and 12 scans; supports scan-state processing.
   IX_Files_SizeBytes	567,884 / 37.3 MB	28–40 MB	-9.3 to +2.7 MB	Used by size-based duplicate processing and has high fragmentation.

   Estimates assume SQL Server PAGE compression and fill factor 95; actual size depends on data compressibility and row distribution. Online rebuilds still consume CPU, I/O, log space, and temporary sort space.

   Risk: Medium; schedule outside the busiest synchronization window and verify transaction-log capacity.

   Confidence: High for IX_Files_RootId_PathHash; Medium-High for the other three.

3. 3. Drop unused nonconstraint indexes after filtered-index creation

   Drop the following indexes:

   • dbo.Files.IX_Files_IsImage_IsQuarantined: 0 seeks, 0 scans, 17.6 MB.
   • dbo.Files.IX_Files_QuickHash: 0 seeks, 0 scans, 6.2 MB.
   • dbo.Actions.IX_Actions_FileId_ActionType: 0 seeks, 0 scans, 1.6 MB.
   • dbo.DupMembers.IX_DupMembers_FileId: 0 seeks, 0 scans, 5.9 MB.
   • dbo.DupGroups.IX_DupGroups_WastedBytes: 0 seeks, 2 scans, 3.7 MB and 683 updates.

   These are not reported as primary-key or unique-constraint backing indexes. Retain all constraint-enforcing indexes. DMV counters reset after restart, detach, restore, or index recreation; the recommendation is therefore appropriate only for the supplied observation period.

   Storage reclaimed: approximately 35.0 MB.

   Risk: Medium, mainly because the supplied observation window is not explicitly stated. Confidence: Medium-High.

4. 4. Enable FORCE_LAST_GOOD_PLAN

   The desired state is DEFAULT while the actual state is OFF, with reason AUTO_CONFIGURED. Enable the database-scoped option. This is a safe corrective action for a workload showing large, scan-heavy executions, even though no forced-plan failures were reported.

   Risk: Low. Automatic tuning may choose a prior plan that is safer but not optimal for every parameter distribution; monitor plan selection after enabling.

   Confidence: High.

5. 5. Improve synchronization query design and staging indexes

   The most expensive write statements join dbo.Files to temporary tables using RootId, PathHash. The existing IX_Files_RootId_PathHash is the correct access path and should be retained and rebuilt. Ensure #Staging and #HashUpdate receive temporary indexes on their join keys before the UPDATE or INSERT statements. This is a query-code and temporary-table change rather than a permanent database index recommendation.

   The large Query Store entries show 3,465 seconds for the hash-update statement and 3,305 seconds for a synchronization UPDATE. Because the staging-table definitions were not supplied, no executable permanent-table script is generated for them.

   Risk: Low for adding correctly scoped temporary indexes; confidence: Medium.

Index usage assessment

Low-cardinality columns such as IsImage, IsVideo, IsPlaceholder, and IsQuarantined should not receive broad standalone indexes. Their use inside highly selective filtered predicates is justified by the Query Store workload.

Page density and maintenance

Indexes below approximately 75% page utilization include 12 structures. The highest-impact cases are the dbo.Files indexes selected for rebuild. Fragmentation is above 80% on most of them, indicating wasted pages and likely page-split or update pressure. The supplied latch waits are modest, so the primary expected benefit is reduced I/O and improved scan efficiency rather than latch-wait elimination.

Query Store workload insights

The Query Store telemetry contains several suspiciously large duration values and inconsistent execution-count formatting, so absolute totals should be validated against runtime statistics before declaring a sustained outage. Nevertheless, the relative pattern is clear: dbo.Files scans and synchronization operations dominate the workload.

No forced-plan failures were detected. Therefore there are no affected query_id or plan_id values, failure counts, timestamps, or failure reasons to classify. No unforce or plan recreation is warranted.

Automatic tuning

Because Automatic Plan Correction is currently not enabled, the immediate action is to enable it rather than manually force or unforce any plan. After enabling, allow the engine to monitor and revert regressions while the index changes are validated.

One-line remediation: Enable last-good-plan correction, add the three selective filtered indexes, rebuild the four important fragmented indexes, then remove only the five unused nonconstraint indexes.

Constraints and statistics

• All supplied foreign keys and check constraints are trusted and enabled. No ALTER TABLE ... WITH CHECK CHECK CONSTRAINT action is required.
• Trusted constraints allow optimizer join elimination and predicate simplification. Do not disable them during index maintenance.
• Primary keys and their foreign-key relationships must remain intact. None of the recommended changes drops or recreates a primary key, so no foreign-key drop/recreate sequence is required.
• Automatic statistics creation and updates are enabled. Index creation and rebuilds create or refresh relevant index statistics. The implementation script also updates dbo.Files statistics with FULLSCAN after structural changes.
• Do not add a columnstore index: no table over 1,000,000 rows was reported. A columnstore index would add storage and write-maintenance overhead without sufficient evidence of an analytic scan workload.

Storage impact summary

The net range excludes transient sort and transaction-log space. Rebuild estimates are uncertain because actual compression ratios and row widths were not supplied. The new video index is also a range because no source candidate size was reported.

Scripts

Create filtered indexes for high-cost dbo.Files workloads

USE [PhotoCleanup];
GO

IF NOT EXISTS
(
    SELECT 1
    FROM sys.indexes
    WHERE object_id = OBJECT_ID(N'dbo.Files')
      AND name = N'IX_Files_LiveImage_PHashPending'
)
BEGIN
    CREATE NONCLUSTERED INDEX [IX_Files_LiveImage_PHashPending]
    ON [dbo].[Files] ([PHash])
    INCLUDE ([FileId], [RootId], [RelativePath], [SizeBytes], [Extension])
    WHERE [IsImage] = 1
      AND [IsMissing] = 0
      AND [IsQuarantined] = 0
      AND [IsPlaceholder] = 0
      AND [PHash] IS NULL
    WITH
    (
        DATA_COMPRESSION = PAGE,
        SORT_IN_TEMPDB = ON,
        ONLINE = ON,
        FILLFACTOR = 95
    );
END;
GO

IF NOT EXISTS
(
    SELECT 1
    FROM sys.indexes
    WHERE object_id = OBJECT_ID(N'dbo.Files')
      AND name = N'IX_Files_LiveVideo_FingerprintPending'
)
BEGIN
    CREATE NONCLUSTERED INDEX [IX_Files_LiveVideo_FingerprintPending]
    ON [dbo].[Files] ([VideoFingerprint])
    INCLUDE ([FileId], [RootId], [RelativePath], [SizeBytes], [Extension])
    WHERE [IsVideo] = 1
      AND [IsMissing] = 0
      AND [IsQuarantined] = 0
      AND [IsPlaceholder] = 0
      AND [VideoFingerprint] IS NULL
    WITH
    (
        DATA_COMPRESSION = PAGE,
        SORT_IN_TEMPDB = ON,
        ONLINE = ON,
        FILLFACTOR = 95
    );
END;
GO

IF NOT EXISTS
(
    SELECT 1
    FROM sys.indexes
    WHERE object_id = OBJECT_ID(N'dbo.Files')
      AND name = N'IX_Files_Live_SizeBytes'
)
BEGIN
    CREATE NONCLUSTERED INDEX [IX_Files_Live_SizeBytes]
    ON [dbo].[Files] ([SizeBytes])
    INCLUDE ([FileId], [IsPlaceholder], [IsImage], [IsVideo], [IsQuarantined], [ScanState])
    WHERE [IsMissing] = 0
    WITH
    (
        DATA_COMPRESSION = PAGE,
        SORT_IN_TEMPDB = ON,
        ONLINE = ON,
        FILLFACTOR = 95
    );
END;
GO

Rebuild fragmented dbo.Files indexes with online PAGE compression

USE [PhotoCleanup];
GO

IF EXISTS
(
    SELECT 1
    FROM sys.indexes
    WHERE object_id = OBJECT_ID(N'dbo.Files')
      AND name = N'PK_Files'
)
BEGIN
    ALTER INDEX [PK_Files] ON [dbo].[Files]
    REBUILD WITH
    (
        ONLINE = ON,
        SORT_IN_TEMPDB = ON,
        DATA_COMPRESSION = PAGE,
        FILLFACTOR = 95
    );
END;
GO

IF EXISTS
(
    SELECT 1
    FROM sys.indexes
    WHERE object_id = OBJECT_ID(N'dbo.Files')
      AND name = N'IX_Files_RootId_PathHash'
)
BEGIN
    ALTER INDEX [IX_Files_RootId_PathHash] ON [dbo].[Files]
    REBUILD WITH
    (
        ONLINE = ON,
        SORT_IN_TEMPDB = ON,
        DATA_COMPRESSION = PAGE,
        FILLFACTOR = 95
    );
END;
GO

IF EXISTS
(
    SELECT 1
    FROM sys.indexes
    WHERE object_id = OBJECT_ID(N'dbo.Files')
      AND name = N'IX_Files_ScanState_IsQuarantined'
)
BEGIN
    ALTER INDEX [IX_Files_ScanState_IsQuarantined] ON [dbo].[Files]
    REBUILD WITH
    (
        ONLINE = ON,
        SORT_IN_TEMPDB = ON,
        DATA_COMPRESSION = PAGE,
        FILLFACTOR = 95
    );
END;
GO

IF EXISTS
(
    SELECT 1
    FROM sys.indexes
    WHERE object_id = OBJECT_ID(N'dbo.Files')
      AND name = N'IX_Files_SizeBytes'
)
BEGIN
    ALTER INDEX [IX_Files_SizeBytes] ON [dbo].[Files]
    REBUILD WITH
    (
        ONLINE = ON,
        SORT_IN_TEMPDB = ON,
        DATA_COMPRESSION = PAGE,
        FILLFACTOR = 95
    );
END;
GO

UPDATE STATISTICS [dbo].[Files] WITH FULLSCAN;
GO

Drop unused nonconstraint indexes after replacement creation

USE [PhotoCleanup];
GO

DROP INDEX IF EXISTS [IX_Files_IsImage_IsQuarantined] ON [dbo].[Files];
GO

DROP INDEX IF EXISTS [IX_Files_QuickHash] ON [dbo].[Files];
GO

DROP INDEX IF EXISTS [IX_Actions_FileId_ActionType] ON [dbo].[Actions];
GO

DROP INDEX IF EXISTS [IX_DupMembers_FileId] ON [dbo].[DupMembers];
GO

DROP INDEX IF EXISTS [IX_DupGroups_WastedBytes] ON [dbo].[DupGroups];
GO

Enable automatic last-good-plan correction

USE [PhotoCleanup];
GO

ALTER DATABASE SCOPED CONFIGURATION
SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON);
GO

Supporting scripts (not part of the change)

Measure index usage, physical density, and compression after implementation

USE [PhotoCleanup];
GO

SELECT
    OBJECT_SCHEMA_NAME(i.object_id) AS SchemaName,
    OBJECT_NAME(i.object_id) AS TableName,
    i.name AS IndexName,
    i.type_desc,
    i.is_disabled,
    i.fill_factor,
    ds.user_seeks,
    ds.user_scans,
    ds.user_lookups,
    ds.user_updates,
    ips.avg_page_space_used_in_percent,
    ips.avg_fragmentation_in_percent,
    ips.page_count,
    ips.record_count,
    p.data_compression_desc
FROM sys.indexes AS i
LEFT JOIN sys.dm_db_index_usage_stats AS ds
    ON ds.database_id = DB_ID()
   AND ds.object_id = i.object_id
   AND ds.index_id = i.index_id
CROSS APPLY sys.dm_db_index_physical_stats
(
    DB_ID(), i.object_id, i.index_id, NULL, 'SAMPLED'
) AS ips
LEFT JOIN sys.partitions AS p
    ON p.object_id = i.object_id
   AND p.index_id = i.index_id
   AND p.partition_number = ips.partition_number
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')
)
AND i.name IS NOT NULL
AND ips.alloc_unit_type_desc = N'IN_ROW_DATA'
ORDER BY ips.avg_page_space_used_in_percent ASC, ips.avg_fragmentation_in_percent DESC;
GO

Check automatic tuning state and Query Store forced-plan failures

USE [PhotoCleanup];
GO

SELECT
    name,
    value,
    value_for_secondary
FROM sys.database_scoped_configurations
WHERE name = N'AUTOMATIC_TUNING';

SELECT
    qpf.query_id,
    qpf.plan_id,
    qpf.failure_count,
    qpf.last_force_failure_time,
    qpf.last_force_failure_reason_desc
FROM sys.query_store_plan AS qsp
INNER JOIN sys.query_store_query AS q
    ON q.query_id = qsp.query_id
INNER JOIN sys.query_store_plan_forcing_policy AS qpf
    ON qpf.plan_id = qsp.plan_id
WHERE qpf.failure_count > 0
ORDER BY qpf.last_force_failure_time DESC;
GO

Validation and rollback criteria

Three-step safe implementation playbook

1. During a maintenance window, create the three filtered indexes. Confirm creation succeeds and that log, tempdb, CPU, and I/O remain within limits.
2. Rebuild the four selected dbo.Files indexes online, then update dbo.Files statistics with FULLSCAN.
3. After a stable observation period, drop the five unused indexes and enable FORCE_LAST_GOOD_PLAN. Recheck Query Store and index usage counters.

Rollback criteria: stop or revert the change if synchronization duration increases by more than 20%, writes or log generation materially exceed the maintenance budget, blocking appears, or any critical query regresses by more than 20% at p95 for two consecutive observation intervals. If a new index is unused and adds measurable write latency, remove that specific index. If compression increases CPU materially without reducing I/O, rebuild the affected index using the prior compression setting.

• Confirm the filtered indexes are enabled and have nonzero seeks or scans for their target queries.
• Confirm page density improves substantially and fragmentation is reduced after rebuild.
• Compare Query Store duration, CPU, logical reads, writes, execution count, and plan count before and after.
• Verify synchronization INSERT and UPDATE statements still use the RootId, PathHash access path.
• Confirm no foreign key or check constraint becomes untrusted or disabled.
• Confirm automatic tuning reports FORCE_LAST_GOOD_PLAN as ON.
• Reassess dropped indexes only after a full representative workload period, since usage counters can reset.