Watch Brent Tune Queries - Misleading Stored Proc Edition

Tune.sql

/*
Watch Brent Tune Queries: Misleading Stored Proc Edition
Brent Ozar - v1.0 - 2024-11-08

https://BrentOzar.com/go/watch

This demo requires:
* Any version of SQL Server, but I use SQL Server 2022
	running in 2022 compat level. If you want to use
	an earlier version, your plans will vary.
* Any Stack Overflow database - I use the large one:
	https://www.BrentOzar.com/go/querystack

This first RAISERROR is just to make sure you don't accidentally hit F5 and
run the entire script. You don't need to run this:
*/
RAISERROR(N'Oops! No, don''t just hit F5. Run these demos one at a time.', 20, 1) WITH LOG;
GO


/* Set up the environment: */
USE StackOverflow;
GO
ALTER DATABASE SCOPED CONFIGURATION
	SET LAST_QUERY_PLAN_STATS = ON;
GO
ALTER DATABASE CURRENT 
	SET COMPATIBILITY_LEVEL = 160
GO
CREATE INDEX DownVotes_UpVotes 
	ON dbo.Users(DownVotes, UpVotes)
	INCLUDE (Reputation, DisplayName, Location);
CREATE INDEX Location_Reputation ON dbo.Users(Location, Reputation);
CREATE INDEX OwnerUserId ON dbo.Posts(OwnerUserId);
CREATE INDEX Score_OwnerUserId ON dbo.Posts(Score, OwnerUserId);
CREATE INDEX UserId ON dbo.Badges(UserId);
CREATE INDEX UserId ON dbo.Votes(UserId);
GO
DBCC FREEPROCCACHE;
GO


/* Watch Brent Tune a Query

Resources: BrentOzar.com/go/watch
Questions: PollGab.com/room/tune */

CREATE OR ALTER PROC dbo.usp_Report AS
BEGIN
	SELECT TOP 250 u.Reputation, u.DisplayName, u.Location, u.Id,
		COUNT(DISTINCT p.Id) AS Posts,
		COUNT(DISTINCT b.Id) AS Badges,
		COUNT(DISTINCT v.Id) AS Votes	
	FROM dbo.Users u
		INNER JOIN dbo.Posts p ON u.Id = p.OwnerUserId
		INNER JOIN dbo.Badges b ON u.Id = b.UserId
		INNER JOIN dbo.Votes v ON u.Id = v.UserId
	WHERE (u.DownVotes + u.UpVotes) > 1000000
	GROUP BY u.Reputation, u.DisplayName, u.Location, u.Id
	ORDER BY u.Reputation DESC;

	WITH TopLocations AS (
		SELECT TOP 1 Location
		FROM dbo.Users
		WHERE Location <> ''
		GROUP BY Location
		ORDER BY COUNT(*) DESC
	)
	SELECT TOP 250 u.Reputation, u.DisplayName, u.Location, u.Id,
		COUNT(DISTINCT p.Id) AS Posts,
		COUNT(DISTINCT b.Id) AS Badges,
		COUNT(DISTINCT v.Id) AS Votes	
	FROM TopLocations tl
		INNER JOIN dbo.Users u ON tl.Location = u.Location
		INNER JOIN dbo.Posts p ON u.Id = p.OwnerUserId
		INNER JOIN dbo.Badges b ON u.Id = b.UserId
		INNER JOIN dbo.Votes v ON u.Id = v.UserId
	GROUP BY u.Reputation, u.DisplayName, u.Location, u.Id
	ORDER BY u.Reputation DESC;
END
GO




/* From FirstResponderKit.org: */
sp_BlitzCache


/* Turning on Last Actual Plan so you can
see the plan in sp_BlitzCache: */

ALTER DATABASE SCOPED CONFIGURATION
	SET LAST_QUERY_PLAN_STATS = ON;
GO



/* Options to consider, easiest to hardest:

Index tuning
Query hints
Query Store hints
Query rewrites, temp tables, CTEs
Dynamic SQL
Adding columnstore indexes, indexed views
Creating reporting tables

What kind of performance gain are we looking for:
percentage, or order of magnitude?

How long do I have:
one hour, one day, or one week?

Set a 25-minute timer.
*/






/* Breaking the query into phases with a variable: */

DECLARE @TopLocation NVARCHAR(200) = (
	SELECT TOP 1 Location
	FROM dbo.Users
	WHERE Location <> ''
	GROUP BY Location
	ORDER BY COUNT(*) DESC);


SELECT TOP 250 u.Reputation, u.DisplayName, u.Location, u.Id,
	COUNT(DISTINCT p.Id) AS Posts,
	COUNT(DISTINCT b.Id) AS Badges,
	COUNT(DISTINCT v.Id) AS Votes	
FROM dbo.Users u
	INNER JOIN dbo.Posts p ON u.Id = p.OwnerUserId
	INNER JOIN dbo.Badges b ON u.Id = b.UserId
	INNER JOIN dbo.Votes v ON u.Id = v.UserId
WHERE u.Location = N'India'
GROUP BY u.Reputation, u.DisplayName, u.Location, u.Id
ORDER BY u.Reputation DESC;








/* Breaking the query into phases with a temp table: */

DROP TABLE IF EXISTS #TopUsers;
CREATE TABLE #TopUsers 
	(Id INT PRIMARY KEY CLUSTERED,
	Reputation INT, DisplayName NVARCHAR(40),
	Location NVARCHAR(200));


WITH TopLocations AS (
	SELECT TOP 1 Location
	FROM dbo.Users
	WHERE Location <> ''
	GROUP BY Location
	ORDER BY COUNT(*) DESC
)
INSERT INTO #TopUsers (Id, Reputation, DisplayName, Location)
SELECT TOP 250 u.Id, u.Reputation, u.DisplayName, u.Location
FROM TopLocations tl
	INNER JOIN dbo.Users u ON tl.Location = u.Location
ORDER BY u.Reputation DESC;

SELECT u.Reputation, u.DisplayName, u.Location, u.Id,
	COUNT(DISTINCT p.Id) AS Posts,
	COUNT(DISTINCT b.Id) AS Badges,
	COUNT(DISTINCT v.Id) AS Votes	
FROM #TopUsers u
	INNER JOIN dbo.Posts p ON u.Id = p.OwnerUserId
	INNER JOIN dbo.Badges b ON u.Id = b.UserId
	INNER JOIN dbo.Votes v ON u.Id = v.UserId
GROUP BY u.Reputation, u.DisplayName, u.Location, u.Id
ORDER BY u.Reputation DESC;












/* How about instead of joins, we use subqueries? */

SELECT u.Reputation, u.DisplayName, u.Location, u.Id,
	Posts = (SELECT COUNT(DISTINCT p.Id) FROM dbo.Posts p WHERE u.Id = p.OwnerUserId),
	Badges = (SELECT COUNT(DISTINCT b.Id) FROM dbo.Badges b WHERE u.Id = b.UserId),
	Votes = (SELECT COUNT(DISTINCT v.Id) FROM dbo.Votes v WHERE u.Id = v.UserId)
FROM #TopUsers u
ORDER BY u.Reputation DESC;
GO











CREATE OR ALTER PROC dbo.usp_Report_Fixed AS
BEGIN
	SELECT TOP 250 u.Reputation, u.DisplayName, u.Location, u.Id,
		COUNT(DISTINCT p.Id) AS Posts,
		COUNT(DISTINCT b.Id) AS Badges,
		COUNT(DISTINCT v.Id) AS Votes	
	FROM dbo.Users u
		INNER JOIN dbo.Posts p ON u.Id = p.OwnerUserId
		INNER JOIN dbo.Badges b ON u.Id = b.UserId
		INNER JOIN dbo.Votes v ON u.Id = v.UserId
	WHERE (u.DownVotes + u.UpVotes) > 1000000
	GROUP BY u.Reputation, u.DisplayName, u.Location, u.Id
	ORDER BY u.Reputation DESC;

DROP TABLE IF EXISTS #TopUsers;
CREATE TABLE #TopUsers 
	(Id INT PRIMARY KEY CLUSTERED,
	Reputation INT, DisplayName NVARCHAR(40),
	Location NVARCHAR(200));


WITH TopLocations AS (
	SELECT TOP 1 Location
	FROM dbo.Users
	WHERE Location <> ''
	GROUP BY Location
	ORDER BY COUNT(*) DESC
)
INSERT INTO #TopUsers (Id, Reputation, DisplayName, Location)
SELECT TOP 250 u.Id, u.Reputation, u.DisplayName, u.Location
FROM TopLocations tl
	INNER JOIN dbo.Users u ON tl.Location = u.Location
ORDER BY u.Reputation DESC;

SELECT u.Reputation, u.DisplayName, u.Location, u.Id,
	Posts = (SELECT COUNT(DISTINCT p.Id) FROM dbo.Posts p WHERE u.Id = p.OwnerUserId),
	Badges = (SELECT COUNT(DISTINCT b.Id) FROM dbo.Badges b WHERE u.Id = b.UserId),
	Votes = (SELECT COUNT(DISTINCT v.Id) FROM dbo.Votes v WHERE u.Id = v.UserId)
FROM #TopUsers u
ORDER BY u.Reputation DESC;
END
GO






/* Recap:

Turn on Last Actual Plans.

Ask about the gain we're looking for,
and how much time we have to spend.

Set 25-minute timers, write recaps.

Options to consider:
Index tuning
Query hints
Query Store hints
Query rewrites, temp tables, CTEs
Dynamic SQL
Adding columnstore indexes, indexed views
Creating reporting tables

Learn more:
https://BrentOzar.com/go/tunequeries

See you at the Summit again next year in Seattle!
*/



/*
License: Creative Commons Attribution-ShareAlike 4.0 International (CC BY-SA 4.0)
More info: https://creativecommons.org/licenses/by-sa/4.0/

You are free to:
* Share - copy and redistribute the material in any medium or format
* Adapt - remix, transform, and build upon the material for any purpose, even 
  commercially

Under the following terms:
* Attribution - You must give appropriate credit, provide a link to the license, 
  and indicate if changes were made. You may do so in any reasonable manner, 
  but not in any way that suggests the licensor endorses you or your use.
* ShareAlike - If you remix, transform, or build upon the material, you must
  distribute your contributions under the same license as the original.
*/