Query Tuner Report – RockyPC / SQLStorm – QueryHash 0x872C7773968C6AAF

Executive summary

CRITICAL The estimated statement cost is 202.053, which is critical under the supplied thresholds. The dominant cost is the tag-matching branch, not the final ranking.

1. Address the non-sargable tag join and its repeated work. The predicate Posts.Tags LIKE '%' + Tags.TagName + '%' forces a broad scan and a nested-loops comparison against all 1,232 tags. This branch accounts for estimated subtree cost 193.69.
2. Add targeted covering indexes for the two large join/aggregation paths. The plan scans 926,084 Votes, 439,352 Badges, and Posts repeatedly because only clustered primary-key indexes are available for the relevant foreign-key columns.
3. Enable automatic statistics management and refresh heavily modified statistics. Auto-create and auto-update statistics are disabled. Votes statistics show 1,600,424 modifications since their last update.
4. Review the query's join correctness before production use. The final join is tp.UserId = rp.PostId, although RankedPosts exposes a post identifier. This is preserved in the supplied statement, but it likely produces unintended matches.

Environment and scope

Detailed plan findings

Detailed prioritized recommendations

1. Priority 1 — Fix or redesign tag storage and matching.

   Long-term, normalize the relationship between Posts and Tags into a bridge table, for example PostTags(PostId, TagId), populated from the tag representation. That change is not scripted here because the bridge table and source-column parsing contract are not present in the input. It is the only durable way to make tag membership seekable and avoid the 104,581-rewind nested-loops pattern.

   Evidence: Node 8 has estimated cost 193.690 and applies LIKE '%'+TagName+'%'. Node 10 shows a Lazy Spool with 104,581 estimated rewinds.

   Confidence: 95%. Risk: Requires schema and data-pipeline work; delimiter and escaping rules must be defined to preserve semantics.

2. Priority 2 — Add the targeted access-path indexes in the Scripts section.

   Create a filtered, narrow Posts index for PostTypeId = 1, a Posts OwnerUserId covering index, a Votes PostId covering index, and a Badges UserId covering index. These are deliberately narrower than the broad DMV suggestions because this query uses Badges.Id and does not use Class or Name.

   Evidence: Posts has 740,016 rows and a 400.3 MB clustered index with 5,374 scans. Votes and Badges have 926,084 and 439,352 rows, respectively, and are scanned at Nodes 21 and 23.

   Confidence: 86%. Risk: Additional write, storage, and maintenance cost; validate usage before retaining all four.

3. Priority 3 — Enable automatic statistics maintenance and refresh the heavily changed tables.

   Use synchronous automatic update statistics for predictable compilation behavior. The supplied workload has no parameters, so asynchronous updates are not necessary for this particular statement and can temporarily compile against stale statistics.

   Evidence: All automatic statistics settings are disabled; Votes has 1,600,424 modifications since the reported statistics updates.

   Confidence: 93%. Risk: Statistics refreshes consume I/O and CPU; schedule FULLSCAN only for the targeted maintenance window.

4. Priority 4 — Resolve the apparent final join defect after establishing intended semantics.

   The supplied statement joins TopUsers.UserId to RankedPosts.PostId. This is not a performance-only issue: changing it to an owner-user join would change the result and therefore cannot be included as an exact-equivalence rewrite.

   Evidence: The final plan Hash Match probes Users.Id against Posts.Id, confirming the compiled join is UserId = PostId.

   Confidence: 98% that this is the compiled predicate; intent confidence is lower because business requirements were not supplied.

5. Priority 5 — Do not add a columnstore index for this statement yet.

   Batch mode on rowstore is already being used. A columnstore index would add substantial storage and write complexity while the dominant problem is the non-sargable text join and the query's multiway aggregation shape.

   Evidence: The plan reports BatchModeOnRowStoreUsed="true", and the dominant tag branch remains row-mode Nested Loops.

   Confidence: 82%.

Index recommendations

Size estimates are ranges because the input does not provide exact declared widths for Title, Tags, index page counts, or fill factor. Existing reported sizes were used directly where applicable: Posts clustered index 400.3 MB, Users 44.8 MB, Votes 11.8 MB, Badges 8.0 MB, Tags 0.1 MB. The new estimates assume PAGE compression and approximately 90% fill factor after normal maintenance; clustered keys are implicitly carried in nonclustered leaf rows.

Indexes not recommended: The DMV candidates involving Badges(Class/Name), Votes(UserId/BountyAmount), Users(Reputation), and unrelated Posts(CreationDate/Score/ViewCount) are not directly required by this statement. Their reported impact is workload-wide, not proof of benefit for this query.

Statistics recommendations

• Enable AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICS for SQLStorm.
• Refresh statistics on Posts, Votes, Badges, and Users after index creation. The supplied plan used statistics updated around 2026-09-12, while Votes had 1,600,424 modifications.
• Use FULLSCAN for the targeted statistics refresh only when the maintenance window permits it. The recommended script uses FULLSCAN for deterministic baseline quality.
• After the baseline, use a normal sampled maintenance strategy appropriate to data-change volume rather than repeated FULLSCAN operations.
• Do not add a plan guide or OPTION (RECOMPILE). There are no parameters, and no Query Store history was supplied to show plan instability.

Evidence: The plan's OptimizerStatsUsage reports Votes statistics modification count 1,600,424. Database settings report all automatic statistics options disabled.

Storage impact

No index is recommended for removal or modification, so no storage is reclaimed and no modified-index before/after delta applies. The net estimate includes only the four new indexes and excludes unreported internal allocation, fragmentation, and statistics storage.

Before/after plan comparison

The second panel is an expected physical-design outcome, not an observed after-plan. SQL Server may continue to choose scans when most rows are needed. The largest guaranteed architectural opportunity remains normalizing tag membership.

Scripts

Create targeted indexes implementing Priority 2

USE [SQLStorm];
GO

CREATE NONCLUSTERED INDEX [IX_Posts_Question_TagScan]
ON [dbo].[Posts] ([PostTypeId])
INCLUDE ([CreationDate], [Title], [Tags])
WHERE [PostTypeId] = 1
WITH (DATA_COMPRESSION = PAGE, ONLINE = ON, SORT_IN_TEMPDB = ON);
GO

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

CREATE NONCLUSTERED INDEX [IX_Votes_PostId_VoteType]
ON [dbo].[Votes] ([PostId])
INCLUDE ([VoteTypeId])
WITH (DATA_COMPRESSION = PAGE, ONLINE = ON, SORT_IN_TEMPDB = ON);
GO

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

Enable automatic statistics maintenance implementing Priority 3

USE [SQLStorm];
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 OFF;
GO

Refresh statistics implementing Priority 3

USE [SQLStorm];
GO

UPDATE STATISTICS [dbo].[Posts] WITH FULLSCAN;
GO

UPDATE STATISTICS [dbo].[Votes] WITH FULLSCAN;
GO

UPDATE STATISTICS [dbo].[Badges] WITH FULLSCAN;
GO

UPDATE STATISTICS [dbo].[Users] WITH FULLSCAN;
GO

UPDATE STATISTICS [dbo].[Tags] WITH FULLSCAN;
GO

No rewritten query is included because the supplied query has both a potentially unintended UserId-to-PostId join and multiplicative outer joins. A rewrite that changes either join or aggregation behavior would not be guaranteed to return exactly the same rows and values as the original.

Supporting scripts (not part of the change)

Measure logical reads, CPU, elapsed time, and the actual plan

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 p
    JOIN
        Tags 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 u
    LEFT JOIN
        Posts p ON u.Id = p.OwnerUserId
    LEFT JOIN
        Votes v ON p.Id = v.PostId
    LEFT JOIN
        Badges 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 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 tp
LEFT JOIN
    RankedPosts 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

Inspect resulting index usage and physical sizes

USE [SQLStorm];
GO

SELECT
    OBJECT_SCHEMA_NAME(i.object_id) AS SchemaName,
    OBJECT_NAME(i.object_id) AS TableName,
    i.name AS IndexName,
    i.type_desc,
    ips.page_count,
    CONVERT(decimal(18,2), ips.page_count * 8.0 / 1024.0) AS SizeMB,
    i.fill_factor,
    ips.avg_fragmentation_in_percent
FROM sys.indexes AS i
JOIN sys.dm_db_index_physical_stats
(
    DB_ID(),
    NULL,
    NULL,
    NULL,
    'SAMPLED'
) AS ips
    ON ips.object_id = i.object_id
   AND ips.index_id = i.index_id
WHERE OBJECT_SCHEMA_NAME(i.object_id) = 'dbo'
  AND OBJECT_NAME(i.object_id) IN ('Posts', 'Votes', 'Badges', 'Users', 'Tags')
ORDER BY TableName, IndexName;
GO

SELECT
    OBJECT_SCHEMA_NAME(i.object_id) AS SchemaName,
    OBJECT_NAME(i.object_id) AS TableName,
    i.name AS IndexName,
    us.user_seeks,
    us.user_scans,
    us.user_lookups,
    us.user_updates
FROM sys.indexes AS i
LEFT JOIN sys.dm_db_index_usage_stats AS us
    ON us.database_id = DB_ID()
   AND us.object_id = i.object_id
   AND us.index_id = i.index_id
WHERE OBJECT_SCHEMA_NAME(i.object_id) = 'dbo'
  AND OBJECT_NAME(i.object_id) IN ('Posts', 'Votes', 'Badges', 'Users', 'Tags')
ORDER BY TableName, IndexName;
GO

Confidence and validation

Validate changes by running the supporting measurement script before and after applying the recommendations. Compare logical reads, CPU time, elapsed time, actual row counts, memory grants, spills, and the actual operator choices. Retain an index only if its read benefit justifies its measured write and maintenance overhead.