Query Tuner – RockyPC / SQLStorm / 0x932168333A069240 – 2026-05-31

Executive summary

1. Implement the Comments and Votes indexes first. These target the largest avoidable scans and the spool-heavy EXISTS branch.
2. Rewrite the query second. The rewrite removes row multiplication and should reduce hashes, sorts, and compilation complexity.
3. Add the Posts date-oriented covering index if the rewritten query still scans Posts heavily.

Workload context

Detailed prioritized recommendations

Priority 1: Fix repeated Comments scans and the spool-backed EXISTS

Finding: The query pays for Comments twice: once for the comment count aggregate and again for the EXISTS predicate. The second access is especially expensive because SQL Server builds an Eager Spool from a full clustered scan.

• Evidence 1: Index Spool (Eager Spool), estimated subtree cost 15.0741, driven by Clustered Index Scan on Comments with EstimatedRowsRead=351,440.
• Evidence 2: plan-level missing index on Comments(PostId) with Impact=40.0023%; DMV also shows missing index on Comments(PostId) with Total Seeks+Scans=276.

Recommendation: Create a covering index on Comments(PostId) INCLUDE (Id, UserId). This single index supports both COUNT(c.Id) and EXISTS (... WHERE c.PostId = rp.PostId AND c.UserId IS NOT NULL).

Before/after query shape example: current logic effectively checks ... OR EXISTS (SELECT 1 FROM Comments c WHERE c.PostId = rp.PostId AND c.UserId IS NOT NULL); after the index, the same logic can stay unchanged but should become seekable.

Confidence: High

Priority 2: Replace the broad Votes scan with a filtered index

Finding: The query only needs bounty votes (VoteTypeId = 8) but scans the clustered index for all votes.

• Evidence 1: Clustered Index Scan on Votes reads 926,084 rows and estimates only 1,478 qualifying rows.
• Evidence 2: plan missing index on Votes for VoteTypeId with includes PostId, BountyAmount; DMV also has recommendations for Votes(PostId, VoteTypeId) and Votes(VoteTypeId).

Recommendation: Prefer a filtered index over the generic missing-index script because the predicate is constant and extremely selective. Use Votes(PostId) INCLUDE (BountyAmount) WHERE VoteTypeId = 8.

Before/after query shape example: current join condition LEFT JOIN Votes v ON p.Id = v.PostId AND v.VoteTypeId = 8 is already good; the missing piece is physical support.

Confidence: High

Priority 3: Rewrite UserReputation to avoid row multiplication and COUNT(DISTINCT)

Finding: The current UserReputation CTE joins Users to both Posts and Badges before grouping. For users with multiple posts and badges, this multiplies rows and forces a distinct aggregate.

• Evidence 1: Hash Match (Aggregate) computes COUNT(DISTINCT [Posts].[Id]) and SUM([Expr1014]).
• Evidence 2: the plan scans Users (EstimatedRowsRead=267,193), Posts again (EstimatedRowsRead=246,672), and Badges (EstimatedRowsRead=439,352) inside this branch.

Recommendation: Aggregate Posts by OwnerUserId first, aggregate Badges by UserId first, then join those smaller grouped sets to Users. This removes the need for COUNT(DISTINCT) and reduces hash input cardinality.

Before: Users LEFT JOIN Posts LEFT JOIN Badges GROUP BY Users.Id, Users.Reputation

After: Users LEFT JOIN (SELECT OwnerUserId, COUNT(*) ... FROM Posts GROUP BY OwnerUserId) LEFT JOIN (SELECT UserId, SUM(Class) ... FROM Badges GROUP BY UserId)

Additional note: ROW_NUMBER() values in both CTEs are currently unused in the final select/filter. Removing them simplifies the plan and may reduce sort requirements.

Confidence: High

Priority 4: Reduce Posts scanning and row-store sort work

Finding: The RankedPosts branch filters on CreationDate but currently scans the clustered index. A date-oriented covering index can reduce rows flowing into the aggregate and join pipeline.

• Evidence 1: the plan shows Clustered Index Scan on Posts with predicate CreationDate >= '2023-10-01 12:34:56.000', reading 246,672 rows to estimate 8,351.92.
• Evidence 2: DMV missing index on Posts for CreationDate including OwnerUserId, and another for OwnerUserId, CreationDate.

Recommendation: If the query remains scan-heavy after the rewrite, add Posts(CreationDate DESC, OwnerUserId) INCLUDE (Id, Title). This is more aligned to the selective date predicate than the plan’s generic OwnerUserId-only suggestion.

Pruning guidance: Do not create both Posts indexes blindly. Start with the rewrite; if an index is still needed, prefer IX_Posts_CreationDate_OwnerUserId for this exact query. Create IX_Posts_OwnerUserId_Id only if broader workload evidence justifies it.

Confidence: Medium

Statistics recommendations

• No urgent stats problem is visible. Referenced stats show recent updates with ModificationCount=0 and SamplingPercent=100.
• After creating indexes, update statistics on the affected tables/indexes. This helps the optimizer immediately cost the new access paths.
• Keep Auto Create / Auto Update Statistics enabled. They are already enabled and appropriate here.
• Do not enable async stats solely for this query. There is no evidence of compile blocking; the issue is more about plan shape and missing access paths.

Evidence: optimizer stats usage lists current stats on Posts, Comments, Votes, Badges, and Users; all shown modification counts are 0.

Confidence: High

Table-specific insights

Optional broader-workload index: if badge-by-user aggregation is common beyond this one query, a narrow index on Badges(UserId) INCLUDE (Class) is defensible. It is not in the top 3 for this query alone, so it is not prioritized first.

Before/after plan comparison

Validation guidance

• Validate each change incrementally, not all at once.
• Capture before/after metrics using SET STATISTICS IO ON and SET STATISTICS TIME ON.
• Compare logical reads on Comments, Votes, and Posts specifically.
• After each index creation, inspect the new estimated or actual plan for elimination of:
  • Comments clustered scans
  • Index Spool (Eager Spool)
  • full Votes clustered scan for VoteTypeId = 8
  • COUNT(DISTINCT) aggregate in the user branch

Scripts

Create the highest-priority Comments covering index (implements Priority 1)

USE [SQLStorm];
GO

CREATE INDEX [IX_Comments_PostId_Includes]
ON [dbo].[Comments] ([PostId])
INCLUDE ([Id], [UserId])
WITH (ONLINE = ON, DATA_COMPRESSION = PAGE, SORT_IN_TEMPDB = ON);
GO

Create the filtered Votes index for bounty lookups (implements Priority 2)

USE [SQLStorm];
GO

CREATE INDEX [IX_Votes_PostId_Bounty_VoteType8]
ON [dbo].[Votes] ([PostId])
INCLUDE ([BountyAmount])
WHERE [VoteTypeId] = 8
WITH (ONLINE = ON, DATA_COMPRESSION = PAGE, SORT_IN_TEMPDB = ON);
GO

Create the optional Posts date-oriented covering index (implements Priority 4)

USE [SQLStorm];
GO

CREATE INDEX [IX_Posts_CreationDate_OwnerUserId]
ON [dbo].[Posts] ([CreationDate] DESC, [OwnerUserId])
INCLUDE ([Id], [Title])
WITH (ONLINE = ON, DATA_COMPRESSION = PAGE, SORT_IN_TEMPDB = ON);
GO

Create the optional Posts owner aggregation index if broader workload testing justifies it (implements Priority 4 alternative)

USE [SQLStorm];
GO

CREATE INDEX [IX_Posts_OwnerUserId_Id]
ON [dbo].[Posts] ([OwnerUserId], [Id])
WITH (ONLINE = ON, DATA_COMPRESSION = PAGE, SORT_IN_TEMPDB = ON);
GO

Create the optional Badges-by-user index if this aggregation pattern is common (supports Priority 3 broader workload)

USE [SQLStorm];
GO

CREATE INDEX [IX_Badges_UserId_Class]
ON [dbo].[Badges] ([UserId])
INCLUDE ([Class])
WITH (ONLINE = ON, DATA_COMPRESSION = PAGE, SORT_IN_TEMPDB = ON);
GO

Run the rewritten query shape for testing (implements Priority 3)

USE [SQLStorm];
GO

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO

WITH PostAgg AS
(
    SELECT
        p.OwnerUserId,
        COUNT_BIG(*) AS PostCount
    FROM dbo.Posts AS p
    GROUP BY p.OwnerUserId
),
BadgeAgg AS
(
    SELECT
        b.UserId,
        SUM(ISNULL(b.Class, 0)) AS TotalBadges
    FROM dbo.Badges AS b
    GROUP BY b.UserId
),
ActiveUsers AS
(
    SELECT
        u.Id AS UserId,
        u.Reputation,
        CONVERT(int, pa.PostCount) AS PostCount,
        ISNULL(ba.TotalBadges, 0) AS TotalBadges
    FROM dbo.Users AS u
    INNER JOIN PostAgg AS pa
        ON pa.OwnerUserId = u.Id
    LEFT JOIN BadgeAgg AS ba
        ON ba.UserId = u.Id
    WHERE pa.PostCount > 5
),
RecentPosts AS
(
    SELECT
        p.Id AS PostId,
        p.Title,
        p.CreationDate,
        p.OwnerUserId
    FROM dbo.Posts AS p
    WHERE p.CreationDate >= DATEADD(year, -1, CAST('2024-10-01T12:34:56' AS datetime))
),
CommentAgg AS
(
    SELECT
        c.PostId,
        COUNT(c.Id) AS CommentCount,
        MAX(CASE WHEN c.UserId IS NOT NULL THEN 1 ELSE 0 END) AS HasUserComment
    FROM dbo.Comments AS c
    GROUP BY c.PostId
),
BountyAgg AS
(
    SELECT
        v.PostId,
        SUM(v.BountyAmount) AS TotalBounty
    FROM dbo.Votes AS v
    WHERE v.VoteTypeId = 8
    GROUP BY v.PostId
)
SELECT
    rp.PostId,
    rp.Title,
    rp.CreationDate,
    ua.UserId,
    ua.Reputation,
    ISNULL(ca.CommentCount, 0) AS CommentCount,
    ba.TotalBounty,
    ua.PostCount,
    ua.TotalBadges
FROM RecentPosts AS rp
INNER JOIN ActiveUsers AS ua
    ON ua.UserId = rp.OwnerUserId
LEFT JOIN CommentAgg AS ca
    ON ca.PostId = rp.PostId
LEFT JOIN BountyAgg AS ba
    ON ba.PostId = rp.PostId
WHERE ISNULL(ba.TotalBounty, 0) > 0
   OR ISNULL(ca.HasUserComment, 0) = 1
ORDER BY
    ua.Reputation DESC,
    rp.CreationDate DESC
OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY;
GO

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
GO

Refresh statistics on affected tables after index changes (implements Statistics recommendations)

USE [SQLStorm];
GO

UPDATE STATISTICS dbo.Comments WITH FULLSCAN;
UPDATE STATISTICS dbo.Votes WITH FULLSCAN;
UPDATE STATISTICS dbo.Posts WITH FULLSCAN;
UPDATE STATISTICS dbo.Badges WITH FULLSCAN;
GO