Skip to content

Instantly share code, notes, and snippets.

Avatar
🕵️‍♂️
Figuring it out

Eitan Blumin EitanBlumin

🕵️‍♂️
Figuring it out
View GitHub Profile
View Check_Index_Fragmentation_Based_on_Page_Fullness_and_Fill_Factor.sql
/**************************************************************************
Check Index Fragmentation based on Page Fullness and Fill Factor
***************************************************************************
Author: Eitan Blumin | https://www.eitanblumin.com
Version History:
2020-10-20 Added @MaxDOP parameter, and better comments & indentation
2020-01-07 First version
Description:
This script was inspired by Erik Darling's blog post here:
View realign_identity_to_max_value.sql
/*
Author: Eitan Blumin (t: @EitanBlumin | b: eitanblumin.com)
Date: November, 2018
Description:
Re-align Identity Last Value to Actual Max Value
More info: https://eitanblumin.com/2018/11/06/re-align-identity-last-value-to-actual-max-value/
*/
DECLARE @CurrTable SYSNAME, @CurrCol SYSNAME, @LastValue BIGINT
DECLARE @CMD NVARCHAR(MAX), @Result NVARCHAR(MAX)
@EitanBlumin
EitanBlumin / access_violation_dm_db_index_operational_stats.sql
Last active Oct 4, 2020
psst! hey, kid! wanna see an access violation error? here, run this on a SQL Server with MAXDOP != 1
View access_violation_dm_db_index_operational_stats.sql
/*
=================================================
Reproduce an access violation error in SQL Server
=================================================
The following script reproduces an Access Violation error
caused by a parallelism plan involving specific system table functions.
The error occurses ONLY with parallel execution plans.
Discovered by Eitan Blumin and Nathan Lifshes on 2020-09-06
The error has been reproduced in multiple SQL Server versions that were tested:
@EitanBlumin
EitanBlumin / Get Top Query Plans with Warnings from Cache.sql
Last active Sep 30, 2020
Get Top Query Plans with Warnings from SQL Plan Cache
View Get Top Query Plans with Warnings from Cache.sql
/***************************************************************************
Get Top Query Plans with Warnings from Cache
--------------------------------------------
Author: Eitan Blumin | https://www.eitanblumin.com
Change Log:
2020-01-29 - Added a few more warnings from sp_BlitzCache: https://www.brentozar.com/blitzcache/
2020-01-12 - First version
****************************************************************************/
DECLARE
@EitanBlumin
EitanBlumin / Generate recommendations for clustered indexes.sql
Last active Sep 30, 2020
Use existing non-clustered index usage stats, and missing index stats, to generate clustered-index recommendations for heap tables (more info: https://eitanblumin.com/2019/12/30/resolving-tables-without-clustered-indexes-heaps/ )
View Generate recommendations for clustered indexes.sql
-------------------------------------------------------
------ Generate Clustered Index Recommendations -------
-------------------------------------------------------
-- Author: Eitan Blumin | https://www.eitanblumin.com
-- More info: https://eitanblumin.com/2019/12/30/resolving-tables-without-clustered-indexes-heaps/
-------------------------------------------------------
-- Description:
-- ------------
-- This script finds all heap tables, and "guestimates" a clustered index recommendation for each.
-- The script implements the following algorithm:
@EitanBlumin
EitanBlumin / ultimate_compression_savings_estimation_whole_database.sql
Last active Sep 30, 2020
This ultimate script performs compression savings estimation check for an ENTIRE database, includes comparison between PAGE and ROW compression types, cautionary table size checks, AND it generates the remediation scripts for you! (more info: https://eitanblumin.com/2020/02/18/ultimate-compression-savings-estimation-script-entire-database/ )
View ultimate_compression_savings_estimation_whole_database.sql
----------------------------------------------------------------
-------- Ultimate Compression Savings Estimation Check ---------
----------------------------------------------------------------
-- Author: Eitan Blumin | https://www.eitanblumin.com
-- Create Date: 2019-12-08
-- Last Update: 2020-09-06
-- Source: http://bit.ly/SQLCompressionEstimation
-- Full Link: https://gist.github.com/EitanBlumin/85cf620f7267b234d677f9c3027fb7ce
----------------------------------------------------------------
-- Description:
View check_untrusted_check_constraints.sql
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp;
CREATE TABLE #tmp (DBName SYSNAME, SchemaName SYSNAME, TableName SYSNAME, FullTableName AS QUOTENAME(SchemaName) + N'.' + QUOTENAME(TableName), UntrustedObject SYSNAME);
DECLARE @CMD NVARCHAR(MAX)
SET @CMD = N'SELECT DB_NAME(), OBJECT_SCHEMA_NAME(parent_object_id), OBJECT_NAME(parent_object_id), [name]
FROM sys.check_constraints
WHERE is_not_trusted = 1 AND is_not_for_replication = 0 AND is_disabled = 0;'
IF CONVERT(varchar(300),SERVERPROPERTY('Edition')) = 'SQL Azure'
BEGIN
@EitanBlumin
EitanBlumin / TempDB Sizing Check and Remediation.sql
Last active Sep 30, 2020
TempDB Sizing Check and Remediation script
View TempDB Sizing Check and Remediation.sql
/*
-----------------------------------
TempDB Sizing Check and Remediation
-----------------------------------
Author: Eitan Blumin | https://www.eitanblumin.com
Description:
This script makes sure that all TempDB files are equally sized, based on a calculation that takes into consideration
the disk volume where the TempDB files are located.
This check only works when TempDB files are isolated from other databases and exist on their own dedicated volume.
@EitanBlumin
EitanBlumin / Foreign Keys Without Corresponding Indexes.sql
Last active Sep 30, 2020
Generate Index Creation script for FK without indexes
View Foreign Keys Without Corresponding Indexes.sql
/*========================================================================================================================
-- Description: This query retrieves all the foreign keys in spesific DB that dont have corresponding indexes.
-- Scope: Database
-- Author: Guy Glantser | https://www.madeiradata.com
-- Create Date: 08/04/2012
-- Type: Query Plug&play
-- Last Updated On: 08/04/2012
-- Notes:
=========================================================================================================================*/
@EitanBlumin
EitanBlumin / Find Unused Indexes.sql
Created Jan 22, 2020
Script to find unused indexes in all databases, for tables that are old (more than 30 days old) and big (more than 200k rows)
View Find Unused Indexes.sql
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp;
CREATE TABLE #tmp (DBName SYSNAME, SchemaName SYSNAME, TableName SYSNAME, IndexName SYSNAME NULL, RowsCount INT, IndexSizeKB INT, UpdatesCount INT NULL, DropCMD NVARCHAR(MAX), TableCreatedDate DATETIME NULL, LastStatsDate DATETIME);
INSERT INTO #tmp(DBName, SchemaName, TableName, IndexName, RowsCount, IndexSizeKB, DropCMD, LastStatsDate, TableCreatedDate, UpdatesCount)
EXEC sp_MSforeachdb N'
IF EXISTS (SELECT * FROM sys.databases WHERE database_id > 4 AND name = ''?'' AND state_desc = ''ONLINE'' AND DATABASEPROPERTYEX([name], ''Updateability'') = ''READ_WRITE'')
BEGIN
USE [?];
PRINT DB_NAME();
SELECT
You can’t perform that action at this time.