Skip to content

Instantly share code, notes, and snippets.

View EitanBlumin's full-sized avatar
🕵️‍♂️
Figuring it out

Eitan Blumin EitanBlumin

🕵️‍♂️
Figuring it out
View GitHub Profile
@EitanBlumin
EitanBlumin / InstancePropertiesComparison.sql
Last active May 15, 2022 14:48
Compare SQL Server Instance Properties
----------------------------------------------------------------------------------
-- Author: Eitan Blumin (t: @EitanBlumin | b: eitanblumin.com)
-- Date: 26/06/18
-- Description:
-- Compares server level objects and definitions as outputted by the first script (InstancePropertiesGenerateForCompare.sql).
--
-- Instructions:
-- Run InstancePropertiesGenerateForCompare.sql on each server. Save output to a CSV file.
-- Use this script ( InstancePropertiesComparison.sql ) to load the files into a table, and output any differences
-- Don't forget to change file paths accordingly.
@EitanBlumin
EitanBlumin / Online Index Operations without Enterprise.sql
Last active January 10, 2024 10:36
Generate Script to allow performing ONLINE index operations and heavy changes on huge tables, without needing Enterprise edition of SQL Server
/***********************************************************************************
Copyright: Eitan Blumin (c) 2018
https://gist.github.com/EitanBlumin/79222fc2be5163cec828d0a69270a0ab
***********************************************************************************/
-- TODO: Rename the _NEW object names to their original names (primary key, default and check constraints)
-- TODO: Identify constraints with NOCHECK
GO
IF OBJECT_ID('tempdb..#PrintMax', 'P') IS NOT NULL DROP PROCEDURE #PrintMax;
@EitanBlumin
EitanBlumin / Generate Procedure Unit Test with Auto-Comparison.sql
Last active September 17, 2020 02:06
Use this script to generate and run a "unit test" for two stored procedures. This script is good as a "sanity check" of sorts, that makes sure the operational effect of two procedures is the same. The script also prints out the duration of each procedure in milliseconds.
/**************************************************************************************************/
/* Generate Procedure Unit Test with Automatic Comparison */
/**************************************************************************************************/
-- Author: Eitan Blumin
-- Date: 2018-11-21
-- Description: Use this script to generate and run a "unit test" for two stored procedures.
-- Each procedure is considered to be affecting one or more database tables.
-- The contents of these tables can be compared before and after each unit test,
-- and the results of each of the two stored procedures can be compared.
@jpvelasco
jpvelasco / SQLDatabaseYAMLBuildConfiguration.yml
Last active May 8, 2022 08:19
SQL YAML Build Configuration for Azure Pipelines
# Adventure Works SQL Database Project Build Configuration
trigger:
- master
pool:
vmImage: 'VS2017-Win2016'
variables:
solution: '**/*.sln'
@EitanBlumin
EitanBlumin / PrintMax_Minified_Temporary_Procedure.sql
Last active September 2, 2020 09:51
This is a minified version of the PrintMax procedure (originally written by Ben Dill). It's created as a temporary procedure.
IF OBJECT_ID('tempdb..#PrintMax') IS NOT NULL DROP PROC #PrintMax;
GO
/*
Author: Eitan Blumin (t: @EitanBlumin | b: eitanblumin.com)
Description:
This is a minified version of the PrintMax procedure (originally written by Ben Dill).
It's created as a temporary procedure.
*/
CREATE PROCEDURE #PrintMax @str NVARCHAR(MAX)
AS
@EitanBlumin
EitanBlumin / ultimate_compression_savings_estimation_whole_database.sql
Last active February 12, 2024 18:32
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/ )
----------------------------------------------------------------
-------- Ultimate Compression Savings Estimation Check ---------
----------------------------------------------------------------
-- Author: Eitan Blumin | https://www.eitanblumin.com
-- Create Date: 2019-12-08
-- Source: http://bit.ly/SQLCompressionEstimation
-- Full Link: https://gist.github.com/EitanBlumin/85cf620f7267b234d677f9c3027fb7ce
-- GitHub Repo: https://github.com/MadeiraData/MadeiraToolbox/blob/master/Utility%20Scripts/ultimate_compression_savings_estimation_whole_database.sql
-- Blog: https://eitanblumin.com/2020/02/18/ultimate-compression-savings-estimation-script-entire-database/
----------------------------------------------------------------
@EitanBlumin
EitanBlumin / Generate_recommendations_for_clustered_indexes.sql
Last active January 30, 2023 20:24
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/ )
-------------------------------------------------------
------ 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:
/**************************************************************************
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:
@EitanBlumin
EitanBlumin / helper_script_for_adding_schemabinding_to_scalar_functions.sql
Last active December 24, 2020 12:06
Helper T-SQL script for adding SCHEMABINDING on scalar functions (checks in ALL databases on the server)
/*
Author: Eitan Blumin | https://www.eitanblumin.com
Description:
Helper T-SQL script for adding SCHEMABINDING on scalar functions (checks in ALL databases on the server)
Added support for Azure SQL DB: Performs the same check across schemas instead of across databases
Instructions:
1. Run the script to detect all scalar functions with disabled SCHEMABINDING, that can potentially have it enabled.
@EitanBlumin
EitanBlumin / TempDB Sizing Check and Remediation.sql
Last active July 12, 2021 13:41
TempDB Sizing Check and Remediation script
/*
-----------------------------------
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.