View system_named_constraints_demo.sql
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
DROP TABLE IF EXISTS [dbo].[TestTable2]; | |
DROP TABLE IF EXISTS [dbo].[TestTable1]; | |
DROP TABLE IF EXISTS TestEdgeTable; | |
DROP TABLE IF EXISTS TestNodeTable1; | |
DROP TABLE IF EXISTS TestNodeTable2; | |
DROP TABLE IF EXISTS TestNodeTable3; | |
GO | |
CREATE TABLE [dbo].[TestTable1]( | |
[ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY, |
View SQLDatabaseYAMLBuildConfiguration.yml
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
# SQL Database Project Build Configuration | |
trigger: | |
- master | |
pool: | |
vmImage: 'VS2017-Win2016' | |
variables: | |
solution: '**/*.sln' |
View SQLSentry_Heartbeat_Monitoring.sql
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
-- Run this in the right SQL Sentry database | |
--USE [SentryOne]; | |
--USE [SQLSentry]; | |
GO | |
IF OBJECT_ID('[dbo].[heartbeat_log]') IS NULL | |
BEGIN | |
CREATE TABLE [dbo].[heartbeat_log]( | |
[servername] [nvarchar](300) CONSTRAINT PK_Heartbeat_Log PRIMARY KEY CLUSTERED WITH(IGNORE_DUP_KEY=ON,DATA_COMPRESSION=PAGE), | |
[heartbeatdate] [datetime] NULL, | |
[ActualHeartbeatDate] [datetime] NULL |
View Low PAGE Compression Success Rates.sql
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 for low PAGE compression success rates | |
============================================ | |
Author: Eitan Blumin | |
Date: 2022-01-13 | |
Based on blog post by Paul Randal: | |
https://www.sqlskills.com/blogs/paul/the-curious-case-of-tracking-page-compression-success-rates/ | |
*/ | |
DECLARE | |
/* threshold parameters: */ |
View Hypothetical Indexes - Example Usage.sql
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
/*** TODO: Replace [dbo].[MyTableName] with the name of your specific table ***/ | |
-- step 1: CREATE | |
-- CREATE hypothetical indexes using the WITH STATISTICS_ONLY clause: | |
/* TODO: Replace with your own index definitions, but don't forget to use WITH STATISTICS_ONLY */ | |
CREATE NONCLUSTERED INDEX [IX_Hypothetical] |
View Move-SqlFilesAlwaysOn.ps1
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 2021 Eitan Blumin <@EitanBlumin, https://www.eitanblumin.com> | |
# while at Madeira Data Solutions <https://www.madeiradata.com> | |
# | |
# Licensed under the MIT License (the "License"); | |
# | |
# Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions: | |
# | |
# The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software. | |
# | |
# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR CO |
View Table and Index High Unused Space.sql
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
DECLARE | |
@TopPerDB int = 50, | |
@MinimumRowCount int = 1000, | |
@MinimumUnusedSizeMB int = 1024, | |
@MinimumUnusedSpacePct int = 40, | |
@RebuildIndexOptions varchar(max) = 'ONLINE = ON, SORT_IN_TEMPDB = ON, MAXDOP = 1' -- , RESUMABLE = ON -- adjust as needed | |
SET NOCOUNT, ARITHABORT, XACT_ABORT ON; | |
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; | |
DECLARE @command NVARCHAR(MAX); |
View extended properties change tracking wrapper functions.sql
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
CREATE FUNCTION dbo.ct_get_last_copied_version | |
( | |
@table_name sysname, | |
@target_identifier sysname = null -- optional parameter if you have multiple sync targets | |
) | |
RETURNS bigint | |
AS | |
BEGIN | |
RETURN ISNULL((SELECT convert(bigint, [value]) | |
FROM sys.extended_properties |
View extended properties global variable wrapper functions.sql
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
-- Function to Retrieve a global variable value | |
-- don't forget to convert to the correct data type | |
CREATE FUNCTION dbo.global_variable(@VariableName sysname) | |
RETURNS sql_variant | |
AS | |
BEGIN | |
RETURN (SELECT [value] | |
FROM sys.extended_properties | |
WHERE major_id = 0 AND minor_id = 0 | |
AND [name] = @VariableName) |
View Asynchronous Ledger Demo.sql
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
/* | |
Asynchronous Ledger Demo | |
======================== | |
Author: Eitan Blumin | https://eitanblumin.com | https://madeiradata.com | |
Date: 2021-07-03 | |
Description: | |
This script demonstrates a use case of a high-throughput table | |
which serves as a "hot-spot" for inserts and updates and queries. | |
This causes performance problems due to long lock chains, possible deadlocks, | |
and sometimes even worker thread starvation. |
NewerOlder