Query Exercise: Improving Cardinality Estimation

Your challenge for this week is to tune a query. Say Stack Overflow has a dashboard that shows the top-ranking users in their most popular location. It’s even got an index to support it:

CREATE INDEX Location ON dbo.Users(Location); GO CREATE OR ALTER PROC dbo.GetTopUsersInTopLocation AS SELECT TOP 200 u.Reputation, u.Id, u.DisplayName, u.WebsiteUrl, u.CreationDate FROM dbo.Users u WHERE u.Location = (SELECT TOP 1 Location FROM dbo.Users WHERE Location <> N » GROUP BY Location ORDER BY COUNT(*) DESC) ORDER BY u.Reputation DESC; GO

You can test it with any version of the Stack Overflow database. To test it, we’ll turn on a