Skip to content

Instantly share code, notes, and snippets.

Avatar
❤️

Brent Ozar BrentOzar

❤️
View GitHub Profile
@BrentOzar
BrentOzar / CreateDatabases
Created Jun 25, 2016
Create a bunch of databases in a loop
View CreateDatabases
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 / TopQueries.sql
Last active Jul 4, 2021
Some of the top StackOverflow queries from http://data.stackexchange.com. Great for demoing wide SELECT workloads.
View TopQueries.sql
USE StackOverflow;
GO
IF OBJECT_ID('dbo.usp_Q7521') IS NULL
EXEC ('CREATE PROCEDURE dbo.usp_Q7521 AS RETURN 0;')
GO
ALTER PROC dbo.usp_Q7521 @UserId INT AS
BEGIN
/* Source: http://data.stackexchange.com/stackoverflow/query/7521/how-unsung-am-i */
View gist:66f52a7b59a3dabf772a376cbf039850
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 / StackOverflow-Indexes.sql
Created Sep 3, 2017
Creating basic indexes on the public Stack Overflow database export
View StackOverflow-Indexes.sql
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 / WaitCategories.sql
Created Sep 20, 2017
Create ##WaitCategories table to categorize SQL Server wait stats
View WaitCategories.sql
/*
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
(
View Testing Snapshot Materialized Views.sql
/*
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 / Looping through SQL Server trace flags to find the max current one
Last active Feb 17, 2019
Looping through SQL Server trace flags to find the max one
View Looping through SQL Server trace flags to find the max current one
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
BrentOzar / Finding undocumented trace flags.sql
Last active Jan 11, 2018
Stored procedure that continuously restarts SQL Server looking for undocumented trace flags
View Finding undocumented trace flags.sql
/*
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
*/
View Azure Managed Instance Errorlog 1
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
View Azure Managed Instance Errorlog 2
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