Skip to content

Instantly share code, notes, and snippets.

Avatar
❤️

Brent Ozar BrentOzar

❤️
View GitHub Profile
View An Introduction to SQL Server Statistics with Playing Cards
/* SQL Server Statistics Explained with Playing Cards
v0.1 - 2020-08-14
https://BrentOzar.com/go/learnstats
This first RAISERROR is just to make sure you don't accidentally hit F5 and
run the entire script. You don't need to run this:
*/
RAISERROR(N'Oops! No, don''t just hit F5. Run these demos one at a time.', 20, 1) WITH LOG;
GO
View SQL Server 2019 inline function bug
ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 140;
GO
CREATE OR ALTER FUNCTION dbo.Test()
RETURNS INT AS
BEGIN
DECLARE @i BIGINT;
SELECT TOP 1 @i = CHECKSUM(*)
FROM master.dbo.spt_values;
RETURN 1;
@BrentOzar
BrentOzar / sys.index_resumable_operations.sql
Created Feb 20, 2020
Showing the (in)accuracy of sys.index_resumable_operations.
View sys.index_resumable_operations.sql
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);
View UsersMemberships.sql
USE StackOverflow;
GO
DROP TABLE IF EXISTS dbo.UsersMemberships;
CREATE TABLE dbo.UsersMemberships
(Id INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
UserId INT NOT NULL,
StartDate DATETIME NOT NULL,
EndDate DATETIME NOT NULL,
CancelledEarlyDate DATETIME NULL);
@BrentOzar
BrentOzar / How to Make SELECT COUNT(*) Queries Crazy Fast.sql
Created Dec 22, 2019
How to Make SELECT COUNT(*) Queries Crazy Fast.sql
View How to Make SELECT COUNT(*) Queries Crazy Fast.sql
USE StackOverflow;
GO
DropIndexes;
GO
ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 140;
GO
/* Check the table's size: */
sp_BlitzIndex @TableName = 'Votes';
GO
View Branching proc.sql
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
BrentOzar / sp_Blitz_as_script.sql
Created Dec 2, 2019
Long query without the create proc
View sp_Blitz_as_script.sql
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 ,
View Troubleshooting memory consumption
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
BrentOzar / Create ~1000 partitioned indexes on the Stack Overflow Users table
Created Apr 23, 2019
Creates a database with an empty Users table, with about 1,000 indexes, each on a partition scheme with about 1,000 partitions
View Create ~1000 partitioned indexes on the Stack Overflow Users table
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,
View Decimal length demo
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;