Skip to content

Instantly share code, notes, and snippets.

Avatar
🕵️‍♂️
Figuring it out

Eitan Blumin EitanBlumin

🕵️‍♂️
Figuring it out
View GitHub Profile
@EitanBlumin
EitanBlumin / Invalid_owner_for_system_schema_role_or_database.sql
Created Nov 25, 2020
TSQL script to check for invalid owners of system Roles, Schemas, or Databases
View Invalid_owner_for_system_schema_role_or_database.sql
/*
Invalid owner for a system Role, Schema, or Database
====================================================
Author: Eitan Blumin | Madeira Data Solutions | https://www.madeiradata.com
Date: 2020-11-25
Description:
System roles and schemas must have specific owning users or roles.
For example, all system database roles such as db_owner, db_datawriter, db_datareader, etc. must be owned by dbo.
@EitanBlumin
EitanBlumin / Parse Vulnerability Assessment Result Files into HTML.sql
Last active Nov 11, 2020
Script to parse multiple Vulnerability Assessment Tool result files into an HTML reference list ( https://eitanblumin.com/sql-vulnerability-assessment-tool-rules-reference-list/ )
View Parse Vulnerability Assessment Result Files into HTML.sql
/******************************************************************
Parse SQL Vulnerability Assessment Tool Results
***********************************************
Author: Eitan Blumin | https://www.eitanblumin.com
Description:
Use this script to parse a Vulnerability Assessment Tool
results file into a relational structure and save in an HTML page.
This will output the T-SQL queries used by VAT behind the scenes
and their respective meta-data, as displayed in the VAT.
@EitanBlumin
EitanBlumin / MaxDOP_Configuration_Check.sql
Created Sep 16, 2020
T-SQL script to check and make sure that the configured MAXDOP is in the recommended range based on Microsoft best practice
View MaxDOP_Configuration_Check.sql
/*
Check that the configured value for MAXDOP is in the recommended range, as described in this KB article:
https://support.microsoft.com/en-us/help/2806535/recommendations-and-guidelines-for-the-max-degree-of-parallelism-confi
If @WhatIf = 0 then MAXDOP will automatically be changed to the recommended setting.
*/
-- change this to 1 to only display findings without actually changing the config:
DECLARE @WhatIf BIT = 0;
--------------------------------------
@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 / SentryOne_AlwaysOn_Inventory_Check.sql
Created Aug 20, 2020
Query to run in the SentryOne database to check your inventory of AlwaysOn Availability Groups
View SentryOne_AlwaysOn_Inventory_Check.sql
SELECT SiteName, ReplicaName
, NumberOfAvailabilityGroups = COUNT(DISTINCT AGName)
, NumberOfPrimaries = COUNT(DISTINCT PrimaryReplica)
FROM
(
SELECT DISTINCT
ES.ObjectName AS ReplicaName, S.Name AS SiteName
, AG.Name AS AGName
, AG.PrimaryReplica
FROM [SentryOne].[AlwaysOn].[AvailabilityGroup] AS AG
@EitanBlumin
EitanBlumin / Find_Top_Exec_Plans_to_Optimize.sql
Last active Sep 22, 2020
T-SQL script to find cached execution plans with good potential for performance optimization (warnings, missing indexes, bad operators, etc.)
View Find_Top_Exec_Plans_to_Optimize.sql
/*
=======================================================
Find Top Exec Plans to Optimize
=======================================================
Author: Eitan Blumin | eitanblumin.com , madeiradata.com
Date: 2020-08-12
Description:
Use this script to discover execution plans with a good
potential for performance optimization.
Finds execution plans with warnings and problematic operators.
@EitanBlumin
EitanBlumin / MoveHistoricalDataForTable.sql
Created Jul 30, 2020
Stored procedure to move time-based data from one table to another, for archiving historical data
View MoveHistoricalDataForTable.sql
IF OBJECT_ID('dbo.ArchivingActivityLog') IS NULL
BEGIN
CREATE TABLE dbo.ArchivingActivityLog
(
Id INT NOT NULL IDENTITY(1,1),
SourceTable SYSNAME NOT NULL,
Command NVARCHAR(MAX) NULL,
StartTime DATETIME NOT NULL CONSTRAINT DF_ArchivingActivityLog_StartTime DEFAULT (GETDATE()),
EndTime DATETIME NULL,
RowsMoved INT NULL,
@EitanBlumin
EitanBlumin / detect_not_secured_connections.sql
Last active Sep 2, 2020
T-SQL monitoring script to make sure that all connections to the SQL Server instance are secured with SSL
View detect_not_secured_connections.sql
/*
Detect Non Secured Connections (SSL) to the SQL Server instance
===============================================================
Author: Eitan Blumin (t: @EitanBlumin | b: eitanblumin.com)
Last Update: 2020-07-15
Description: Use this to make sure that all connections to the SQL Server instance are secured with SSL.
*/
SELECT CONCAT('Not secured connection(s) detected of '
, ISNULL(QUOTENAME(COALESCE(ses.original_login_name, ses.nt_user_name, ses.login_name)), 'an unknown login')
, ' from ', ISNULL(QUOTENAME(client_net_address), 'an unknown address')
@EitanBlumin
EitanBlumin / find_unused_indexes_all_databases.sql
Created Jun 30, 2020
Find all unused indexes across all of your databases. Supports both on-premise instances, as well as Azure SQL Databases.
View find_unused_indexes_all_databases.sql
/*
Author: Eitan Blumin (t: @EitanBlumin | b: https://eitanblumin.com)
Description: Use this script to retrieve all unused indexes across all of your databases.
The data returned includes various index usage statistics and a corresponding drop command.
Supports both on-premise instances, as well as Azure SQL Databases.
*/
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
DECLARE @CMD NVARCHAR(MAX);
SET @CMD = N'
PRINT DB_NAME();
@EitanBlumin
EitanBlumin / GeneratePeriods_Inline.sql
Last active Nov 11, 2020
Table Function to generate periods for time series, based on an end date, period type, and number of periods back
View GeneratePeriods_Inline.sql
/*
Author: Eitan Blumin (t: @EitanBlumin | b: https://eitanblumin.com)
Date Created: 2013-09-01
Last Update: 2020-07-28
Description:
CTE-based Inline Table Function to generate periods for time series, based on an end date, period type, and number of periods back.
Supported period types:
MI - Minute
H - Hour
You can’t perform that action at this time.