AI SQL Tuner Recommendations

Tuning Goal: Code Review

Server: RockyPC  |  Database: SQLStorm

Version: SQL Server 2022 (16.x), 16.0.1165.1 RTM-GDR, Developer Edition (64-bit), Engine Edition: Enterprise

Executive summary

Review scope & object counts

Detailed priortized recommendations

1. Eliminate transaction-held waits and tighten write transaction scope Priority 1
   • Affected object: dbo.usp_UpdateTablesWithDelay (called by dbo.usp_UpdateTablesInLoop).
   • WAITFOR DELAY '00:00:05' inside an open transaction holds X locks longer than necessary and invites blocking/deadlocks.
   • Also replace GETDATE() with SYSUTCDATETIME() for deterministic UTC audit behavior.

   -- Recommended pattern
   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 TRAN;
   
       UPDATE dbo.Users
         SET Reputation = @NewReputation,
             LastAccessDate = SYSUTCDATETIME()
       WHERE Id = @UserId;
   
       UPDATE dbo.Posts
         SET Score = @NewScore,
             LastActivityDate = SYSUTCDATETIME()
       WHERE Id = @PostId;
   
       COMMIT;
     END TRY
     BEGIN CATCH
       IF @@TRANCOUNT > 0 ROLLBACK;
       THROW;
     END CATCH;
   
     -- if delay is needed for test harness behavior, do it after commit
     -- WAITFOR DELAY '00:00:05';
   END;

   Confidence: 0.99
2. Remove ORDER BY NEWID() random selection on large tables Priority 1
   • Affected object: dbo.usp_UpdateTablesInLoop.
   • This forces full-sort behavior and scales poorly on Users (267k) and Posts (246k).

   -- Lightweight random key sampling pattern
   DECLARE @MaxUserId int = (SELECT MAX(Id) FROM dbo.Users);
   DECLARE @MaxPostId int = (SELECT MAX(Id) FROM dbo.Posts);
   
   SELECT TOP (1) @RandomUserId = u.Id
   FROM dbo.Users u
   WHERE u.Id >= ABS(CHECKSUM(NEWID())) % @MaxUserId
   ORDER BY u.Id;
   
   SELECT TOP (1) @RandomPostId = p.Id
   FROM dbo.Posts p
   WHERE p.Id >= ABS(CHECKSUM(NEWID())) % @MaxPostId
   ORDER BY p.Id;

   Confidence: 0.97
3. Correct row-multiplication joins and non-key joins (logic + performance) Priority 1
   • Frequent anti-patterns found: joining on DisplayName/Title, ON 1=1, joining unrelated keys (e.g., PostId = UserId).
   • Notable procedures: sp09909, sp09867, sp09831, sp09828, sp09826, sp09982, sp09886, sp09884, sp00987.
   • These patterns explain heavy scans seen on clustered indexes (Users, Posts, Votes, PostHistory).

   -- Example correction (name-based join -> key-based join)
   -- bad: ON ua.DisplayName = tp.OwnerName
   -- good:
   JOIN dbo.Users u ON u.Id = tp.OwnerUserId
   JOIN UserActivity ua ON ua.UserId = u.Id

   Confidence: 0.98
4. Replace correlated scalar subqueries with pre-aggregated joins Priority 2
   • Common in: sp09974, sp09940, sp09946, sp09854, sp09824, sp09815, sp00099.
   • Pattern causes repeated lookups/scans per row.

   -- Replace N scalar subqueries with one grouped source
   WITH VoteAgg AS (
     SELECT PostId,
            SUM(CASE WHEN VoteTypeId = 2 THEN 1 ELSE 0 END) AS UpVotes,
            SUM(CASE WHEN VoteTypeId = 3 THEN 1 ELSE 0 END) AS DownVotes
     FROM dbo.Votes
     GROUP BY PostId
   )
   SELECT p.Id, p.Title, v.UpVotes, v.DownVotes
   FROM dbo.Posts p
   LEFT JOIN VoteAgg v ON v.PostId = p.Id;

   Confidence: 0.95
5. Make date filtering SARGable and parameter-driven Priority 2
   • Many procedures hard-code literal timestamps and use expressions like CAST('date' AS DATETIME) - DATEADD(...) (e.g., sp09921, sp09963, sp09978, sp09905, sp09844).
   • Use direct range predicates and input parameters so plans can benefit from SQL Server 2022 PSP.

   DECLARE @FromDate datetime2(0) = DATEADD(YEAR, -1, SYSUTCDATETIME());
   
   ... WHERE p.CreationDate >= @FromDate;

   Confidence: 0.96
6. Prevent aggregate overcount from fan-out joins Priority 2
   • Several procs aggregate after joining multiple 1-to-many tables at once (Posts+Votes+Comments+Badges), inflating counts.
   • Examples: sp09941, sp09977, sp09990, sp09881, sp09814.
   • Aggregate each child table first, then join per key.
   Confidence: 0.94
7. Refactor tag search (LIKE '%tag%') to normalized token matching Priority 3
   • Seen in many procedures: sp09990, sp09982, sp00992, sp09886, sp09881.
   • Leading wildcard prevents index seeks and drives scans.
   • Use parsed tag table (PostId, TagName) populated once; index (TagName, PostId).
   Confidence: 0.92
8. Index tuning aligned to observed scan-heavy access Priority 3
   • Given usage data: high clustered scans on Votes, PostHistory, Users, Posts, Comments.
   • Create/adjust focused covering indexes for frequent GROUP BY / WHERE patterns.

   -- Candidate examples (validate with workload before deployment)
   CREATE INDEX IX_Votes_PostId_VoteTypeId_UserId
   ON dbo.Votes(PostId, VoteTypeId, UserId)
   INCLUDE (CreationDate, BountyAmount);
   
   CREATE INDEX IX_Posts_PostTypeId_Score_CreationDate
   ON dbo.Posts(PostTypeId, Score DESC, CreationDate DESC)
   INCLUDE (OwnerUserId, ViewCount, Title, AnswerCount, CommentCount);
   
   CREATE INDEX IX_Comments_UserId_PostId
   ON dbo.Comments(UserId, PostId)
   INCLUDE (CreationDate, Score);

   Confidence: 0.90
9. Reduce unnecessary OFFSET/FETCH without selective predicates Priority 3
   • Many procs use OFFSET 0 FETCH NEXT N after large sorts; equivalent TOP (N) with targeted ORDER BY can be simpler and cheaper.
   • Examples: sp09998, sp09995, sp09991, sp09874, sp09851.
   Confidence: 0.86
10. Use naming/schema and error-handling standards consistently Priority 3
    • Avoid sp* naming (reserved lookup behavior and ambiguity); prefer schema-qualified dbo.usp_*.
    • Standardize TRY/CATCH + THROW for mutating procedures.
    • Reduce PRINT in loops for production; use lightweight logging table if needed.
    Confidence: 0.88

Confidence levels