LitKnd

/***********************************************************************
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

/***********************************************************************
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 */

LitKnd

/***********************************************************************
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

All public gists https://gist.github.com/litknd unless otherwise specified in gist

Copyright 2017, Kendra Little

MIT License, http://www.opensource.org/licenses/mit-license.php

LitKnd

/************************************************************
SET UP THE REPRO
************************************************************/

WHILE @@trancount > 0
	ROLLBACK
GO

USE master;
GO

LitKnd

/* 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

LitKnd

/*************************************************************
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.

LitKnd

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

/***********************************************************
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

/* 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