Index Tuning Report – RockyPC / SQLStorm – 2026-05-25

Executive Summary

1. Create a nonclustered index on dbo.PostHistory(PostId, CreationDate) including PostHistoryTypeId. This is the best single index change because dbo.PostHistory is large (847,593 rows, 820.3 MB), has 338 clustered scans, and Query Store contains multiple queries joining and aggregating by PostId, often with recent-date filters.
2. Create a filtered or narrow analytical index on dbo.Votes for (PostId, VoteTypeId). This supports the most common vote aggregation pattern in Query Store and addresses 804 clustered scans on dbo.Votes.
3. Create dbo.Comments(PostId, CreationDate). Comment counting and recent-comment rollups appear repeatedly in long-running statements; current access is scan-heavy with 549 clustered scans on a 351,440-row table.
4. Create dbo.Badges(UserId) including Class, Name, and Date. This consolidates multiple missing index requests into one practical index and supports badge aggregation by user.
5. Apply targeted stats refresh after index deployment. The workload shows suspiciously poor estimates on some columns, especially Votes.UserId; refreshing statistics after creating the new indexes is low risk and useful.
6. Do not spend effort on rebuild/reorganize right now. Page density is excellent across sampled large indexes (98%–99.85%) and fragmentation is negligible.
7. Primary performance limit is also query shape, not only indexing. Many top queries use non-sargable tag searches such as Posts.Tags LIKE '%' + TagName + '%' and wide fanout CTEs with COUNT(DISTINCT) across several large joins. Indexes will help, but code patterns remain the bigger ceiling.

Environment & Scope

• SQL Server 2022 Enterprise-capable platform features are available, including online index operations, page compression, filtered indexes, and columnstore.
• No recommendation is made for system databases.
• No table exceeds 1,000,000 rows in the provided data, so clustered or nonclustered columnstore is not recommended in this run. The two closest tables, dbo.Votes (926,084 rows) and dbo.PostHistory (847,593 rows), remain better served by targeted rowstore indexes for the observed workload.
• Database statistics settings are healthy: Auto Create Stats = ON, Auto Update Stats = ON, Async OFF.
• Observed user_updates = 0 for the listed indexes indicates a read-mostly or static workload snapshot, reducing downside from adding selective nonclustered indexes.

Workload Findings

1. Scan-heavy access dominates the hot tables

2. Query Store patterns match the missing-index themes

Several long-running Query Store statements repeatedly join:

• Users to Posts on OwnerUserId
• Posts to Votes on PostId
• Posts to Comments on PostId
• Users to Badges on UserId
• Posts to PostHistory on PostId

Representative statements that should benefit directly from the recommended indexes include:

• ... LEFT JOIN Votes v ON p.Id = v.PostId ... SUM(CASE WHEN v.VoteTypeId = 2 THEN 1 ELSE 0 END) ...
• ... LEFT JOIN Comments c ON p.Id = c.PostId ... COUNT(c.Id) ...
• ... LEFT JOIN Badges b ON u.Id = b.UserId ... SUM(CASE WHEN b.Class = 1 THEN 1 ELSE 0 END) ...
• ... FROM PostHistory ph WHERE ph.CreationDate > DATEADD(YEAR,-1,...) GROUP BY ph.PostId ...

3. Page density and fragmentation are already excellent

No sampled index shows low page density or problematic fragmentation. Current page density ranges from 98.29% to 99.85%, so there is no evidence that page splits or fill factor issues are a current priority.

4. Query shape is a major contributor

The most expensive queries are not classic OLTP lookups. They are wide analytical CTEs with multiple LEFT JOINs, COUNT(DISTINCT), window functions, and non-sargable predicates like Posts.Tags LIKE '%' + TagName + '%'. Indexes will reduce I/O, but these queries still need code changes for material improvement.

Detailed Prioritized Recommendations

1. P1Create dbo.PostHistory(PostId, CreationDate) INCLUDE (PostHistoryTypeId)

   • Why: PostHistory is one of the largest and most scan-heavy tables in the database. Missing index telemetry requests PostId INCLUDE PostHistoryTypeId, and Query Store shows repeated usage where history is joined by PostId and often filtered by recent CreationDate.
   • Cardinality: PostId has estimated 246,673 distinct values out of 847,593 rows, which is strong.
   • Why this shape: Adding CreationDate to the key broadens usefulness beyond the exact missing-index suggestion and better supports recent-history queries without significantly enlarging the index.
   • Overhead: Low risk in this dataset because observed updates are zero.
   • Expected benefit: Lower logical reads and better join strategy selection for history rollups, close-reason analysis, and per-post history counts.
   • Confidence: 0.92

2. P1Create filtered dbo.Votes(PostId, VoteTypeId) INCLUDE (BountyAmount) for common analytical vote types

   • Why: The hottest analytical pattern on Votes is aggregation by PostId with conditional logic on VoteTypeId for upvotes, downvotes, and bounty-related types.
   • Cardinality: PostId has 242,634 distinct values; PostId, VoteTypeId has 347,249 distinct combinations. This is much more selective than UserId alone.
   • Workload examples: Statements repeatedly contain logic like SUM(CASE WHEN v.VoteTypeId = 2 THEN 1 ELSE 0 END), SUM(CASE WHEN v.VoteTypeId IN (8,9) THEN v.BountyAmount ELSE 0 END), and LEFT JOIN Votes v ON p.Id = v.PostId.
   • Why filtered: Restricting to VoteTypeId IN (2,3,8,9) keeps the index smaller while matching the dominant workload patterns seen in Query Store.
   • Overhead: Very acceptable given zero observed updates.
   • Expected benefit: Faster vote aggregation, reduced scans, smaller memory grants, and lower spill risk in wide CTE queries.
   • Confidence: 0.90

3. P1Create dbo.Comments(PostId, CreationDate)

   • Why: Comments has 549 clustered scans on 351,440 rows, and the missing index report requests PostId. Query Store frequently counts comments per post and sometimes limits to recent periods.
   • Cardinality: PostId has 122,541 distinct values; PostId, CreationDate is nearly unique, which is excellent for ordered post-comment access.
   • Why this shape: The composite key supports both equality joins and ordered or bounded date access without needing a separate date-driven index.
   • Expected benefit: Faster post/comment fanout joins and reduced cost for comment count/windowing operations.
   • Confidence: 0.88

4. P2Create dbo.Badges(UserId) INCLUDE (Class, Name, Date)

   • Why: Three separate missing-index signals point to UserId with Class and Name as included columns. Query Store frequently summarizes badges per user and by badge class.
   • Cardinality: UserId has 154,016 distinct values over 439,352 rows, which is good.
   • Why include Date: It is highly selective and commonly useful for time-based badge analysis, while still keeping the index relatively narrow.
   • Expected benefit: Better support for joins from Users to Badges and badge count/category summaries.
   • Confidence: 0.87

5. P3Optional: create filtered dbo.Votes(UserId) INCLUDE (VoteTypeId, BountyAmount, PostId) where UserId IS NOT NULL

   • Why optional: The missing index report requests UserId, and some Query Store statements aggregate user-level votes and bounties directly from Votes.
   • Caution: Column statistics show only about 929 distinct values on Votes.UserId, implying low selectivity and/or heavy null skew. That weakens the case for a broad unfiltered index.
   • Why filtered: Limiting to non-null UserId reduces size and targets the actual join predicate.
   • Priority note: Implement only after verifying the PostId-led index is insufficient for the user-centric queries.
   • Confidence: 0.61

6. P3Refresh statistics on affected large tables after index deployment

   • Why: Auto-stats are enabled, but a post-change targeted refresh is useful here because the workload is analytical and estimate-sensitive. It also helps validate the newly created access paths immediately.
   • Targets: Votes, Comments, PostHistory, Badges, and optionally Users.
   • Expected benefit: Better cardinality estimates and more consistent plan selection.
   • Confidence: 0.84

7. P4Code-level tuning recommendation: reduce fanout and non-sargable tag matching

   • Why: Many top Query Store statements spend extreme CPU and read volume on patterns that no normal B-tree index can fully solve, especially Posts.Tags LIKE '%' + TagName + '%' and multi-branch CTEs that join Users, Posts, Votes, Comments, and Badges before aggregating.
   • Recommended code changes:
     • Pre-aggregate Votes, Comments, Badges, and PostHistory in separate grouped subqueries before joining to Users or Posts.
     • Replace repeated COUNT(DISTINCT ...) across fanout joins with staged aggregates.
     • Normalize tags into a junction table rather than parsing or wildcard-searching the delimited Posts.Tags string.
   • Confidence: 0.89

Deferred / Not Recommended

DeferredUsers(Reputation) index

• The missing index report shows high average cost but only 8.10% average impact and just 21 seeks/scans.
• Reputation has 2,443 distinct values across 267,193 rows, which is moderate but not strong enough by itself for the broad range predicates seen in the workload.
• Most Query Store filters such as U.Reputation > 100 or > 1000 are likely to qualify a sizable portion of the table, so the benefit is uncertain.
• Recommendation: Do not add this index in the first tuning wave.

Not recommendedIndex rebuild/reorganize work

• Fragmentation is between 0.00% and 0.33% on sampled large indexes.
• Page density is extremely high.
• No evidence supports maintenance-driven rebuilds for performance improvement right now.

Not recommendedDropping or recreating primary keys

• No recommendation here requires PK redefinition.
• This avoids unnecessary foreign key churn and operational risk.

Not recommendedColumnstore for this run

• The provided large-table section reports no tables above the >1,000,000-row threshold used for this tuning goal.
• Given the mixed join/filter patterns and row counts, targeted rowstore indexes are the safer, clearer first step.

Query Store Forced Plan Failures

No forced plan failures were detected.

• No affected query_id / plan_id pairs were provided or observed.
• No immediate forced-plan remediation is required.
• No evidence in this dataset suggests a forced-plan bug condition or a need to manually unforce plans.

Automatic Tuning

• FORCE_LAST_GOOD_PLAN: Desired state = ON, Actual state = ON.
• Assessment: Correctly configured. No action required.
• Operational note: Because Automatic Plan Correction is already enabled and no forced-plan failures are present, the safe action is to leave this setting in place while applying index changes.

Visual Aids

Recommended physical design changes by table

Table          Current State                         Recommended Change
-------------  ------------------------------------  ----------------------------------------------
PostHistory    Clustered PK only, 338 scans          + NCI (PostId, CreationDate) INCLUDE (PostHistoryTypeId)
Votes          Clustered PK only, 804 scans          + Filtered NCI (PostId, VoteTypeId) INCLUDE (BountyAmount)
Votes          User-centric vote rollups             + Optional filtered NCI (UserId) INCLUDE (VoteTypeId, BountyAmount, PostId)
Comments       Clustered PK only, 549 scans          + NCI (PostId, CreationDate)
Badges         Clustered PK only, 455 scans          + NCI (UserId) INCLUDE (Class, Name, Date)
Users          Existing clustered PK heavily used    No new index in first wave

Scripts

Create nonclustered index on dbo.PostHistory for post and recent-history access (Recommendation P1)

USE [SQLStorm];
GO
CREATE NONCLUSTERED INDEX [IX_PostHistory_PostId_CreationDate]
ON [dbo].[PostHistory] ([PostId] ASC, [CreationDate] ASC)
INCLUDE ([PostHistoryTypeId])
WITH (
    ONLINE = ON,
    DATA_COMPRESSION = PAGE,
    SORT_IN_TEMPDB = ON,
    FILLFACTOR = 100
);
GO

Create filtered nonclustered index on dbo.Votes for common per-post analytical vote types (Recommendation P1)

USE [SQLStorm];
GO
CREATE NONCLUSTERED INDEX [IX_Votes_PostId_VoteTypeId_Analytics]
ON [dbo].[Votes] ([PostId] ASC, [VoteTypeId] ASC)
INCLUDE ([BountyAmount])
WHERE [VoteTypeId] IN (2, 3, 8, 9)
WITH (
    ONLINE = ON,
    DATA_COMPRESSION = PAGE,
    SORT_IN_TEMPDB = ON,
    FILLFACTOR = 100
);
GO

Create nonclustered index on dbo.Comments for post comment counting and recent comment access (Recommendation P1)

USE [SQLStorm];
GO
CREATE NONCLUSTERED INDEX [IX_Comments_PostId_CreationDate]
ON [dbo].[Comments] ([PostId] ASC, [CreationDate] ASC)
WITH (
    ONLINE = ON,
    DATA_COMPRESSION = PAGE,
    SORT_IN_TEMPDB = ON,
    FILLFACTOR = 100
);
GO

Create nonclustered index on dbo.Badges for user badge aggregation and lookup coverage (Recommendation P2)

USE [SQLStorm];
GO
CREATE NONCLUSTERED INDEX [IX_Badges_UserId]
ON [dbo].[Badges] ([UserId] ASC)
INCLUDE ([Class], [Name], [Date])
WITH (
    ONLINE = ON,
    DATA_COMPRESSION = PAGE,
    SORT_IN_TEMPDB = ON,
    FILLFACTOR = 100
);
GO

Create optional filtered nonclustered index on dbo.Votes for user-centric vote and bounty rollups (Recommendation P3)

USE [SQLStorm];
GO
CREATE NONCLUSTERED INDEX [IX_Votes_UserId_NotNull]
ON [dbo].[Votes] ([UserId] ASC)
INCLUDE ([VoteTypeId], [BountyAmount], [PostId])
WHERE [UserId] IS NOT NULL
WITH (
    ONLINE = ON,
    DATA_COMPRESSION = PAGE,
    SORT_IN_TEMPDB = ON,
    FILLFACTOR = 100
);
GO

Refresh statistics on affected tables after index deployment (Recommendation P3)

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

Validation query to measure usage of newly created indexes after deployment

USE [SQLStorm];
GO
SELECT
    OBJECT_SCHEMA_NAME(i.[object_id], DB_ID()) AS [schema_name],
    OBJECT_NAME(i.[object_id], DB_ID()) AS [table_name],
    i.[name] AS [index_name],
    ius.user_seeks,
    ius.user_scans,
    ius.user_lookups,
    ius.user_updates,
    ius.last_user_seek,
    ius.last_user_scan,
    ius.last_user_update
FROM sys.indexes AS i
LEFT JOIN sys.dm_db_index_usage_stats AS ius
    ON i.[object_id] = ius.[object_id]
   AND i.[index_id] = ius.[index_id]
   AND ius.[database_id] = DB_ID()
WHERE i.[name] IN (
    N'IX_PostHistory_PostId_CreationDate',
    N'IX_Votes_PostId_VoteTypeId_Analytics',
    N'IX_Comments_PostId_CreationDate',
    N'IX_Badges_UserId',
    N'IX_Votes_UserId_NotNull'
)
ORDER BY [table_name], [index_name];
GO

Rollback script to remove only the recommended nonclustered indexes if needed

USE [SQLStorm];
GO
IF EXISTS (SELECT 1 FROM sys.indexes WHERE [name] = N'IX_PostHistory_PostId_CreationDate' AND [object_id] = OBJECT_ID(N'dbo.PostHistory'))
    DROP INDEX [IX_PostHistory_PostId_CreationDate] ON [dbo].[PostHistory];
IF EXISTS (SELECT 1 FROM sys.indexes WHERE [name] = N'IX_Votes_PostId_VoteTypeId_Analytics' AND [object_id] = OBJECT_ID(N'dbo.Votes'))
    DROP INDEX [IX_Votes_PostId_VoteTypeId_Analytics] ON [dbo].[Votes];
IF EXISTS (SELECT 1 FROM sys.indexes WHERE [name] = N'IX_Comments_PostId_CreationDate' AND [object_id] = OBJECT_ID(N'dbo.Comments'))
    DROP INDEX [IX_Comments_PostId_CreationDate] ON [dbo].[Comments];
IF EXISTS (SELECT 1 FROM sys.indexes WHERE [name] = N'IX_Badges_UserId' AND [object_id] = OBJECT_ID(N'dbo.Badges'))
    DROP INDEX [IX_Badges_UserId] ON [dbo].[Badges];
IF EXISTS (SELECT 1 FROM sys.indexes WHERE [name] = N'IX_Votes_UserId_NotNull' AND [object_id] = OBJECT_ID(N'dbo.Votes'))
    DROP INDEX [IX_Votes_UserId_NotNull] ON [dbo].[Votes];
GO