Query Tuning Report – VMEnt\SQL25 / ForumDemo – 0xE91FED510A31399B

Tuning goal: Query Tuner — reduce logical reads, repeated work, and elapsed time while preserving exactly the same result set and ordering.

Server: VMEnt\SQL25  |  Database: ForumDemo  |  Engine: SQL Server 2025 RTM 17.0.1000.7, Enterprise Developer Edition  |  Query hash: 0xE91FED510A31399B

Executive summary

Primary bottleneck: the estimated plan has a correlated subquery that scans dbo.Votes repeatedly. Operator 30 has an estimated 738,571 rows per execution, reads 945,284 rows, and is estimated to execute 87 times. Its own estimated cost is 91.4432, while the parent hash join is estimated at 334.3632, or 76% of the statement cost.

The estimated statement cost is 441.17, which is critical under the supplied thresholds. These are optimizer estimates, not measurements. Query Store provides the only runtime measurements: one execution took 2,955.85 ms, used 9,957.31 ms CPU, performed 505,869 logical reads, returned 25 rows, and ran at runtime DOP 4.

  1. Rewrite the three correlated aggregates into set-based aggregates. This is the highest-impact change because it prevents the Votes scan from being repeated for each qualifying user and prevents repeated Posts lookups.
  2. Add a covering Votes index on (PostId, VoteTypeId). This is supported by the plan's 96.44% missing-index impact and the DMV's 99.42% average user impact.
  3. Replace non-sargable year predicates with half-open datetime ranges. This removes the conversion warning on Comments and makes CreationDate searchable by an index.
  4. Add supporting indexes only after applying the rewrite or confirming the workload needs them. Candidate indexes for Posts and Comments can reduce scans and lookups, but their benefit is less certain than the Votes index because the rewritten query changes the access pattern.
  5. Do not force a plan or add a MAXDOP hint at this stage. CE feedback and DOP feedback are eligible but have not yet persisted feedback. A user hint would prevent CE feedback, and a MAXDOP hint would make the query ineligible for DOP feedback.

Confidence: high for the Votes bottleneck and set-based rewrite; medium-high for the Votes index; medium for the optional Posts and Comments indexes because their final value depends on the post-rewrite plan.

Scope and environment

ItemFinding
StatementOne SELECT statement; estimated plan compiled with CE model 160 and FULL optimization.
Estimated cost441.1700, critical. This is an estimate, not elapsed time.
Estimated resultTOP (25); the underlying pre-row-goal estimate is 86.8367 rows.
ParallelismEstimated parallel plan, estimated available DOP 2 in the plan, server MAXDOP 4, effective database MAXDOP 4. Query Store reports runtime DOP 4.
SET optionsANSI_NULLS ON, ANSI_PADDING ON, ANSI_WARNINGS ON, ARITHABORT OFF, CONCAT_NULL_YIELDS_NULL ON, NUMERIC_ROUNDABORT OFF, QUOTED_IDENTIFIER ON.
Parameters and variablesNone. Literal values 20000, 2 and 2020 are embedded in the statement. Parameter sensitivity and local-variable density are therefore not applicable.
Plan cache and actual planNo last actual plan is available. LAST_QUERY_PLAN_STATS is OFF and the query has not run since its plan left cache.

The supplied index, missing-index, usage, and plan-cache statistics cover approximately 2.4 days since the latest database startup. They should not be treated as lifetime workload statistics.

Execution plan analysis

1. Repeated Votes scan — highest estimated impact

The plan evaluates the upvote subquery once for each estimated qualifying user. The inner side scans the clustered Votes index, applies VoteTypeId = 2, and then hashes against qualifying answer IDs. This is the dominant estimated work.

  • Operator 30: clustered index scan on dbo.Votes.PK__Votes__3214EC07006169CE; estimated 738,571 qualifying rows per execution, 945,284 rows read, 87 estimated executions, estimated own cost 91.4432.
  • Operator 24: hash match estimated own cost 334.3632, 76% of statement cost. This is the costliest logical branch, not a measured elapsed-time percentage.
  • Existing index evidence: IX_Votes_VoteTypeId has zero seeks and 6,400 updates in the observed period, while the clustered Votes index has 120 scans. The single-column VoteTypeId index is not solving the PostId lookup/join pattern.

2. Non-sargable date predicates

The Posts predicates use YEAR(a.CreationDate) = 2020, represented in the plan as datepart(year, CreationDate) = 2020. The Comments predicate explicitly converts the table column with CONVERT(varchar(4), c.CreationDate, 112) = '2020'.

  • Posts operator 11 scans 246,672 rows and estimates 11,339.9 matches. The date expression is a residual predicate rather than a range seek.
  • Comments operator 44 applies the conversion to the table column after clustered lookups. The plan warning is Plan-affecting conversion: Cardinality Estimate.
  • The conversion is on dbo.Comments.CreationDate, a datetime NOT NULL column. This is materially different from converting a literal or parameter; converting the column can prevent an efficient seek and distort estimates.

3. Repeated Posts lookup work

Each qualifying user's Posts access first seeks IX_Posts_OwnerUserId, estimated at 7.0746 rows per execution, and then performs clustered lookups to evaluate PostTypeId and CreationDate. Operators 20, 29 and 41 each show an estimated 613.335 executions and an estimated 0.214409 rows per execution.

This is an inefficient consequence of the query's repeated subqueries and the existing index containing only OwnerUserId. The rewrite removes most of this repeated access. A covering Posts index should be considered only if the post-rewrite plan still scans or performs substantial lookups.

4. Users scan

Operator 13 scans the clustered Users index, reading all estimated 267,193 rows and estimating 415 qualifying rows after Reputation and the bitmap semi-join. The scan is not the principal bottleneck: its estimated subtree cost is 1.58349, compared with 427.914 for the upvote branch. A new Reputation index is not a priority for this statement.

5. Comments access and lookups

The existing IX_Comments_PostId is used for estimated 9.96565 rows per execution and approximately 130.719 rebinds, followed by clustered lookups with the date conversion residual predicate. Operator 44 has an estimated own cost of 4.04975. This is materially smaller than the Votes branch but should improve after replacing the year conversion and, if necessary, adding (PostId, CreationDate).

6. Memory, spills and missing predicates

The estimated plan reports no spill warning, no missing join predicate, and no hash bailout warning. Estimated serial required memory is 2,088 KB and desired memory is 3,688 KB; granted and used memory are not known because this is an estimated plan. The TopN Sort has estimated 25 output rows and a pre-row-goal input estimate of 86.8367 rows.

Detailed prioritized recommendations

  1. Rewrite the correlated subqueries as set-based aggregates

    Build the 2020 answer set once, aggregate votes once, and aggregate 2020 comments once. This preserves the original semantics: votes are filtered only by VoteTypeId and answer PostId, while comments are filtered by their 2020 CreationDate and answer PostId.

    Evidence: the current plan repeats the Votes clustered scan 87 times and estimates 505,869 logical reads in the measured Query Store execution. The rewrite directly targets this repeated work.

    Expected benefit: high, but the exact improvement must be measured. It should reduce repeated scans and may change the plan to hash or merge aggregates over each base table once.

  2. Add the Votes join-supporting index

    Create a nonclustered index on dbo.Votes(PostId, VoteTypeId). It supports the join from answer IDs to votes and the VoteTypeId filter. The clustered key Id is implicitly present as the row locator for a nonclustered index, but it is not needed by the count.

    Evidence: the plan reports a missing-index candidate with equality columns PostId and VoteTypeId and 96.44% estimated statement-cost impact. The DMV reports average user impact 99.42 and 80 seeks/scans. Existing IX_Votes_VoteTypeId has zero seeks.

    Estimated size: approximately 15–30 MB after creation. Basis: 945,284 rows, approximately 4-byte PostId plus 2-byte VoteTypeId, the implicit 4-byte clustered key, row/page overhead, and PAGE compression. The input does not provide a missing-index storage estimate, so this is a range rather than a measured size.

    Risk: additional insert/update/delete maintenance. Votes has 6,400 updates on each listed index during the 2.4-day observation period; the new index adds one more write target and requires statistics and index maintenance. Validate before retaining it.

  3. Use half-open date ranges

    For a datetime year, use a predicate equivalent to CreationDate >= '20200101' AND CreationDate < '20210101'. This is a non-executable illustrative fragment outside the Scripts section.

    Evidence: Posts uses datepart(year, CreationDate); Comments has the explicit plan-affecting conversion on the table column. Both CreationDate columns are datetime, and the Comments date column is NOT NULL.

    Expected benefit: improved selectivity and seekability when a suitable leading index is available; it also removes the conversion warning. It does not by itself guarantee a seek on the current indexes.

  4. Evaluate a Posts covering access path after the rewrite

    If the rewritten query still scans Posts or performs repeated lookups, create (PostTypeId, CreationDate, OwnerUserId) including Id. This matches the fixed post type and date range and supplies the answer owner and answer ID.

    Evidence: the current plan scans 246,672 Posts rows and the existing owner-only index estimates 7.0746 rows per user followed by clustered lookups. The plan already uses a user-created statistic named ST_Posts_PostType_Creation_Owner, but that statistic is not an index.

    Estimated size: approximately 20–40 MB. Basis: 246,672 rows, key widths of approximately 2 bytes for smallint PostTypeId, 8 bytes for datetime CreationDate and 4 bytes for OwnerUserId, included 4-byte Id, clustering-key overhead, and PAGE compression. Exact column metadata and candidate size are not supplied, so this is a range.

    Risk: Posts has 19,200 clustered-index updates in the observed period; the new index will add write and maintenance work. Because the existing IX_Posts_OwnerUserId has 16,120 seeks and only 3.3 MB size, do not drop or replace it based on this query alone.

  5. Evaluate a Comments date-covering index after the rewrite

    If the post-rewrite plan still performs many lookups, create (PostId, CreationDate). This supports answer-to-comment joins and the 2020 date range without converting the Comments column.

    Evidence: the existing IX_Comments_PostId has 32,000 seeks but operator 44 still performs clustered lookups with the date conversion. Comments has 365,839 rows, 4,800 updates in the observed period, and the existing index is only 5.1 MB.

    Estimated size: approximately 8–18 MB. Basis: 365,839 rows, 4-byte PostId, 8-byte datetime CreationDate, implicit clustered key, row/page overhead, and PAGE compression. Exact candidate size is not reported.

    Risk: additional maintenance for 4,800 observed Comments updates. Retain the existing PostId index unless broader usage confirms that the new composite index makes it redundant; this report does not contain enough workload evidence to recommend dropping it.

Index recommendations and storage impact

IndexPurposeEstimated sizeEvidence and impactWrite/maintenance risk
dbo.Votes(PostId, VoteTypeId)Join answer PostIds to VoteTypeId 2 votes.15–30 MB estimated; 945,284 rows; PAGE-compressed assumption.Plan impact 96.44%; DMV average user impact 99.42; 80 DMV seeks/scans.Votes indexes show 6,400 updates during the observation period; adds one write target.
dbo.Posts(PostTypeId, CreationDate, OwnerUserId) INCLUDE (Id)Range-filter and cover 2020 answers.20–40 MB estimated; 246,672 rows; PAGE-compressed assumption.Current scan reads 246,672 rows; owner-only index causes lookup work.Posts clustered index shows 19,200 updates; apply only if post-rewrite plan validates the benefit.
dbo.Comments(PostId, CreationDate)Join comments by PostId and seek the date range.8–18 MB estimated; 365,839 rows; PAGE-compressed assumption.Existing PostId index has 32,000 seeks but date filtering occurs in clustered lookups.Comments indexes show 4,800 updates; do not drop the existing index without broader evidence.

Estimated net storage change: approximately 43–88 MB if all three indexes are created. This is an estimate based on supplied row counts and the stated key-width/compression assumptions; it is not a measured storage result. No index drop is recommended, so no storage reclamation is claimed.

Statistics and configuration findings

Automatic statistics are disabled. The database metadata reports AUTO_CREATE_STATISTICS, AUTO_UPDATE_STATISTICS and AUTO_UPDATE_STATISTICS_ASYNC disabled. This is a configuration risk because new or changed data can remain unrepresented in statistics.

The plan's statistics do not show evidence that a refresh alone caused the bottleneck. Statistics used by the plan were generally updated on 2026-10-30. The relevant modifications are 1.4% of Votes rows, 2.7% of Comments rows and 0% of Posts and Users rows. The sample rates are 71.3% for Votes, 16.0% for Comments and 37.5% for Posts. Low sample rate alone is not sufficient evidence of a bad estimate.

  • Votes: IX_Votes_VoteTypeId has 12,800 modifications against 932,484 rows, or 1.4%, sampled 71.3%. No update is prioritized solely from these figures.
  • Comments: ST_Comments_Post_Creation and IX_Comments_PostId have 9,600 modifications against 356,239 rows, or 2.7%, sampled 16.0%. The plan's explicit conversion is the stronger issue; refresh statistics after the rewrite/index changes if estimates remain inaccurate.
  • Posts: plan-used statistics have 0 modifications since update. The scan is caused primarily by predicate shape and access-path choice, not demonstrably stale statistics.

CE feedback is available because CE_FEEDBACK is ON, compatibility level is 160, and Query Store is READ_WRITE. CE feedback for expressions is ON. However, query_id 50 has no CE feedback row and no Query Store hint; no CE adjustment has yet been reported. DOP feedback is also ON and eligible, but no persisted DOP feedback exists.

The estimated plan is parallel because its cost is far above the cost threshold of 5 and no serial restriction is reported. It uses parallel operators with effective MAXDOP 4. This is not a T-SQL UDF restriction, MAXDOP 1 restriction, or nonparallelizable intrinsic-function restriction. Query Store measured runtime DOP was 4, matching the effective limit.

Actual and Query Store evidence

Measured Query Store query_id 50, context_settings_id 1, plan_id 48:

  • Executions: 1
  • Average duration: 2,955.85 ms
  • Average CPU: 9,957.31 ms
  • Logical reads: 505,869
  • Rows returned: 25
  • Runtime DOP: 4
  • Query Store plan hash: 0x3AFBC4216AD80719, the same shape as the estimated plan

There is no last actual plan section with operator actuals, so no estimate-divergence starting operator can be identified and no actual-versus-estimated row error can be calculated. The available measured Query Store plan has the same shape as the estimated plan, confirming that the measured execution used the same plan shape, but not proving that every operator's actual row count matched its estimate.

Query Store has only one SET-options context and one plan. The context matches the Studio estimated-plan SET options, so there is no evidence here that the application received a different SET-options plan.

Do not add a forced plan or query hint as the first response. A user query hint or user Query Store hint would make CE feedback ineligible. A MAXDOP hint would also make DOP feedback ineligible. Neither feature has currently persisted a correction, so structural query and index changes are preferable.

Before/after plan comparison

AreaCurrent estimated/measured evidenceExpected optimized shape
Answer discoveryPosts clustered scan reads 246,672 estimated rows; repeated owner seeks and lookups occur for the correlated subqueries.One range-filtered AnswerPosts input, preferably using the optional Posts composite index if the optimizer selects it.
UpvotesVotes clustered scan estimated 738,571 rows per execution, 87 executions; parent hash branch estimated cost 427.914.One aggregate over Votes joined to AnswerPosts, with a seek or efficient ordered/hash access through (PostId, VoteTypeId).
CommentsPostId seeks feed clustered lookups; CreationDate is converted in the residual predicate.Half-open datetime range; optional (PostId, CreationDate) access path avoids the column conversion and lookup residual.
RuntimeQuery Store measured 2,955.85 ms, 9,957.31 ms CPU and 505,869 logical reads for one execution.Expected to reduce repeated work, but no improvement percentage is asserted until the rewritten query is executed with the same workload.

The optimized plan cannot be displayed as an actual plan because the input contains no post-change execution plan. The comparison above is a structural expectation, not a measured result.

Scripts

Create the high-priority Votes covering index

USE [ForumDemo];
GO

IF NOT EXISTS
(
    SELECT 1
    FROM sys.indexes
    WHERE object_id = OBJECT_ID(N'dbo.Votes')
      AND name = N'IX_Votes_PostId_VoteTypeId'
)
BEGIN
    CREATE NONCLUSTERED INDEX [IX_Votes_PostId_VoteTypeId]
    ON [dbo].[Votes] ([PostId], [VoteTypeId])
    WITH (DATA_COMPRESSION = PAGE);
END;
GO

Create the optional Posts access index for the rewritten query

USE [ForumDemo];
GO

IF NOT EXISTS
(
    SELECT 1
    FROM sys.indexes
    WHERE object_id = OBJECT_ID(N'dbo.Posts')
      AND name = N'IX_Posts_PostType_Creation_Owner'
)
BEGIN
    CREATE NONCLUSTERED INDEX [IX_Posts_PostType_Creation_Owner]
    ON [dbo].[Posts] ([PostTypeId], [CreationDate], [OwnerUserId])
    INCLUDE ([Id])
    WITH (DATA_COMPRESSION = PAGE);
END;
GO

Create the optional Comments date-covering index

USE [ForumDemo];
GO

IF NOT EXISTS
(
    SELECT 1
    FROM sys.indexes
    WHERE object_id = OBJECT_ID(N'dbo.Comments')
      AND name = N'IX_Comments_PostId_CreationDate'
)
BEGIN
    CREATE NONCLUSTERED INDEX [IX_Comments_PostId_CreationDate]
    ON [dbo].[Comments] ([PostId], [CreationDate])
    WITH (DATA_COMPRESSION = PAGE);
END;
GO

Rewritten query — set-based answer, vote and comment aggregates

USE [ForumDemo];
GO

WITH AnswerPosts AS
(
    SELECT
        a.Id,
        a.OwnerUserId
    FROM dbo.Posts AS a
    WHERE a.PostTypeId = 2
      AND a.CreationDate >= '20200101'
      AND a.CreationDate < '20210101'
),
AnswerCounts AS
(
    SELECT
        ap.OwnerUserId,
        COUNT(*) AS Answers2020
    FROM AnswerPosts AS ap
    GROUP BY ap.OwnerUserId
),
UpvoteCounts AS
(
    SELECT
        ap.OwnerUserId,
        COUNT(*) AS Upvotes2020
    FROM AnswerPosts AS ap
    INNER JOIN dbo.Votes AS v
        ON v.PostId = ap.Id
       AND v.VoteTypeId = 2
    GROUP BY ap.OwnerUserId
),
CommentCounts AS
(
    SELECT
        ap.OwnerUserId,
        COUNT(*) AS Comments2020
    FROM AnswerPosts AS ap
    INNER JOIN dbo.Comments AS c
        ON c.PostId = ap.Id
       AND c.CreationDate >= '20200101'
       AND c.CreationDate < '20210101'
    GROUP BY ap.OwnerUserId
)
SELECT TOP (25)
    u.Id,
    u.DisplayName,
    u.Reputation,
    ac.Answers2020,
    ISNULL(uc.Upvotes2020, 0) AS Upvotes2020,
    ISNULL(cc.Comments2020, 0) AS Comments2020
FROM dbo.Users AS u
INNER JOIN AnswerCounts AS ac
    ON ac.OwnerUserId = u.Id
LEFT JOIN UpvoteCounts AS uc
    ON uc.OwnerUserId = u.Id
LEFT JOIN CommentCounts AS cc
    ON cc.OwnerUserId = u.Id
WHERE u.Reputation >= 20000
ORDER BY
    ISNULL(uc.Upvotes2020, 0) DESC,
    ac.Answers2020 DESC,
    u.Id;
GO

Supporting scripts (not part of the change)

No supporting executable scripts are included. Validation should be performed with the application's existing parameterization and SET options, using the standard measurement process described below.

Validation and rollout guidance

  • Capture the original query's result count and a deterministic checksum of all returned columns before applying changes.
  • Run the original and rewritten query with the same SET options as the application: especially ANSI_NULLS, ANSI_WARNINGS, QUOTED_IDENTIFIER, ARITHABORT and CONCAT_NULL_YIELDS_NULL.
  • Use SET STATISTICS IO ON and SET STATISTICS TIME ON during controlled testing. Compare logical reads, CPU time, elapsed time, returned-row count and result checksum.
  • Apply the high-priority Votes index first, then test the rewritten query. Apply the Posts and Comments indexes only if the post-change plan and measurements show that their scans or lookups remain material.
  • Inspect the actual execution plan after each change for spills, residual predicates, scan counts, lookup counts and actual-to-estimated rows. The current report cannot assess those actual operator values because the last actual plan is unavailable.
  • Allow Query Store to observe repeated executions before considering plan management. Avoid forcing a plan or adding MAXDOP hints while CE feedback and DOP feedback remain eligible.