Getting Better Query Plans by Improving SQL Server's Estimates.sql

/*
Getting Better Query Plans by Improving SQL's Estimates
v1.1 - 2018-11-05

This demo requires:

* Any supported version of SQL Server (2008 or newer.) There are a few times
  in here where I'll show SQL Server 2017/2019 stuff, but I'll call that out.
* Stack Overflow database: https://www.BrentOzar.com/go/querystack
  The small 2010 version will work fine. Of course, the exact row counts and
  logical page reads will be different on each Stack database, but that's OK.



License: Creative Commons Attribution-ShareAlike 3.0 Unported (CC BY-SA 3.0)
More info: https://creativecommons.org/licenses/by-sa/3.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.
* ShareAlike - If you remix, transform, or build upon the material, you must
  distribute your contributions under the same license as the original.
*/

USE StackOverflow2013;
GO
/* Start in SQL 2008 compatibility, old skool: */
ALTER DATABASE [StackOverflow2013] SET COMPATIBILITY_LEVEL = 100
GO
/* Drop all your nonclustered indexes: https://BrentOzar.com/go/dropindexes */
DropIndexes;
GO

/* Get an estimated plan: */
SELECT Location, COUNT(*)
  FROM dbo.Users
  GROUP BY Location
  ORDER BY COUNT(*) DESC
  OPTION (MAXDOP 1);
GO


/* 
Run it with actual plans on.



Run it several times, and note that the estimates aren't updating, and the sort
spills to disk every single time.

*/
SELECT Location, COUNT(*)
  FROM dbo.Users
  GROUP BY Location
  ORDER BY COUNT(*) DESC
  OPTION (MAXDOP 1);
GO 3





/* Try SQL Server 2017's newer cardinality estimator: */
ALTER DATABASE [StackOverflow2013] SET COMPATIBILITY_LEVEL = 140
GO
SELECT Location, COUNT(*)
  FROM dbo.Users
  GROUP BY Location
  ORDER BY COUNT(*) DESC
  OPTION (MAXDOP 1);
GO


/* Is SQL Server 2019 any better? */
ALTER DATABASE [StackOverflow2013] SET COMPATIBILITY_LEVEL = 150
GO
SELECT Location, COUNT(*)
  FROM dbo.Users
  GROUP BY Location
  ORDER BY COUNT(*) DESC
  OPTION (MAXDOP 1);
GO



/* Running them back to back to see the difference
(Yes, there are query-level hints for this, but more on that later.) */
ALTER DATABASE [StackOverflow2013] SET COMPATIBILITY_LEVEL = 140
GO
SELECT Location, COUNT(*)
  FROM dbo.Users
  GROUP BY Location
  ORDER BY COUNT(*) DESC
  OPTION (MAXDOP 1);
GO
ALTER DATABASE [StackOverflow2013] SET COMPATIBILITY_LEVEL = 150
GO
SELECT Location, COUNT(*)
  FROM dbo.Users
  GROUP BY Location
  ORDER BY COUNT(*) DESC
  OPTION (MAXDOP 1);
GO


/* Let's go back to 2017, since you're still stuck in the present: */
ALTER DATABASE [StackOverflow2013] SET COMPATIBILITY_LEVEL = 140
GO


/* Try creating a statistic. Will the sort still spill to disk? */
CREATE STATISTICS STAT_Location ON dbo.Users(Location);
GO

SELECT Location, COUNT(*)
  FROM dbo.Users
  GROUP BY Location
  ORDER BY COUNT(*) DESC
  OPTION (MAXDOP 1);
GO



/* You can update the stat with fullscan if you like: */
UPDATE STATISTICS dbo.Users WITH FULLSCAN;
GO
SELECT Location, COUNT(*)
  FROM dbo.Users
  GROUP BY Location
  ORDER BY COUNT(*) DESC
  OPTION (MAXDOP 1);
GO
/* You might have to free the plan cache, too. (sigh)
DBCC FREEPROCCACHE
*/




/* But we're still doing the sort. Let's see what else we can do. */
DropIndexes
GO

/* How about an index? Will that remove the sort altogether? */
CREATE INDEX IX_Location ON dbo.Users(Location);
GO
SELECT Location, COUNT(*)
  FROM dbo.Users
  GROUP BY Location
  ORDER BY COUNT(*) DESC
  OPTION (MAXDOP 1);
GO


/* 

No - but we:
* Aren't spilling the sort to disk (because the index includes a stat), AND
* The object we're scanning is smaller, AND
* We're doing a stream aggregate instead of a hash match

*/






/* Next up: */
CREATE OR ALTER PROC dbo.usp_UsersInTopLocation AS
BEGIN
DECLARE @TopLocation NVARCHAR(100);

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

SELECT *
  FROM dbo.Users
  WHERE Location = @TopLocation
  ORDER BY DisplayName;
END
GO

EXEC usp_UsersInTopLocation
GO



CREATE OR ALTER PROC dbo.usp_UsersInTopLocation_StatementRecompile AS
BEGIN
DECLARE @TopLocation NVARCHAR(100);

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

SELECT *
  FROM dbo.Users
  WHERE Location = @TopLocation
  ORDER BY DisplayName OPTION (RECOMPILE);
END
GO

EXEC usp_UsersInTopLocation_StatementRecompile;
GO



CREATE OR ALTER PROC dbo.usp_UsersInTopLocation_ProcRecompile WITH RECOMPILE AS
BEGIN
DECLARE @TopLocation NVARCHAR(100);

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

SELECT *
  FROM dbo.Users
  WHERE Location = @TopLocation
  ORDER BY DisplayName;
END
GO
EXEC usp_UsersInTopLocation_ProcRecompile
GO



CREATE OR ALTER PROC dbo.usp_UsersInTopLocation_CTE AS
BEGIN
WITH TopLocation AS (SELECT TOP 1 Location
  FROM dbo.Users
  WHERE Location <> ''
  GROUP BY Location
  ORDER BY COUNT(*) DESC)
SELECT u.*
  FROM TopLocation
    INNER JOIN dbo.Users u ON TopLocation.Location = u.Location
  ORDER BY DisplayName;
END
GO

EXEC usp_UsersInTopLocation_CTE
GO




CREATE OR ALTER PROC dbo.usp_UsersInTopLocation_Subquery AS
BEGIN
SELECT *
  FROM dbo.Users u
  WHERE Location = (SELECT TOP 1 Location
					  FROM dbo.Users
					  WHERE Location <> ''
					  GROUP BY Location
					  ORDER BY COUNT(*) DESC)
  ORDER BY DisplayName;
END
GO

EXEC usp_UsersInTopLocation_Subquery
GO


/* And for bonus points, if you want to tackle this one at home: */
CREATE OR ALTER PROC dbo.usp_UsersInTop5Locations AS
BEGIN
SELECT *
  FROM dbo.Users u
  WHERE Location IN (SELECT TOP 5 Location
					  FROM dbo.Users
					  WHERE Location <> ''
					  GROUP BY Location
					  ORDER BY COUNT(*) DESC)
  ORDER BY DisplayName;
END
GO

EXEC usp_UsersInTop5Locations
GO


















CREATE OR ALTER PROC dbo.usp_UsersInTop5Locations_TempTables AS
BEGIN
CREATE TABLE #TopLocations (Location NVARCHAR(100));
INSERT INTO #TopLocations (Location)
  SELECT TOP 5 Location
	FROM dbo.Users
	WHERE Location <> ''
	GROUP BY Location
	ORDER BY COUNT(*) DESC;

SELECT *
  FROM #TopLocations tl
  INNER JOIN dbo.Users u ON tl.Location COLLATE DATABASE_DEFAULT = u.Location COLLATE DATABASE_DEFAULT 
  ORDER BY DisplayName;
END
GO

DROP TABLE #TopLocations;
GO
EXEC usp_UsersInTop5Locations_TempTables
GO








CREATE TABLE #TopLocations (Location NVARCHAR(100));
INSERT INTO #TopLocations (Location)
  SELECT TOP 5 Location
	FROM dbo.Users
	WHERE Location <> ''
	GROUP BY Location
	ORDER BY COUNT(*) DESC;

SELECT *
  FROM #TopLocations tl
  INNER JOIN dbo.Users u ON tl.Location = u.Location
  ORDER BY DisplayName;
GO

/* Look at the query plan, get the temp table & stats names, or do it the easy way: */
USE tempdb;
GO
/* Get the stat name: */
SELECT name FROM tempdb.sys.stats WHERE object_id = OBJECT_ID('#TopLocations');
/* And put it in here: */
DBCC SHOW_STATISTICS('#TopLocations', '_WA_Sys_00000001_ABE152B8');
GO
DROP TABLE #TopLocations;
GO



USE StackOverflow;
GO

EXEC usp_UsersInTop5Locations_TempTables
GO