Skip to content

Instantly share code, notes, and snippets.

View BrentOzar's full-sized avatar
❤️

Brent Ozar BrentOzar

❤️
View GitHub Profile
@BrentOzar
BrentOzar / PlanCacheByDateAndHour.sql
Created July 3, 2018 11:28
Report on SQL Server's plan cache history over time.
/*
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.
@BrentOzar
BrentOzar / ADR and RCSI Demo of Index Rebuilds.sql
Created January 9, 2025 16:11
ADR and RCSI Demo of Index Rebuilds
DROP DATABASE Test;
DROP DATABASE Test_ADR;
DROP DATABASE Test_ADR_RCSI;
DROP DATABASE Test_RCSI;
CREATE DATABASE Test;
CREATE DATABASE Test_ADR;
ALTER DATABASE Test_ADR SET ACCELERATED_DATABASE_RECOVERY = ON;
GO
@BrentOzar
BrentOzar / Finding undocumented trace flags.sql
Last active January 4, 2025 22:04
Stored procedure that continuously restarts SQL Server looking for undocumented trace flags
/*
THIS IS A SPECTACULARLY BAD IDEA.
Never, ever, ever run this in production.
Don't even run it on a VM that you care about.
(I specifically build a throwaway VM just to run this.)
Get the latest version:
https://gist.github.com/BrentOzar/5d8e4e8cbc1d89d1021b6f889762d835
*/
@BrentOzar
BrentOzar / Tune.sql
Last active November 8, 2024 14:49
Watch Brent Tune Queries - Misleading Stored Proc Edition
/*
Watch Brent Tune Queries: Misleading Stored Proc Edition
Brent Ozar - v1.0 - 2024-11-08
https://BrentOzar.com/go/watch
This demo requires:
* Any version of SQL Server, but I use SQL Server 2022
running in 2022 compat level. If you want to use
an earlier version, your plans will vary.
/*
Watch Brent Tune Queries: Top Posts From Top Locations
v1.2 - 2024-09-07
https://www.BrentOzar.com/go/tunequeries
This demo requires:
* Any supported version of SQL Server
* A large Stack Overflow database: https://www.BrentOzar.com/go/querystack
@BrentOzar
BrentOzar / gist:9e8834098ec320ba8754399d21a612bc
Created July 25, 2024 09:44
Dynamically generating large queries
DECLARE @NumberOfLayers INT = 1000;
SELECT 0 AS Ordered, 'DECLARE @t TABLE (Id INT PRIMARY KEY CLUSTERED);'
UNION
SELECT 1 AS Ordered, 'WITH CTE1 AS (SELECT * FROM @t t1)'
UNION
SELECT value AS Ordered, ', CTE' + CAST(value AS VARCHAR(10)) + ' AS (SELECT cA.* ' +
' FROM CTE' + CAST(value - 1 AS VARCHAR(10)) + ' cA INNER JOIN ' +
' CTE' + CAST(value - 1 AS VARCHAR(10)) + ' cB ON cA.Id = cB.Id) '
FROM GENERATE_SERIES(2,@NumberOfLayers)
@BrentOzar
BrentOzar / Conversation.tsql
Created January 11, 2024 18:44
Triggers to keep Posts.CommentCount in sync
/* What sent to ChatGPT 4: */
You are a T-SQL database developer working with Microsoft SQL Server 2019. Given these two tables:
CREATE TABLE [dbo].[Comments](
[Id] [int] IDENTITY(1,1) NOT NULL,
[CreationDate] [datetime] NOT NULL,
[PostId] [int] NOT NULL,
[Score] [int] NULL,
[Text] [nvarchar](700) NOT NULL,
/* 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
@BrentOzar
BrentOzar / schema.sql
Created October 21, 2022 16:50
Stack Overflow Data Dump Schema
USE [StackOverflow]
GO
/****** Object: Table [dbo].[Badges] Script Date: 10/21/2022 12:48:56 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Badges]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Badges](
CREATE DATABASE [٠০౦০٠];
GO
USE [٠০౦০٠];
GO
CREATE SCHEMA [٠০౦০٠];
GO
CREATE TABLE [٠০౦০٠].[٠০౦০٠]([٠০౦০٠] NVARCHAR(20), [۰০౦০٠] NVARCHAR(20), [٠০౦০۰] NVARCHAR(20), [۰০౦০۰] NVARCHAR(20));
GO
CREATE UNIQUE CLUSTERED INDEX [٠০౦০٠] ON [٠০౦০٠].[٠০౦০٠]([٠০౦০٠], [۰০౦০٠], [٠০౦০۰], [۰০౦০۰]);
GO