SQL Code Review – RockyPC / SQLStorm – 2026-05-16

Executive Summary

This review covers a large library of analytic stored procedures over a Stack-Exchange-style schema (Posts, Users, Votes, Badges, Comments, PostHistory). The dominant tables are large (Votes 926K, PostHistory 848K, Badges 439K, Comments 351K, Users 267K, Posts 247K) yet the index usage summary shows only the clustered PKs on the identity column — there are no visible nonclustered indexes on the foreign-key / filter columns the procedures repeatedly use (OwnerUserId, PostId, UserId, PostTypeId, CreationDate, VoteTypeId, Class). This is the single biggest performance lever.

Top Priorities

1. CRITICAL  Add foreign-key/filter nonclustered indexes (Posts.OwnerUserId, Votes.PostId, Votes.UserId, Badges.UserId, Comments.PostId, Comments.UserId, PostHistory.PostId, Posts.ParentId, Posts.PostTypeId+CreationDate). Almost every procedure joins/groups on these.
2. CRITICAL  Eliminate the Posts.Tags LIKE '%' + t.TagName + '%' pattern (sp00060, sp09990, sp09982, sp00992, sp09881, sp09886, sp09915). It is non-sargable, returns false positives, and forces a Cartesian-style scan of Posts × Tags. Normalize via a PostTags table or use STRING_SPLIT with a join to Tags.
3. CRITICAL  Remove the long-held transaction with WAITFOR DELAY '00:00:05' inside dbo.usp_UpdateTablesWithDelay. This holds X locks on Users and Posts for 5+ seconds per call and will cause severe blocking under load.
4. HIGH  Replace many-CTE "join-everything-then-filter" patterns (e.g., sp00060, sp09990, sp09895, sp09886, sp09836-style procs) with OUTER APPLY / TOP N patterns as demonstrated in sp00060Fixed. The fixed version is the template — adopt it broadly.
5. HIGH  Fix multiple logic bugs: DATEADD(YEAR,1,0) arithmetic (sp09978, sp09921, sp09844, sp09905, sp09923, sp09844, sp09826, sp09841) computes nonsense dates; join conditions like ON tp.UpVotes > 100 AND b.UserId = (SELECT OwnerUserId ...) (sp09994) and JOIN PopularTags ON ups.PostCount > 5 (sp00992) produce Cartesian results; sp09826 joins Users ON tp.PostId = u.Id (joins post IDs to user IDs).
6. HIGH  Stop using ORDER BY NEWID() against multi-hundred-thousand row tables in usp_UpdateTablesInLoop — full sort of Users + Posts each iteration.
7. MEDIUM  Adopt consistent two-part naming (dbo.Posts), SET XACT_ABORT ON, schema-qualified CREATE OR ALTER PROCEDURE dbo.xxx, and remove use of reserved word Rank as a column alias.

Review Scope & Counts

Confidence in this scope: HIGH for the procedures supplied. The absence of nonclustered indexes in the supplied usage summary is taken at face value; if other indexes exist but were not surfaced, some "missing index" recommendations may already be satisfied.

Detailed Prioritized Recommendations

1. 1. Create Foundational Nonclustered Indexes CRITICAL Confidence: HIGH

   Every single analytic procedure in the batch joins/aggregates on the same handful of columns. With only the identity-column clustered PKs in place, each call must scan the entire Posts (398 MB), PostHistory (820 MB), and Votes heaps when joining. Recommended indexes (see Scripts):

   • Posts (OwnerUserId) INCLUDE (PostTypeId, Score, ViewCount, CreationDate, Title)
   • Posts (PostTypeId, CreationDate) INCLUDE (OwnerUserId, Score, ViewCount, Title)
   • Posts (ParentId) INCLUDE (PostTypeId) filtered WHERE ParentId IS NOT NULL
   • Votes (PostId, VoteTypeId)
   • Votes (UserId, VoteTypeId)
   • Badges (UserId) INCLUDE (Class, Name, Date)
   • Comments (PostId) INCLUDE (UserId, Score, CreationDate)
   • Comments (UserId)
   • PostHistory (PostId, PostHistoryTypeId, CreationDate) INCLUDE (UserId)
   • PostHistory (UserId, CreationDate)
   • Users (Reputation DESC) INCLUDE (DisplayName)
   • PostLinks (PostId, LinkTypeId) INCLUDE (RelatedPostId)

   Create with ONLINE = ON (Enterprise/Developer supports it) and DATA_COMPRESSION = PAGE — wide narrow row tables here benefit substantially.

2. 2. Eliminate LIKE '%' + Tag + '%' on Posts.Tags CRITICAL HIGH

   Found in sp00060, sp09990, sp09982, sp00992, sp09881, sp09886, sp09915. Issues:

   • Non-sargable — forces full Posts scan for each Tag row.
   • Incorrect — '%c%' matches 'c', 'c++', 'csharp', etc.
   • O(Posts × Tags) explosion (247K × 1232 = ~304M comparisons just to build the join).

   Preferred fix: normalize tags into dbo.PostTags(PostId, TagId) with PKs and an index on (TagId, PostId). Interim fix: parse on read with STRING_SPLIT(REPLACE(REPLACE(p.Tags,'<',''),'>',','), ',') and join to Tags by exact match (still expensive but correct). See script Build PostTags normalization.

3. 3. Fix the Long-Held Transaction in usp_UpdateTablesWithDelay CRITICAL HIGH

   The procedure opens an explicit transaction, updates Users, then issues WAITFOR DELAY '00:00:05' inside the transaction, then updates Posts. During those 5 seconds, X locks (and key range locks under serializable, or U→X lock conversions) are held on both rows. Worse, usp_UpdateTablesInLoop calls this repeatedly with random IDs. Under any concurrent load, this guarantees blocking and likely deadlocks.

   Recommendations:

   • Remove the WAITFOR from inside the transaction entirely. If delay simulation is needed, do it before BEGIN TRAN or after COMMIT.
   • Update rows in primary-key order across callers to reduce deadlock risk.
   • Consider SET LOCK_TIMEOUT on the loop driver to fail fast.
   • Replace PRINT-heavy diagnostics with optional RAISERROR(... ,0,1) WITH NOWAIT only when a debug flag is set.

4. 4. Stop ORDER BY NEWID() on Large Tables HIGH HIGH

   usp_UpdateTablesInLoop uses SELECT TOP 1 @Id = Id FROM Users ORDER BY NEWID() and the same on Posts — each iteration computes NEWID() for 267K (Users) and 247K (Posts) rows, then sorts them. Replace with sampling on the clustered index:

   -- Cheap pseudo-random pick
   SELECT TOP (1) @RandomUserId = Id
   FROM dbo.Users TABLESAMPLE SYSTEM (1 PERCENT)
   ORDER BY (SELECT NULL);
   -- Fallback if 0 rows: pick by Id range
   IF @RandomUserId IS NULL
     SELECT @RandomUserId = Id FROM dbo.Users WHERE Id =
       (ABS(CHECKSUM(NEWID())) % (SELECT MAX(Id) FROM dbo.Users)) + 1;

5. 5. Replace "Join-Everything CTEs" with OUTER APPLY / TOP N HIGH HIGH

   sp00060Fixed is the canonical template and should be applied to nearly every other proc in the batch. Key transformations:

   • Pre-aggregate Posts, Votes, Badges, Comments separately by UserId — never join Posts→Votes→Badges in one CTE (the Cartesian fan-out triples and quadruples counts; e.g., sp09991, sp09941, sp09851, sp09812, sp09877 all over-count because each user's badges multiply through the Posts→Votes join).
   • For "latest post per user/tag/type" use OUTER APPLY (SELECT TOP (1) … ORDER BY CreationDate DESC) instead of ROW_NUMBER() over the whole table.
   • Push TOP/OFFSET–FETCH as close to the leaf as possible, not in a wrapper CTE after a full aggregation.
   Example of the over-count bug (present in sp09991 and many siblings): joining Users LEFT JOIN Posts LEFT JOIN Votes and also LEFT JOIN Badges in the same CTE causes BadgeCount and Upvotes to be multiplied. Splitting into per-entity aggregates as in sp00060Fixed fixes both correctness and performance.

6. 6. Fix Date Arithmetic Bugs HIGH HIGH

   Multiple procs use CAST('2024-10-01 12:34:56' AS DATETIME) - DATEADD(YEAR, 1, 0). DATEADD(YEAR, 1, 0) returns 1901-01-01, so the expression subtracts ~123 years of ticks rather than 1 year. Affected: sp09978, sp09921, sp09844, sp09905, sp09923, sp09826, sp00990, sp09963. Replace with:

   WHERE p.CreationDate >= DATEADD(YEAR, -1, CAST('2024-10-01 12:34:56' AS datetime2))

   Also: sp09971 and sp09889 use GETDATE() directly inside a SARG which is fine in 2022 but consider converting via a local DECLARE @AsOf datetime2 = SYSDATETIME(); for plan reuse and Parameter Sensitivity Plan optimization.

7. 7. Repair Outright Join Bugs HIGH HIGH

   • sp09994: LEFT JOIN Badges b ON tp.UpVotes > 100 AND b.UserId = (SELECT OwnerUserId FROM Posts WHERE Id = tp.PostId) — produces a Cartesian product for every post with UpVotes>100. Should be: derive OwnerUserId in the CTE then LEFT JOIN Badges b ON b.UserId = rp.OwnerUserId (with a deterministic TOP 1 selection).
   • sp09826: JOIN Users u ON tp.PostId = u.Id — joins PostId to UserId. Should be tp.OwnerUserId = u.Id (carry OwnerUserId through CTE).
   • sp00992: LEFT JOIN PopularTags p ON ups.PostCount > 5 — Cartesian.
   • sp09881: CROSS JOIN PopularTags then OFFSET 100 — Cartesian explosion before paging.
   • sp09974: CROSS JOIN PopularPosts — every user × every popular post.
   • sp09833: JOIN PostTypes pt ON rp.PostId IS NOT NULL — Cartesian.
   • sp09886: JOIN TagPostDetails T ON A.DisplayName = T.Tag — joining a username to a tag string is almost certainly wrong.
   • sp09895: re-runs the entire PopularPosts query inside a CROSS JOIN derived table instead of referencing the CTE.
   • sp09836-style (sp09911, sp09905, sp09891, sp09836): joining Badges b ON b.UserId = (SELECT OwnerUserId FROM Posts WHERE Id = …) — non-deterministic and slow; pull OwnerUserId through the CTE.

8. 8. Replace Correlated Subqueries with Pre-Aggregates HIGH HIGH

   sp09974 uses four correlated SELECT COUNT(*) FROM … per row. sp09946, sp09940, sp09854, sp09904, sp09831, sp09815 do similar. With no supporting indexes today these become per-row scans. Refactor into a single pre-aggregated CTE and LEFT JOIN on UserId / PostId.

9. 9. ROW_NUMBER() OVER (PARTITION BY p.Id ORDER BY …) is Pointless MEDIUM HIGH

   sp09971 and sp09955 partition by the primary key p.Id — every partition has exactly 1 row, so rn is always 1. The CTE materializes a sort for no reason. Either remove the windowed filter entirely, or partition by a meaningful column (e.g., OwnerUserId).

10. 10. Avoid Reserved/Confusing Identifiers MEDIUM HIGH

    Many procs alias windowed positions as Rank, which is also a T-SQL function name. While it parses, it is brittle and harms readability. Use RankNo, RowNo, or PostRank. Also avoid Rank/VoteCount shadowing within nested CTEs.

11. 11. Standardize Procedure Header & Error Handling MEDIUM HIGH

    Inconsistent across the batch. Adopt:

    CREATE OR ALTER PROCEDURE dbo.spXXXX
    AS
    BEGIN
      SET NOCOUNT ON;
      SET XACT_ABORT ON;
      SET ANSI_WARNINGS ON;
      SET ARITHABORT ON;
      ...
    END
    GO

    Schema-qualify every object reference (dbo.Posts, not Posts) — saves an implicit name-resolution lookup and improves plan-cache reuse.

12. 12. Mind AVG on int Columns MEDIUM HIGH

    AVG(p.Score), AVG(p.ViewCount), AVG(b.Class), etc., return integer when the column is int (truncation). Use AVG(CAST(p.Score AS decimal(18,4))) or AVG(p.Score * 1.0) where business meaning is fractional. Affected: sp09972, sp09950, sp09894, sp09909, sp09833, sp09877, sp09874, sp09829, sp09823.

13. 13. Replace STRING_AGG Over Unbounded Sets MEDIUM HIGH

    sp09967 emits STRING_AGG(P.Title, '; ') over all a user's posts (potentially thousands). sp09908 aggregates voter info similarly. Risks: 8000-byte truncation for non-MAX, large memory grants, and unbounded result sizes. Cap with STRING_AGG(... ) WITHIN GROUP (ORDER BY …) on a pre-TOP (N)-limited derived table.

14. 14. Reduce Plan-Cache Pollution & Leverage 2022 Features LOW MEDIUM

    • Hard-coded literal date strings ('2024-10-01 12:34:56') inside dozens of procs create rigid plans. Pass an @AsOfDate datetime2 parameter and benefit from Parameter Sensitive Plan optimization (SQL 2022).
    • Consider OPTION (USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE')) and rely on DOP feedback for the heaviest analytic procs.
    • For pure analytic aggregation procs (sp09812, sp09823, sp09887, sp09851), consider a nonclustered columnstore index on Posts/Votes/PostHistory — Enterprise/Developer-only and a strong fit for these scan-heavy GROUP BYs.
    • Enable Query Store if not already; with so many similarly-shaped procs, regression hunting will be a daily task.

Common Anti-Patterns Observed

Locking & Concurrency

• usp_UpdateTablesWithDelay: the 5-second WAITFOR inside the transaction is the dominant concurrency hazard in the batch. Combined with random PK access from usp_UpdateTablesInLoop, parallel runs will deadlock on Users/Posts.
• The analytic procs are read-only, but without supporting indexes they scan the largest tables (PostHistory 820 MB) and will take Sch-S / IS / S locks that can block concurrent writers. Consider SET TRANSACTION ISOLATION LEVEL READ COMMITTED SNAPSHOT at the database level (RCSI) — it eliminates reader/writer blocking with low overhead.
• None of the procs use explicit table hints like NOLOCK (good), but if RCSI is not enabled, large scans may build up shared locks. Validate is_read_committed_snapshot_on.

Script Enable RCSI is provided.

Indexing Observations

The index usage list shows only clustered PKs and reports zero seeks on most of them, with a small number of scans. That is consistent with the procs in this batch: virtually every plan ends up scanning the leaf level of the clustered index because the predicates do not match the leading key. Once the indexes from Recommendation #1 are in place, expect:

• Order-of-magnitude drop in logical reads on Posts/Votes/Badges/Comments/PostHistory.
• Disappearance of large hash-aggregate spills on procs like sp09812, sp09851, sp09887.
• Better DOP-feedback behavior on SQL 2022 because the plans will switch from huge parallel scans to index seeks with smaller memory grants.

SQL Server 2022 Opportunities

• Parameter Sensitive Plan (PSP) optimization — convert hard-coded date literals to parameters; PSP will keep multiple cached plans for skewed ranges.
• DOP feedback — leave MAXDOP at instance default and let the engine reduce parallelism for tiny result sets in these analytic procs.
• Optimized plan forcing / Query Store hints — for any proc that regresses after indexing, use sp_query_store_set_hints to apply RECOMPILE or OPTIMIZE FOR UNKNOWN without code changes.
• Approximate aggregates — APPROX_COUNT_DISTINCT() for cardinality-style reporting on Posts/Votes.
• Nonclustered columnstore on Posts/Votes/PostHistory for analytic procs — Enterprise/Developer feature, ideal here.

Scripts

Script 1 — Foundational Nonclustered Indexes (Recommendation #1)

USE [SQLStorm];
GO
SET XACT_ABORT ON;
GO

-- Posts: most heavily filtered table
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name='IX_Posts_OwnerUserId_Incl' AND object_id=OBJECT_ID('dbo.Posts'))
CREATE NONCLUSTERED INDEX IX_Posts_OwnerUserId_Incl
    ON dbo.Posts (OwnerUserId)
    INCLUDE (PostTypeId, Score, ViewCount, CreationDate, LastActivityDate, AcceptedAnswerId, AnswerCount, CommentCount)
    WITH (ONLINE = ON, DATA_COMPRESSION = PAGE, FILLFACTOR = 90);
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name='IX_Posts_PostType_CreationDate' AND object_id=OBJECT_ID('dbo.Posts'))
CREATE NONCLUSTERED INDEX IX_Posts_PostType_CreationDate
    ON dbo.Posts (PostTypeId, CreationDate)
    INCLUDE (OwnerUserId, Score, ViewCount, AnswerCount, CommentCount)
    WITH (ONLINE = ON, DATA_COMPRESSION = PAGE);
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name='IX_Posts_ParentId' AND object_id=OBJECT_ID('dbo.Posts'))
CREATE NONCLUSTERED INDEX IX_Posts_ParentId
    ON dbo.Posts (ParentId)
    INCLUDE (PostTypeId, Score, OwnerUserId)
    WHERE ParentId IS NOT NULL
    WITH (ONLINE = ON, DATA_COMPRESSION = PAGE);
GO

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

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

-- Badges
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name='IX_Badges_UserId' AND object_id=OBJECT_ID('dbo.Badges'))
CREATE NONCLUSTERED INDEX IX_Badges_UserId
    ON dbo.Badges (UserId)
    INCLUDE (Class, Name, [Date])
    WITH (ONLINE = ON, DATA_COMPRESSION = PAGE);
GO

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

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

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

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

-- Users
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name='IX_Users_Reputation' AND object_id=OBJECT_ID('dbo.Users'))
CREATE NONCLUSTERED INDEX IX_Users_Reputation
    ON dbo.Users (Reputation DESC)
    INCLUDE (DisplayName, CreationDate, LastAccessDate, UpVotes, DownVotes, Views)
    WITH (ONLINE = ON, DATA_COMPRESSION = PAGE);
GO

-- PostLinks
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name='IX_PostLinks_PostId_LinkType' AND object_id=OBJECT_ID('dbo.PostLinks'))
CREATE NONCLUSTERED INDEX IX_PostLinks_PostId_LinkType
    ON dbo.PostLinks (PostId, LinkTypeId)
    INCLUDE (RelatedPostId)
    WITH (ONLINE = ON, DATA_COMPRESSION = PAGE);
GO

Script 2 — Normalize Tags into a PostTags Bridge (Recommendation #2)

USE [SQLStorm];
GO
SET XACT_ABORT ON;
GO

IF OBJECT_ID('dbo.PostTags','U') IS NULL
BEGIN
    CREATE TABLE dbo.PostTags
    (
        PostId int NOT NULL,
        TagId  int NOT NULL,
        CONSTRAINT PK_PostTags PRIMARY KEY CLUSTERED (PostId, TagId)
            WITH (DATA_COMPRESSION = PAGE)
    );

    CREATE NONCLUSTERED INDEX IX_PostTags_TagId_PostId
        ON dbo.PostTags (TagId, PostId)
        WITH (DATA_COMPRESSION = PAGE);
END
GO

-- Populate from existing Posts.Tags (format: '<tag1><tag2>...')
;WITH Split AS
(
    SELECT  p.Id AS PostId,
            LTRIM(RTRIM(REPLACE(value,'>',''))) AS TagName
    FROM    dbo.Posts AS p
    CROSS APPLY STRING_SPLIT(REPLACE(p.Tags,'><','>|<'),'|') AS s(value)
    WHERE   p.Tags IS NOT NULL
      AND   LEN(p.Tags) > 0
)
INSERT dbo.PostTags (PostId, TagId)
SELECT DISTINCT s.PostId, t.Id
FROM   Split AS s
JOIN   dbo.Tags AS t
       ON  t.TagName = REPLACE(s.TagName,'<','')
WHERE  NOT EXISTS (SELECT 1 FROM dbo.PostTags pt
                   WHERE pt.PostId = s.PostId AND pt.TagId = t.Id);
GO

Script 3 — Refactor usp_UpdateTablesWithDelay to Eliminate Long Transaction (Recommendation #3)

USE [SQLStorm];
GO
CREATE OR ALTER PROCEDURE dbo.usp_UpdateTablesWithDelay
    @UserId         int,
    @NewReputation  int,
    @PostId         int,
    @NewScore       int,
    @SimulateDelay  bit = 0      -- only set in test harness, NEVER in prod
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    -- Optional simulated latency BEFORE the transaction so no locks are held
    IF @SimulateDelay = 1
        WAITFOR DELAY '00:00:05';

    BEGIN TRY
        BEGIN TRANSACTION;

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

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

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        THROW;
    END CATCH
END
GO

Script 4 — Refactor usp_UpdateTablesInLoop to Avoid ORDER BY NEWID() (Recommendation #4)

USE [SQLStorm];
GO
CREATE OR ALTER PROCEDURE dbo.usp_UpdateTablesInLoop
    @LoopCount         int          = 10,
    @DelayBetweenCalls varchar(8)   = '00:00:01'
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @Counter int = 0,
            @MaxUserId int, @MaxPostId int,
            @RandomUserId int, @RandomPostId int,
            @NewReputation int, @NewScore int,
            @SuccessCount int = 0, @ErrorCount int = 0;

    SELECT @MaxUserId = MAX(Id) FROM dbo.Users;
    SELECT @MaxPostId = MAX(Id) FROM dbo.Posts;

    WHILE @Counter < @LoopCount
    BEGIN
        SET @Counter += 1;

        SET @RandomUserId = ((ABS(CHECKSUM(NEWID())) % @MaxUserId) + 1);
        SET @RandomPostId = ((ABS(CHECKSUM(NEWID())) % @MaxPostId) + 1);
        SET @NewReputation = ABS(CHECKSUM(NEWID())) % 10000;
        SET @NewScore      = (ABS(CHECKSUM(NEWID())) % 100) - 10;

        BEGIN TRY
            EXEC dbo.usp_UpdateTablesWithDelay
                 @UserId        = @RandomUserId,
                 @NewReputation = @NewReputation,
                 @PostId        = @RandomPostId,
                 @NewScore      = @NewScore,
                 @SimulateDelay = 0;
            SET @SuccessCount += 1;
        END TRY
        BEGIN CATCH
            SET @ErrorCount += 1;
        END CATCH

        IF @Counter < @LoopCount
            WAITFOR DELAY @DelayBetweenCalls;
    END

    SELECT @LoopCount AS TotalIterations,
           @SuccessCount AS Successful,
           @ErrorCount   AS Errors;
END
GO

Script 5 — Template Rewrite Applied to sp09991 (Recommendations #5, #7, #11)

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

    ;WITH PostAgg AS
    (
        SELECT  p.OwnerUserId AS UserId,
                COUNT_BIG(*) AS PostCount,
                SUM(CASE WHEN p.PostTypeId = 1 THEN 1 ELSE 0 END) AS QuestionCount,
                SUM(CASE WHEN p.PostTypeId = 2 THEN 1 ELSE 0 END) AS AnswerCount,
                SUM(CASE WHEN p.PostTypeId = 3 THEN 1 ELSE 0 END) AS WikiCount
        FROM    dbo.Posts AS p
        WHERE   p.OwnerUserId IS NOT NULL
        GROUP BY p.OwnerUserId
    ),
    VoteAgg 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
        JOIN    dbo.Votes AS v ON v.PostId = p.Id
        WHERE   p.OwnerUserId IS NOT NULL
          AND   v.VoteTypeId IN (2,3)
        GROUP BY p.OwnerUserId
    ),
    BadgeAgg AS
    (
        SELECT  b.UserId,
                COUNT_BIG(*)                                     AS BadgeCount,
                SUM(CASE WHEN b.Class = 1 THEN 1 ELSE 0 END)     AS GoldBadges,
                SUM(CASE WHEN b.Class = 2 THEN 1 ELSE 0 END)     AS SilverBadges,
                SUM(CASE WHEN b.Class = 3 THEN 1 ELSE 0 END)     AS BronzeBadges
        FROM    dbo.Badges AS b
        GROUP BY b.UserId
    )
    SELECT TOP (10)
        u.Id          AS UserId,
        ISNULL(pa.PostCount,0)    AS PostCount,
        ISNULL(pa.QuestionCount,0) AS QuestionCount,
        ISNULL(pa.AnswerCount,0)   AS AnswerCount,
        ISNULL(pa.WikiCount,0)     AS WikiCount,
        ISNULL(va.Upvotes,0)       AS Upvotes,
        ISNULL(va.Downvotes,0)     AS Downvotes,
        ISNULL(ba.BadgeCount,0)    AS BadgeCount,
        ISNULL(ba.GoldBadges,0)    AS GoldBadges,
        ISNULL(ba.SilverBadges,0)  AS SilverBadges,
        ISNULL(ba.BronzeBadges,0)  AS BronzeBadges,
        RANK() OVER (ORDER BY ISNULL(va.Upvotes,0) - ISNULL(va.Downvotes,0) DESC) AS UserRank
    FROM   dbo.Users AS u
    LEFT JOIN PostAgg  AS pa ON pa.UserId = u.Id
    LEFT JOIN VoteAgg  AS va ON va.UserId = u.Id
    LEFT JOIN BadgeAgg AS ba ON ba.UserId = u.Id
    WHERE  ISNULL(pa.PostCount,0) > 0
    ORDER BY UserRank;
END
GO

Script 6 — Centralized Date Window Helper (Recommendation #6)

USE [SQLStorm];
GO
CREATE OR ALTER FUNCTION dbo.fn_GetReportWindow (@AsOf datetime2, @YearsBack int)
RETURNS TABLE
AS
RETURN
(
    SELECT
        WindowStart = DATEADD(YEAR, -@YearsBack, @AsOf),
        WindowEnd   = @AsOf
);
GO

-- Example usage replacing CAST('2024-10-01 12:34:56' AS DATETIME) - DATEADD(YEAR,1,0):
-- SELECT WindowStart, WindowEnd FROM dbo.fn_GetReportWindow(SYSUTCDATETIME(), 1);
GO

Script 7 — Enable Read Committed Snapshot Isolation (Locking)

USE [master];
GO
-- Eliminates reader/writer blocking for the analytic procs.
-- Requires no active user sessions in the target database while ALTER is committed.
ALTER DATABASE [SQLStorm] SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE [SQLStorm] SET ALLOW_SNAPSHOT_ISOLATION ON;
GO

Script 8 — Enable / Tune Query Store (Recommendation #14)

USE [master];
GO
ALTER DATABASE [SQLStorm] SET QUERY_STORE = ON
(
    OPERATION_MODE              = READ_WRITE,
    CLEANUP_POLICY              = ( STALE_QUERY_THRESHOLD_DAYS = 30 ),
    DATA_FLUSH_INTERVAL_SECONDS = 900,
    INTERVAL_LENGTH_MINUTES     = 30,
    MAX_STORAGE_SIZE_MB         = 2048,
    QUERY_CAPTURE_MODE          = AUTO,
    SIZE_BASED_CLEANUP_MODE     = AUTO,
    MAX_PLANS_PER_QUERY         = 200,
    WAIT_STATS_CAPTURE_MODE     = ON
);
GO

Script 9 — Optional Nonclustered Columnstore for Analytic Workload (Recommendation #14)

USE [SQLStorm];
GO
-- Validate disk space and maintenance window before applying.
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name='NCCI_Votes' AND object_id=OBJECT_ID('dbo.Votes'))
CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_Votes
    ON dbo.Votes (PostId, UserId, VoteTypeId, BountyAmount, CreationDate);
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name='NCCI_PostHistory' AND object_id=OBJECT_ID('dbo.PostHistory'))
CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_PostHistory
    ON dbo.PostHistory (PostId, PostHistoryTypeId, UserId, CreationDate);
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name='NCCI_Posts' AND object_id=OBJECT_ID('dbo.Posts'))
CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_Posts
    ON dbo.Posts (Id, OwnerUserId, PostTypeId, Score, ViewCount, AnswerCount, CommentCount, CreationDate, LastActivityDate, ParentId, AcceptedAnswerId);
GO