SQL Server

How I wish SSRS had a best fit line capability built into its charts... but at least up to SQL 2014, alas such is not the case.

So I cobbled together a SQL function to calculate it for me.  The idea was I could pass in a table of data to the function and get the M and X values back.

To do this we need a table we can pass in as a parameter.

CREATE TYPE [dbo].[LineRegressionSource] AS TABLE(
	[Period] [NUMERIC](18, 4) NULL,
	[PartitionKey] [INT] NULL,
	[Value] [NUMERIC](18, 4) NULL
)

I have a schema in my Datawarehouse called Tools, I put things there to be used by report writers and the like

CREATE SCHEMA Tools AUTHORIZATION dbo

And the function, this makes heavy use of a windowing function.. what I figure best is to build a table variable and populate it and then feed it in.

/************************************************************************
FUNCTION:			LinearRegression
AUTHOR:				mtassin
DATE:				2016-03-03
PURPOSE:			To calculate the slope and Y Intercept of a best fit line
					given a table of data.  Based on the following equations
		
						y =mx + b

						where m = n(SUM(xy)) - SUM(x)*SUM(y)
									-----------------
								  n(SUM(x^2))-(SUM(x))^2

						and b = (SUM(y) - m(SUMX))/COUNT(X)
Parameters:			
@Table     LineRegressionSource  (Period int,PartitionKey int ,Value Numeric(19,3))

								Note, that this Linear Regression function expects to receive data in pre-summarized form
								i.e. Only one row per value that would be plotted on a graph.
								Period corresponds to a coordinate point on the X Axis
								PartitionKey allows for multiple sets of data to be simultaneously linearly regressed, partitioned by said key
								Value  corresponds to a coordinate point on the Y axis.


						Value
                         |
						 | 
						 |     x
						 |
			-------------+--------------  Period
			             |
						 |
						 |
Modifications:

**************************************************************************/
CREATE FUNCTION Tools.LinearRegression(@Table AS LineRegressionSource READONLY)
RETURNS @SUMX TABLE(    PartitionKey int,
	NSUMXY numeric(38,5),
	SUMXSUMY numeric(38,5),
	NSUMX2 numeric(38,5),
	SUMX2 numeric(38,5),
	SUMX numeric(38,5),
	SUMY numeric(38,5),
	COUNTX numeric(38,5),
	M  numeric(38,5),
	B  numeric(38,5))
AS
BEGIN

INSERT INTO @Sumx(PartitionKey,NSUMXY,SUMXSUMY,NSUMX2,SUMX2,SUMX,SUMY,COUNTX,M,B)
SELECT
DISTINCT
PartitionKey,
NSUMXY = COUNT(Period) OVER (PARTITION BY PartitionKey)  SUM(Period  Value) OVER (PARTITION BY PartitionKey),
SUMXSUMY = SUM(Period)OVER (PARTITION BY PartitionKey)  SUM(Value)OVER (PARTITION BY PartitionKey),
NSUMX2 = COUNT(period)OVER (PARTITION BY PartitionKey)  SUM(Period*Period) OVER (PARTITION BY PartitionKey),
SUMX2 = SUM(Period) OVER (PARTITION BY PartitionKey)  SUM(Period) OVER (PARTITION BY PartitionKey),
SUMX = SUM(Period) OVER (PARTITION BY PartitionKey),
SUMY = SUM(Value) OVER (PARTITION BY PartitionKey),
COUNTX = COUNT(Period) OVER (PARTITION BY PartitionKey),
NULL,
NULL
FROM 
@Table

UPDATE @SUMX
SET M = (NSUMXY - SUMXSUMY)/(NSUMX2 - SUMX2),
B = (SUMY - (NSUMXY - SUMXSUMY)/(NSUMX2 - SUMX2)  SUMX)/COUNTX

RETURN
END

DECLARE @T AS LineRegressionSource


INSERT INTO @T(Period,PartitionKey,Value)
VALUES(1,1,2),(2,1,4),(3,1,6),(4,1,8)

SELECT  FROM Tools.LinearRegression(@T)

Excel shows that the formula is Y=2X, which is Y=2X+0

Ever have to move your databases to different drives for whatever reason?  For me it's a SAN change-out.

I won't claim it's perfect, but I whipped this up to make it easier to move a database without having to type it all.

/******************************************
Procedure:		MoveDatabases
Author:			mtassin
Date:			2016-02-03
Purpose:		To relocate databases from one location to another
Parameters:
@Databasename	sysname		The name of the database to relocate
@NewLocation	nvarchar(520)	The new location to move the database files to
@NewLogLocation nvarchar(520)   The new location for the Transaction Logs if NULL will use @NewLocation

********************************************/
CREATE PROCEDURE dbo.MoveDatabases
@DatabaseName sysname,
@NewLocation nvarchar(520),
@NewLogLocation NVARCHAR(520)=NULL
AS

IF @NewLocation IS NULL
BEGIN
	PRINT 'You must specify a new location to move to'
	return
END

IF NOT EXISTS (SELECT 1 FROM sys.databases WHERE name = @DatabaseName) 
BEGIN
	PRINT 'Invalid Databasename specified'
	RETURN
END

IF @NewLogLocation IS NULL SET @NewLogLocation = @NewLocation

IF OBJECT_ID('tempdb..#sysfiles') IS NOT NULL DROP TABLE #sysfiles
create TABLE #sysfiles(
sysfilekey BIGINT IDENTITY(1,1) PRIMARY KEY,
fileid SMALLINT,
groupid SMALLINT,
size INT,
maxsize INT,
growth INT,
[status] INT,
perf INT,
name sysname,
filename NVARCHAR(520),
newfilename NVARCHAR(520)
)

DECLARE @SQL NVARCHAR(MAX)='Insert into #sysfiles(fileid,groupid,size,maxsize,growth,status,perf,name,filename) select  from [' + @databasename + '].sys.sysfiles'
DECLARE @CRLF NVARCHAR(max) = CHAR(10) + CHAR(13)
DECLARE @RC INT=0

EXEC sp_executesql @SQL

SELECT @RC = COUNT(*)
FROM #sysfiles
WHERE CHARINDEX(@NewLocation,filename) > 0

IF @RC != 0 
BEGIN
	PRINT 'Database has already been moved'
	RETURN
end
IF @DatabaseName = 'master' 
BEGIN
	PRINT 'Moving master requires additional configuration steps.  Please refer to this article'
	PRINT 'https://msdn.microsoft.com/en-us/library/ms345408.aspx#master'
	RETURN
END
UPDATE #sysfiles
SET newfilename = @NewLocation + REVERSE(LEFT(REVERSE(filename),CHARINDEX('\',REVERSE(filename))-1))
WHERE groupid != 0

UPDATE #sysfiles
SET newfilename = @NewLogLocation + REVERSE(LEFT(REVERSE(filename),CHARINDEX('\',REVERSE(filename))-1))
WHERE groupid = 0



SET @SQL = NULL

SELECT 
@SQL = COALESCE(@SQL + 'ALTER DATABASE [' + @DatabaseName +'] MODIFY FILE(NAME=[' + name + '],FILENAME=''' + newfilename + '''); ','ALTER DATABASE [' + @DatabaseName +'] MODIFY FILE(NAME=[' + name + '],FILENAME=''' + newfilename + '''); ' )
FROM 
#sysfiles

EXEC sp_executesql @SQL


IF @DatabaseName = 'tempdb'
BEGIN
	PRINT 'Tempdb can only be moved by a server restart new location for files is set, reset server to continue'
	RETURN
END
SET @SQL = ''

SET @SQL = @SQL + 'ALTER DATABASE [' + @DatabaseName + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE '  + @CRLF + 'ALTER DATABASE [' + @DatabaseName + '] SET OFFLINE ' 
EXEC sp_executesql @SQL
SET @SQL = ''

SELECT 
@SQL += 'exec xp_cmdshell ''MOVE /Y ' + filename + ' ' + newfilename  + '''' +@CRLF
FROM 
#sysfiles

EXEC sp_executesql @SQL
SET @SQL = ''


SET @SQL = @SQL + 'ALTER DATABASE [' + @DatabaseName + '] SET ONLINE ' + @CRLF+ 'ALTER DATABASE [' + @DatabaseName + '] SET MULTI_USER ' 
EXEC sp_executesql @SQL

With SQL Server 2012 Microsoft introduced the SSIS Project Deployment system to better integrate SSIS with SQL Server.

With that comes a special database SSISDB that tracks packages and executions of packages.

There's a maintenance script that deletes old package history. cleanup_server_retention_window

This script depends on using ON DELETE CASCADE triggers to delete records at the top of a tree of tables and use those deletions to cascade down.

Where I have worked, I've found that when you run hundreds or thousands of executions of SSIS packages in a day this causes a problem.

The issue is that the deletion at the top table (internal.operations) as it cascades down through these tables the lock escalation will ultimately result in multiple table locks which will keep any other SSIS packages from executing.
 

--[internal].[operations]
 --  [internal].[executions]
 --      [internal].[executable_statistics]
 --      [internal].[execution_component_phases]
 --      [internal].[execution_data_statistics]
 --      [internal].[execution_data_taps]
 --      [internal].[execution_parameter_values]
 --      [internal].[execution_property_override_values]
 --  [internal].[extended_operation_info]
 --  [internal].[operation_messages] --(DATA HEAVY)
 --      [internal].[event_messages] --(DATA HEAVY)
 --          [internal].[event_message_context]
 --  [internal].[operation_os_sys_info]
 --  [internal].[operation_permissions]
 --  [internal].[validations]

I've seen a script that will erase the SSISDB's entire execution history via truncate.

http://thinknook.com/truncate-ssis-catalog-database-operation-log-tables-2012-11-03/

but this isn't great when you're only wanting to routinely purge history older than a given date.

So I came up with this... it's less set oriented than I would care for, but it keeps the lock escalation from table locks which will allow other SSIS jobs to execute.
It starts at the bottom tables and deletes rows from there in batches as specified by the @batch_size parameter, then works its way back up the tree.

You might want to play with @batch_size to find where your locking sweet spot is. 

The @operations_divisor is what to divide your batch size by.  This is because when you delete rows at that level, the FK Cascade will descend to every table in the chain, and may escallate locks there even though we've done everything we can to avoid that. 

You'll also likely want to disable Lock Escalation on the SSISDB tables because this will still cascade down the FK chains, just after deleting the records from the lower level tables first.

Disable Lock Escalation

USE SSISDB
GO

DECLARE @SQL NVARCHAR(MAX)

SELECT
@SQL = COALESCE(@SQL + N'ALTER TABLE [' + SCHEMA_NAME(schema_id) + N'].[' + name + N'] SET (LOCK_ESCALATION=DISABLE);','ALTER TABLE [' + SCHEMA_NAME(schema_id) + '].[' + name + '] SET (LOCK_ESCALATION=DISABLE);')
FROM 
sys.tables

EXEC sp_executesql @SQL

Create some indexes to help reduce table contention and speed up cleanup

USE [SSISDB]
GO

/****** Object:  Index [idx_operations_main]    Script Date: 12/4/2015 9:34:36 AM ******/
CREATE NONCLUSTERED INDEX [idx_operations_main] ON [internal].[operations]
(
	[end_time] ASC,
	[created_time] ASC,
	[status] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO


/****** Object:  Index [IX_ExecutableStatistics_Execution_id]    Script Date: 12/4/2015 9:35:19 AM ******/
CREATE NONCLUSTERED INDEX [IX_ExecutableStatistics_Execution_id] ON [internal].[executable_statistics]
(
	[execution_id] ASC
)
INCLUDE ( 	[statistics_id]) 
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

GO

/****** Object:  Index [idx_execution_component_phases_execution_id]    Script Date: 12/4/2015 9:35:52 AM ******/
CREATE NONCLUSTERED INDEX [idx_execution_component_phases_execution_id] ON [internal].[execution_component_phases]
(
	[execution_id] ASC
)
INCLUDE ( 	[phase_stats_id]) 
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO


/****** Object:  Index [idx_execution_data_statistics]    Script Date: 12/4/2015 9:36:26 AM ******/
CREATE NONCLUSTERED INDEX [idx_execution_data_statistics] ON [internal].[execution_data_statistics]
(
	[execution_id] ASC
)
INCLUDE ( 	[data_stats_id]) 
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

GO



/****** Object:  Index [idx_execution_data_taps]    Script Date: 12/4/2015 9:36:57 AM ******/
CREATE NONCLUSTERED INDEX [idx_execution_data_taps] ON [internal].[execution_data_taps]
(
	[execution_id] ASC
)
INCLUDE ( 	[data_tap_id]) 
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

GO
USE [SSISDB]
GO

/****** Object:  Index [IX_ExecutionParameterValue_ExecutionId]    Script Date: 12/4/2015 9:37:23 AM ******/
CREATE NONCLUSTERED INDEX [IX_ExecutionParameterValue_ExecutionId] ON [internal].[execution_parameter_values]
(
	[execution_id] ASC
)
INCLUDE ( 	[object_type],
	[parameter_data_type],
	[parameter_name],
	[parameter_value],
	[sensitive_parameter_value],
	[base_data_type],
	[sensitive],
	[required],
	[value_set],
	[runtime_override]) 
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

GO


/****** Object:  Index [IX_ExecutionParameterValue_ExecutionId2]    Script Date: 12/4/2015 9:37:38 AM ******/
CREATE NONCLUSTERED INDEX [IX_ExecutionParameterValue_ExecutionId2] ON [internal].[execution_parameter_values]
(
	[parameter_name] ASC
)
INCLUDE ( 	[execution_id],
	[object_type],
	[parameter_data_type],
	[parameter_value],
	[sensitive_parameter_value],
	[base_data_type],
	[sensitive],
	[required],
	[value_set],
	[runtime_override]) 
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

GO



/****** Object:  Index [idx_execution_property_override_values]    Script Date: 12/4/2015 9:38:08 AM ******/
CREATE NONCLUSTERED INDEX [idx_execution_property_override_values] ON [internal].[execution_property_override_values]
(
	[execution_id] ASC
)
INCLUDE ( 	[property_id]) 
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

GO


/****** Object:  Index [idx_extended_operation_info]    Script Date: 12/4/2015 9:38:48 AM ******/
CREATE NONCLUSTERED INDEX [idx_extended_operation_info] ON [internal].[extended_operation_info]
(
	[operation_id] ASC
)
INCLUDE ( 	[info_id]) 
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

GO


/****** Object:  Index [IX_OperationMessages_Operation_id]    Script Date: 12/4/2015 9:39:22 AM ******/
CREATE NONCLUSTERED INDEX [IX_OperationMessages_Operation_id] ON [internal].[operation_messages]
(
	[operation_id] ASC
)
INCLUDE ( 	[operation_message_id]) 
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

GO


/****** Object:  Index [IX_EventMessages_Operation_id]    Script Date: 12/4/2015 9:40:08 AM ******/
CREATE NONCLUSTERED INDEX [IX_EventMessages_Operation_id] ON [internal].[event_messages]
(
	[operation_id] ASC,
	[event_message_id] ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

GO


/****** Object:  Index [IX_EventMessageContext_Operation_id]    Script Date: 12/4/2015 9:40:34 AM ******/
CREATE NONCLUSTERED INDEX [IX_EventMessageContext_Operation_id] ON [internal].[event_message_context]
(
	[event_message_id] ASC,
	[operation_id] ASC
)
INCLUDE ( 	[context_id]) 
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO


/****** Object:  Index [idx_operations_os_sys_info]    Script Date: 12/4/2015 9:41:04 AM ******/
CREATE NONCLUSTERED INDEX [idx_operations_os_sys_info] ON [internal].[operation_os_sys_info]
(
	[operation_id] ASC
)
INCLUDE ( 	[info_id],
	[total_physical_memory_kb],
	[available_physical_memory_kb],
	[total_page_file_kb],
	[available_page_file_kb],
	[cpu_count]) 
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

GO


/****** Object:  Index [IX_OperationMessages_Operation_id]    Script Date: 12/4/2015 9:41:33 AM ******/
CREATE NONCLUSTERED INDEX [IX_OperationMessages_Operation_id] ON [internal].[operation_messages]
(
	[operation_id] ASC
)
INCLUDE ( 	[operation_message_id]) 
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

GO



/****** Object:  Index [IX_EventMessages_Operation_id2]    Script Date: 12/4/2015 3:05:48 PM ******/
CREATE NONCLUSTERED INDEX [IX_EventMessages_Operation_id2] ON [internal].[event_messages]
(
	[event_message_id] ASC,
	[operation_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO


/****** Object:  Index [IX_EventMessageContext_Operation_id2]    Script Date: 12/4/2015 3:07:40 PM ******/
CREATE NONCLUSTERED INDEX [IX_EventMessageContext_Operation_id2] ON [internal].[event_message_context]
(
	[operation_id] ASC,
	[event_message_id] ASC
)
INCLUDE ( 	[context_id]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO



/****** Object:  Index [IX_EventMessageContext_Operation_id]    Script Date: 12/4/2015 3:08:11 PM ******/
CREATE NONCLUSTERED INDEX [IX_EventMessageContext_Operation_id] ON [internal].[event_message_context]
(
	[event_message_id] ASC,
	[operation_id] ASC
)
INCLUDE ( 	[context_id]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

/****** Object:  Index [IX_EventMessages_Operation_id]    Script Date: 12/4/2015 3:09:11 PM ******/
CREATE NONCLUSTERED INDEX [IX_EventMessages_Operation_id] ON [internal].[event_messages]
(
	[operation_id] ASC,
	[event_message_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO


/****** Object:  Index [IX_EventMessages_Operation_id2]    Script Date: 12/4/2015 3:09:39 PM ******/
CREATE NONCLUSTERED INDEX [IX_EventMessages_Operation_id2] ON [internal].[event_messages]
(
	[event_message_id] ASC,
	[operation_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

Replace the call to [internal].[cleanup_server_retention_window] in your SSIS DB Maintenance job in SQL Agent with a call to this.

Replacement Procedure for internal.cleanup_server_retention_window

USE [SSISDB]
GO
/****** Object:  StoredProcedure [internal].[cleanup_server_retention_window_bottomup]    Script Date: 12/3/2015 6:32:07 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/***********************************************************************
Procedure: [internal].[cleanup_server_retention_window_bottomup]
Author:    Mark Tassin
Date: 2013-05-14
Purpose:  To control deletions from the SSISDB operational records in a way that 
          doesn't kill SQL Server performance
Parameters:
 @batch_size		int		The number of records to delete in a givne batch
 @retention_days	int		The number of days of records to retain
 @operations_divisor    int             Divides the @batch_size by this number to reduce the 
                                        rows deleted at the internal.operations level because
                                        deletions there cascade to all the tables below.
 Documentation:
--[internal].[operations]
 --  [internal].[executions]
 --      [internal].[executable_statistics]
 --      [internal].[execution_component_phases]
 --      [internal].[execution_data_statistics]
 --      [internal].[execution_data_taps]
 --      [internal].[execution_parameter_values]
 --      [internal].[execution_property_override_values]
 --  [internal].[extended_operation_info]
 --  [internal].[operation_messages] --(DATA HEAVY)
 --      [internal].[event_messages] --(DATA HEAVY)
 --          [internal].[event_message_context]
 --  [internal].[operation_os_sys_info]
 --  [internal].[operation_permissions]
 --  [internal].[validations]
 ***********************************************************************/
CREATE PROCEDURE [internal].[cleanup_server_retention_window_bottomup]
 @batch_size int = 50000,
 @retention_window_length int = 15,
 @operations_divisor INT = 100
 AS
 SET NOCOUNT ON 
 DECLARE @Rows_Affected INT=1
 DECLARE @Temp_Date DATETIMEOFFSET = DATEADD(dd,-@retention_window_length,SYSDATETIMEOFFSET())

 --Internal.event_message_context
 WHILE @Rows_Affected > 0
 BEGIN
	 DELETE TOP(@batch_size) a 
	 FROM 
	 internal.event_message_context a WITH (ROWLOCK) 
	 JOIN internal.operations d ON 
	 a.operation_id = d.operation_id
	 WHERE  d.[end_time] <= @temp_date 
   
	 SELECT @Rows_Affected = @@ROWCOUNT
   
	 DELETE TOP(@batch_size) a 
	 FROM 
	 internal.event_message_context a WITH (ROWLOCK) 
	 JOIN internal.operations d ON 
	 a.operation_id = d.operation_id
	 WHERE    
	   d.[end_time] IS NULL AND d.[status] = 1 AND d.[created_time] <= @temp_date 

	SELECT @Rows_Affected += @@ROWCOUNT
END

--internal.event_messages
SET @Rows_Affected = 1
WHILE @Rows_Affected > 0
 BEGIN
	 DELETE TOP(@batch_size) b
	 FROM 
	 internal.event_messages b WITH (ROWLOCK)  
	 JOIN internal.operations d ON 
	 b.operation_id = d.operation_id
	 WHERE  d.[end_time] <= @temp_date 
   
	 SELECT @Rows_Affected = @@ROWCOUNT
   
	 DELETE TOP(@batch_size) b
	 FROM 
	 internal.event_messages b WITH (ROWLOCK) 
	 JOIN internal.operations d ON 
	 b.operation_id = d.operation_id
	 WHERE    
	   d.[end_time] IS NULL AND d.[status] = 1 AND d.[created_time] <= @temp_date 

	SELECT @Rows_Affected += @@ROWCOUNT
END

--internal.operation_messages
SET @Rows_Affected = 1
WHILE @Rows_Affected > 0
 BEGIN
	 DELETE TOP(@batch_size) c
	 FROM 
	 internal.operation_messages c WITH (ROWLOCK) JOIN internal.operations d ON 
	 c.operation_id = d.operation_id
	 WHERE  d.[end_time] <= @temp_date 
   
	 SELECT @Rows_Affected = @@ROWCOUNT
   
	 DELETE TOP(@batch_size) c
	 FROM 
	 internal.operation_messages c WITH (ROWLOCK) JOIN internal.operations d ON 
	 c.operation_id = d.operation_id
	 WHERE    
	   d.[end_time] IS NULL AND d.[status] = 1 AND d.[created_time] <= @temp_date 

	SELECT @Rows_Affected += @@ROWCOUNT
END

--internal.extended_operation_info
SET @Rows_Affected=1
WHILE @Rows_Affected > 0 
BEGIN
	DELETE TOP(@batch_size) a 
	FROM 
	internal.extended_operation_info a WITH (ROWLOCK) JOIN internal.operations b ON 
	a.operation_id = b.operation_id
	WHERE  b.[end_time] <= @temp_date 

	SELECT @Rows_Affected = @@ROWCOUNT

	DELETE TOP(@batch_size) a 
	FROM 
	internal.extended_operation_info a WITH (ROWLOCK) JOIN internal.operations b ON 
	a.operation_id = b.operation_id
		 WHERE    
		   b.[end_time] IS NULL AND b.[status] = 1 AND b.[created_time] <= @temp_date 
	SELECT @Rows_Affected += @@ROWCOUNT
END

--internal.opeation_os_sys_info
SET @Rows_Affected=1
WHILE @Rows_Affected > 0 
BEGIN
	DELETE TOP(@batch_size) a 
	FROM 
	[internal].[operation_os_sys_info] a WITH (ROWLOCK) JOIN internal.operations b ON 
	a.operation_id = b.operation_id
	WHERE  b.[end_time] <= @temp_date 

	SELECT @Rows_Affected = @@ROWCOUNT

	DELETE TOP(@batch_size) a 
	FROM 
	[internal].[operation_os_sys_info] a WITH (ROWLOCK) JOIN internal.operations b ON 
	a.operation_id = b.operation_id
		 WHERE    
		   b.[end_time] IS NULL AND b.[status] = 1 AND b.[created_time] <= @temp_date 
	SELECT @Rows_Affected += @@ROWCOUNT
END


--internal.operation_permissions
SET @Rows_Affected=1
WHILE @Rows_Affected > 0 
BEGIN
	DELETE TOP(@batch_size) a 
	FROM 
	[internal].[operation_permissions] a WITH (ROWLOCK) JOIN internal.operations b ON 
    a.object_id = b.operation_id
	WHERE  b.[end_time] <= @temp_date 

	SELECT @Rows_Affected = @@ROWCOUNT

	DELETE TOP(@batch_size) a 
	FROM 
	[internal].[operation_permissions] a WITH (ROWLOCK) JOIN internal.operations b ON 
	a.object_id = b.operation_id
		 WHERE    
		   b.[end_time] IS NULL AND b.[status] = 1 AND b.[created_time] <= @temp_date 
	SELECT @Rows_Affected += @@ROWCOUNT
END

--internal.validations
SET @Rows_Affected=1
WHILE @Rows_Affected > 0 
BEGIN
	DELETE TOP(@batch_size) a 
	FROM 
	[internal].[validations] a WITH (ROWLOCK) JOIN internal.operations b ON 
	a.validation_id = b.operation_id
	WHERE  b.[end_time] <= @temp_date 

	SELECT @Rows_Affected = @@ROWCOUNT

	DELETE TOP(@batch_size) a 
	FROM 
	[internal].[validations] a WITH (ROWLOCK) JOIN internal.operations b ON 
	a.validation_id = b.operation_id
		 WHERE    
		   b.[end_time] IS NULL AND b.[status] = 1 AND b.[created_time] <= @temp_date 
	SELECT @Rows_Affected += @@ROWCOUNT
END

--internal.executable_statistics
SET @Rows_Affected=1
WHILE @Rows_Affected > 0 
BEGIN
	DELETE TOP(@batch_size) a 
	FROM 
	internal.executable_statistics a WITH (ROWLOCK) 
	JOIN internal.operations c ON 
	a.execution_id = c.operation_id 
	WHERE  c.[end_time] <= @temp_date 

	SELECT @Rows_Affected = @@ROWCOUNT
	DELETE TOP(@batch_size) a 
	FROM 
	internal.executable_statistics a WITH (ROWLOCK) 
	JOIN internal.operations c ON 
	a.execution_id = c.operation_id 
	WHERE    
	c.[end_time] IS NULL AND c.[status] = 1 AND c.[created_time] <= @temp_date 

	SELECT @Rows_Affected += @@ROWCOUNT
END


--[internal].[execution_component_phases]
SET @Rows_Affected=1
WHILE @Rows_Affected > 0 
BEGIN
	DELETE TOP(@batch_size) a 
	FROM 
	[internal].[execution_component_phases] a WITH (ROWLOCK)
	JOIN internal.operations c ON 
	a.execution_id = c.operation_id 
	WHERE  c.[end_time] <= @temp_date 

	SELECT @Rows_Affected = @@ROWCOUNT
	DELETE TOP(@batch_size) a 
	FROM 
	[internal].[execution_component_phases] a WITH (ROWLOCK)
	JOIN internal.operations c ON 
	a.execution_id = c.operation_id 
	WHERE    
	c.[end_time] IS NULL AND c.[status] = 1 AND c.[created_time] <= @temp_date 

	SELECT @Rows_Affected += @@ROWCOUNT
END

--[internal].[execution_data_statistics]
SET @Rows_Affected=1
WHILE @Rows_Affected > 0 
BEGIN
	DELETE TOP(@batch_size) a 
	FROM 
	[internal].[execution_data_statistics] a WITH (ROWLOCK) 
	JOIN internal.operations c ON 
	a.execution_id = c.operation_id 
	WHERE  c.[end_time] <= @temp_date 

	SELECT @Rows_Affected = @@ROWCOUNT
	DELETE TOP(@batch_size) a 
	FROM 
	[internal].[execution_data_statistics] a WITH (ROWLOCK) 
	JOIN internal.operations c ON 
	a.execution_id = c.operation_id 
	WHERE    
	c.[end_time] IS NULL AND c.[status] = 1 AND c.[created_time] <= @temp_date 

	SELECT @Rows_Affected += @@ROWCOUNT
END

--[internal].[execution_data_taps]
SET @Rows_Affected=1
WHILE @Rows_Affected > 0 
BEGIN
	DELETE TOP(@batch_size) a 
	FROM 
	[internal].[execution_data_taps] a WITH (ROWLOCK) 
	JOIN internal.operations c ON 
	a.execution_id = c.operation_id 
	WHERE  c.[end_time] <= @temp_date 

	SELECT @Rows_Affected = @@ROWCOUNT
	DELETE TOP(@batch_size) a 
	FROM 
	[internal].[execution_data_taps] a WITH (ROWLOCK) 
	JOIN internal.operations c ON 
	a.execution_id = c.operation_id 
	WHERE    
	c.[end_time] IS NULL AND c.[status] = 1 AND c.[created_time] <= @temp_date 

	SELECT @Rows_Affected += @@ROWCOUNT
END

--[internal].[execution_parameter_values]
SET @Rows_Affected=1
WHILE @Rows_Affected > 0 
BEGIN
	DELETE TOP(@batch_size) a 
	FROM 
	[internal].[execution_parameter_values] a WITH (ROWLOCK) 
	JOIN internal.operations c ON 
	a.execution_id = c.operation_id 
	WHERE  c.[end_time] <= @temp_date 

	SELECT @Rows_Affected = @@ROWCOUNT
	DELETE TOP(@batch_size) a 
	FROM 
	[internal].[execution_parameter_values] a WITH (ROWLOCK) 
	JOIN internal.operations c ON 
	a.execution_id = c.operation_id 
	WHERE    
	c.[end_time] IS NULL AND c.[status] = 1 AND c.[created_time] <= @temp_date 

	SELECT @Rows_Affected += @@ROWCOUNT
END

--[internal].[execution_property_override_values]
SET @Rows_Affected=1
WHILE @Rows_Affected > 0 
BEGIN
	DELETE TOP(@batch_size) a 
	FROM 
	[internal].[execution_property_override_values] a WITH (ROWLOCK) 
	JOIN internal.operations c ON 
	a.execution_id = c.operation_id 
	WHERE  c.[end_time] <= @temp_date 

	SELECT @Rows_Affected = @@ROWCOUNT
	DELETE TOP(@batch_size) a 
	FROM 
	[internal].[execution_property_override_values] a WITH (ROWLOCK) 
	JOIN internal.operations c ON 
	a.execution_id = c.operation_id 
	WHERE    
	c.[end_time] IS NULL AND c.[status] = 1 AND c.[created_time] <= @temp_date 

	SELECT @Rows_Affected += @@ROWCOUNT
END

--internal.executions
SET @Rows_Affected=1
WHILE @Rows_Affected > 0 
BEGIN
	DELETE TOP(@batch_size) b
	FROM 
	internal.executions b WITH (ROWLOCK) JOIN internal.operations c ON 
	b.execution_id = c.operation_id 
	WHERE  c.[end_time] <= @temp_date 

	SELECT @Rows_Affected = @@ROWCOUNT
	DELETE TOP(@batch_size) b 
	FROM 
	internal.executions b WITH (ROWLOCK) JOIN internal.operations c ON 
	b.execution_id = c.operation_id 
	WHERE    
	c.[end_time] IS NULL AND c.[status] = 1 AND c.[created_time] <= @temp_date 

	SELECT @Rows_Affected += @@ROWCOUNT
END


--we're going to reduce the batch size here because deleting rows from this table 
--will cascade into all of the tables below.
SET @batch_size = @batch_size / @operations_divisor
--internal.operations
SET @Rows_Affected=1
WHILE @Rows_Affected > 0 
BEGIN
	DELETE TOP(@batch_size) c 
	FROM 
	internal.operations c WITH (ROWLOCK)
	WHERE  [end_time] <= @temp_date 

	SELECT @Rows_Affected = @@ROWCOUNT
	DELETE TOP(@batch_size) c
	FROM 
	internal.operations c WITH (ROWLOCK)
	WHERE    
	[end_time] IS NULL AND [status] = 1 AND [created_time] <= @temp_date 

	SELECT @Rows_Affected += @@ROWCOUNT
END


 

Thanks to Ben Kubicek to referencing this article.  http://www.sqlservercentral.com/articles/SSISDB/138330/

Based on that, we'll also grant execute on my proc to our job owner.  Wasn't an issue for me, as I've pretty much made sa own all of my standard maintenance agent jobs, but for those of you who need it.

GRANT EXECUTE ON internal.cleanup_server_retention_window_bottomup TO [##MS_SSISServerCleanupJobUser##]

I personally changed my job's execution to just specify the retention window, mostly because getting to that configuration setting to confirm my values match didn't matter to me, but Ben also has a good point, if you want that configuration value to drive your retention window, add this bit of code.

declare @retention_window_length int

select @retention_window_length = cast(property_value as int) from [catalog].[catalog_properties] where property_name = 'RETENTION_WINDOW'

exec [internal].[cleanup_server_retention_window_bottomup] @retention_window_length = @retention_window_length

SSIS_Maintenance_Deploy.zip