Skip to content

Instantly share code, notes, and snippets.

@BrentOzar

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

Last active September 14, 2024 12:55
Show Gist options
  • Save BrentOzar/957d2e76b4b51990944bc0ac5c4d4b36 to your computer and use it in GitHub Desktop.
Save BrentOzar/957d2e76b4b51990944bc0ac5c4d4b36 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
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.
*/
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment