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

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

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

<HealthEvent>
    <HealthInformation>
        <SourceId>System.FM</SourceId>
        <Property>State</Property>
        <TimeToLiveSeconds>0</TimeToLiveSeconds>
        <State>OK</State>
        <Description>Fabric node is up.</Description>
        <SequenceNumber>14</SequenceNumber>
        <RemoveWhenExpired>false</RemoveWhenExpired>
    </HealthInformation>