Skip to content

Instantly share code, notes, and snippets.

View LitKnd's full-sized avatar
🏠
Working from home

Kendra Little LitKnd

🏠
Working from home
View GitHub Profile
@LitKnd
LitKnd / !DDL-Trigger-Sample.sql
Last active January 9, 2022 17:05
SQL Server DDL Trigger for Index Create, Alter, Rename
/***********************************************************************
Copyright 2016, Kendra Little - LittleKendra.com
MIT License, http://www.opensource.org/licenses/mit-license.php
***********************************************************************/
USE WideWorldImporters;
GO
IF (select count(*) from sys.schemas where name='ddl')=0
exec ('CREATE SCHEMA ddl AUTHORIZATION dbo;');
@LitKnd
LitKnd / !configure-blocked-process-report.sql
Last active April 24, 2023 19:57
Blocked Process Report - Configuring and Tracing - SQL Server
/***********************************************************************
Copyright 2016, Kendra Little - littlekendra.com
MIT License, http://www.opensource.org/licenses/mit-license.php
***********************************************************************/
/***********************************************************************
FIRST, TELL SQL SERVER TO ISSUE THE BLOCKED PROCESS REPORT
***********************************************************************/
/* Check if there are any pending configuration items before you start */
/***********************************************************************
Copyright 2016, Kendra Little - LittleKendra.com
MIT License, http://www.opensource.org/licenses/mit-license.php
***********************************************************************/
/***********************************************************************
COLLECT DEADLOCK GRAPHS WITH EXTENDED EVENTS
This script contains TSQL to:
* Create an Extended Events Trace collecting sqlserver.xml_deadlock_report
@LitKnd
LitKnd / License-Info.md
Last active May 17, 2017 21:39
License Information for public Gists under LitKnd
/************************************************************
SET UP THE REPRO
************************************************************/
WHILE @@trancount > 0
ROLLBACK
GO
USE master;
GO
/* Note: don't run this all at once. There are prompts to run some queries in another session, etc. */
WHILE @@trancount > 0
ROLLBACK
GO
USE master;
GO
IF DB_ID('lockingtest') IS NOT NULL
/*************************************************************
Demo script for adding partitions to the left end of a LEFT
defined partitioned function in SQL Server which uses one filegroup
per partition.
Note: this is demo code only! Use at your own risk.
Always test changing a partition function &/or scheme carefully a
gainst a restored backup of your database and look carefully for
data movement or any performance concerns.
SELECT
sc.name + N'.' + so.name as [Schema.Table],
si.index_id as [Index ID],
si.type_desc as [Structure],
si.name as [Index],
stat.row_count AS [Rows],
stat.in_row_reserved_page_count * 8./1024./1024. as [In-Row GB],
stat.lob_reserved_page_count * 8./1024./1024. as [LOB GB],
p.partition_number AS [Partition #],
pf.name as [Partition Function],
@LitKnd
LitKnd / Execution-Cache-Single-Use-Plans-Explore.sql
Created January 31, 2017 17:50
TSQL to do a quick and dirty look at single-use plans in the execution plan cache of a SQL Server.
/***********************************************************
TSQL to do a quick and dirty look at single-use plans in
the execution plan cache of a SQL Server.
************************************************************/
/* Size of single use adhoc plans in execution plan cache */
SELECT
objtype,
cacheobjtype,
SUM(size_in_bytes)/1024./1024. as [MB]
@LitKnd
LitKnd / Restore-WideWorldImporters-Full-Backup.sql
Created January 31, 2017 22:51
Sample code to restore the free WideWorldImporters Sample Database
/* Download the WideWorldImporters sample database from Microsoft's GitHub page:
This file contains sample TSQL to restore the file WideWorldImporters-Full.bak
Notes & Warnings:
This will BLOW AWAY any existing database named WideWorldImporters.
Adjust the location of the backup file to the location you stored it on your test instance.
This is suitable for dedicated test instances only.
*/
use master;
GO