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

Executive summary

• No forced plan failures were detected.
• No automatic tuning correction is required; FORCE_LAST_GOOD_PLAN is already enabled and aligned.
• No low page-density issue was found. All reported indexes are above 90% page density; no rebuild recommendation is justified from the provided density data.
• No index drops are recommended from the provided evidence.
• Columnstore is not recommended: no table exceeds 1,000,000 rows in the supplied data, and the workload is mixed analytical/reporting but not large enough here to justify additional columnstore maintenance overhead.

Detailed prioritized recommendations

Indexes to create

1. dbo.PostHistory

• Recommendation: Create (PostId, PostHistoryTypeId, CreationDate DESC) including (UserId, Comment, UserDisplayName).
• Why this shape: PostId is highly selective (~246,673 distinct), PostHistoryTypeId is low-cardinality and should be second, and CreationDate supports latest-history access.
• Workload evidence:
  • Missing index request: Equality Columns: [PostId], include [PostHistoryTypeId], average impact 99.60.
  • Plan cache / Query Store examples:
    • LEFT JOIN PostHistory ph ON p.Id = ph.PostId
    • LEFT JOIN PostHistory PH ON P.Id = PH.PostId with PH.PostHistoryTypeId IN (10, 11)
    • SUM(CASE WHEN ph.PostHistoryTypeId = 10 THEN 1 ELSE 0 END)
• Overhead assessment: Acceptable. Observed user_updates=0 in supplied usage stats and the table is read-heavy in the captured workload. Even allowing for DMV reset limitations, read benefit outweighs maintenance cost.

2. dbo.Comments

• Recommendation: Create (PostId, CreationDate DESC) including (UserId, Score).
• Why: PostId has strong cardinality (~122,541 distinct), and comment-count queries repeatedly join by post. CreationDate helps recent-comment ranking patterns.
• Workload evidence:
  • Index usage shows only clustered PK activity with 215 scans.
  • Examples:
    • LEFT JOIN Comments c ON p.Id = c.PostId
    • (SELECT COUNT(*) FROM Comments c WHERE c.PostId = p.Id)
    • COUNT(c.Id) AS CommentCount
• Overhead assessment: Low to moderate. Narrow key, moderate row count, and strong recurring join pattern justify it.

3. dbo.Badges

• Recommendation: Create (UserId, Class, Date DESC) including (Name).
• Why: Two missing-index recommendations both start with UserId. The composite cardinality of UserId, Class is strong; Class alone is not. Date DESC supports latest-badge lookups.
• Workload evidence:
  • Missing index request: [UserId] include [Class], impact 98.94.
  • Duplicate missing request on [UserId] alone indicates a reusable access path is needed.
  • Examples:
    • LEFT JOIN Badges b ON u.Id = b.UserId
    • SUM(CASE WHEN b.Class = 1 THEN 1 ELSE 0 END)
    • b.Date = ( SELECT MAX(Date) FROM Badges WHERE UserId = u.Id )
• Overhead assessment: Acceptable. Table is medium-sized (439,352 rows), scan-heavy (180 scans), and observed writes are zero in captured usage.

4. dbo.Votes filtered post-centric index

• Recommendation: Create filtered (PostId, VoteTypeId) including (UserId, BountyAmount, CreationDate) with filter VoteTypeId IN (2,3,8,9).
• Why: Most heavy queries count upvotes/downvotes and bounty amounts per post. PostId is highly selective (~242,634 distinct), and PostId, VoteTypeId has strong combined selectivity.
• Workload evidence:
  • Votes clustered PK has 348 scans and no nonclustered support.
  • Examples:
    • LEFT JOIN Votes v ON p.Id = v.PostId
    • WHERE v.VoteTypeId IN (2, 3)
    • AND v.VoteTypeId = 8
    • COUNT(*) FROM Votes v WHERE v.PostId = p.Id AND v.VoteTypeId = 2
• Overhead assessment: Better than a broad votes index because the filter limits storage and maintenance. This is preferred over a single unfiltered wide index.

5. dbo.Votes optional user-centric filtered index

• Recommendation: Create filtered (UserId, VoteTypeId) including (PostId, BountyAmount, CreationDate) where UserId IS NOT NULL AND VoteTypeId IN (2,3,8,9).
• Why: Several Query Store statements aggregate votes by user. This index is secondary because the provided density for UserId appears unexpectedly low-selectivity, so benefit may vary.
• Workload evidence:
  • Missing index request on dbo.Votes: [UserId] include [VoteTypeId], [BountyAmount].
  • Examples:
    • LEFT JOIN Votes v ON u.Id = v.UserId
    • SUM(CASE WHEN v.VoteTypeId = 2 THEN 1 ELSE 0 END)
• Overhead assessment: Medium. Add only if user-centric reports are recurring and important.

Query/code changes

1. Replace repeated correlated subqueries with pre-aggregation.
   • High-cost examples repeatedly use patterns like (SELECT COUNT(*) FROM Comments c WHERE c.PostId = p.Id) and (SELECT COUNT(*) FROM Votes v WHERE v.PostId = p.Id AND v.VoteTypeId = 2).
   • Rewrite these into grouped derived tables or temp tables keyed by PostId, then join once.
   • Expected effect: lower repeated scans, lower reads, lower spill risk.
2. Remove unnecessary joins to lookup tables for fixed vote-type predicates.
   • Example: JOIN VoteTypes vt ON v.VoteTypeId = vt.Id followed by checks for vt.Id = 2 or vt.Id = 3.
   • Use v.VoteTypeId directly unless lookup attributes are required.
   • Expected effect: simpler plans and lower join cost.
3. Materialize large intermediate rowsets when multiple CTE layers multiply rows.
   • Several Query Store statements chain many CTEs over Users, Posts, Votes, Comments, and Badges with ranking and aggregation.
   • Use temp tables after major aggregation points such as per-post vote totals or per-user post totals.
   • Expected effect: lower memory grants, fewer spills, better cardinality stability.
4. Avoid tag searches using LIKE '%' + TagName + '%' against Posts.Tags.
   • This pattern is non-SARGable and cannot be fixed effectively with a normal B-tree index.
   • Normalize tags into a bridge table such as PostTags(PostId, TagId), or use a search-specific design.
   • Expected effect: this is one of the biggest potential gains for the tag-related reporting queries.
5. Correct logically suspicious badge aggregations.
   • One plan-cache statement shows COUNT(b.Id) AS GoldBadges, COUNT(b.Id) AS SilverBadges, COUNT(b.Id) AS BronzeBadges while joining b.Class = 1. That returns identical values for all three columns.
   • Use separate conditional sums by class, or separate pre-aggregations.

Statistics, maintenance, and tuning settings

• Statistics: Recommend targeted FULLSCAN updates on Posts, Votes, Badges, PostHistory, and Comments after index creation. This is justified by multiple large memory grants and spills in Query Store.
• Auto stats settings: AUTO_CREATE_STATISTICS = ON and AUTO_UPDATE_STATISTICS = ON are appropriate. No change recommended.
• Async stats: AUTO_UPDATE_STATISTICS_ASYNC = OFF is acceptable here; no change recommended from supplied evidence.
• Page density / fragmentation: No rebuild or reorganize action recommended. Reported page density is healthy:
  • IX_Posts_OwnerUserId_Covering: 90.23%
  • All other listed indexes: 98%+
• Forced plan failures: None detected. No unforce action needed.
• Automatic tuning: FORCE_LAST_GOOD_PLAN is already ON and matches desired state; keep enabled.

Not recommended

• No index drops. The provided usage sample does not show enough evidence to safely remove any existing index. Several small-table clustered PK scans are normal and harmless.
• No primary key changes. There is no evidence to justify dropping and recreating PKs or touching related foreign keys.
• No filtered index on low-cardinality columns alone. Columns such as Badges.Class, Posts.PostTypeId, and Votes.VoteTypeId are poor standalone index keys.
• No columnstore index. Largest reported table counts remain below the stated 1,000,000-row threshold.
• No page-density-driven fill factor reduction for existing indexes. Current density is already high and does not indicate page-split pressure in supplied data.

Scripts

Create nonclustered index for PostHistory joins and close-event analysis (Recommendation 1)

USE [SQLStorm];
GO
CREATE NONCLUSTERED INDEX [IX_PostHistory_PostId_PostHistoryTypeId_CreationDate]
ON [dbo].[PostHistory]
(
    [PostId] ASC,
    [PostHistoryTypeId] ASC,
    [CreationDate] DESC
)
INCLUDE
(
    [UserId],
    [Comment],
    [UserDisplayName]
)
WITH
(
    ONLINE = ON,
    SORT_IN_TEMPDB = ON,
    DATA_COMPRESSION = PAGE,
    FILLFACTOR = 95
);
GO

Create nonclustered index for Comments by PostId and recency (Recommendation 2)

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

Create composite Badges index for UserId, Class, and latest badge date (Recommendation 3)

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

Create filtered Votes index for post-centric vote and bounty aggregations (Recommendation 4)

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

Create optional filtered Votes index for user-centric vote aggregations (Recommendation 5)

USE [SQLStorm];
GO
CREATE NONCLUSTERED INDEX [IX_Votes_UserId_VoteTypeId_Filtered]
ON [dbo].[Votes]
(
    [UserId] ASC,
    [VoteTypeId] ASC
)
INCLUDE
(
    [PostId],
    [BountyAmount],
    [CreationDate]
)
WHERE [UserId] IS NOT NULL
  AND [VoteTypeId] IN (2, 3, 8, 9)
WITH
(
    ONLINE = ON,
    SORT_IN_TEMPDB = ON,
    DATA_COMPRESSION = PAGE,
    FILLFACTOR = 95
);
GO

Refresh statistics on major workload tables after index creation (Recommendation 6)

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