Skip to content

Instantly share code, notes, and snippets.

View BrentOzar's full-sized avatar
❤️

Brent Ozar BrentOzar

❤️
634 followers · 11 following
View GitHub Profile
@BrentOzar
BrentOzar / Create ~1000 partitioned indexes on the Stack Overflow Users table
Created April 23, 2019 16:22
Creates a database with an empty Users table, with about 1,000 indexes, each on a partition scheme with about 1,000 partitions
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
BrentOzar / Decimal length demo
Created March 6, 2019 12:59
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
BrentOzar / Automatic tuning details
Last active February 14, 2019 22:46
{
"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
BrentOzar / Getting Better Query Plans by Improving SQL Server's Estimates.sql
Created February 7, 2019 18:48
/*
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
BrentOzar / Posts_MultiTenant.sql
Created October 24, 2018 11:40
Creating a multi-tenant version of StackOverflow.dbo.Posts
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
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 / Fun With ASCII
Last active July 9, 2022 21:19
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
@BrentOzar
BrentOzar / fn_hadr_fabric_get_node_health_states.xml
Created March 16, 2018 19:00
<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>
@BrentOzar
BrentOzar / Azure Managed Instance Errorlog 2
Created March 16, 2018 18:29
This file has been truncated, but you can view the full file.
Date,Source,Severity,Message
03/16/2018 00:37:03,spid142s,Unknown,[CFabricReplicaManager::GetServiceDescriptionFromFabric] EndGetServiceDescription for fabric service uri: fabric:/Worker.CL/ce39943cee8e/SQL.LogicalServer/ce39943cee8e completed with result: 80071bcd
03/16/2018 00:37:03,spid142s,Unknown,[CFabricReplicaManager::GetServiceDescriptionFromFabric] WaitForCallbackInvocation for fabric service uri: fabric:/Worker.CL/ce39943cee8e/SQL.LogicalServer/ce39943cee8e completed with result: 0
03/16/2018 00:37:03,spid142s,Unknown,[CFabricReplicaManager::GetServiceDescriptionFromFabric] BeginGetServiceDescription for fabric service uri: fabric:/Worker.CL/ce39943cee8e/SQL.LogicalServer/ce39943cee8e completed with result: 0
03/16/2018 00:37:03,spid142s,Unknown,[CFabricReplicaManager::GetServiceDescriptionFromFabric] GetFabricServiceNameInt for fabric service uri: fabric:/Worker.CL/ce39943cee8e/SQL.LogicalServer/ce39943cee8e completed with result: 0
03/16/2018 00:37:03,spid142s,Unknown,[CFabricReplicaManager::GetSe
@BrentOzar
BrentOzar / Azure Managed Instance Errorlog 1
Created March 16, 2018 18:28
This file has been truncated, but you can view the full file.
Date,Source,Severity,Message
03/15/2018 21:01:51,spid120s,Unknown,[CFabricReplicaManager::GetServiceDescriptionFromFabric] EndGetServiceDescription for fabric service uri: fabric:/Worker.CL/ce39943cee8e/SQL.LogicalServer/ce39943cee8e completed with result: 80071bcd
03/15/2018 21:01:51,spid120s,Unknown,[CFabricReplicaManager::GetServiceDescriptionFromFabric] WaitForCallbackInvocation for fabric service uri: fabric:/Worker.CL/ce39943cee8e/SQL.LogicalServer/ce39943cee8e completed with result: 0
03/15/2018 21:01:51,spid120s,Unknown,[CFabricReplicaManager::GetServiceDescriptionFromFabric] BeginGetServiceDescription for fabric service uri: fabric:/Worker.CL/ce39943cee8e/SQL.LogicalServer/ce39943cee8e completed with result: 0
03/15/2018 21:01:51,spid120s,Unknown,[CFabricReplicaManager::GetServiceDescriptionFromFabric] GetFabricServiceNameInt for fabric service uri: fabric:/Worker.CL/ce39943cee8e/SQL.LogicalServer/ce39943cee8e completed with result: 0
03/15/2018 21:01:51,spid120s,Unknown,[CFabricReplicaManager::GetSe