Skip to content

Instantly share code, notes, and snippets.

Avatar
❤️

Brent Ozar BrentOzar

❤️
View GitHub Profile
View Automatic tuning details
{
"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"
View Getting Better Query Plans by Improving SQL Server's Estimates.sql
/*
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 Oct 24, 2018
Creating a multi-tenant version of StackOverflow.dbo.Posts
View Posts_MultiTenant.sql
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 Jul 3, 2018
Report on SQL Server's plan cache history over time.
View PlanCacheByDateAndHour.sql
/*
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.
View Fun With ASCII
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
View fn_hadr_fabric_get_node_health_states.xml
<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>
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
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
@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
*/
@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();