This is the 4th part of the blog series where we discuss about SQL server performance tuning exercises generated by Claude.
This time I asked for expert level challenge to see how it going to be.
Scenario
This time we have a Internal app.
"Community Activity Digest" screen in a .NET Core / React app. Users pick a date range and page through questions ranked by an engagement score. Standard grid, 50 rows a page.
Issue: The Digest screen "randomly freezes." Not always — mornings are mostly fine, but between roughly 10am and 4pm when the ops team is all in the tool, users report the grid spinner sitting there for 40–90 seconds and then, without them doing anything, the data just appears. Refresh immediately after and it's fast. Nobody can reproduce it on demand.
Dev team has already on the issue. They have checked 3 things and ruled them out.
1.The plan in cache is identical between fast and slow runs — they grabbed it both times. Same handle, same plan, same estimated cost. So they've ruled out parameter sniffing.
2. cpu_time on the slow runs is tiny compared to total_elapsed_time — roughly 2 seconds of CPU against 60 seconds elapsed. But investigation showed procedure return exactly 50 rows and API layer reads the reader with not much processing.
3. Logical reads are the same on fast and slow executions
Some more additional details are also provided, mentioning might not relate or not.
- Page 1 is exactly as slow as page 400.
- There is a known bug: some posts appear two or three times in the list, and the duplicates push real results onto the next page.
The DBA has already added covering indexes for this query and considers the indexing "done."
Setup
Following setup script change the compatibility level and create the index mentioned above.
USE StackOverflow2010; -- or StackOverflow2013
GO
/* Production runs on compat 140. Match it — this matters, and I'm not
going to tell you why yet. */
ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 140;
GO
/* The DBA's "covering" indexes. These are genuinely reasonable indexes
for this query shape. Heads up: the INCLUDE lists make these fat.
On StackOverflow2010 expect a few GB and several minutes; on 2013
expect considerably more of both. Build them when you have time. */
DROP INDEX IF EXISTS IX_Posts_PostTypeId_CreationDate_Includes ON dbo.Posts;
CREATE INDEX IX_Posts_PostTypeId_CreationDate_Includes
ON dbo.Posts (PostTypeId, CreationDate)
INCLUDE (OwnerUserId, Score, CommentCount, ViewCount, Title, Tags);
GO
DROP INDEX IF EXISTS IX_Comments_PostId_CreationDate ON dbo.Comments;
CREATE INDEX IX_Comments_PostId_CreationDate
ON dbo.Comments (PostId, CreationDate)
INCLUDE (Text, UserId);
GO
Stored procedure behind the page
CREATE OR ALTER PROCEDURE dbo.usp_GetActivityDigestPage
@StartDate datetime,
@EndDate datetime,
@PageNumber int,
@PageSize int
AS
BEGIN
SET NOCOUNT ON;
;WITH Digest AS
(
SELECT
PostId = p.Id,
p.Title,
p.Tags,
p.CreationDate,
p.Score,
p.ViewCount,
p.CommentCount,
CommentText = c.Text,
CommentDate = c.CreationDate,
AuthorName = u.DisplayName,
AuthorLocation = u.Location,
AuthorAboutMe = u.AboutMe,
EngagementScore = (p.Score * 2) + p.CommentCount,
RowNum = ROW_NUMBER() OVER (
ORDER BY (p.Score * 2) + p.CommentCount DESC,
p.Id ASC)
FROM dbo.Posts AS p
LEFT JOIN dbo.Comments AS c
ON c.PostId = p.Id
JOIN dbo.Users AS u
ON u.Id = p.OwnerUserId
WHERE p.PostTypeId = 1
AND p.CreationDate >= @StartDate
AND p.CreationDate < @EndDate
)
SELECT PostId, Title, Tags, CreationDate, Score, ViewCount, CommentCount,
CommentText, CommentDate, AuthorName, AuthorLocation, AuthorAboutMe,
EngagementScore, RowNum
FROM Digest
WHERE RowNum BETWEEN ((@PageNumber - 1) * @PageSize) + 1
AND (@PageNumber * @PageSize)
ORDER BY RowNum;
END
GO
Typical call to the store procedure
EXEC dbo.usp_GetActivityDigestPage
@StartDate = '2010-01-01', @EndDate = '2010-07-01',
@PageNumber = 1, @PageSize = 50;
EXEC dbo.usp_GetActivityDigestPage
@StartDate = '2010-01-01', @EndDate = '2010-07-01',
@PageNumber = 400, @PageSize = 50;
Setting up like above and running the query, will not re-produce the intermittent issue. Claude as gone on to explain how to re-produce the intermittent issue.
First it wants us to constraint memory.
SELECT [name], value_in_use FROM sys.configurations
WHERE [name] = 'max server memory (MB)';
EXEC sys.sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sys.sp_configure 'max server memory (MB)', 4096; RECONFIGURE;
Then open three SSMS windows and use a synchronised start so they all fire at once. Set the time a minute or two ahead:
WAITFOR TIME '14:30:00';
EXEC dbo.usp_GetActivityDigestPage
@StartDate = '2010-01-01', @EndDate = '2010-07-01',
@PageNumber = 1, @PageSize = 50;
And in a fourth window, watch what happens while they run. Poll this repeatedly during the window:
SELECT r.session_id,
r.status,
r.wait_type,
r.wait_time,
r.cpu_time,
r.total_elapsed_time,
r.logical_reads,
g.request_time,
g.grant_time,
g.requested_memory_kb,
g.granted_memory_kb,
g.ideal_memory_kb,
g.required_memory_kb,
g.queue_id,
g.wait_time_ms,
g.dop
FROM sys.dm_exec_requests AS r
LEFT JOIN sys.dm_exec_query_memory_grants AS g
ON g.session_id = r.session_id
WHERE r.session_id > 50
AND r.session_id <> @@SPID;
This is what it want me to look into:
Tell me the wait the users are actually experiencing, and explain precisely why CPU time and logical reads are both flat across fast and slow executions while elapsed time varies by 30x — that pairing is the whole diagnostic signature and if your explanation doesn't account for both, it's not the right answer.
Then explain why page 1 and page 400 cost the same. There's a specific structural reason, and it's not "SQL Server is dumb about paging."
Then the fix. I want the primary rewrite — the one that attacks the root cause rather than the symptom — with before/after numbers for whatever metric you decide is the one that matters here. I also want you to tell me what you'd do about that duplicate-rows bug in the backlog, and whether you think it's a separate issue or not.
Finally, three questions I want you to have an opinion on rather than just an answer to. One: I set compat level to 140 on purpose. What would change at 150, would it actually solve this, and what are the limitations of relying on it? Two: there's a hint you could add to this query that would make the symptom disappear in about ten seconds of work — name it, and tell me why you would or wouldn't ship it. Three: could you eliminate the expensive operator entirely with a schema change? Sketch it and tell me what it costs you.
A fair warning on measurement: if you test by running the proc in SSMS and reading the grid, you'll see ASYNC_NETWORK_IO show up because of one particular column in the select list, and it's a red herring — it's 50 rows, it isn't your problem. Measure the thing that's actually being contended for.
After reading the whole thing, I felt we are looking into blocking issue, even without going into details.
However, when ran queries as mentioned, they completed too quickly to monitor. Therefore, I ask for a solution. Then Claude gave me same query running 25 times in a loop (with page numbers, so not the same data returned).
This time I was able to capture the results, and this is what I got.
So my conclusion was queries are sometimes go because of the memory contention (RESOURCE_SEMAPHORE wait).
My conclusion was mostly correct and Claude has shown more information from the above result set. For example, requested time and grant time different.
After that, Claude wanted me to check the plan and see why this simple query is asking for 650 Mb memory grant.
This is the actual execution plan:
Looking at the plan, I have identified, that SQL server is feeding all column required by query from post table to sort operator. This is probably the issue here. I have also identified that duplicate row bug is caused by joining to comments table. Comments table can include multiple rows per post.
;WITH Ranked AS
(
-- Only what's needed to decide the order. Sort row ~20 bytes instead of 446.
SELECT p.Id,
EngagementScore = (p.Score * 2) + p.CommentCount,
RowNum = ROW_NUMBER() OVER (ORDER BY (p.Score * 2) + p.CommentCount DESC,
p.Id ASC)
FROM dbo.Posts AS p
WHERE p.PostTypeId = 1
AND p.CreationDate >= @StartDate
AND p.CreationDate < @EndDate
)
First part of the query just fetches the field required for sort and sort it (see above).
PageIds AS
(
SELECT Id, EngagementScore, RowNum
FROM Ranked
WHERE RowNum BETWEEN ((@PageNumber - 1) * @PageSize) + 1
AND (@PageNumber * @PageSize)
)
Second part of the query, isolate the records required for the requested page (because this is paginated stored procedure).
Finally main query fetch all other fields required and also remove the duplication in Comments table.
SELECT pg.Id AS PostId, p.Title, p.Tags, p.CreationDate, p.Score,
p.ViewCount, p.CommentCount,
lc.CommentText, lc.CommentDate,
u.DisplayName AS AuthorName, u.Location AS AuthorLocation,
u.AboutMe AS AuthorAboutMe,
pg.EngagementScore, pg.RowNum
FROM PageIds AS pg
JOIN dbo.Posts AS p ON p.Id = pg.Id -- 50 clustered seeks: the "lookups"
LEFT JOIN dbo.Users AS u ON u.Id = p.OwnerUserId
OUTER APPLY
(
SELECT TOP (1) c.Text AS CommentText, c.CreationDate AS CommentDate
FROM dbo.Comments AS c
WHERE c.PostId = p.Id
ORDER BY c.CreationDate DESC
) AS lc
ORDER BY pg.RowNum;
In question Claude ask there were 3 questions asked on my openion.
Question 1: Changing Compact level to 150
This will cause adaptive memory grant to kick in, but I said I don't want it to kick in as it will cause varied memroy grants.
Question 2: Hint which can be used.
I couldn't figure this out. Claudes answer was using MAX_GRANT_PERCENT
OPTION (MAX_GRANT_PERCENT = 5);
However, it didn't reqlly want it applied as long time solution.
Quetion 3: Schema Change to Help
I said about having sorting column (Score) as calculated column. Claude agreed and also suggested adding it part of the index.
Conclusion
Overall, this exercise was very good one. it was very impressive how Claude approached the issue and how it provided the answer step by step. Very good learning experience.
