SQL Server Performance Tuning Excersises - 3

This is the 3rd part of the blog series where we discuss about SQL server performance tuning exercises generated by Claude.

This time I asked for medium level challenge like the last time. I feel shouldn't go to expert level too soon.

Claude has chosen Stack Overflow database this time also.

Steup

Interesting setup script given by Claude:

USE StackOverflow2013;   -- or StackOverflow2010, whatever you're using
GO

-- Record this first so you can put it back later
SELECT name, compatibility_level 
FROM sys.databases 
WHERE database_id = DB_ID();
GO

-- This shop did a lift-and-shift onto SQL 2019/2022 hardware
-- but never touched the compat level. Very common.
ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 140;
GO

As you can see above script set the compatibility level to 140, i.e. 2017, but has a comment saying they (shop in the example) did a lift and shift to 2019/2022. Then we have these two helper functions written by a developer sometimes back.

CREATE OR ALTER FUNCTION dbo.fn_GetUserAnswerCount (@UserId int)
RETURNS int
AS
BEGIN
    DECLARE @AnswerCount int;

    SELECT @AnswerCount = COUNT(*)
    FROM dbo.Posts AS p
    WHERE p.OwnerUserId = @UserId
      AND p.PostTypeId = 2;      -- 2 = Answer

    RETURN ISNULL(@AnswerCount, 0);
END
GO

CREATE OR ALTER FUNCTION dbo.fn_GetUserTier (@Reputation int)
RETURNS varchar(20)
AS
BEGIN
    DECLARE @Tier varchar(20);

    IF @Reputation >= 100000
        SET @Tier = 'Legendary';
    ELSE IF @Reputation >= 25000
        SET @Tier = 'Elite';
    ELSE IF @Reputation >= 5000
        SET @Tier = 'Trusted';
    ELSE IF @Reputation >= 1000
        SET @Tier = 'Established';
    ELSE
        SET @Tier = 'Newcomer';

    RETURN @Tier;
END
GO

Then index added by previous DBA

IF NOT EXISTS (SELECT 1 FROM sys.indexes 
               WHERE name = 'IX_Posts_OwnerUserId_PostTypeId' 
                 AND object_id = OBJECT_ID('dbo.Posts'))
    CREATE NONCLUSTERED INDEX IX_Posts_OwnerUserId_PostTypeId
        ON dbo.Posts (OwnerUserId, PostTypeId);
GO
-- Heads up: on the 2013 dataset this build takes a few minutes.

Finally, we have stored procedure use for troublesome report.

CREATE OR ALTER PROCEDURE dbo.usp_GetActiveContributorReport
    @MinReputation    int,
    @LastAccessSince  datetime
AS
BEGIN
    SET NOCOUNT ON;

    SELECT  u.Id,
            u.DisplayName,
            u.Reputation,
            u.LastAccessDate,
            dbo.fn_GetUserAnswerCount(u.Id)  AS AnswerCount,
            dbo.fn_GetUserTier(u.Reputation) AS ReputationTier
    FROM    dbo.Users AS u
    WHERE   u.Reputation     >= @MinReputation
      AND   u.LastAccessDate >= @LastAccessSince
    ORDER BY u.Reputation DESC;
END
GO

Scenario

The "Active Contributors" page in the internal admin tool takes between 40 and 90 seconds to load. It used to be quicker when the site was smaller, and it's been getting steadily worse. Nobody changed the code. CommandTimeout is set to 120 in the .NET app, so it doesn't actually time out — it just makes everyone hate the page.

From the DBA. "I checked the plan and the estimated cost is about 2.5. That's nothing. This query doesn't even show up in our 'top 20 most expensive queries by cost' report. But it's sitting at number one in Query Store for total CPU time, by a mile." She also mentions the plan is completely serial no matter what — she tried slapping OPTION (MAXDOP 8) on it and the plan shape didn't budge. MAXDOP on the server is 8, cost threshold is 50.

From the sysadmin. During the report run, one CPU core pins at 100% and the other seven are idle. Disk queue length is flat. No blocking, no lock waits.

From the developer who wrote it. "I ran SET STATISTICS IO ON and it barely shows anything — some reads on Users and that's basically it. The Posts table hardly registers, which is weird because the report is obviously counting posts." He also points out that if he runs the inner count for a single user by hand:

SELECT COUNT(*) FROM dbo.Posts WHERE OwnerUserId = 22656 AND PostTypeId = 2;

...it returns instantly, single-digit milliseconds. So in his mind the function can't possibly be the problem.

What Claude expected from me

Get the procedure returning the same result set in a couple of seconds on warm cache, and — more importantly - be able to explain why every cost-based diagnostic in the box lied to you about this query.

A few things I want you to actually measure rather than assume: total worker time before and after (sys.dm_exec_procedure_stats or Query Store, not wall clock), whether the plan goes parallel after your fix, and what the real logical read count on Posts was all along.

Why did the plan never go parallel, and what specifically about one of those two functions caused it? (Only one of them is guilty of this.) Why did the plan never go parallel, and what specifically about one of those two functions caused it? (Only one of them is guilty of this.)

And finally: once the function problem is gone, what's the next bottleneck in this query? There's one more thing in there worth fixing.

My Solution

Here is my version of the stored procedure:

ALTER   PROCEDURE [dbo].[usp_GetActiveContributorReport_MDA]
    @MinReputation    int,
    @LastAccessSince  datetime
AS
BEGIN
    SET NOCOUNT ON;

    CREATE TABLE #tempPosts (OwnerUserId int PRIMARY KEY, AnswerCount int)
    INSERT INTO #tempPosts
    SELECT  OwnerUserId, COUNT(*)
    FROM dbo.Posts AS p
    WHERE p.PostTypeId = 2
    GROUP BY OwnerUserId

    SELECT  u.Id,
            u.DisplayName,
            u.Reputation,
            u.LastAccessDate,
            tp.AnswerCount,
            CASE 
                WHEN u.Reputation >= 100000 THEN 'Legendary'
                WHEN u.Reputation >= 25000 THEN 'Elite'
                WHEN u.Reputation >= 5000 THEN 'Trusted'
                WHEN u.Reputation >= 1000 THEN 'Established'
                ELSE 'Newcomer'
            END As ss
    FROM    dbo.Users AS u INNER JOIN #tempPosts tp ON u.Id = tp.OwnerUserId
    WHERE   u.Reputation     >= @MinReputation
      AND   u.LastAccessDate >= @LastAccessSince
    ORDER BY u.Reputation DESC;
END

I told Claude following:

• Plan didn't go parallel even after the modification. Probably due to lack of data volume in 2010 version of the stack overflow database.
• The reason original plan has not gone parallel was because there were UDF function (both functions are causing issues not one).
• Further improvements can be gain by adding more indexes to Users table.
• The reason why actual stats were hidden, was because they were behind UDF function.
• My stats:

Claude's Answer

ALTER PROCEDURE dbo.usp_GetActiveContributorReport_MDA
    @MinReputation    int,
    @LastAccessSince  datetime
AS
BEGIN
    SET NOCOUNT ON;

    CREATE TABLE #tempPosts
    (
        OwnerUserId int NOT NULL PRIMARY KEY,
        AnswerCount int NOT NULL
    );

    INSERT INTO #tempPosts (OwnerUserId, AnswerCount)
    SELECT p.OwnerUserId, COUNT_BIG(*)
    FROM   dbo.Posts AS p
    WHERE  p.PostTypeId = 2
      AND  p.OwnerUserId IS NOT NULL
    GROUP BY p.OwnerUserId;

    SELECT  u.Id,
            u.DisplayName,
            u.Reputation,
            u.LastAccessDate,
            ISNULL(tp.AnswerCount, 0) AS AnswerCount,
            CASE
                WHEN u.Reputation >= 100000 THEN 'Legendary'
                WHEN u.Reputation >=  25000 THEN 'Elite'
                WHEN u.Reputation >=   5000 THEN 'Trusted'
                WHEN u.Reputation >=   1000 THEN 'Established'
                ELSE 'Newcomer'
            END AS ReputationTier
    FROM    dbo.Users AS u
    LEFT JOIN #tempPosts AS tp ON tp.OwnerUserId = u.Id
    WHERE   u.Reputation     >= @MinReputation
      AND   u.LastAccessDate >= @LastAccessSince
    ORDER BY u.Reputation DESC;
END

Note that Claude has put p.OwnerUserId IS NOT NULL into the where clause of the select statement where we fetch posts. Its argument was, since OwnerUserId is primary key and if there are records with no OwnerUserId this will fail. I think fair enough argument.

Second different was, LEFT JOIN was used with temp table instead of INNER JOIN. This something I missed, in a hurry. Since I used INNER join, some of the records (where there were no answers) has missed. In my result set I had 102084 records, where Claude has 168634 records. That's a good catch.

Third different was, ISNULL(tp.AnswerCount, 0) As AnswerCount. This is go hand in hand with above LEFT outer join, because if answer count is null, it will need to appear as 0.

In it's answer, it admitted, it got wrong about just one function being the cause of procedure not going parallel. Following code shows, both functions could be in-lined in higher compatibility levels.

SELECT OBJECT_NAME(object_id), is_inlineable
FROM   sys.sql_modules
WHERE  object_id IN (OBJECT_ID('dbo.fn_GetUserAnswerCount'),
                     OBJECT_ID('dbo.fn_GetUserTier'));

So changing compatibility level 150 will auto improve the stored procedure without a single line of code change. But there are lot of counter arguments against function in-lining feature, so I would still go for hand made stored procedure.

Claude in it's solution, talk about OUTTER APPLY vs pre-aggregation (which we used). But OUTTER apply is subject to parameter sniffing (in this case), so I would careful about it.

Conclusion

It was a good solution from Claude and good explanation. But notice LLMs are still making some mistakes (function in-lining scenario). Note that I have used Opus 5 (High) in this scenario, which one of the most capable models. So, we need to use them carefully. I would use AI in query tuning any day, but I will be careful and analyse the solutions it gives before apply to production.