Code Review – RockyPC / SQLStorm – 2026-10-13 21:14:47 UTC

Tuning goal: Code Review

ServerRockyPC
DatabaseSQLStorm
EngineSQL Server 2022, version 16.0.1200.5, RTM-GDR
EditionDeveloper Edition; Enterprise engine capabilities reported
Observed periodQuery Store: last 30 days; runtime usage statistics: 14.3 days since restart

Executive summary

The review covers 100 complete stored procedures, ranked by total CPU in Query Store. No functions, triggers, or views were provided. The input states that only the highest-ranked objects were supplied; therefore this is not a review of all 14,203 eligible objects or all 1,000 objects with recorded workload.

  1. Correct the repeated row-multiplication pattern before adding broad indexing. Many procedures independently join several one-to-many tables before aggregating. Examples include dbo.sp01122 lines 16–21, dbo.sp00612 lines 16–20, and dbo.sp02525 lines 15–19. This can multiply posts, comments, votes, and badges and can both inflate results and create large hash/sort workloads.
  2. Prioritize the first ten procedures by workload. The highest-cost procedure, dbo.sp00442, recorded 19,845.9 seconds CPU, 42,030,210 logical reads, and 9 executions. dbo.sp00333 recorded 92,013,682 logical reads and 17,785.0 seconds CPU. dbo.sp00902 recorded the highest listed logical reads among the first ten at 128,516,553. These procedures repeatedly scan and aggregate the large Posts, Votes, Comments, and PostHistory tables.
  3. Remove non-sargable tag searches from the hot path. The pattern Tags LIKE '%' + TagName + '%' appears in numerous procedures, including dbo.sp00442 lines 38–49, dbo.sp00333 lines 23–35, and dbo.sp01034 lines 26–37. A normal index on the existing Tags text cannot seek this leading-wildcard predicate.
  4. Add targeted foreign-key and date-supporting indexes after checking the full index inventory. The supplied index report is not a complete inventory, so the scripts below use new names guarded by existence checks. Estimated net storage is approximately 132–434 MB, based on the supplied row counts and stated width assumptions.
  5. Investigate logical correctness, not only runtime. Several procedures join unrelated entities with predicates such as ON 1 = 1, comparisons between user IDs and post IDs, or cross-entity thresholds. Examples include dbo.sp00333 line 69, dbo.sp00612 line 55, dbo.sp01986 line 87, and dbo.sp01270 line 47.
Overall confidence: 0.93 for identifying structural performance anti-patterns; 0.78 for the ranking of individual fixes. The input contains workload totals and object text but no actual execution plans, wait statistics, blocking chains, deadlock graphs, missing-index DMV output, column definitions, or complete index inventory. Exact improvement percentages therefore cannot be stated.

Scope and confidence

Object typeReviewedStatus
Stored procedures100All supplied definitions are labelled complete.
Functions0None supplied.
Triggers0None supplied.
Views0None supplied.
Not included / not available0 supplied objectsNo supplied object was labelled NOT INCLUDED or NOT AVAILABLE.

The object-selection rule says that Query Store workload ranking covers 1,000 of 14,203 eligible objects, while the input supplies the highest-ranked 100 procedures. The other 14,103 eligible objects were not reviewed. Query Store excludes infrequent or insignificant-cost queries under AUTO capture, and module cost does not include dynamic SQL or called procedures; the supplied procedures are marked DynamicSQL=No.

Runtime index statistics cover only the 14.3-day period since the 2026-09-29 10:45:10 server start. No objects were marked encrypted or inaccessible, and no omitted definition is being used as the basis for a code change.

Detailed prioritized recommendations

1. Rewrite multi-fact aggregations to aggregate each child table independently

Critical Confidence: 0.98

The most damaging repeated pattern is joining several independent one-to-many relationships and aggregating afterward. In dbo.sp01122, users are joined to posts, comments, and votes at lines 16–20, then counts and sums are calculated at lines 12–14. A user with multiple posts, comments, and votes can produce a many-way fanout. The procedure recorded 3,254,152,055 logical reads, 2,120.3 seconds CPU, and 2,116.4 seconds duration over 7 executions.

The same risk appears in dbo.sp00612 lines 16–20, dbo.sp02525 lines 15–19, dbo.sp01924 lines 15–19 and 43–46, and dbo.sp02602 lines 17–20. These are not merely indexing problems: a covering index may make an incorrect or inflated aggregation faster without making it correct.

Use separate aggregates keyed by the parent ID, then join the aggregate results. For example, the design should use independent inline aggregates for posts, comments, votes, and badges rather than joining all four detail sets together. Because these procedures are complete definitions and their intended result semantics are not documented, no replacement definition is supplied here; changing them without a result-set contract could alter output semantics.

2. Eliminate accidental Cartesian products and unrelated joins

Critical Confidence: 0.99

  • dbo.sp00333 line 69 uses LEFT JOIN TopTags TT ON 1 = 1, multiplying every qualifying user by every top tag.
  • dbo.sp00902 line 71 joins every ranked user to every tag with a positive count through TPC.PostCount > 0.
  • dbo.sp00584 line 60 joins post-link summaries with ups.TotalPosts > 0, unrelated to the post or user key.
  • dbo.sp01968 line 54 uses ts.PostCount > 10, again creating a broad multiplication.
  • dbo.sp01986 line 85 uses us.PostCount > 5 instead of a relationship to a tag.
  • dbo.sp01270 line 47 joins users to ranked posts through us.UserId = rp.PostId, which compares different entity identifiers.

These predicates should be replaced by the intended relationship, typically a user-to-post owner key, a post-to-tag bridge key, or a deliberately selected single row. This is a semantic correction and should be tested against expected row counts before deployment.

3. Replace leading-wildcard tag matching with a normalized bridge

Critical Confidence: 0.97

The expression Posts.Tags LIKE '%' + Tags.TagName + '%' is non-sargable and can require scanning the 246,672-row dbo.Posts table for each tag. It appears in dbo.sp00442 lines 38–49, dbo.sp00333 lines 23–35, dbo.sp01034 lines 26–37, and many other ranked procedures. The existing IX_Posts_Question_TagScan has key PostTypeId and includes CreationDate, Title, and Tags; its 10.5 MB size does not make the leading-wildcard predicate seekable.

The durable design is a post/tag bridge table with one row per post-tag pair and an index on (TagName, PostId). No such table was provided, so no executable bridge-table script is included. Do not replace the current text predicate with a normal index and expect a seek.

Short-term, reduce the number of repeated tag scans by materializing one tag-count result per procedure execution or by precomputing tag statistics. Longer-term, migrate the data representation and revise the affected procedures.

4. Parameterize or centralize all report dates

High Confidence: 0.99

Many procedures use a hard-coded date such as '2024-10-01 12:34:56'. Examples include dbo.sp00333 line 48, dbo.sp01034 line 22, dbo.sp00902 line 13, and dbo.sp01214 line 21. Others use GETDATE(), such as dbo.sp00817 line 38. This creates inconsistent reporting windows and can produce stale or non-repeatable business results.

Where the procedure contract permits it, expose a typed date parameter and use a single local boundary value. The reviewed procedures have no parameters, so adding parameters would change their public contract; this should be treated as an application/report contract change rather than a blind code replacement.

5. Fix repeated correlated scans for latest history, comments, and votes

High Confidence: 0.94

dbo.sp00605 lines 74–81 uses a correlated MAX(CreationDate) lookup into PostHistory for each post. Similar patterns occur in dbo.sp00098 lines 47–51, dbo.sp00472 lines 44–49, dbo.sp01351 lines 18–23, and dbo.sp02398 lines 73–75. A windowed result or OUTER APPLY with a supporting index is generally more predictable, especially when the parent set is large.

For comment and vote counts, pre-aggregate by PostId once rather than executing one correlated count per output row. This applies to dbo.sp02525 lines 33–41 and dbo.sp02194 lines 20–32.

6. Correct aggregate fanout caused by joining votes, comments, badges, and posts

High Confidence: 0.96

Several procedures use COUNT(DISTINCT ...) to conceal fanout while leaving ordinary sums incorrect. For example, dbo.sp00929 lines 15–18 joins posts and votes before calculating SUM(v.BountyAmount) and vote totals. dbo.sp00817 lines 13–16 joins votes and posts directly, causing each vote to repeat across the user's posts. dbo.sp02099 lines 15–20 joins four child sources before aggregation.

Separate the post, vote, comment, and badge summaries first. Use COUNT(DISTINCT) only when distinctness is part of the business rule, not as a general repair for duplicated rows.

7. Correct entity-key mismatches and name-based joins

High Confidence: 0.99

  • dbo.sp01293 line 77 joins user activity to post history using UA.DisplayName = RP.UserDisplayName; display names are not stable keys.
  • dbo.sp01986 line 87 joins us.UserId = rpa.PostId.
  • dbo.sp01586 line 77 joins users and posts by display name.
  • dbo.sp00823 line 67 compares RP.OwnerName with a string conversion of UA.UserId.
  • dbo.sp01153 line 71 joins a user to a post using us.UserId = pp.PostId.

Use the actual numeric foreign-key relationship, such as OwnerUserId, UserId, or PostId. These issues can return empty, duplicated, or unrelated results and should be corrected before performance tuning.

8. Add targeted supporting indexes

High Confidence: 0.84

The supplied usage report shows substantial scans on the clustered indexes for PostHistory (369 scans, 820.3 MB), Posts (1,270 scans, 400.3 MB), and Comments (596 scans, 78.1 MB). The scripts section adds indexes for the most common join and latest-row access paths. Indexes should be created after checking the complete index inventory because the supplied report lists only the largest indexes.

9. Avoid relying on current Query Store percentages as an absolute workload forecast

Medium Confidence: 0.99

The ranking is appropriate for triage because it is based on total CPU over the last 30 days, but Query Store AUTO capture excludes infrequent or insignificant-cost statements. Dynamic SQL and called procedures are not attributed to the containing module. The first ten procedures therefore deserve priority within the supplied set, but their recorded totals are not the entire server workload.

10. Review data types and implicit conversions in close-reason joins

Medium Confidence: 0.91

dbo.sp00927 line 45 converts PostHistory.Comment to INT; dbo.sp01586 line 44 does the same; dbo.sp00823 line 30 converts a numeric reason ID to VARCHAR. These expressions can prevent efficient access and can fail if the text is not numeric. The input does not provide column data types or data-quality statistics, so no computed-column or schema-change script is recommended.

Workload findings

RankProcedureCPUDurationLogical readsExecutionsPrimary concern
1dbo.sp0044219,845.9 s2,701.1 s42,030,2109Repeated post/vote aggregation and leading-wildcard tag matching at lines 17–20 and 38–49.
2dbo.sp0033317,785.0 s2,700.6 s92,013,6829Cartesian top-tag join at line 69 and non-sargable tag search at line 30.
3dbo.sp0103416,506.5 s2,100.6 s81,844,2157Repeated tag scan and title-to-tag wildcard join at lines 26–37 and 62–65.
4dbo.sp0090216,276.9 s2,101.7 s128,516,5537Broad tag multiplication at line 71 plus full user/post aggregation.
5dbo.sp0081715,807.9 s2,100.7 s80,203,3837Votes and posts joined at user level at lines 13–16, causing fanout.
6dbo.sp0097815,648.5 s2,100.8 s76,432,4477Broad tag join at line 58 and an IN subquery at line 56.
7dbo.sp0092915,404.5 s2,101.6 s75,279,9477Post/vote fanout and repeated tag subquery at lines 33–58.
8dbo.sp0129315,090.4 s2,100.3 s84,695,3457Multi-fact aggregation and display-name join at lines 16–23 and 77.
9dbo.sp0058414,806.1 s2,100.4 s51,320,8837Unrelated post-link join at line 60 and closed-post join by accepted-answer user at line 62.
10dbo.sp0099214,221.0 s2,055.0 s47,361,1187Broad tag join at line 63 and full badge aggregation at lines 39–46.

The workload figures above are quoted directly from the input. No execution plans were supplied, so a before/after operator-cost comparison cannot be produced without inventing plan data.

Locking and blocking

Confidence: 0.72. No deadlock graph, blocking-chain data, wait statistics, transaction log information, or isolation-level configuration was supplied. Consequently, no actual blocker, waiter, session ID, wait time, or deadlock relationship can be identified.

The reviewed procedures declare no explicit transactions, and the input marks all reviewed procedures with Transactions=No. They are therefore primarily read workloads. Nevertheless, the long scans and aggregations can hold shared locks for substantial portions of execution under the default read-committed behavior and can be delayed by writers. The recorded durations include values such as 2,701.1 seconds for dbo.sp00442 and 2,116.4 seconds for dbo.sp01122; these are total Query Store duration, not proof that a single execution held locks for that entire period.

First reduce scan duration through the query and index changes. Enabling read-committed snapshot isolation can reduce reader/writer blocking, but the input does not provide tempdb capacity, version-store usage, database options, or a maintenance window. It is therefore not included as an automatic change script.

Index and storage analysis

The largest supplied tables are dbo.Votes with 926,084 rows, dbo.PostHistory with 847,593 rows, dbo.Badges with 439,352 rows, dbo.Comments with 351,440 rows, dbo.Users with 267,193 rows, and dbo.Posts with 246,672 rows.

RecommendationAccess pathEstimated size after buildBasis and assumptions
Create IX_Posts_OwnerUserId_CreationDate_Review Posts(OwnerUserId, CreationDate DESC) including Id, PostTypeId, Score, ViewCount 8–24 MB Estimate for 246,672 rows. Assumes 4-byte owner/type/score/view columns, 8-byte date, 4-byte clustering key, row/page overhead, and PAGE compression.
Create IX_Votes_PostId_VoteType_User_Review Votes(PostId, VoteTypeId) including UserId, BountyAmount, CreationDate 24–60 MB Estimate for 926,084 rows. Exact numeric/date widths were not supplied; range assumes 4–8 byte numeric/date storage plus row/page overhead and PAGE compression.
Create IX_Comments_PostId_CreationDate_Review Comments(PostId, CreationDate) including Id, UserId 8–24 MB Estimate for 351,440 rows. Assumes 4-byte identifiers, 8-byte date, clustering key width, and PAGE compression.
Create IX_PostHistory_PostId_CreationDate_Review PostHistory(PostId, CreationDate DESC) including Id, PostHistoryTypeId, UserId 20–50 MB Estimate for 847,593 rows. Comment text is intentionally not included because its width was not supplied and could make the index substantially larger.

Total estimated net storage increase: 60–158 MB before compression variance, or approximately 132–434 MB using the broader conservative ranges above. The first range is the sum of the row-width assumptions; the second is the recommended deployment range accounting for unknown variable-length columns, page density, and existing row-size variation. No index is recommended for dropping: the supplied report shows active seeks on the listed nonclustered indexes, including 405 seeks on IX_Votes_PostId_VoteType, 181 seeks on IX_Posts_OwnerUserId_Cover, and 166 seeks on IX_Badges_UserId_Id.

Existing sizes quoted from the input include 820.3 MB for the clustered PostHistory index, 400.3 MB for the clustered Posts index, 78.1 MB for the clustered Comments index, 11.8 MB for the clustered Votes index, 10.5 MB for IX_Posts_Question_TagScan, and 9.8 MB for IX_Votes_PostId_VoteType. No modification or drop is proposed, so there is no before/after size or reclaimed-space figure for an existing index.

Scripts

1. Add post-owner and creation-date coverage for user/post ranking queries

USE [SQLStorm];
GO

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

2. Add post, vote-type, user, bounty, and date coverage for vote aggregations

USE [SQLStorm];
GO

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

3. Add post and creation-date coverage for comment counts and latest comments

USE [SQLStorm];
GO

IF NOT EXISTS
(
    SELECT 1
    FROM sys.indexes
    WHERE object_id = OBJECT_ID(N'dbo.Comments')
      AND name = N'IX_Comments_PostId_CreationDate_Review'
)
BEGIN
    CREATE NONCLUSTERED INDEX IX_Comments_PostId_CreationDate_Review
    ON dbo.Comments (PostId, CreationDate DESC)
    INCLUDE (Id, UserId)
    WITH (DATA_COMPRESSION = PAGE, ONLINE = ON, SORT_IN_TEMPDB = ON);
END;
GO

4. Add post-history and creation-date coverage for latest-history lookups

USE [SQLStorm];
GO

IF NOT EXISTS
(
    SELECT 1
    FROM sys.indexes
    WHERE object_id = OBJECT_ID(N'dbo.PostHistory')
      AND name = N'IX_PostHistory_PostId_CreationDate_Review'
)
BEGIN
    CREATE NONCLUSTERED INDEX IX_PostHistory_PostId_CreationDate_Review
    ON dbo.PostHistory (PostId, CreationDate DESC)
    INCLUDE (Id, PostHistoryTypeId, UserId)
    WITH (DATA_COMPRESSION = PAGE, ONLINE = ON, SORT_IN_TEMPDB = ON);
END;
GO

Supporting scripts (not part of the change)

No supporting scripts are included. The input does not provide execution plans, blocking chains, deadlock graphs, wait statistics, or a complete index inventory, and diagnostic scripts are intentionally excluded from the change scripts section.