Code Review – RockyPC / SQLStorm – 2026-05-31

Executive summary

1. Top priority: Correct logic and cardinality errors before tuning indexes. Several procedures contain incorrect joins or row-multiplying patterns that can return wrong results and waste CPU and I/O. Highest-risk objects: dbo.sp00060, dbo.sp09982, dbo.sp09990, dbo.sp09974.
   Confidence: 98%
2. Reduce lock duration immediately in dbo.usp_UpdateTablesWithDelay. It holds an explicit transaction open across WAITFOR DELAY '00:00:05', which is a direct blocking and deadlock risk.
   Confidence: 99%
3. Add a small set of targeted nonclustered indexes to support the repeated access patterns on Posts, Votes, Badges, Comments, and PostHistory. The current usage summary shows heavy clustered scans on the largest tables.
   Confidence: 94%
4. Standardize aggregation strategy by pre-aggregating each child table separately before joining. Many procedures aggregate across Posts, Votes, Comments, and Badges in a single query, which overcounts and inflates work.
   Confidence: 96%
5. Remove non-SARGable tag matching and stale literal date filters. LIKE '%' + TagName + '%' on Posts.Tags forces scans and can match incorrect tags. Hard-coded 2024 dates make logic stale and estimates less reliable.
   Confidence: 93%
6. Retire or quarantine low-value unused procedures. Most reviewed procedures had zero Query Store executions in the last 30 days and appear to be generated test patterns or demos rather than production-grade routines.
   Confidence: 88%

Scope and objects reviewed

Detailed prioritized recommendations

1. Replace or retire dbo.sp00060; keep the corrected pattern from dbo.sp00060Fixed

   • dbo.sp00060 has a probable join bug: LEFT JOIN RankedPosts rp ON tp.UserId = rp.PostId. That joins a user ID to a post ID.
   • It also uses Posts.Tags LIKE '%' + Tags.TagName + '%', which is non-SARGable, scan-heavy, and can produce false positives.
   • dbo.sp00060Fixed is materially better because it pre-aggregates posts, votes, and badges separately and gets the latest post with OUTER APPLY TOP (1).
   • Recommendation: replace callers of dbo.sp00060 with dbo.sp00060Fixed logic or alter dbo.sp00060 to the corrected implementation.

   Objects: dbo.sp00060, dbo.sp00060Fixed. Confidence: 99%.

2. Move WAITFOR outside the transaction in dbo.usp_UpdateTablesWithDelay

   • The current code updates dbo.Users, then waits 5 seconds, then updates dbo.Posts, all inside one explicit transaction.
   • This guarantees unnecessary lock retention on Users and raises the chance of blocking chains, deadlocks, and log flush delays.
   • For a test harness, delay should occur before the transaction or between separate autonomous operations, not while locks are held.
   • dbo.usp_UpdateTablesInLoop amplifies the problem by repeatedly calling it and using ORDER BY NEWID(), which scans large tables.

   Objects: dbo.usp_UpdateTablesWithDelay, dbo.usp_UpdateTablesInLoop. Confidence: 99%.

3. Fix fanout aggregation anti-patterns across posts, votes, badges, and comments

   • Common pattern: join parent to multiple one-to-many tables, then aggregate in one pass. This multiplies rows and inflates counts and sums.
   • Examples with likely overcount risk:
     • dbo.sp09991
     • dbo.sp09972
     • dbo.sp09977
     • dbo.sp09994
     • dbo.sp09964
     • dbo.sp00060
   • Preferred pattern: aggregate each child table by key first, then join the aggregated results.
   • This improves correctness first, then performance.

   Confidence: 96%.

4. Correct high-risk join logic that can produce invalid result sets

   • dbo.sp09982: JOIN PostSummary ps ON us.UserId = ps.PostId is almost certainly wrong.
   • dbo.sp09990: LEFT JOIN PopularTags UTC ON UA.PostsChanged > 0 is effectively a conditional cross join for users with activity.
   • dbo.sp09974: CROSS JOIN PopularPosts multiplies every qualifying user by every top post, which is usually not intended.
   • dbo.sp09971 and dbo.sp09955: ROW_NUMBER() OVER (PARTITION BY p.Id ...) or equivalent per unique key is redundant and hides inefficiency.

   Confidence: 97%.

5. Add targeted covering indexes for the dominant access paths

   • The largest tables are Votes (926k), PostHistory (848k), Badges (439k), Comments (351k), Users (267k), and Posts (247k).
   • Usage summary shows extensive scans on clustered PKs, especially:
     • Posts clustered index scans: 2342
     • Users clustered index scans: 1650
     • Votes clustered index scans: 1173
     • Comments clustered index scans: 793
     • Badges clustered index scans: 648
     • PostHistory clustered index scans: 476
   • Recommended first-wave indexes:
     • Posts (OwnerUserId, PostTypeId, CreationDate DESC, Id DESC) include common output columns
     • Votes (PostId, VoteTypeId)
     • Badges (UserId, Class)
     • Comments (PostId) and optionally Comments (UserId) for correlated comment counts
     • PostHistory (PostId, PostHistoryTypeId, CreationDate DESC)
   • Because this is SQL Server 2022 Enterprise-capable, ONLINE = ON and DATA_COMPRESSION = PAGE are valid options for these additions.

   Confidence: 94%.

6. Replace correlated scalar subqueries and repeated per-row subqueries

   • Examples:
     • dbo.sp09974 uses multiple scalar subqueries for per-user counts and per-post vote counts.
     • dbo.sp09967 uses STRING_AGG in a per-row correlated subquery.
     • dbo.sp00996 uses a per-user correlated comment count.
     • dbo.sp09978 uses a per-row correlated duplicate count on PostLinks.
   • Rewrite with pre-aggregated CTEs or temp tables keyed once per join key.
   • This reduces repeated scans and stabilizes memory grants.

   Confidence: 92%.

7. Remove or redesign non-SARGable tag searches

   • dbo.sp00060, dbo.sp09990, and dbo.sp09982 use tag matching against Posts.Tags with leading wildcard patterns.
   • This prevents index seeks and is also semantically weak because plain substring matching can confuse similar tags.
   • Long-term fix: normalize post-tag relationships into a bridge table such as PostTags(PostId, TagId).
   • Short-term fix: avoid tag expansion unless truly required; if required, stage tags outside the hot query path.

   Confidence: 95%.

8. Remove stale literal dates and use parameters or current-date logic

   • Many procedures hard-code '2024-10-01 12:34:56'. Examples: dbo.sp09995, dbo.sp09994, dbo.sp09993, dbo.sp09982, dbo.sp09978, dbo.sp09974, dbo.sp09972, dbo.sp09964, dbo.sp09963, dbo.sp09954.
   • That makes the logic age badly and undermines cardinality accuracy as data distribution changes.
   • Use input parameters or expressions such as DATEADD(YEAR, -1, SYSUTCDATETIME()).

   Confidence: 90%.

9. Prefer temp tables for reusable intermediate top-N sets when estimates are unstable

   • Several procedures chain multiple CTEs and then sort, rank, and rejoin large sets.
   • For larger executions, materializing selective sets into temp tables with supporting temp indexes can outperform repeated CTE expansion.
   • This is especially relevant for dbo.sp09994, dbo.sp09995, dbo.sp09955, and dbo.sp09960.

   Confidence: 78%.

10. Deprecate or isolate low-value unused procedures

    • 22 of 25 reviewed procedures show no Query Store executions in the last 30 days.
    • Many of these share similar synthetic naming and repetitive patterns, suggesting generated or experimental code.
    • Retiring them reduces review surface, plan cache churn, accidental misuse, and maintenance overhead.

    Confidence: 88%.

Object-specific findings

Locking visual

Index and table observations

Scripts

Script 1: Replace dbo.sp00060 with the corrected implementation from Recommendation 1

USE [SQLStorm];
GO
CREATE OR ALTER PROCEDURE dbo.sp00060
AS
BEGIN
    SET NOCOUNT ON;

    ;WITH PostCounts AS
    (
        SELECT
            p.OwnerUserId AS UserId,
            COUNT_BIG(*) AS TotalPosts
        FROM dbo.Posts AS p
        WHERE p.OwnerUserId IS NOT NULL
        GROUP BY p.OwnerUserId
    ),
    VoteCounts AS
    (
        SELECT
            p.OwnerUserId AS UserId,
            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
        FROM dbo.Posts AS p
        LEFT JOIN dbo.Votes AS v
            ON v.PostId = p.Id
           AND v.VoteTypeId IN (2,3)
        WHERE p.OwnerUserId IS NOT NULL
        GROUP BY p.OwnerUserId
    ),
    BadgeCounts AS
    (
        SELECT
            b.UserId,
            COUNT_BIG(*) AS BadgeCount
        FROM dbo.Badges AS b
        GROUP BY b.UserId
    ),
    TopUsers AS
    (
        SELECT
            u.Id AS UserId,
            u.DisplayName,
            CONVERT(int, ISNULL(pc.TotalPosts, 0)) AS TotalPosts,
            CONVERT(int, ISNULL(vc.UpVotes, 0)) AS UpVotes,
            CONVERT(int, ISNULL(vc.DownVotes, 0)) AS DownVotes,
            CONVERT(int, ISNULL(bc.BadgeCount, 0)) AS BadgeCount,
            DENSE_RANK() OVER
            (
                ORDER BY
                    ISNULL(pc.TotalPosts, 0) DESC,
                    ISNULL(vc.UpVotes, 0) - ISNULL(vc.DownVotes, 0) DESC
            ) AS UserRank
        FROM dbo.Users AS u
        LEFT JOIN PostCounts AS pc
            ON pc.UserId = u.Id
        LEFT JOIN VoteCounts AS vc
            ON vc.UserId = u.Id
        LEFT JOIN BadgeCounts AS bc
            ON bc.UserId = u.Id
        WHERE ISNULL(pc.TotalPosts, 0) > 10
    )
    SELECT
        tu.UserRank,
        tu.DisplayName,
        tu.TotalPosts,
        tu.UpVotes,
        tu.DownVotes,
        tu.BadgeCount,
        lp.Title AS LatestPostTitle,
        lp.CreationDate AS LatestPostDate,
        CASE WHEN lp.PostId IS NULL THEN NULL ELSE 'Latest' END AS PostStatus
    FROM TopUsers AS tu
    OUTER APPLY
    (
        SELECT TOP (1)
            p.Id AS PostId,
            p.Title,
            p.CreationDate
        FROM dbo.Posts AS p
        WHERE p.OwnerUserId = tu.UserId
          AND p.PostTypeId = 1
        ORDER BY p.CreationDate DESC, p.Id DESC
    ) AS lp
    ORDER BY tu.UserRank, lp.CreationDate DESC;
END;
GO

Script 2: Shorten lock duration in dbo.usp_UpdateTablesWithDelay for Recommendation 2

USE [SQLStorm];
GO
CREATE OR ALTER PROCEDURE dbo.usp_UpdateTablesWithDelay
    @UserId INT,
    @NewReputation INT,
    @PostId INT,
    @NewScore INT
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    DECLARE @RowsAffected1 INT = 0;
    DECLARE @RowsAffected2 INT = 0;

    BEGIN TRY
        -- Keep any test delay outside the transaction to avoid holding locks unnecessarily.
        WAITFOR DELAY '00:00:05';

        BEGIN TRANSACTION;

        UPDATE dbo.Users
        SET Reputation = @NewReputation,
            LastAccessDate = GETDATE()
        WHERE Id = @UserId;

        SET @RowsAffected1 = @@ROWCOUNT;

        UPDATE dbo.Posts
        SET Score = @NewScore,
            LastActivityDate = GETDATE()
        WHERE Id = @PostId;

        SET @RowsAffected2 = @@ROWCOUNT;

        COMMIT TRANSACTION;

        SELECT
            'Success' AS Status,
            'Both tables updated successfully' AS Message,
            @RowsAffected1 AS UsersRowsAffected,
            @RowsAffected2 AS PostsRowsAffected;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;

        SELECT
            'Error' AS Status,
            ERROR_NUMBER() AS ErrorNumber,
            ERROR_MESSAGE() AS ErrorMessage,
            ERROR_LINE() AS ErrorLine,
            ERROR_PROCEDURE() AS ErrorProcedure;

        THROW;
    END CATCH
END;
GO

Script 3: Create first-wave supporting indexes for Recommendations 3 and 5

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_PostTypeId_CreationDate_Id'
)
BEGIN
    CREATE INDEX IX_Posts_OwnerUserId_PostTypeId_CreationDate_Id
    ON dbo.Posts (OwnerUserId, PostTypeId, CreationDate DESC, Id DESC)
    INCLUDE (Title, Score, ViewCount, AnswerCount, CommentCount, LastActivityDate, AcceptedAnswerId)
    WITH (ONLINE = ON, SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);
END
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 INDEX IX_Votes_PostId_VoteTypeId
    ON dbo.Votes (PostId, VoteTypeId)
    WITH (ONLINE = ON, SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);
END
GO

IF NOT EXISTS
(
    SELECT 1
    FROM sys.indexes
    WHERE object_id = OBJECT_ID(N'dbo.Badges')
      AND name = N'IX_Badges_UserId_Class'
)
BEGIN
    CREATE INDEX IX_Badges_UserId_Class
    ON dbo.Badges (UserId, Class)
    WITH (ONLINE = ON, SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);
END
GO

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

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

Script 4: Add an optional user-centric comments index for Recommendation 6 where comment-by-user counts matter

USE [SQLStorm];
GO
IF NOT EXISTS
(
    SELECT 1
    FROM sys.indexes
    WHERE object_id = OBJECT_ID(N'dbo.Comments')
      AND name = N'IX_Comments_UserId'
)
BEGIN
    CREATE INDEX IX_Comments_UserId
    ON dbo.Comments (UserId)
    INCLUDE (PostId, Score, CreationDate)
    WITH (ONLINE = ON, SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);
END
GO