AI SQL Tuner Recommendations

Medium Confidence: 90%

Combining all fixes from recommendations 1–3, here is the complete rewritten stored procedure:

ALTER PROCEDURE sp00060
AS
BEGIN
    SET NOCOUNT ON;

    WITH PostCounts AS (
        SELECT OwnerUserId, COUNT(*) AS TotalPosts
        FROM dbo.Posts
        WHERE OwnerUserId IS NOT NULL
        GROUP BY OwnerUserId
        HAVING COUNT(*) > 10   -- Push filter early
    ),
    VoteCounts AS (
        SELECT p.OwnerUserId,
            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 p
        INNER JOIN dbo.Votes v ON p.Id = v.PostId
        WHERE v.VoteTypeId IN (2, 3)
          AND p.OwnerUserId IS NOT NULL
        GROUP BY p.OwnerUserId
    ),
    BadgeCounts AS (
        SELECT UserId, COUNT(*) AS BadgeCount
        FROM dbo.Badges
        GROUP BY UserId
    ),
    TopUsers AS (
        SELECT 
            u.Id AS UserId,
            u.DisplayName,
            pc.TotalPosts,
            ISNULL(vc.UpVotes, 0) AS UpVotes,
            ISNULL(vc.DownVotes, 0) AS DownVotes,
            ISNULL(bc.BadgeCount, 0) AS BadgeCount,
            DENSE_RANK() OVER (
                ORDER BY pc.TotalPosts DESC, 
                         ISNULL(vc.UpVotes, 0) - ISNULL(vc.DownVotes, 0) DESC
            ) AS UserRank
        FROM dbo.Users u
        INNER JOIN PostCounts pc ON u.Id = pc.OwnerUserId
        LEFT JOIN VoteCounts vc ON u.Id = vc.OwnerUserId
        LEFT JOIN BadgeCounts bc ON u.Id = bc.UserId
    ),
    RankedPosts AS (
        SELECT 
            p.Id AS PostId,
            p.OwnerUserId,
            p.Title,
            p.CreationDate,
            ROW_NUMBER() OVER (
                PARTITION BY pt.TagId 
                ORDER BY p.CreationDate DESC
            ) AS rn,
            t.TagName
        FROM dbo.Posts p
        INNER JOIN dbo.PostTags pt ON p.Id = pt.PostId
        INNER JOIN dbo.Tags t ON pt.TagId = t.Id
        WHERE p.PostTypeId = 1
    )
    SELECT 
        tp.UserRank,
        tp.DisplayName,
        tp.TotalPosts,
        tp.UpVotes,
        tp.DownVotes,
        tp.BadgeCount,
        rp.Title AS LatestPostTitle,
        rp.CreationDate AS LatestPostDate,
        CASE 
            WHEN rp.rn = 1 THEN 'Latest'
            ELSE 'Older'
        END AS PostStatus
    FROM TopUsers tp
    LEFT JOIN RankedPosts rp ON tp.UserId = rp.OwnerUserId
    ORDER BY tp.UserRank, rp.CreationDate DESC;
END;

Expected impact: Estimated cost reduction from ~200 to <10. Memory grant reduction from ~5.6GB to <200MB. Execution time improvement of 90%+.

Low Confidence: 90%

This stored procedure takes no parameters, so traditional parameter sniffing is not a concern. The query uses only literal constants (PostTypeId = 1, VoteTypeId = 2, VoteTypeId = 3, TotalPosts > 10). The plan will always be compiled with the same values.

However, note that ARITHABORT is set to false in the plan's StatementSetOptions. This can cause the plan to not be shared with SSMS sessions (which default to ARITHABORT ON), potentially leading to two cached plans. Consider adding SET ARITHABORT ON to the stored procedure.

ALTER PROCEDURE sp00060
AS
BEGIN
    SET NOCOUNT ON;
    SET ARITHABORT ON;  -- Ensure plan sharing with SSMS
    ...

SQL Server 2022's Parameter Sensitive Plan (PSP) optimization and DOP feedback features are available on this instance but are not applicable since there are no parameters and the query runs parallel (Branches=1 indicates a parallel plan).

After implementing the recommended changes, validate performance improvements by comparing before/after metrics:

-- Run BEFORE and AFTER applying changes
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

EXEC sp00060;

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

Key Metrics to Compare

• Logical Reads — Expect a reduction from hundreds of thousands to low thousands
• CPU Time — Expect 90%+ reduction by eliminating the LIKE cross join
• Elapsed Time — Expect significant wall-clock improvement
• Memory Grant (KB) — Check actual vs granted in sys.dm_exec_query_memory_grants; expect reduction from ~5.6GB to <200MB
• TempDB Spills — Check Sort and Hash warnings in the actual execution plan; expect elimination of spills

Additional Validation

-- Check for tempdb spills in actual plan
SET STATISTICS XML ON;
EXEC sp00060;
SET STATISTICS XML OFF;
-- Look for SpillToTempDb="1" in Sort and Hash operators

-- Monitor memory grants
SELECT 
    session_id, 
    requested_memory_kb, 
    granted_memory_kb, 
    used_memory_kb,
    max_used_memory_kb
FROM sys.dm_exec_query_memory_grants
WHERE session_id = @@SPID;

Verify PostTags Bridge Table Accuracy

-- Spot-check: compare LIKE-based results with bridge table results
SELECT TOP 100 p.Id, p.Tags, t.TagName
FROM dbo.Posts p
INNER JOIN dbo.PostTags pt ON p.Id = pt.PostId
INNER JOIN dbo.Tags t ON pt.TagId = t.Id
WHERE p.PostTypeId = 1
ORDER BY p.Id;

-- Count comparison
SELECT COUNT(*) FROM dbo.PostTags;
-- Should approximate the ~104K rows estimated by the LIKE join