BrentOzar

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

BrentOzar

{  
   "createIndexDetails":{  
      "indexName":"nci_wi_Badges_6E3E4E1FB3A8DD0F4201053BD6EA0D5F",
      "indexType":"NONCLUSTERED",
      "schema":"[dbo]",
      "table":"[Badges]",
      "indexColumns":"[Name], [Date]",
      "includedColumns":"[UserId]",
      "indexActionStartTime":"2019-02-14T13:12:15",
      "indexActionDuration":"00:02:56.9860000"

BrentOzar

DECLARE @CurrentTraceFlag INT = 1
/* Hard coding in a max number in case they get wise to this trick and start failing silently */
WHILE @CurrentTraceFlag < 20000
	BEGIN
		BEGIN TRY
			DBCC TRACEON(@CurrentTraceFlag, -1);
			DBCC TRACEOFF(@CurrentTraceFlag, -1);
		END TRY
		BEGIN CATCH
			SELECT @CurrentTraceFlag, ERROR_NUMBER(), ERROR_MESSAGE();

BrentOzar

CREATE TABLE dbo.UsersDemo (Id INT PRIMARY KEY CLUSTERED, Reputation DECIMAL(16,0));
GO
INSERT INTO dbo.UsersDemo (Id, Reputation)
  SELECT Id, Reputation
  FROM dbo.Users WITH (NOLOCK);
GO


/* Turn on actual plans and run both of these: */
SET STATISTICS IO ON;

BrentOzar

CREATE DATABASE MuchoPartitions;
GO
USE MuchoPartitions;
GO

CREATE TABLE [dbo].[Users](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[AboutMe] [nvarchar](max) NULL,
	[Age] [int] NULL,
	[CreationDate] [datetime] NOT NULL,

BrentOzar

SELECT msm.*, hbp.*, pmem_in_use_kb = pm.physical_memory_in_use_kb, large_pages_kb = pm.large_page_allocations_kb,
        locked_pages_kb = pm.locked_page_allocations_kb, commit_limit_kb = available_commit_limit_kb,
        tot_mem_kb = sm.total_physical_memory_kb, [version] = @@VERSION,
        sm.available_physical_memory_kb
FROM sys.dm_os_process_memory pm
CROSS JOIN sys.dm_os_sys_memory sm
CROSS JOIN (SELECT max_server_mem = value_in_use FROM sys.configurations
            WHERE NAME = 'max server memory (MB)') msm
CROSS JOIN (SELECT hybrid_buffer_pool = value_in_use FROM sys.configurations
            WHERE NAME = 'hybrid_buffer_pool') hbp

BrentOzar

DECLARE
    @Help TINYINT = 0 ,
    @CheckUserDatabaseObjects TINYINT = 1 ,
    @CheckProcedureCache TINYINT = 0 ,
    @OutputType VARCHAR(20) = 'TABLE' ,
    @OutputProcedureCache TINYINT = 0 ,
    @CheckProcedureCacheFilter VARCHAR(10) = NULL ,
    @CheckServerInfo TINYINT = 0 ,
    @SkipChecksServer NVARCHAR(256) = NULL ,
    @SkipChecksDatabase NVARCHAR(256) = NULL ,

BrentOzar

CREATE PROCEDURE dbo.GetUnshippedOrders @IsShipped bit
AS
BEGIN
  SET NOCOUNT ON;
  IF @IsShipped = 1
    EXEC dbo.GetUnshippedOrders_UsesFilteredIndex
  ELSE
    SELECT OrderID, OrderDate FROM dbo.Orders WHERE IsShipped = @IsShipped;
END
GO

BrentOzar

USE StackOverflow;
GO
DropIndexes;
GO
ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 140;
GO

/* Check the table's size: */
sp_BlitzIndex @TableName = 'Votes';
GO

BrentOzar

DROP TABLE IF EXISTS dbo.DiningRoomTable;
GO
CREATE TABLE dbo.DiningRoomTable (Id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, Stuffing CHAR(1000));
INSERT INTO dbo.DiningRoomTable (Stuffing)
  SELECT 'Stuff'
  FROM sys.messages;
GO

CREATE INDEX IX_Stuffing ON dbo.DiningRoomTable(Stuffing)
  WITH (ONLINE = ON, RESUMABLE = ON);