Query Tuner – RockyPC/SQLStorm – QueryHash 0x872C7773968C6AAF

Executive summary

1. Critical: fix the tag-matching design. The join p.Tags LIKE '%' + t.TagName + '%' is evaluated by a nested-loops plan over approximately 104,436 question rows and 1,232 tags. The nested loops operator has own cost 157.2936, and the spool subtree is 33.6225.
2. Critical correctness review: validate the final join. The query joins tp.UserId = rp.PostId, not a user/post-owner relationship. This may be intentional, but it is a strong semantic anomaly. Changing it would not preserve the current result set, so no corrective rewrite is supplied as an equivalent rewrite.
3. High: remove aggregate row multiplication for the intended business result. Posts, Votes, and Badges are joined before aggregation, producing an estimated 1,677,770 intermediate rows and requiring two distinct aggregates. A corrected business rewrite could be materially faster, but it would change results where a user has both multiple badges and multiple vote rows.
4. High: refresh statistics. Automatic statistics creation and update are disabled. Votes statistics show 1,600,424 modifications since their last update on 2026-09-12, while the plan relies on those statistics.
5. Do not blindly add DMV missing indexes. The supplied plan's dominant cost is not an uncovered equality seek. Existing indexes already support the Votes/PostId and Badges/UserId scans, and the DMV candidates are workload-wide recommendations without size or plan-specific benefit evidence.

Environment and scope

Evidence: The input reports 37.6 hours of uptime and explicitly states that DMVs and plan cache were cleared at restart. The plan uses CE model 160 and batch mode on rowstore.

Plan diagnosis

Detailed prioritized recommendations

Index assessment

Evidence: The plan uses all listed nonclustered indexes in the relevant branches. The observation period is only 37.6 hours, so low update counts are not evidence that an index has no write cost.

Storage impact

Current reported referenced-index footprint: approximately 482.3 MB, calculated by summing the nine reported index sizes: 44.8 + 400.3 + 9.8 + 2.4 + 11.8 + 4.6 + 8.0 + 10.5 + 0.1 MB. This is an estimate based on the supplied rounded sizes, not a new storage measurement.

Evidence: The input reports sizes for every referenced index, but no size for a new tag bridge or for the DMV missing-index candidates. No recommendation can legitimately claim a new-index MB value without those dimensions.

Before/after plan comparison

Scripts

Refresh statistics used by the query and enable automatic statistics management — Recommendation 4

USE [SQLStorm];
GO

UPDATE STATISTICS [dbo].[Votes] [_WA_Sys_00000003_5BE2A6F2] WITH FULLSCAN;
GO
UPDATE STATISTICS [dbo].[Votes] [ST_Votes_VoteTypeId_CreationDate_PostId] WITH FULLSCAN;
GO
UPDATE STATISTICS [dbo].[Votes] [IX_Votes_PostId_VoteType] WITH FULLSCAN;
GO
UPDATE STATISTICS [dbo].[Votes] [ST_Votes_Post_VoteType_User] WITH FULLSCAN;
GO
UPDATE STATISTICS [dbo].[Posts] [PK__Posts__3214EC07AF21AC3E] WITH FULLSCAN;
GO
UPDATE STATISTICS [dbo].[Posts] [IX_Posts_Question_TagScan] WITH FULLSCAN;
GO
UPDATE STATISTICS [dbo].[Posts] [ST_Posts_PostType_Creation_Owner] WITH FULLSCAN;
GO
UPDATE STATISTICS [dbo].[Posts] [IX_Posts_OwnerUserId_Cover] WITH FULLSCAN;
GO
UPDATE STATISTICS [dbo].[Posts] [_WA_Sys_00000002_440B1D61] WITH FULLSCAN;
GO
UPDATE STATISTICS [dbo].[Posts] [_WA_Sys_00000005_440B1D61] WITH FULLSCAN;
GO
UPDATE STATISTICS [dbo].[Badges] [ST_Badges_User_Class_Date] WITH FULLSCAN;
GO
UPDATE STATISTICS [dbo].[Badges] [_WA_Sys_00000002_628FA481] WITH FULLSCAN;
GO
UPDATE STATISTICS [dbo].[Users] [PK__Users__3214EC079D12375D] WITH FULLSCAN;
GO

ALTER DATABASE [SQLStorm] SET AUTO_CREATE_STATISTICS ON;
GO
ALTER DATABASE [SQLStorm] SET AUTO_UPDATE_STATISTICS ON;
GO
ALTER DATABASE [SQLStorm] SET AUTO_UPDATE_STATISTICS_ASYNC ON;
GO

Implementation note: The script changes only statistics and database statistics settings. It does not add, drop or modify indexes, so its index storage impact is 0 MB.

Supporting scripts (not part of the change)

Capture actual I/O, CPU time and execution plan for before/after validation

USE [SQLStorm];
GO

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SET STATISTICS XML ON;

WITH RankedPosts AS
(
    SELECT
        p.Id AS PostId,
        p.Title,
        p.CreationDate,
        ROW_NUMBER() OVER
        (
            PARTITION BY t.Id
            ORDER BY p.CreationDate DESC
        ) AS rn,
        t.TagName
    FROM Posts AS p
    JOIN Tags AS t
        ON p.Tags LIKE '%' + t.TagName + '%'
    WHERE p.PostTypeId = 1
),
UserStats AS
(
    SELECT
        u.Id AS UserId,
        u.DisplayName,
        COUNT(DISTINCT p.Id) AS TotalPosts,
        SUM(CASE WHEN v.VoteTypeId = 2 THEN 1 ELSE 0 END) AS UpVotes,
        SUM(CASE WHEN v.VoteTypeId = 3 THEN 1 ELSE 0 END) AS DownVotes,
        COUNT(DISTINCT b.Id) AS BadgeCount
    FROM Users AS u
    LEFT JOIN Posts AS p
        ON u.Id = p.OwnerUserId
    LEFT JOIN Votes AS v
        ON p.Id = v.PostId
    LEFT JOIN Badges AS b
        ON u.Id = b.UserId
    GROUP BY
        u.Id,
        u.DisplayName
),
TopUsers AS
(
    SELECT
        us.UserId,
        us.DisplayName,
        us.TotalPosts,
        us.UpVotes,
        us.DownVotes,
        us.BadgeCount,
        DENSE_RANK() OVER
        (
            ORDER BY
                us.TotalPosts DESC,
                us.UpVotes - us.DownVotes DESC
        ) AS UserRank
    FROM UserStats AS us
    WHERE us.TotalPosts > 10
)
SELECT
    tp.UserRank,
    tp.DisplayName,
    tp.TotalPosts,
    tp.UpVotes,
    tp.DownVotes,
    tp.BadgeCount,
    rp.Title AS LatestPostTitle,
    rp.CreationDate AS LatestPostDate,
    CASE
        WHEN rp.rn = 1 THEN 'Latest'
        ELSE 'Older'
    END AS PostStatus
FROM TopUsers AS tp
LEFT JOIN RankedPosts AS rp
    ON tp.UserId = rp.PostId
ORDER BY
    tp.UserRank,
    rp.CreationDate DESC;

SET STATISTICS XML OFF;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
GO

Validation requirement: Compare logical reads, CPU time, elapsed time, actual rows, memory grant, spills and returned-row checksum before and after the statistics change. The supplied plan is estimated only, so actual performance cannot be claimed yet.

Confidence and validation

Validate all changes with SET STATISTICS IO ON and SET STATISTICS TIME ON, plus an actual execution plan. Confirm that the output rows and values remain unchanged for any change claimed to be performance-only. Because Query Store has no matching history and the server restarted 37.6 hours ago, reported usage and missing-index figures should be treated as provisional.