Watch Brent Tune a Query - Top Posts from Top Locations.sql

/*
Watch Brent Tune Queries: Top Posts From Top Locations
v1.2 - 2024-09-07
https://www.BrentOzar.com/go/tunequeries
 
 
This demo requires:
* Any supported version of SQL Server
* A large Stack Overflow database: 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
 
 
/* I'm using the large Stack database: */
USE StackOverflow;
GO
/* I'm using 2022 compat level to give SQL Server every possible chance,
but if you have an older server, use the newest compat level you have. */
ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 160;
ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON; /* 2019+ only */
EXEC DropIndexes;
GO
/* These indexes should help our query: */
CREATE INDEX Location ON dbo.Users(Location);
CREATE INDEX UserId_Incl ON dbo.Comments(UserId) INCLUDE (CreationDate);
CREATE INDEX OwnerUserId ON dbo.Posts(OwnerUserId);
CREATE INDEX CreationDate_Incl ON dbo.Comments(CreationDate) INCLUDE (UserId);
CREATE INDEX CreationDate_Incl ON dbo.Posts(CreationDate) INCLUDE(OwnerUserId);

GO
CREATE OR ALTER PROC dbo.rpt_TopPostsByLocation
	@Location NVARCHAR(100), @StartDate DATE, @EndDate DATE AS
BEGIN
	SELECT TOP 250 p.Title AS QuestionTitle, p.Score AS QuestionScore, p.CreationDate,
		u.DisplayName AS UserDisplayName, u.Reputation AS User_Reputation, u.AboutMe
	FROM dbo.Users u
		INNER JOIN dbo.Posts p ON u.Id = p.OwnerUserId
	WHERE u.Location = @Location
	  AND p.CreationDate >= @StartDate
	  AND p.CreationDate < @EndDate
	  AND p.CommentCount > 0
	ORDER BY p.Score DESC
END
GO
DBCC FREEPROCCACHE;

EXEC dbo.rpt_TopPostsByLocation @Location = 'San Diego, CA',
	@StartDate = '2024-09-01', @EndDate = '2024-09-30';

EXEC dbo.rpt_TopPostsByLocation @Location = 'San Diego, CA',
	@StartDate = '2014-09-01', @EndDate = '2014-09-30';

EXEC dbo.rpt_TopPostsByLocation @Location = 'San Diego, CA',
	@StartDate = '2014-01-01', @EndDate = '2014-12-31';

EXEC dbo.rpt_TopPostsByLocation @Location = 'San Diego, CA',
	@StartDate = '2001-01-01', @EndDate = '2028-12-31';



/* If a query takes a long time to run, your options include:

* Get the estimated plan

* Look at the live plan with sp_BlitzWho or Activity Monitor
  SQL 2016 SP1 or newer: https://www.brentozar.com/archive/2017/10/get-live-query-plans-sp_blitzwho/

* Run it with Live Query Statistics on

* Get the last actual plan with sp_BlitzCache:
  SQL 2019 or newer: https://www.brentozar.com/archive/2016/08/run-sp_blitzcache-single-query/
*/


GO	



CREATE OR ALTER PROC dbo.rpt_TopPosts_FromTopLocations
	@StartDate DATE, @EndDate DATE AS
BEGIN
	WITH TopLocations AS (SELECT TOP 10 u.Location, COUNT(*) AS Users
		FROM dbo.Users u
		WHERE u.Location <> ''
		GROUP BY u.Location
		ORDER BY COUNT(*) DESC
	)
	SELECT TOP 250 p.Title AS QuestionTitle, p.Score AS QuestionScore, p.CreationDate,
		u.DisplayName AS UserDisplayName, u.Reputation AS User_Reputation, u.AboutMe
	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.PostTypes pt ON p.PostTypeId = pt.Id
	WHERE pt.Type = 'Question'
	  AND p.CreationDate >= @StartDate
	  AND p.CreationDate < @EndDate
	  AND p.CommentCount > 0
	ORDER BY p.Score DESC
END
GO


/* Turn on actual plans & our query options: */
SET STATISTICS IO, TIME ON;

/* My users have been complaining about this: */
EXEC rpt_TopPosts_FromTopLocations 
	@StartDate = '2024-01-01', @EndDate = '2024-12-31'
GO

/* If a query takes a long time to run, your options include:

* Get the estimated plan

* Look at the live plan with sp_BlitzWho or Activity Monitor
  SQL 2016 SP1 or newer: https://www.brentozar.com/archive/2017/10/get-live-query-plans-sp_blitzwho/

* Run it with Live Query Statistics on

* Get the last actual plan with sp_BlitzCache:
  SQL 2019 or newer: https://www.brentozar.com/archive/2016/08/run-sp_blitzcache-single-query/
*/



/* Things to think about as you tune this:

* Estimates vs actuals
* Index suggestions
* Parallelism

*/



/*
License: Creative Commons Attribution-ShareAlike 4.0 Unported (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.
* No additional restrictions — You may not apply legal terms or technological 
  measures that legally restrict others from doing anything the license permits.
*/