Skip to content

Instantly share code, notes, and snippets.

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

Kendra Little LitKnd

🏠
Working from home
182 followers · 7 following
View GitHub Profile
@LitKnd
LitKnd / Top queries by cpu time.sql
Last active March 2, 2019 19:35
SELECT TOP 20
(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
@LitKnd
LitKnd / top queries by physical reads.sql
Last active April 2, 2021 11:07
Top queries by writes, top queries by physical reads
SELECT TOP 20
(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 [#],
qs.total_physical_reads as [physical reads],
CASE WHEN execution_count = 0 THEN 0 ELSE
@LitKnd
LitKnd / Top Queries by Logical Reads.sql
Last active May 25, 2017 16:39
SELECT TOP 20
(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 [#],
qs.total_logical_reads as [logical reads],
CASE WHEN execution_count = 0 THEN 0 ELSE
@LitKnd
LitKnd / Dear-SQL-DBA-Episode-43-Plan-Guides-Removing-Query-Hints.ps1
Last active May 17, 2017 21:39
<#
Copyright (c) 2017 SQL Workbooks LLC
Terms of Use: https://sqlworkbooks.com/terms-of-service/
Contact: help@sqlworkbooks.com
#>
function LoopParameterizedQuery{
param($Limit, $Connection, $FakeAppQuery, $Parameters=@{})
@LitKnd
LitKnd / hacking-batch-mode-against-rowstore-indexes.sql
Created May 10, 2017 22:22
/*****************************************************************************
Copyright (c) 2017 SQL Workbooks LLC
Terms of Use: https://sqlworkbooks.com/terms-of-service/
Contact: help@sqlworkbooks.com
Setup:
Download the database to restore from https://github.com/LitKnd/BabbyNames/releases/tag/v1.1
Either the large or small database works in this case.
(If you don't have the big one yet, use BabbyNames.bak.zip, it's only 41MB to download.)
You must restore to SQL Server 2016 or a higher version.
@LitKnd
LitKnd / dear-sql-dba-episode-39-are-statistics-making-my-query-slow.sql
Last active April 20, 2017 00:29
/*
All public gists https://gist.github.com/litknd
Copyright 2017, Kendra Little
MIT License, http://www.opensource.org/licenses/mit-license.php
*/
USE master
GO
/*******************************************************************
@LitKnd
LitKnd / Test-Query-Optimizer-Hotfixes-Database-Scope.sql
Created April 3, 2017 16:27
DBCC TRACEOFF (4199, -1);
GO
IF @@TRANCOUNT > 0
ROLLBACK;
GO
SET NOCOUNT ON;
GO
USE master;
@LitKnd
LitKnd / Metadata_and_Size_Info_for_Index.sql
Created March 14, 2017 11:20
DECLARE
@schemaname sysname = 'Sales',
@tablename sysname ='Orders',
@indexid TINYINT = 1;
/* Clustered index is always indexid=1 */
DECLARE @objectid INT;
SELECT @objectid=OBJECT_ID(CONCAT(QUOTENAME(@schemaname),'.', QUOTENAME(@tablename)));
@LitKnd
LitKnd / Missing-Index-Recommendations-Example-BabbyNames.sql
Created March 6, 2017 16:23
/* Sample code that explores index recommendations in BabbyNames */
USE BabbyNames;
GO
SET NOCOUNT ON;
GO
SELECT
ref.FirstName,
@LitKnd
LitKnd / Cause a Stack Dump in SQL Server 2014.sql
Last active March 5, 2017 03:06
/* This doesn't cause a stack dump in SQL Server 2016 SP1-- looks like
it broke in 2012 and was fixed sometime before 2016 SP1
*/
Use AdventureWorks2012;
GO
CREATE INDEX ix_poorly_named_filtered_index on
Sales.SalesOrderHeader (OrderDate)