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

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

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

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

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

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

{  
   "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

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

CREATE TABLE [dbo].[Posts_MultiTenant](
	[Id] [int] IDENTITY(1,1) NOT NULL,
    [CompanyCode] VARCHAR(10) NOT NULL,
	[AcceptedAnswerId] [int] NULL,
	[AnswerCount] [int] NULL,
	[Body] [nvarchar](max) NOT NULL,
	[ClosedDate] [datetime] NULL,
	[CommentCount] [int] NULL,
	[CommunityOwnedDate] [datetime] NULL,
	[CreationDate] [datetime] NOT NULL,

BrentOzar

/* 
SQL Server Plan Cache History by Date & Time
Brent Ozar, 2018/07/03 - https://BrentOzar.com/go/plansbydate

This is free and unencumbered software released into the public domain.

Anyone is free to copy, modify, publish, use, compile, sell, or
distribute this software, either in source code form or as a compiled
binary, for any purpose, commercial or non-commercial, and by any
means.