This is the part to of the series of blogs where we discuss Performance Tuning Excersises generate by Claude.
Setup
Require StackOverflow database 2010 (10GB) or 2013 (50GB) version.
You can find the download instructions for StackOverflow database in this Brent Ozar page.
Following script create indexes required.
-- Start from a known state
DROP INDEX IF EXISTS IX_Users_Reputation ON dbo.Users;
DROP INDEX IF EXISTS IX_Posts_OwnerUserId ON dbo.Posts;
GO
CREATE INDEX IX_Users_Reputation
ON dbo.Users (Reputation)
INCLUDE (DisplayName);
CREATE INDEX IX_Posts_OwnerUserId
ON dbo.Posts (OwnerUserId)
INCLUDE (Score);
GO
-- Fresh, fullscan stats so you can't blame stale statistics.
-- This matters: I want to take that explanation off the table up front.
UPDATE STATISTICS dbo.Users WITH FULLSCAN;
UPDATE STATISTICS dbo.Posts WITH FULLSCAN;
GO
Then we create the stored procedure:
CREATE OR ALTER PROC dbo.rpt_TopContributors
@MinReputation INT
AS
BEGIN
SET NOCOUNT ON;
SELECT TOP (100)
u.Id,
u.DisplayName,
u.Reputation,
COUNT_BIG(p.Id) AS PostCount,
MAX(p.Score) AS BestPostScore
FROM dbo.Users AS u
JOIN dbo.Posts AS p
ON p.OwnerUserId = u.Id
WHERE u.Reputation >= @MinReputation
GROUP BY u.Id, u.DisplayName, u.Reputation
ORDER BY PostCount DESC;
END
GO
Note that in here we are filtering based on reputation points. So, we need two end of reputation points to check the stored procedure. In here Claude has picked 100000 as high reputation point, which means only few users are returned and 10 has the low reputation threshold, which return most of the users in Users table.
So, our test execution script will be:
SET STATISTICS IO, TIME ON;
EXEC sp_recompile 'dbo.rpt_TopContributors'; -- safer than FREEPROCCACHE
EXEC dbo.rpt_TopContributors @MinReputation = 100000;
EXEC dbo.rpt_TopContributors @MinReputation = 10;
In round 1 of testing, we run high reputation threshold first and then lower reputation (as per above).
Result Set 1: @MinReputation = 100000 (compiled with @MinReputation = 10000):
Table 'Posts'. Scan count 453, logical reads 2622, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Users'. Scan count 1, logical reads 6, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
SQL Server Execution Times:
CPU time = 110 ms, elapsed time = 240 ms.
SQL Server Execution Times:
CPU time = 110 ms, elapsed time = 245 ms.
Result Set 2: @MinReputation = 10 (Compiled with @MinReputation = 100000):
Table 'Posts'. Scan count 234232, logical reads 755528, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Users'. Scan count 1, logical reads 1010, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
SQL Server Execution Times:
CPU time = 1593 ms, elapsed time = 1796 ms.
SQL Server Execution Times:
CPU time = 1593 ms, elapsed time = 1796 ms.
In round 2 of testing, we run min reputation threshold first and then higher reputation second.
Result Set 3: @MinReputation = 10 (compiled with @MinReputation = 10):
Table 'Users'. Scan count 0, logical reads 318, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 19, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Posts'. Scan count 1, logical reads 8334, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
SQL Server Execution Times:
CPU time = 750 ms, elapsed time = 971 ms.
SQL Server Execution Times:
CPU time = 765 ms, elapsed time = 1034 ms.
Result Set 4: @MinReputation = 100000 (compiled with @MinReputation = 10):
Table 'Users'. Scan count 0, logical reads 342, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 19, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Posts'. Scan count 1, logical reads 8334, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
SQL Server Execution Times:
CPU time = 735 ms, elapsed time = 875 ms.
SQL Server Execution Times:
CPU time = 735 ms, elapsed time = 875 ms.
My thoughts:
In above I have used StackOverflow 2010 database.
Looking at the results, we can see result set 2, i.e. executing with @MinReputation = 10 when stored procedure compiled with @MinReputation = 100000 is the worst scenario. Although time wise it is not much visible in 2010 version of the stack overflow database, it has significantly more logical reads (about 755000 in my case).
If we look at the execution plan for first two result sets, it starts with seeking into reputation index. It is because SQL server thought there will be very small number of users with reputation higher than 100000 (because plan is compiled with @MinReputation = 100000). So, it only found few with reputation higher than 100000. But when we ran with @MinReputation = 10, reputation index returned lot more users (234232 user in my case). In the second part of plan SQL server index seek on each of those users return. This index seek was ok when there were less users (less number of seeks), but for large number of users it is too much. This is why you see performance degrade.
If plan was compiled with @MinReputation = 10 (result set 3 and 4), we can see different shape of plan. In this case SQL server, choose to scan the entire post owners index and sort it on post count. Then for each group (group by OwnerUserId), it seeked into the users table, until it finds 100 users (to full fill TOP 100 users).
For result set 3 and 4, logical reads and cpu time is mostly similar.
My Solution
Knowing that result set 3 and 4 behaved fairly equally not depending on parameter, I suggest we optimize the plan for @MinReputation = 10. So, no matter which parameter we use, it will use the plan compiled for @MinReputation = 10.
ALTER PROC [dbo].[rpt_TopContributors]
@MinReputation INT
AS
BEGIN
SET NOCOUNT ON;
SELECT TOP (100)
u.Id,
u.DisplayName,
u.Reputation,
COUNT_BIG(p.Id) AS PostCount,
MAX(p.Score) AS BestPostScore
FROM dbo.Users AS u
JOIN dbo.Posts AS p
ON p.OwnerUserId = u.Id
WHERE u.Reputation >= @MinReputation
GROUP BY u.Id, u.DisplayName, u.Reputation
ORDER BY PostCount DESC
OPTION (OPTIMIZE FOR (@MinReputation = 10));
END
Above is my solution, note the use of OPTIMIZE FOR hint.
Claude Solution:
Well Claude has accepted my solution, but told me to OPTIMIZE for UNKNOWN. But ideal solution it suggested is using "temp" table to fetch users first and then query posts. To do this query will be split to two. See below:
ALTER PROC [dbo].[rpt_TopContributors]
@MinReputation INT
AS
BEGIN
SET NOCOUNT ON;
SELECT u.Id,
u.DisplayName,
u.Reputation
INTO #tempUsers
FROM dbo.Users u
WHERE u.Reputation >= @MinReputation
SELECT TOP (100)
u.Id,
u.DisplayName,
u.Reputation,
COUNT_BIG(p.Id) AS PostCount,
MAX(p.Score) AS BestPostScore
FROM #tempUsers AS u
JOIN dbo.Posts AS p
ON p.OwnerUserId = u.Id
GROUP BY u.Id, u.DisplayName, u.Reputation
ORDER BY PostCount DESC
END
Running above shows fairly low number of logical reads and cpu time for all parameter combinations.
Also Claude has mentioned with temp table usage, I get automatic recompilation, based on the number of rows it retrieved into the temp table. This was something I didn't thought of.
