Skip to content

Instantly share code, notes, and snippets.

View BrentOzar's full-sized avatar
❤️

Brent Ozar BrentOzar

❤️
681 followers · 11 following
View GitHub Profile
@BrentOzar
BrentOzar / CreateDatabases
Created June 25, 2016 12:03
Create a bunch of databases in a loop
DECLARE @StringTemplate VARCHAR(1000);
DECLARE @StringToExecute VARCHAR(1000);
DECLARE @Counter TINYINT = 1;
SET @StringTemplate = 'CREATE DATABASE [ConsumeMassQuantities##REPLACE##]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N''ConsumeMassQuantities'', FILENAME = N''D:\MSSQL\Data\ConsumeMassQuantities##REPLACE##.mdf'' , SIZE = 8192KB , FILEGROWTH = 65536KB )
LOG ON
( NAME = N''ConsumeMassQuantities_log'', FILENAME = N''D:\MSSQL\Data\ConsumeMassQuantities##REPLACE##_log.ldf'' , SIZE = 8192KB , FILEGROWTH = 65536KB )'
@BrentOzar
BrentOzar / gist:66f52a7b59a3dabf772a376cbf039850
Created November 22, 2016 18:32
CREATE DATABASE [Collation_100_CI_AS_KS_WS_SC]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'Collation_100_CI_AS_KS_WS_SC', FILENAME = N'M:\MSSQL\Data\Collation_100_CI_AS_KS_WS_SC.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
LOG ON
( NAME = N'Collation_100_CI_AS_KS_WS_SC_log', FILENAME = N'M:\MSSQL\Data\Collation_100_CI_AS_KS_WS_SC_log.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
COLLATE Latin1_General_100_CS_AS_KS_WS_SC
GO
sp_BlitzIndex @DatabaseName = 'Collation_100_CI_AS_KS_WS_SC';
@BrentOzar
BrentOzar / WaitCategories.sql
Created September 20, 2017 18:06
Create ##WaitCategories table to categorize SQL Server wait stats
/*
Wait stat categories from: https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-query-store-wait-stats-transact-sql
Updated on: 2017/09/20
*/
IF OBJECT_ID('tempdb..##WaitCategories') IS NULL
BEGIN
/* We reuse this one by default rather than recreate it every time. */
CREATE TABLE ##WaitCategories
(
@BrentOzar
BrentOzar / StackOverflow-Indexes.sql
Created September 3, 2017 12:06
Creating basic indexes on the public Stack Overflow database export
CREATE INDEX IX_UserId ON dbo.Badges(UserId);
GO
CREATE INDEX IX_PostId ON dbo.Comments(PostId);
GO
CREATE INDEX IX_UserId ON dbo.Comments(UserId);
GO
CREATE INDEX IX_PostId ON dbo.PostLinks(PostId);
GO
CREATE INDEX IX_RelatedPostId ON dbo.PostLinks(RelatedPostId);
GO
@BrentOzar
BrentOzar / Testing Snapshot Materialized Views.sql
Created October 3, 2017 14:50
/*
This message gives me a clue about the database requirements:
10650 – Refresh of snapshot view(s) requires snapshot isolation to be enabled on the database.
So let's set that up:
*/
CREATE VIEW dbo.Ocean WITH SNAPSHOT
AS
@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
@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 / 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 / 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 / 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