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

Executive summary

The workload is dominated by long-running analytical-style queries that repeatedly join and aggregate on Posts.OwnerUserId, Votes.PostId, Votes.UserId, Badges.UserId, Comments.PostId, and PostHistory.PostId. Existing access paths are almost entirely clustered PK scans on Id.

Because index usage shows essentially no update pressure in the captured period and page density is already excellent across current indexes, the best return is to add a small set of targeted nonclustered indexes rather than rebuild existing ones. Columnstore is not recommended at this time because no table in scope exceeds the stated 1,000,000-row threshold, and the heaviest pain points are still better addressed first with selective rowstore indexes and query rewrites.

Top priorities

1. Index vote aggregation paths first. The most repeated expensive joins are Posts.Id = Votes.PostId and Users.Id = Votes.UserId, often with aggregation by VoteTypeId and occasional use of BountyAmount. The clustered PK on Votes(Id) is not aligned to the workload.
2. Index PostHistory for per-post event lookups. PostHistory is the largest table in scope by size (820.3 MB), and the current workload repeatedly scans it for close/open history and latest history rows by post.
3. Consolidate the three Badge missing-index requests into one index. All missing-index suggestions on Badges share the same leading key UserId; one properly covered index is enough.
4. Support Comments by PostId and Posts by OwnerUserId. These are common join and grouping columns in plan cache and Query Store, but the current design forces repeated scans.
5. Prefer code fixes for tag searches. The most expensive queries also use Posts.Tags LIKE '%' + TagName + '%', which no normal b-tree index can optimize. Indexes help surrounding joins, but the tag-search pattern itself needs redesign.

Detailed prioritized recommendations

1. Create a covering index on dbo.Votes for post-based vote aggregation

2. Create a covering index on dbo.Votes for user-based vote access

3. Create a composite index on dbo.PostHistory for per-post history retrieval

4. Consolidate Badge recommendations into one nonclustered index

5. Create a nonclustered index on dbo.Comments for post joins and comment counts

6. Create a nonclustered index on dbo.Posts for owner-based grouping and recent-post ranking

7. Add a lower-priority index on dbo.Users for reputation-driven filtering and ordering

8. Refresh statistics on the main join tables after index deployment

Query patterns driving recommendations

The biggest end-to-end savings should come from reducing repeated full/large-range scans before aggregation rather than from minor operator tuning.

Operational findings

• No page-density issue found. All reported indexes have excellent average page density (98% to 99.85%) and negligible fragmentation.
• No index rebuild recommendation. There is no evidence of low page utilization below the ~75% threshold, nor operational hotspots such as latch or lock waits on indexes in scope.
• No drop recommendations. Only clustered PKs are present in scope for major tables; there are no redundant nonclustered indexes to remove.
• No heap issue. No large heap tables detected.
• Version note. This instance is SQL Server 2022 RTM-GDR. While not an immediate index action, later CUs often contain optimizer and Query Store fixes. A controlled patch review is advisable after index work is validated.

Automatic tuning

• FORCE_LAST_GOOD_PLAN: Desired state ON, actual state ON.
• Recommendation: No change required. Current configuration is correct and aligns with safe defaults.

Forced plan failures

No forced plan failures detected.

No remediation scripts are required for Query Store forced-plan issues in this run.

Code changes

1. Normalize tag searching. The repeated pattern Posts.Tags LIKE '%' + TagName + '%' is non-SARGable and forces scans. Replace the denormalized tag string search with a junction table such as PostTags(PostId, TagId) and index it on (TagId, PostId) and optionally (PostId, TagId).
2. Pre-aggregate before joining where possible. Several top-cost queries join wide tables first and aggregate later. Prefer patterns like SELECT PostId, COUNT(*) ... GROUP BY PostId in a small derived set, then join back to Posts or Users.
3. Avoid unnecessary COUNT(DISTINCT ...) after fanout joins. Many queries combine Users, Posts, Votes, Comments, and Badges in one layer, which multiplies row counts and inflates memory grants. Aggregate each child table separately by its join key first.
4. Review the spilling query. Query hash 558D53CC7558B81F spilled 38,164 pages. Indexing will help, but the plan shape also suggests row explosion from joining aggregated and non-aggregated sets together.

Deferred / not recommended

• No clustered index or primary key changes. There is no evidence justifying PK replacement, so no FK drop/recreate work is recommended.
• No columnstore recommendation. Largest table in scope is PostHistory at 847,593 rows, below the stated 1,000,000-row threshold.
• No filtered index on low-cardinality Badges.Class alone. Too low-cardinality to be a useful leading key.
• No maintenance-only rebuild scripts. Fragmentation and density do not justify them.
• No system database actions. None recommended.

Scripts

Create Recommendation 1: Votes index for post-based vote aggregation

USE [SQLStorm];
GO
IF NOT EXISTS
(
    SELECT 1
    FROM sys.indexes
    WHERE name = N'IX_Votes_PostId_VoteTypeId'
      AND object_id = OBJECT_ID(N'dbo.Votes')
)
BEGIN
    CREATE NONCLUSTERED INDEX [IX_Votes_PostId_VoteTypeId]
    ON [dbo].[Votes] ([PostId] ASC, [VoteTypeId] ASC)
    INCLUDE ([UserId], [BountyAmount], [CreationDate])
    WITH
    (
        DATA_COMPRESSION = PAGE,
        SORT_IN_TEMPDB = ON,
        ONLINE = ON,
        FILLFACTOR = 100
    );
END
GO

Create Recommendation 2: Votes index for user-based vote access

USE [SQLStorm];
GO
IF NOT EXISTS
(
    SELECT 1
    FROM sys.indexes
    WHERE name = N'IX_Votes_UserId'
      AND object_id = OBJECT_ID(N'dbo.Votes')
)
BEGIN
    CREATE NONCLUSTERED INDEX [IX_Votes_UserId]
    ON [dbo].[Votes] ([UserId] ASC)
    INCLUDE ([VoteTypeId], [PostId], [BountyAmount], [CreationDate])
    WITH
    (
        DATA_COMPRESSION = PAGE,
        SORT_IN_TEMPDB = ON,
        ONLINE = ON,
        FILLFACTOR = 100
    );
END
GO

Create Recommendation 3: PostHistory index for per-post history and latest-history lookups

USE [SQLStorm];
GO
IF NOT EXISTS
(
    SELECT 1
    FROM sys.indexes
    WHERE name = N'IX_PostHistory_PostId_Type_CreateDate'
      AND object_id = OBJECT_ID(N'dbo.PostHistory')
)
BEGIN
    CREATE NONCLUSTERED INDEX [IX_PostHistory_PostId_Type_CreateDate]
    ON [dbo].[PostHistory] ([PostId] ASC, [PostHistoryTypeId] ASC, [CreationDate] DESC)
    INCLUDE ([UserId], [Comment])
    WITH
    (
        DATA_COMPRESSION = PAGE,
        SORT_IN_TEMPDB = ON,
        ONLINE = ON,
        FILLFACTOR = 100
    );
END
GO

Create Recommendation 4: Consolidated Badges index covering UserId access patterns

USE [SQLStorm];
GO
IF NOT EXISTS
(
    SELECT 1
    FROM sys.indexes
    WHERE name = N'IX_Badges_UserId'
      AND object_id = OBJECT_ID(N'dbo.Badges')
)
BEGIN
    CREATE NONCLUSTERED INDEX [IX_Badges_UserId]
    ON [dbo].[Badges] ([UserId] ASC)
    INCLUDE ([Class], [Name], [Date])
    WITH
    (
        DATA_COMPRESSION = PAGE,
        SORT_IN_TEMPDB = ON,
        ONLINE = ON,
        FILLFACTOR = 100
    );
END
GO

Create Recommendation 5: Comments index for post joins and comment counts

USE [SQLStorm];
GO
IF NOT EXISTS
(
    SELECT 1
    FROM sys.indexes
    WHERE name = N'IX_Comments_PostId'
      AND object_id = OBJECT_ID(N'dbo.Comments')
)
BEGIN
    CREATE NONCLUSTERED INDEX [IX_Comments_PostId]
    ON [dbo].[Comments] ([PostId] ASC)
    INCLUDE ([UserId], [CreationDate])
    WITH
    (
        DATA_COMPRESSION = PAGE,
        SORT_IN_TEMPDB = ON,
        ONLINE = ON,
        FILLFACTOR = 100
    );
END
GO

Create Recommendation 6: Posts index for owner-based grouping and recent-post access

USE [SQLStorm];
GO
IF NOT EXISTS
(
    SELECT 1
    FROM sys.indexes
    WHERE name = N'IX_Posts_OwnerUserId_CreationDate'
      AND object_id = OBJECT_ID(N'dbo.Posts')
)
BEGIN
    CREATE NONCLUSTERED INDEX [IX_Posts_OwnerUserId_CreationDate]
    ON [dbo].[Posts] ([OwnerUserId] ASC, [CreationDate] DESC)
    INCLUDE ([Id], [PostTypeId], [Score], [ViewCount], [AcceptedAnswerId], [AnswerCount], [CommentCount], [Title])
    WITH
    (
        DATA_COMPRESSION = PAGE,
        SORT_IN_TEMPDB = ON,
        ONLINE = ON,
        FILLFACTOR = 100
    );
END
GO

Create Recommendation 7: Users index for reputation filters and ordering

USE [SQLStorm];
GO
IF NOT EXISTS
(
    SELECT 1
    FROM sys.indexes
    WHERE name = N'IX_Users_Reputation'
      AND object_id = OBJECT_ID(N'dbo.Users')
)
BEGIN
    CREATE NONCLUSTERED INDEX [IX_Users_Reputation]
    ON [dbo].[Users] ([Reputation] DESC)
    INCLUDE ([Id], [DisplayName], [CreationDate])
    WITH
    (
        DATA_COMPRESSION = PAGE,
        SORT_IN_TEMPDB = ON,
        ONLINE = ON,
        FILLFACTOR = 100
    );
END
GO

Implement Recommendation 8: Refresh key table statistics after index deployment

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