Learn more: https://www.brentozar.com/go/watch

Watch Brent Tune Queries - 3-Parameter Proc Edition.sql

/*
Watch Brent Tune Queries: 3-Parameter Proc Edition
Brent Ozar - v1.3 - 2026-04-11

https://BrentOzar.com/go/watch

This demo requires:
* Any version of SQL Server, but I'm using 
    SQL Server 2025 running in 2025 compat level. 
	Query plans will vary in earlier versions.
* Any Stack Overflow database - I use a 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 CURRENT 
	SET COMPATIBILITY_LEVEL = 170
GO
ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON;
ALTER DATABASE CURRENT SET QUERY_STORE CLEAR;
GO
/* Check to make sure we're on the default options: */
SELECT * FROM sys.database_scoped_configurations
	WHERE is_value_default <> 1;
GO

/* Set up indexes ahead of time. This will take a couple minutes
because we're adding a few indexes on the giant Posts table. */
DropIndexes;
GO
CREATE INDEX Location ON dbo.Users(Location);
GO
CREATE INDEX OwnerUserId ON dbo.Posts(OwnerUserId);
GO
CREATE INDEX CreationDate_Score ON dbo.Posts(CreationDate, Score);
GO
CREATE INDEX Score_CreationDate ON dbo.Posts(Score, CreationDate);
GO




CREATE OR ALTER PROC dbo.TopPostsByLocation
	@Location NVARCHAR(255) = NULL, @StartDate DATETIME = NULL, @EndDate DATETIME = NULL
AS
BEGIN
	IF @Location IS NULL
		BEGIN
		RAISERROR('Location parameter is required.', 16, 1);
		RETURN;
		END
	SELECT TOP 10 p.Score, p.Title, p.Body, p.Tags, u.DisplayName, u.Reputation
	FROM dbo.Users u
	INNER JOIN dbo.Posts p ON u.Id = p.OwnerUserId
	WHERE u.Location = @Location
	  AND p.CreationDate >= ISNULL(@StartDate, '1900-01-01')
	  AND p.CreationDate <= ISNULL(@EndDate, GETDATE())
	ORDER BY p.Score DESC;
END
GO
DBCC FREEPROCCACHE;
GO

EXEC dbo.TopPostsByLocation;
GO
EXEC dbo.TopPostsByLocation @Location = N'India';
GO
/* Check the wait stats in the plan.
If we saw pageiolatch waits, the buffer cache was just empty. Run it again. 

It still takes a while, though. Why?

Try freeing the plan cache, and running it for India directly. */



/* A few calls that might go in first: */
DBCC FREEPROCCACHE;
GO

EXEC dbo.TopPostsByLocation;			/* Oops, no params */
GO
EXEC dbo.TopPostsByLocation @Location = N'India'; /* Big data */
GO
EXEC dbo.TopPostsByLocation @Location = N'Las Vegas'; /* Smaller data */
GO








/* After getting the proc to a good place, let's try a few more params: */
DBCC FREEPROCCACHE;
GO

EXEC dbo.TopPostsByLocation;			/* Oops, no params */
GO
EXEC dbo.TopPostsByLocation @Location = N'India';

EXEC dbo.TopPostsByLocation @Location = N'India',
	@StartDate = '2020-06-06', @EndDate = '2020-06-07'; /* 1 day */

EXEC dbo.TopPostsByLocation @Location = N'India',
	@StartDate = '2020-01-01', @EndDate = '2020-12-31'; /* 1 year */

EXEC dbo.TopPostsByLocation @Location = N'India',
	@StartDate = '2010-01-01', @EndDate = '2019-12-31'; /* 10 years */
GO

EXEC dbo.TopPostsByLocation @Location = N'Las Vegas';

EXEC dbo.TopPostsByLocation @Location = N'Las Vegas',
	@StartDate = '2020-06-06', @EndDate = '2020-06-07'; /* 1 day */

EXEC dbo.TopPostsByLocation @Location = N'Las Vegas',
	@StartDate = '2020-01-01', @EndDate = '2020-12-31'; /* 1 year */

EXEC dbo.TopPostsByLocation @Location = N'Las Vegas',
	@StartDate = '2010-01-01', @EndDate = '2019-12-31'; /* 10 years */
GO

EXEC dbo.TopPostsByLocation @Location = N'Vienna, Austria';

EXEC dbo.TopPostsByLocation @Location = N'Vienna, Austria',
	@StartDate = '2020-06-06', @EndDate = '2020-06-07'; /* 1 day */

EXEC dbo.TopPostsByLocation @Location = N'Vienna, Austria',
	@StartDate = '2020-01-01', @EndDate = '2020-12-31'; /* 1 year */

EXEC dbo.TopPostsByLocation @Location = N'Vienna, Austria',
	@StartDate = '2010-01-01', @EndDate = '2019-12-31'; /* 10 years */
GO






/* Want to go spelunking for unusual outliers?

This query will help you find the top 20 locations that have the most users,
but are not part of the high key values in the statistics histogram for the Location column,
and also have the most Posts.
*/
DECLARE @ObjectID INT = OBJECT_ID('dbo.Users');
DECLARE @StatisticsID INT = (SELECT TOP 1 stats_id FROM sys.stats WHERE object_id = @ObjectID AND name = 'Location');
WITH range_high_keys AS (
	SELECT range_high_key FROM sys.dm_db_stats_histogram(@ObjectID, @StatisticsID)
	WHERE range_high_key <> N''
),
outlier_locations AS (
	SELECT TOP 20 u.Location, SUM(1) AS UsersTotal,
		'SELECT COUNT(*) FROM dbo.Users WHERE Location = ''' + u.Location + '''  AND 1 = (SELECT 1);' AS Query
	FROM dbo.Users u
	LEFT OUTER JOIN range_high_keys r ON u.Location = r.range_high_key
	WHERE r.range_high_key IS NULL
	AND u.Location <> N''
	GROUP BY u.Location
	ORDER BY SUM(1) DESC
)
SELECT o.Location, o.UsersTotal, o.Query, SUM(1) AS PostsTotal
FROM outlier_locations o
INNER JOIN dbo.Users u ON o.Location = u.Location
INNER JOIN dbo.Posts p ON u.Id = p.OwnerUserId
GROUP BY o.Location, o.UsersTotal, o.Query
ORDER BY SUM(1) DESC;


/* 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/watch
*/



/*
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.
*/