Thoughts and Things

So we use Lync (err Skype for Business) here.

Lots of people use the "What's happening today" thing at the top to put various sayings.

Being the one and only DBA... my go to has been

The DBA is my shepherd; I shall not want.
He makes me declare Foreign Key Constraints.
He leads me towards best practices.
He restores my backups.
He leads me in paths of relational integrity
for the database's sake.
Even though I walk through the valley of the shadow of cursors,
I will fear no performance issues,
for he is with me;
his standards and his indexes,
they comfort me.
He prepares a table before me
in the presence of my insistence on denormalizing;
he anoints my table with indexes.

But it being Christmas... I'm working on this...

Oh FROM, Oh FROM the Tay-ay-ay-ay-bel
I SELECT FROM Thee with Ess Queue El
Change the terms over and over in my WHERE
Until the results for the quer-er-er-y appear
Rejoice! Rejoice! Tay-ay-ay-ay-bel
I SELECT FROM thee with Ess Queue El

wish I could think up something better...

Just added my original... the DBA Creed

This is my Database.  There are many like it, but this one is mine.
My Database is my best friend.  It is my life.  I must master it as i must master my life.

My Database, without tables, is useless.  Without constraints, tables are useless.  I must create good covering indexes.
I must write good set based queries, and stop cursors that are trying to kill performance.  I must stop developers from
writing triangle joins, in all but the direst of circumstances.

My database and I know that what counts is not the data we store, the number of tables, nor the standards for the names of objects.
We know that it is the performance that matters, we will perform.

My database is not perfect, there will always be more information to store within it, and new queries to extract it.  Thus I will learn it as a brother.
I will learn its cardinalities, its hot spots, its good indexes, its procedures, and its views.  I will keep my indexes defragmented
and ready for the next query to come, even as I am ready to dive into a poorly performing query and tweak it to keep my database healthy.
We will become part of each other.  We will...

Before God, I swear this creed.  My database and I are the defenders of my enterprise.  We are the masters of performance and throughput.
We are the saviours of the business.

So be it until the enterprise conforms to best practices, and there are no bad queries, only throughput!

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