Index Tuning Report – RockyPC / SQLStorm – 2026-06-14

Executive summary

1. Create a filtered nonclustered index on dbo.PostHistory(PostId, CreationDate) for PostHistoryTypeId = 10. This is the strongest index opportunity because multiple expensive Query Store statements join posts to close-history rows using PH.PostHistoryTypeId = 10 and PH.PostId = P.Id. A filtered design keeps storage modest while targeting a highly repeated access pattern. Confidence: High.
2. Broaden the existing IX_Votes_Bounty8_PostId to also include VoteTypeId. The overlap signal shows a perfect key match on PostId, and many slow statements aggregate votes by post while branching on vote type. Reusing the existing index name avoids adding another nearly redundant structure. Confidence: High.
3. Re-trust all untrusted foreign keys. This is a broad optimizer-quality fix. Untrusted foreign keys prevent join elimination and reduce cardinality quality in a schema that is heavily join-driven. Because the workload is largely analytical/read-heavy and current write pressure is effectively zero in the sample, the validation scan cost is usually worth it. Confidence: High.

• No missing-index DMV rows were present, so recommendations are driven primarily by Query Store workload, overlap signals, cardinality, and existing index layout.
• No duplicate or left-prefix overlapping indexes were detected; no index drops are recommended.
• Page density and fragmentation are already excellent; no rebuild or fill factor changes are justified.
• No forced plan failures were detected.
• FORCE_LAST_GOOD_PLAN is already enabled and correctly aligned with desired state.
• Columnstore is not recommended at this time: no table exceeds the stated 1,000,000-row threshold, and the workload is mixed rather than clearly warehouse-only.

Environment

Visual overview

Diagram: Most costly statements center on Posts with repeated joins to Votes and PostHistory; the best physical design gains are concentrated there, while foreign-key trust improves optimizer behavior across the whole schema.

Detailed prioritized recommendations

1) Add a filtered index for close-history lookups on dbo.PostHistory

• Recommendation: Create IX_PostHistory_PostId_CreationDate_CloseType10 on (PostId, CreationDate) including (Comment) with filter WHERE PostHistoryTypeId = 10.
• Why: Query text patterns repeatedly use JOIN PostHistory PH ON P.Id = PH.PostId AND PH.PostHistoryTypeId = 10, often also returning PH.CreationDate and PH.Comment.
• Evidence: Overlap signals repeatedly point to missing access paths on PostHistory(PostId), PostHistory(PostId, CreationDate), and PostHistoryTypeId-based lookups on a table with 847,593 rows and a clustered-only design.
• Benefit: Reduces full clustered scans of an 820 MB table for close-event workloads; improves joins, date retrieval, and close reason retrieval with much lower storage than a broad unfiltered index.
• Risk: Low in this sample because write activity is zero; medium operationally due to index build cost.
• Expected beneficiaries:
  • Query Hash FE52206CED094538: JOIN PostHistory PH ON P.Id = PH.PostId AND PH.PostHistoryTypeId = 10
  • Query Hash 2FB54B903DE9F146 and other post-ranking queries that pull recent post metadata and correlated activity
  • Queries using close-history or moderation timelines from PostHistory
• Confidence: High.

2) Modify the existing dbo.Votes post-based index to cover vote-type aggregations

• Recommendation: Rebuild IX_Votes_Bounty8_PostId with the same key (PostId) but include (VoteTypeId, BountyAmount).
• Why: The overlap signal shows a perfect key match for a candidate index on [PostId] including [VoteTypeId] with redundancy score 100. Instead of adding another near-duplicate index, extend the existing one.
• Evidence: Slow statements repeatedly aggregate post votes using expressions like SUM(CASE WHEN v.VoteTypeId = 2 THEN 1 ELSE 0 END) and SUM(CASE WHEN v.VoteTypeId = 3 THEN 1 ELSE 0 END).
• Benefit: Better support for post-level vote counts, less clustered scanning on Votes, and consolidation into the existing index footprint.
• Risk: Low. The table has 926,084 rows, but observed write penalty is zero and the existing index already exists.
• Expected beneficiaries:
  • Query Hash 558D53CC7558B81F: aggregates votes per PostId for up/down totals
  • Query Hash EE4BAB640222BBF0: repeated correlated counts on Votes by PostId and VoteTypeId
  • Query Hash 6211E6BFDACEAE1E, 779F0850AFC28347, CE7337517B419CA4: all show high logical reads around post vote summaries
• Confidence: High.

3) Re-trust all untrusted foreign keys

• Recommendation: Validate and re-trust all 18 untrusted foreign keys using ALTER TABLE ... WITH CHECK CHECK CONSTRAINT ....
• Why: Untrusted constraints block important optimizer transformations such as join elimination and can degrade cardinality and plan quality across a schema dominated by joins.
• Evidence: Untrusted FKs exist on Badges, Comments, PostHistory, PostLinks, Posts, Tags, and Votes.
• Benefit: Better plan quality without adding new indexes everywhere; especially useful in queries that join dimensions/reference tables such as VoteTypes, PostTypes, and self-referencing Posts.
• Risk: Medium because validation scans can be expensive on larger tables and will fail if orphaned rows exist.
• Validation note: Execute during a maintenance window. If any statement fails, data cleanup is required before the constraint can become trusted.
• Confidence: High.

4) Apply focused query rewrites before adding broader indexes on Posts, Comments, or Badges

• Recommendation: Prefer code changes over more wide indexes for the current top queries.
• Why: Many expensive statements are dominated by:
  • large CTE stacks,
  • row multiplication from joining Posts to Votes, Comments, and Badges before aggregation,
  • correlated subqueries for counts,
  • window functions over large intermediate sets,
  • non-sargable tag matching using p.Tags LIKE '%' + t.TagName + '%'.
• Code changes with highest value:
  • Pre-aggregate Votes by PostId once, then join the small aggregate result to Posts.
  • Pre-aggregate Comments by PostId once, instead of repeated correlated SELECT COUNT(*) subqueries.
  • Use vote type IDs directly where known instead of joining VoteTypes only to test IDs 2 and 3.
  • Replace LIKE '%' + t.TagName + '%' with normalized tag mapping, parsed tags table, or full-text/search-specific design; a regular b-tree index cannot rescue leading-wildcard searches.
• Examples from workload:
  • Query Hash 872C7773968C6AAF: tag matching via LIKE '%'+TagName+'%' is the major problem, not a missing b-tree.
  • Query Hash EE4BAB640222BBF0 and 6211E6BFDACEAE1E: repeated correlated vote/comment counts create multi-million read patterns.
  • Query Hash 57CAE2375FFE5085, B56CC620F24BC24F, 83371E96D4DD244A: joins and COUNT(DISTINCT) over exploded rowsets likely dominate cost.
• Confidence: High.

5) Refresh statistics after structural changes

• Recommendation: Update statistics on Posts, Votes, PostHistory, Comments, and Badges after index changes and FK re-validation.
• Why: Query patterns are aggregation-heavy; better histograms help memory grants and join order choices. The workload shows very large grants and, in one case, spills.
• Evidence: Query Hash 558D53CC7558B81F had Max Spills = 26,525 and a 165,768 KB grant.
• Risk: Low to medium depending on scan depth; acceptable in maintenance windows.
• Confidence: Medium-High.

Workload evidence

The top Query Store durations are dominated by analytical query shape and join explosion. Indexing helps on Votes and PostHistory, but code patterns remain the main source of extreme runtime.

Changes not recommended

• No index drops: no duplicates or left-prefix overlaps were reported.
• No fill factor changes: page density is already 98% to 99.8%, fragmentation is negligible, and there are no page split or latch hotspot signals.
• No columnstore indexes now: no table exceeds the requested 1,000,000-row threshold, and the workload still depends heavily on row-by-row joins and point relationships.
• No broad new index on Posts yet: current expensive queries are too varied; adding a very wide covering index would risk unnecessary storage bloat without enough proof.
• No low-cardinality-only indexes: columns such as PostTypeId, VoteTypeId, and Class are not recommended as standalone keys because selectivity is poor unless paired with a more selective leading key or a filter.

Query Store forced-plan review

No forced plan failures were detected.

• Affected query_id / plan_id list: none reported.
• Failure classes: none reported.
• Immediate action: none required.
• Follow-up: continue monitoring Query Store after index and constraint changes to confirm plan stabilization and lower logical reads.

Automatic tuning review

Scripts

Recommendation 1: Create filtered index to accelerate close-history lookups on dbo.PostHistory

USE [SQLStorm];
GO
CREATE NONCLUSTERED INDEX [IX_PostHistory_PostId_CreationDate_CloseType10]
ON [dbo].[PostHistory] ([PostId] ASC, [CreationDate] ASC)
INCLUDE ([Comment])
WHERE [PostHistoryTypeId] = 10
WITH (
    ONLINE = ON,
    DATA_COMPRESSION = PAGE,
    SORT_IN_TEMPDB = ON
);
GO

Recommendation 2: Rebuild the existing dbo.Votes PostId index to include VoteTypeId and improve post vote aggregations

USE [SQLStorm];
GO
CREATE NONCLUSTERED INDEX [IX_Votes_Bounty8_PostId]
ON [dbo].[Votes] ([PostId] ASC)
INCLUDE ([VoteTypeId], [BountyAmount])
WITH (
    DROP_EXISTING = ON,
    ONLINE = ON,
    DATA_COMPRESSION = PAGE,
    SORT_IN_TEMPDB = ON
);
GO

Recommendation 3: Re-trust all currently untrusted foreign keys so the optimizer can use them fully

USE [SQLStorm];
GO
ALTER TABLE [dbo].[Badges]      WITH CHECK CHECK CONSTRAINT [FK__Badges__UserId__6477ECF3];
ALTER TABLE [dbo].[Comments]    WITH CHECK CHECK CONSTRAINT [FK__Comments__PostId__4CA06362];
ALTER TABLE [dbo].[Comments]    WITH CHECK CHECK CONSTRAINT [FK__Comments__UserId__4D94879B];
ALTER TABLE [dbo].[PostHistory] WITH CHECK CHECK CONSTRAINT [FK__PostHisto__PostH__5070F446];
ALTER TABLE [dbo].[PostHistory] WITH CHECK CHECK CONSTRAINT [FK__PostHisto__PostI__5165187F];
ALTER TABLE [dbo].[PostHistory] WITH CHECK CHECK CONSTRAINT [FK__PostHisto__UserI__52593CB8];
ALTER TABLE [dbo].[PostLinks]   WITH CHECK CHECK CONSTRAINT [FK__PostLinks__LinkT__571DF1D5];
ALTER TABLE [dbo].[PostLinks]   WITH CHECK CHECK CONSTRAINT [FK__PostLinks__PostI__5535A963];
ALTER TABLE [dbo].[PostLinks]   WITH CHECK CHECK CONSTRAINT [FK__PostLinks__Relat__5629CD9C];
ALTER TABLE [dbo].[Posts]       WITH CHECK CHECK CONSTRAINT [FK__Posts__AcceptedA__48CFD27E];
ALTER TABLE [dbo].[Posts]       WITH CHECK CHECK CONSTRAINT [FK__Posts__LastEdito__47DBAE45];
ALTER TABLE [dbo].[Posts]       WITH CHECK CHECK CONSTRAINT [FK__Posts__OwnerUser__46E78A0C];
ALTER TABLE [dbo].[Posts]       WITH CHECK CHECK CONSTRAINT [FK__Posts__ParentId__49C3F6B7];
ALTER TABLE [dbo].[Posts]       WITH CHECK CHECK CONSTRAINT [FK__Posts__PostTypeI__45F365D3];
ALTER TABLE [dbo].[Tags]        WITH CHECK CHECK CONSTRAINT [FK__Tags__ExcerptPos__59FA5E80];
ALTER TABLE [dbo].[Tags]        WITH CHECK CHECK CONSTRAINT [FK__Tags__WikiPostId__5AEE82B9];
ALTER TABLE [dbo].[Votes]       WITH CHECK CHECK CONSTRAINT [FK__Votes__UserId__5EBF139D];
ALTER TABLE [dbo].[Votes]       WITH CHECK CHECK CONSTRAINT [FK__Votes__VoteTypeI__5DCAEF64];
GO

Recommendation 4: Refresh statistics on the primary workload tables after index and constraint changes

USE [SQLStorm];
GO
UPDATE STATISTICS [dbo].[Posts]       WITH FULLSCAN;
UPDATE STATISTICS [dbo].[Votes]       WITH FULLSCAN;
UPDATE STATISTICS [dbo].[PostHistory] WITH FULLSCAN;
UPDATE STATISTICS [dbo].[Comments]    WITH FULLSCAN;
UPDATE STATISTICS [dbo].[Badges]      WITH FULLSCAN;
GO