SQL Server Code Review – RockyPC / SQLStorm – 2026-09-20

Executive summary

The highest-confidence risks are avoidable locking and transaction duration in dbo.usp_UpdateTablesWithDelay, incorrect or explosive join logic in many reporting procedures, and missing supporting indexes on foreign-key and ranking columns. The supplied workload is small in execution count for most objects, so recommendations are based primarily on deterministic code defects, table size, and the reported scan-heavy index usage rather than measured duration or execution-plan evidence.

Review scope and confidence

• 100 stored procedures reviewed.
• 0 functions, 0 triggers, and 0 views were provided.
• 8 tables over 1,000 rows were reported: Votes, PostHistory, Badges, Comments, Users, Posts, PostLinks, and Tags.
• 12 reported clustered indexes were reviewed from the index usage and size summary.
• Only the most recent objects and summaries provided in the input were reviewed; this is not a complete database inventory.
• No actual execution plans, wait statistics, deadlock graphs, blocking-chain samples, missing-index DMV candidates, or Query Store duration/CPU/IO metrics were supplied.

Detailed prioritized recommendations

1. Remove the five-second wait from the open transaction. P1 Confidence: 99%

   dbo.usp_UpdateTablesWithDelay starts a transaction, updates dbo.Users, waits five seconds, and only then updates dbo.Posts. Locks acquired by the first update can remain held for the entire delay. The revised definition in the Scripts section preserves the parameters and successful result columns while eliminating the deliberate lock hold.

2. Correct the active ranking procedure and remove its invalid join. P1 Confidence: 99%

   dbo.sp00060 joins TopUsers.UserId to RankedPosts.PostId, which is a key-domain error. Its tag predicate p.Tags LIKE '%' + t.TagName + '%' is non-sargable and can match partial tag names. The revised procedure keeps the same output columns and order, returns the latest question for each ranked contributor, and removes the invalid tag join.

3. Pre-aggregate each one-to-many relationship before joining. P1 Confidence: 98%

   Procedures including dbo.sp09991, dbo.sp09950, dbo.sp09941, dbo.sp09908, dbo.sp09814, and many related reports join posts, comments, votes, badges, or history in the same aggregation. A post with 10 comments and 20 votes can produce 200 intermediate rows before aggregation. The safer pattern is separate aggregates by key, followed by one-to-one joins. This is a code-review standard recommendation across the supplied reporting set.

4. Eliminate accidental Cartesian joins and joins by display name. P1 Confidence: 99%

   Examples include dbo.sp09990, dbo.sp09952, dbo.sp09895, dbo.sp09881, dbo.sp09878, dbo.sp09860, and dbo.sp09855. Predicates such as ON UA.PostsChanged > 0, ON 1=1, and joins from DisplayName to DisplayName can multiply rows or associate unrelated people. Use stable key joins such as UserId, OwnerUserId, and PostId.

5. Add targeted nonclustered indexes for the dominant access paths. P2 Confidence: 94%

   The supplied usage summary shows substantial scans on clustered primary keys, including 3,220 scans on dbo.Posts, 1,606 on dbo.Votes, 1,102 on dbo.Comments, 904 on dbo.Badges, and 672 on dbo.PostHistory. The scripts add indexes for owner-based post lookups, vote aggregation, comment aggregation, badge aggregation, and history lookups.

6. Use deterministic date boundaries and avoid hard-coded historical timestamps. P2 Confidence: 97%

   Many procedures use constants such as '2024-10-01 12:34:56' for “recent” reporting. This makes results stale and may prevent a report from representing its stated period. Prefer a parameter, a clearly documented fixed reporting date, or a single statement-start value such as DATEADD(DAY, -30, CONVERT(datetime2, SYSUTCDATETIME())). Use half-open ranges where possible: CreationDate >= @StartDate AND CreationDate < @EndDate.

7. Replace non-sargable tag searches with normalized tag relationships. P2 Confidence: 96%

   Expressions such as Tags LIKE '%' + TagName + '%', Tags LIKE '%<' + TagName + '>%', and repeated STRING_SPLIT calls cannot use an ordinary B-tree index efficiently. The durable design is a junction table such as PostTags(PostId, TagId) maintained at write time. No helper table is scripted because that table and its columns were not present in the supplied input, and executable recommendations are restricted to supplied objects.

8. Prioritize active objects and retire unused test procedures. P2 Confidence: 99%

   Only dbo.sp00060, dbo.sp00060Fixed, and dbo.sp00996, dbo.sp00992, dbo.sp00990, dbo.sp00987, and dbo.sp00099 show six executions in the supplied 30-day Query Store summary. Most other procedures show zero executions. Do not add indexes solely for an unexecuted report; first classify those procedures as retained, deprecated, or test-only.

Locking and blocking analysis

• SET XACT_ABORT ON is appropriate, but it does not make a five-second wait safe inside the transaction.
• The procedure updates by primary key, so the individual row access is selective; the problem is transaction duration, not the key lookup itself.
• Long reporting scans can acquire and retain shared locks long enough to interfere with these updates under the default read-committed isolation level.
• No blocking-chain data or deadlock graph was provided; therefore no specific session-to-session blocking diagram can be inferred safely.

Indexing the foreign-key lookup columns should reduce scan duration and the time reporting statements hold shared locks. Indexing will not compensate for an intentional wait inside a transaction.

Query and T-SQL patterns

Index recommendations

All new indexes below use DATA_COMPRESSION = PAGE, available on the reported Enterprise engine edition. The estimates are ranges because the input does not provide column data types, nullability, average variable-length widths, page density, or existing nonclustered indexes.

Existing index observations

• dbo.PostHistory clustered primary key: 820.3 MB, 672 scans, no seeks. The size is not itself evidence that it should be dropped; the index is the table’s clustered storage and supports the primary key.
• dbo.Posts clustered primary key: 400.3 MB, 3,220 scans and 288 seeks. This is the strongest signal for a covering/access-path index rather than a clustered-key change.
• dbo.Comments, dbo.Votes, and dbo.Badges clustered primary keys have scans but no reported seeks. The proposed foreign-key indexes target the access predicates causing those scans.
• No index is recommended for dropping or modifying. Therefore, dropped-index reclamation is 0 MB, and modified-index before/after/net estimates are not applicable.

Object-specific findings

• dbo.sp00060Fixed: substantially safer than dbo.sp00060 because it separates post, vote, and badge aggregates and uses OUTER APPLY. It should still be reviewed for its business definition: it returns one latest question per user, not one latest post per tag.
• dbo.usp_UpdateTablesInLoop: uses ORDER BY NEWID() against Users and Posts, requiring broad randomization and sorting. It is unused in the last 30 days and appears test-oriented. Avoid scheduling it in production.
• dbo.sp09995: uses a fixed historical date and aggregates votes after ranking recent posts. Its “recent” result will become stale as time advances.
• dbo.sp09990 and dbo.sp09982: contain joins whose predicates do not relate the intended entities, creating likely Cartesian multiplication.
• dbo.sp09978: the expression DATEADD(YEAR, 1, 0) is subtracted from a date to represent a year; use an explicit date boundary to make intent and datatype behavior clear.
• dbo.sp00990: joins RankedPosts.PostId to UserReputation.UserId, another key-domain mismatch analogous to sp00060.
• dbo.sp00099: selects the badge row at the maximum date but can return multiple rows when dates tie. Add a deterministic TOP (1) ordering if this object is retained.
• Unused procedures: the zero-execution status is not proof that an object is safe to delete, but it is strong evidence to avoid tuning it before confirming ownership and retention requirements.

Scripts

Add compressed supporting indexes for the recommended access paths

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'
)
BEGIN
    CREATE NONCLUSTERED INDEX IX_Posts_OwnerUserId_PostTypeId_CreationDate
    ON dbo.Posts (OwnerUserId, PostTypeId, CreationDate DESC, Id DESC)
    WITH (ONLINE = 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 NONCLUSTERED INDEX IX_Votes_PostId_VoteTypeId
    ON dbo.Votes (PostId, VoteTypeId)
    WITH (ONLINE = 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 NONCLUSTERED INDEX IX_Comments_PostId
    ON dbo.Comments (PostId)
    WITH (ONLINE = 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'
)
BEGIN
    CREATE NONCLUSTERED INDEX IX_Badges_UserId
    ON dbo.Badges (UserId)
    WITH (ONLINE = 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_CreationDate'
)
BEGIN
    CREATE NONCLUSTERED INDEX IX_PostHistory_PostId_CreationDate
    ON dbo.PostHistory (PostId, CreationDate DESC, Id DESC)
    WITH (ONLINE = ON, DATA_COMPRESSION = PAGE);
END;
GO

Replace dbo.sp00060 with a correct, deterministic contributor report

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

Replace dbo.usp_UpdateTablesWithDelay without waiting inside the transaction

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;

    BEGIN TRY
        BEGIN TRANSACTION;

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

        DECLARE @RowsAffected1 INT = @@ROWCOUNT;

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

        DECLARE @RowsAffected2 INT = @@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

Supporting scripts (not part of the change)

No executable diagnostic, validation, rollback, or measurement scripts are included. The supplied input did not contain execution plans, wait samples, blocking chains, deadlock graphs, or complete nonclustered-index metadata, and the requested Scripts section is reserved for implementation scripts.