Eitan Blumin EitanBlumin
🕵️♂️
EitanBlumin
/ InstancePropertiesComparison.sql
Last active
May 15, 2022 14:48
Compare SQL Server Instance Properties
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| ---------------------------------------------------------------------------------- | |
| -- 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
/ 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
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| /*********************************************************************************** | |
| 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
/ 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.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| /**************************************************************************************************/ | |
| /* 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
/ SQLDatabaseYAMLBuildConfiguration.yml
Last active
May 8, 2022 08:19
SQL YAML Build Configuration for Azure Pipelines
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| # Adventure Works SQL Database Project Build Configuration | |
| trigger: | |
| - master | |
| pool: | |
| vmImage: 'VS2017-Win2016' | |
| variables: | |
| solution: '**/*.sln' |
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.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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
/ ultimate_compression_savings_estimation_whole_database.sql
Last active
June 26, 2024 03:06
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/ )
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| ---------------------------------------------------------------- | |
| -------- 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
/ 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/ )
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| ------------------------------------------------------- | |
| ------ 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
/ Check_Index_Fragmentation_Based_on_Page_Fullness_and_Fill_Factor.sql
Last active
July 13, 2023 15:14
Check Index Fragmentation Based on Page Fullness and Fill Factor. Inspired by: https://www.erikdarlingdata.com/2019/10/because-your-index-maintenance-script-is-measuring-the-wrong-thing/
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| /************************************************************************** | |
| 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
/ 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)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| /* | |
| 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
/ TempDB Sizing Check and Remediation.sql
Last active
July 12, 2021 13:41
TempDB Sizing Check and Remediation script
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| /* | |
| ----------------------------------- | |
| 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. |
OlderNewer