LitKnd

/*****************************************************************************
Copyright (c) 2017 SQL Workbooks LLC
MIT License, http://www.opensource.org/licenses/mit-license.php


Get WideWorldImporters-Full.bak from Microsoft at:
https://github.com/Microsoft/sql-server-samples/releases/tag/wide-world-importers-v1.0
******************************************************************************/

USE master;

LitKnd

/***********************************************************************
Copyright 2017, SQL Workbooks LLC
MIT License, http://www.opensource.org/licenses/mit-license.php
***********************************************************************/

/* Doorstop*/
RAISERROR('Did you mean to run the whole thing?', 20,1) WITH LOG;
GO

LitKnd

/***********************************************************************
Copyright 2016, Kendra Little - LittleKendra.com
MIT License, http://www.opensource.org/licenses/mit-license.php
***********************************************************************/

/* Doorstop*/
RAISERROR('Did you mean to run the whole thing?', 20,1) WITH LOG;
GO

LitKnd

/*****************************************************************************
Copyright (c) 2017 SQL Workbooks LLC
Terms of Use: https://sqlworkbooks.com/terms-of-service/
Contact: help@sqlworkbooks.com

Setup:
    Download BabbyNames.bak.zip (11.6 MB database backup)
    https://github.com/LitKnd/BabbyNames/releases/tag/v1.0

*****************************************************************************/

LitKnd

DECLARE @whoisactive_table VARCHAR(4000) ;
DECLARE @schema VARCHAR(4000) ;
DECLARE @dsql NVARCHAR(4000) ;

SET @whoisactive_table = QUOTENAME ('##WhoIsActive_' + CAST(NEWID() as varchar(255)));

EXEC sp_WhoIsActive
	@get_transaction_info = 1,
	@output_column_list = '[block%][%]',

LitKnd

select session_id, 
	wait_duration_ms / 1000. / 60. as wait_duration_min,
	wait_type,
	blocking_session_id
from sys.dm_os_waiting_tasks
where blocking_session_id is not null;
GO

LitKnd

SELECT 
	ISNULL(CASE bd.database_id 
		WHEN 32767 THEN 'resource' 
		ELSE db.name END, 'TOTAL') as database_name,
	count(*) * 8./1024./1024. as page_GB,
	sum(free_space_in_bytes) /1024./1024./1024. as free_space_MB
FROM sys.dm_os_buffer_descriptors as bd
LEFT OUTER JOIN sys.databases as db on bd.database_id = db.database_id
GROUP BY 
	CASE bd.database_id WHEN 32767 THEN 'resource' ELSE db.name END

LitKnd

/*****************************************************************************
Copyright (c) 2017 SQL Workbooks LLC
Terms of Use: https://sqlworkbooks.com/terms-of-service/
Contact: help@sqlworkbooks.com
This is only suitable for test environments.


This script is a Query Tuning Challenge! 
To get Kendra's sample solution, attend the free webcast 
	on Thu, June 22, 10AM PST / 1PM EST - Why an Index and Statistics Can Make a Query Slower

LitKnd

SELECT 
	sc.name as schema_name,
	so.name as table_name,
	cc.name as column_name, 
	cc.[definition], 
	cc.is_persisted
FROM sys.computed_columns as cc
join sys.objects as so on cc.object_id = so.object_id
join sys.schemas as sc on so.schema_id = sc.schema_id;
GO

LitKnd

SELECT
	(SELECT CAST(SUBSTRING(st.text, (qs.statement_start_offset/2)+1,   
		((CASE qs.statement_end_offset  
			WHEN -1 THEN DATALENGTH(st.text)  
			ELSE qs.statement_end_offset  
			END
		- qs.statement_start_offset)/2) + 1) AS NVARCHAR(MAX)) FOR XML PATH(''),TYPE) AS [TSQL],
    qs.execution_count AS [#],
    CAST(qs.total_worker_time/1000./1000. AS numeric(30,1)) AS [cpu sec],
	CASE WHEN execution_count = 0 THEN 0 ELSE