Skip to content

Instantly share code, notes, and snippets.

🕵
Figuring it out

Eitan Blumin EitanBlumin

🕵
Figuring it out
Block or report user

Report or block EitanBlumin

Hide content and notifications from this user.

Learn more about blocking users

Contact Support about this user’s behavior.

Learn more about reporting abuse

Report abuse
View GitHub Profile
@EitanBlumin
EitanBlumin / Standardize Database File Sizes and Growth.sql
Last active Jan 28, 2020
Minimal standardization of all Database Files in a SQL Server instance
View Standardize Database File Sizes and Growth.sql
/*
Author: Eitan Blumin | https://www.eitanblumin.com
Description:
This script generates commands to implement a minimal standardization of all database files in the instance.
List of implemented standards:
1. Files must not be allowed to have percentage growth (defaults to 1GB growth instead as a replacement)
2. Files must all have UNLIMITED max size
3. Log files must be at least 64MB in size
4. Log file auto-growth must be in power multiples of 2 between 64MB and 2048MB (i.e. 64,128,256,512,1024,2048) (defaults to 1GB growth instead as a replacement)
5. Data file auto-growth, for data files bigger than 1GB, must be at least 100MB (defaults to 500MB growth instead as a replacement)
View SentryOne GUI Add Targets.ps1
# Source: https://www.sentryone.com/blog/powershell-gui-to-script-back-to-gui
# GUI generated using POSHGUI: https://poshgui.com/
Add-Type -AssemblyName System.Windows.Forms
[System.Windows.Forms.Application]::EnableVisualStyles()
$Form = New-Object system.Windows.Forms.Form
$Form.ClientSize = '600,800'
$Form.text = "Form"
$Form.TopMost = $false
@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
@EitanBlumin
EitanBlumin / Foreign Keys Without Corresponding Indexes.sql
Created Jan 20, 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 / TempDB Sizing Check and Remediation.sql
Created Jan 19, 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 / Get Top Query Plans with Warnings from Cache.sql
Created Jan 12, 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-12 - First version
****************************************************************************/
DECLARE
@MinimumSubTreeCost FLOAT = 30
@EitanBlumin
EitanBlumin / check_untrusted_check_constraints.sql
Created Jan 9, 2020
Find and check untrusted Check Constraints in all active databases
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);
INSERT INTO #tmp(DBName, SchemaName, TableName, UntrustedObject)
EXEC sp_MSforeachdb 'IF EXISTS (SELECT * FROM sys.databases WHERE state_desc = ''ONLINE'' AND name = ''?'' AND DATABASEPROPERTYEX(''?'', ''Updateability'') = ''READ_WRITE'')
BEGIN
USE [?];
SELECT ''?'', 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;
@EitanBlumin
EitanBlumin / helper_script_for_adding_schemabinding_to_scalar_functions.sql
Last active Jan 26, 2020
Helper T-SQL script for adding SCHEMABINDING on scalar functions (checks in ALL databases on the server)
View helper_script_for_adding_schemabinding_to_scalar_functions.sql
/*
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)
Instructions:
1. Run the script to detect all scalar functions with disabled SCHEMABINDING, that can potentially have it enabled.
2. Review the 1st resultset for the full list of detected functions.
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-01-07 First version
Description:
This script was inspired by Erik Darling's blog post here:
https://www.erikdarlingdata.com/2019/10/because-your-index-maintenance-script-is-measuring-the-wrong-thing/
@EitanBlumin
EitanBlumin / Generate recommendations for clustered indexes.sql
Last active Jan 7, 2020
Use existing non-clustered index usage stats, and missing index stats, to generate clustered-index recommendations for heap tables
View Generate recommendations for clustered indexes.sql
-------------------------------------------------------
------ Generate Clustered Index Recommendations -------
-------------------------------------------------------
-- Author: Eitan Blumin | https://www.eitanblumin.com
-------------------------------------------------------
-- Change log:
-- ------------
-- 2020-01-07 Added check of database Updateability, and moved around a few columns
-- 2019-12-29 Added checks for IDENTITY columns, and first DATE/TIME columns
-- 2019-12-23 First version
You can’t perform that action at this time.