Is Your Large SQL Server Database Slowing Down Your Business? Learn How to Master rangeId Partitioning! 🚀

CREATE OR ALTER   VIEW [dbo].[IndexPartition]
AS
SELECT DISTINCT sc.name AS SchemaName,
                t.name  AS TableName,
                i.Name  AS IndexName,
                s.name  aS PScheme,
                f.name  aS PFunction,
                p.partition_number,
                r.value AS [Boundary Value],
                st.row_count
FROM sys.tables AS t
         JOIN sys.schemas AS sc
              ON t.schema_id = sc.schema_id
         JOIN sys.indexes AS i
              ON t.object_id = i.object_id
         JOIN sys.partitions AS p
              ON i.object_id = p.object_id AND i.index_id = p.index_id
         JOIN sys.partition_schemes AS s
              ON i.data_space_id = s.data_space_id
         JOIN sys.partition_functions AS f
              ON s.function_id = f.function_id
         LEFT JOIN sys.partition_range_values AS r
                   ON f.function_id = r.function_id and r.boundary_id = p.partition_number
         LEFT JOIN sys.dm_db_partition_stats st
                   ON st.partition_number = p.partition_number
                       AND OBJECT_ID(t.name) = st.object_id
GO
CREATE OR ALTER     FUNCTION [dbo].[GetFileGroupFromTable]
(@SchemaName sysname, @TableName sysname)
    RETURNS SYSNAME
AS
BEGIN

    RETURN
        (
            SELECT TOP 1	fg.name as [Filegroup]
            FROM sys.partition_functions AS pf
                     JOIN sys.partition_schemes as ps on ps.function_id=pf.function_id
                     JOIN sys.indexes as si on si.data_space_id=ps.data_space_id
                     JOIN sys.objects as so on si.object_id = so.object_id
                     JOIN sys.schemas as sc on so.schema_id = sc.schema_id
                     JOIN sys.partitions as p on
                si.object_id=p.object_id
                    and si.index_id=p.index_id
                     LEFT JOIN sys.partition_range_values as prv on prv.function_id=pf.function_id
                and p.partition_number=
                    CASE pf.boundary_value_on_right WHEN 1
                                                        THEN prv.boundary_id + 1
                                                    ELSE prv.boundary_id
                        END
                /* For left-based functions, partition_number = boundary_id,
                   for right-based functions we need to add 1 */
                     JOIN sys.dm_db_partition_stats as stat on stat.object_id=p.object_id
                and stat.index_id=p.index_id
                and stat.index_id=p.index_id and stat.partition_id=p.partition_id
                and stat.partition_number=p.partition_number
                     JOIN sys.allocation_units as au on au.container_id = p.hobt_id
                and au.type_desc ='IN_ROW_DATA'
                /* Avoiding double rows for columnstore indexes. */
                /* We can pick up LOB page count from partition_stats */
                     JOIN sys.filegroups as fg on fg.data_space_id = au.data_space_id
            WHERE so.[Name] = @TableName
              AND sc.[name] = @SchemaName
        )
END

GO

CREATE OR ALTER         PROCEDURE [dbo].[FkScript]
@TableList NVARCHAR(MAX) = 'ConsumerSession,dbo.Event'
AS

;
WITH CTE AS
         (
		SELECT	DISTINCT
				REPLACE(REPLACE(CASE WHEN CHARINDEX('.',Value) = 0 THEN 'dbo' ELSE LEFT(Value,CHARINDEX('.',Value)-1) END,'[',''),']','') as SchemaName,
				REPLACE(REPLACE(CASE WHEN CHARINDEX('.',Value) = 0 THEN Value ELSE RIGHT(Value,LEN(Value)-CHARINDEX('.',Value)) END,'[',''),']','') as TableName 
		FROM string_split(@TableList, ',') A
		 ),
	CTEIds AS 
		(
		SELECT A.*, B.Object_id, C.schema_id 
		FROM CTE A
		JOIN sys.tables B ON A.[TableName] = B.[Name]
		JOIN sys.schemas C ON B.schema_id = C.schema_id AND C.[Name] = A.SchemaName
		)

SELECT CONVERT(SMALLINT,1) AS OrderId, 'DROP' ScriptType, '
IF EXISTS (SELECT 1 FROM sys.foreign_keys WHERE [Name] = '''+fk.Name+''')
ALTER TABLE '+QUOTENAME(sch2.[Name])+'.'+QUOTENAME(OBJECT_NAME(fk.parent_object_id))+' DROP CONSTRAINT ' +QUOTENAME(fk.Name)+ ';' Script
FROM sys.foreign_keys fk
JOIN CTEIds ON fk.referenced_object_id = CTEIds.Object_Id
JOIN sys.tables tb ON fk.referenced_object_id = tb.object_id
JOIN sys.schemas sch ON sch.schema_id = tb.schema_id
JOIN sys.tables tb2 ON fk.parent_object_id = tb2.object_id
JOIN sys.schemas sch2 ON sch2.schema_id = tb2.schema_id

UNION ALL

SELECT CONVERT(SMALLINT,2) AS OrderId, 'CREATE', '
IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE [Name] = '''+fk.Name+''')
ALTER TABLE '+QUOTENAME(sch2.[Name])+'.'+QUOTENAME(OBJECT_NAME(fk.parent_object_id))+' WITH NOCHECK ADD CONSTRAINT ' +QUOTENAME(fk.Name)
    + ' FOREIGN KEY('+QUOTENAME(sc.name)+')'
    + ' REFERENCES '+QUOTENAME(sch.[Name])+'.'+QUOTENAME(OBJECT_NAME(fk.referenced_object_id))+'('+QUOTENAME(tc.name)+') ;'
FROM sys.foreign_keys fk
JOIN CTEIds ON fk.referenced_object_id = CTEIds.Object_Id
JOIN sys.tables tb ON fk.referenced_object_id = tb.object_id
JOIN sys.schemas sch ON sch.schema_id = tb.schema_id
JOIN sys.tables tb2 ON fk.parent_object_id = tb2.object_id
JOIN sys.schemas sch2 ON sch2.schema_id = tb2.schema_id
         JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
         JOIN sys.columns sc ON sc.column_id = fkc.parent_column_id AND sc.object_id = fk.parent_object_id
         JOIN sys.columns tc ON tc.column_id = fkc.referenced_column_id AND tc.object_id = fk.referenced_object_id

UNION ALL


SELECT CONVERT(SMALLINT,3) AS OrderId, 'CHECK', '
IF EXISTS (SELECT 1 FROM sys.foreign_keys WHERE [Name] = '''+fk.Name+''')
ALTER TABLE '+QUOTENAME(sch2.[Name])+'.'+QUOTENAME(OBJECT_NAME(fk.parent_object_id))+' CHECK CONSTRAINT ' +QUOTENAME(fk.Name)+ ';'
FROM sys.foreign_keys fk
JOIN CTEIds ON fk.referenced_object_id = CTEIds.Object_Id
JOIN sys.tables tb ON fk.referenced_object_id = tb.object_id
JOIN sys.schemas sch ON sch.schema_id = tb.schema_id
JOIN sys.tables tb2 ON fk.parent_object_id = tb2.object_id
JOIN sys.schemas sch2 ON sch2.schema_id = tb2.schema_id

GO

DECLARE 
	@SchemaName SYSNAME = 'Schema', 
	@TableName SYSNAME = 'TableToMergeAndExtend',
	@pscheme VARCHAR(400), 
	@pfunction VARCHAR(400), 
	@Filegroup VARCHAR(400),
	@partitionSize int=10000000,
	@partitionsToMaintain int=10, 
	@ProduceScript bit=0
SELECT @Filegroup = [dbo].[GetFileGroupFromTable](@SchemaName,@TableName)
SELECT TOP 1 @pscheme = PScheme, @pFunction = PFunction 
FROM dbo.IndexPartition
WHERE TableName = @TableName
AND SchemaName = @SchemaName
AND IndexName LIKE 'PK%'  
PRINT 
'	
EXEC dbo.MergeExtendPartitions 
		@pscheme='''+@pscheme+''', @pfunction='''+@pfunction+''', @Tablename='''+@Tablename+''',
		@Filegroup='''+@Filegroup+''',@partitionSize='+convert(varchar,@partitionSize)+',@partitionsToMaintain='+convert(varchar,@partitionsToMaintain)+', 
		@ProduceScript='+convert(varchar,@ProduceScript)+'
'
EXEC dbo.MergeExtendPartitions 
	@pscheme=@pscheme, @pfunction=@pfunction, @Tablename=@Tablename,
	@Filegroup=@Filegroup,@partitionSize=@partitionSize,@partitionsToMaintain=@partitionsToMaintain, 
	@ProduceScript=@ProduceScript
GO

DECLARE 
	@MinIdSelect NVARCHAR(MAX) = 'SELECT Min(Id) FROM Schema.[TableToMergeAndExtend] WHERE [CreatedDate] > @Date',
    @MonthRetention SMALLINT= 14, 
	@Debug BIT = 0,
	@MinIdOutput BIGINT = 0
EXEC [dbo].[Maintenance_PurgeTable] 
	@SchemaName = 'dbo', 
	@TableName = 'MachineConnectivityHistory',
	@MinIdSelect = @MinIdSelect,
    @MonthRetention = @MonthRetention, 
	@Debug = @Debug,
	@MinIdInput = @MinIdOutput,
	@MinIdOutput = @MinIdOutput OUTPUT

GO

CREATE OR ALTER       PROCEDURE [dbo].[MergeExtendPartitions]
    @pscheme VARCHAR(400), @pfunction VARCHAR(400), @Tablename VARCHAR (400), @Filegroup VARCHAR (400),
    @partitionSize INT, @partitionsToMaintain INT, @ProduceScript BIT = 0
AS
    -- SET NOCOUNT ON added to prevent extra result sets from
    SET NOCOUNT ON;

--Variable Declaration
DECLARE
    @highestDataPartition INT
    , @lowestDataPartition INT
    , @highestPartition INT
    , @highestPartValue BIGINT
    , @lowestPartition INT
    --, @partitionSize INT
    , @partValue BIGINT
    --, @partitionsToMaintain INT
    , @partitionsToKeep INT --currently set to keep only one partition in front, but can be changed.
    , @availablePartitions INT
    , @partitionsToCreate INT
    , @sql NVARCHAR(4000)
    , @DatabaseName NVARCHAR(50) = DB_NAME()
    , @Task NVARCHAR(50)
    , @StartTime DATETIME = GETDATE()
    --, @pscheme VARCHAR(400)
    --, @pfunction VARCHAR(400)
    --, @Tablename VARCHAR (400)
    --, @Filegroup VARCHAR (400)
    ;

    --Configure this
--SET @pscheme = 'ps_Prod2_Item' ; --partition function
--SET @pfunction = 'pf_Prod2_Item'; --partition scheme
--SET @Tablename = 'Item'
--SET @Filegroup = 'Item'
--SET @partitionSize = 6000000 ; --40 Million - Preset Partition Size/Chunk
--SET @partitionsToMaintain = 50 ; --Number of Partitions to maintain any given time

--Delete temp table if exists
    DROP TABLE IF EXISTS #Partitions

--Populate Temp Table #Partitions
SELECT
    OBJECT_NAME(i.[object_id]) AS [ObjectName],
    i.index_id,
    prv_left.value AS LowerBoundaryValue,
    prv_right.value AS UpperBoundaryValue,
    CASE pf.boundary_value_on_right
        WHEN 1
            THEN 'RIGHT'
        ELSE 'LEFT'
        END AS PartitionFunctionRange,
    p.partition_number AS PartitionNumber,
    p.rows AS Rows
INTO #Partitions
FROM sys.partitions AS p
         INNER JOIN sys.indexes AS i ON i.object_id = p.object_id AND i.index_id = p.index_id
         INNER JOIN sys.data_spaces AS ds ON ds.data_space_id = i.data_space_id
         INNER JOIN sys.partition_schemes AS ps ON ps.data_space_id = ds.data_space_id
         INNER JOIN sys.partition_functions AS pf ON pf.function_id = ps.function_id
         LEFT OUTER JOIN sys.partition_range_values AS prv_left ON ps.function_id = prv_left.function_id AND prv_left.boundary_id = p.partition_number - 1
         LEFT OUTER JOIN sys.partition_range_values AS prv_right ON ps.function_id = prv_right.function_id AND prv_right.boundary_id = p.partition_number
WHERE ds.name = @pscheme --Partition Scheme Name
  AND i.index_id=1
  AND OBJECT_NAME(i.[object_id]) = @Tablename

    --SELECT * FROM #Partitions ORDER BY PartitionNumber
--DROP TABLE #Partitions

--Set variables for extention
    SET @highestDataPartition = (SELECT MAX(PartitionNumber) FROM #Partitions WHERE Rows <> 0);
    SET @availablePartitions = ((SELECT MAX( PartitionNumber ) FROM #Partitions) - @highestDataPartition) - 1 /* Minus 1 excluding last partition with no upper boundry value*/ ;
    SET @partitionsToCreate = @partitionsToMaintain - @availablePartitions ;
    SET @highestPartition = ((SELECT MAX( PartitionNumber ) FROM #Partitions) - 1) ; /* Minus 1 excluding last partition with no upper boundry value*/
    SET @highestPartValue = (SELECT CONVERT(VARCHAR(12),UpperBoundaryValue,112)FROM #Partitions WHERE PartitionNumber = @highestPartition) ;

--Set variables for merging
    SET @lowestPartition = (SELECT MIN(PartitionNumber) FROM #Partitions) ;
    SET @lowestDataPartition = (SELECT MIN(PartitionNumber) FROM #Partitions WHERE Rows <> 0);
    SET @partitionsToKeep = (@lowestDataPartition - 1); --First partition with data minus 1 keep one empty partition in front of it

--Generate commands for New Partitions
BEGIN TRY
    IF @partitionsToMaintain = @availablePartitions OR @partitionsToMaintain < @availablePartitions
        BEGIN
            PRINT 'No Action Needed. Available Partitions is: ' + CONVERT( VARCHAR(MAX), @availablePartitions ) + ' and Number of Partition to Maintain is: ' + CONVERT( VARCHAR(MAX), @partitionsToMaintain ) ;
        END ;
    ELSE
        BEGIN
            SET @partValue = CAST(@highestPartValue as VARCHAR(12));
            SET @Task = 'Extend Partition'
            WHILE @partitionsToCreate > 0
                BEGIN
                    SET @partValue = @partValue + @partitionSize ; -- Partition Boundary Value Plus @partitionSize
                    SET @sql = 'ALTER PARTITION SCHEME ' + QUOTENAME(@pscheme) + 'NEXT USED ' + QUOTENAME(@Filegroup) + '
			 ALTER PARTITION FUNCTION ' + QUOTENAME(@pfunction) + '() SPLIT RANGE (' + CONVERT(VARCHAR(12), @partValue, 112) + ')' ;

                    BEGIN
                        IF @ProduceScript = 1
                            SELECT @sql
                        ELSE
                            EXEC (@sql);
                        --PRINT @sql;
                    END
                    SET @partitionsToCreate = @partitionsToCreate - 1 ;
                END
        END
END TRY

BEGIN CATCH

    DECLARE @ErrorNumber int;
    DECLARE @ErrorMessage nvarchar(250);
    DECLARE @ErrorSeverity int;
    DECLARE @ErrorState int;
    DECLARE @ErrorLine int;

    SELECT	 @ErrorNumber = ERROR_NUMBER()
         ,@ErrorMessage = ERROR_MESSAGE()
         ,@ErrorSeverity = ERROR_SEVERITY()
         ,@ErrorState = ERROR_STATE()
         ,@ErrorLine = ERROR_LINE();

    RAISERROR(@ErrorMessage,@ErrorSeverity,@ErrorState,@ErrorLine);

END CATCH


--Generate commands to merge old partitions
BEGIN TRY
    IF @lowestPartition >= (@lowestDataPartition - 1)
        BEGIN
            PRINT 'No Action Needed. Current Lowest Partition with Data is: ' + CONVERT(VARCHAR(MAX), @lowestDataPartition) + ' and lowest partition is: ' + CONVERT(VARCHAR(MAX), @lowestPartition);
        END ;
    ELSE
        BEGIN
            SET @Task = 'Merge Partition'
            WHILE @partitionsToKeep > 1
                BEGIN
                    SET @partValue = (SELECT CONVERT(VARCHAR(12),UpperBoundaryValue,112)FROM #Partitions WHERE PartitionNumber = @lowestPartition) ;
                    SET @sql = 'ALTER PARTITION FUNCTION ' + QUOTENAME(@pfunction) + '() MERGE RANGE (' + CONVERT(VARCHAR(12), @partValue, 112 ) + ')' ;

                    BEGIN
                        IF @ProduceScript = 1
                            SELECT @sql
                        ELSE
                            EXEC (@sql);
                        --PRINT @sql
                    END
                    SET @partitionsToKeep = @partitionsToKeep - 1 ;
                    SET @lowestPartition = @lowestPartition +1
                END ;
        END
END TRY

BEGIN CATCH
    SELECT	 @ErrorNumber = ERROR_NUMBER()
         ,@ErrorMessage = ERROR_MESSAGE()
         ,@ErrorSeverity = ERROR_SEVERITY()
         ,@ErrorState = ERROR_STATE()
         ,@ErrorLine = ERROR_LINE();

    RAISERROR(@ErrorMessage,@ErrorSeverity,@ErrorState,@ErrorLine);
END CATCH

--Cleanup
    DROP TABLE IF EXISTS #Partitions ;

GO

CREATE OR ALTER     PROCEDURE [dbo].[Maintenance_PurgeTable]
    @SchemaName SYSNAME,
    @TableName SYSNAME,
    @MinIdSelect NVARCHAR(MAX),
    @MonthRetention TINYINT = 14,
    @Debug BIT = 1,
    @MinIdInput BIGINT = 0,
    @MinIdOutput BIGINT OUTPUT

AS

    SET NOCOUNT ON;
BEGIN TRY
    SET @MonthRetention = ISNULL(@MonthRetention,14)
    --IF @MonthRetention > 10 OR @MonthRetention < 1
    --    RAISERROR('Month retention MUST be between 1 AND 10',16,1)

    -- CALCULATE CS3 Minimum to keep based on ReceivedTime
    DECLARE @Date DATETIME = DATEADD(MONTH, -@MonthRetention, GETDATE())
    --SELECT @Date
    DECLARE @MinCSId BIGINT
    SET @MinCSId = @MinIdInput
    IF @MinCSId IS NULL OR @MinCSId = 0
        BEGIN
            DROP TABLE IF EXISTS #MinId
            CREATE TABLE #MinId (Id BIGINT)
            INSERT INTO #Minid EXEC sp_ExecuteSQL @MinIdSelect, N'@Date DATETIME', @Date
            SELECT TOP 1 @MinCSId = Id FROM #MinId
        END

    IF @MinCSId IS NULL
        RAISERROR('There is no minimum Id',16,1)
    PRINT 'Min id: ' + CONVERT(VARCHAR,@MinCSId)
    SET @MinIdOutput = @MinCSId

    DECLARE @MinPartitionNumberToKeep SMALLINT
    DECLARE @PFunction SYSNAME
    SELECT TOP 1 @PFunction=PFunction FROM dbo.IndexPartition WHERE SchemaName = @SchemaName AND TableName = @TableName
    DECLARE @pfStmt NVARCHAR(MAX)
    SET @pfStmt = 'SELECT Id = $Partition.['+@PFunction+'](@MinCSId)'

    DROP TABLE IF EXISTS #MinPartitionNumberToKeep
    CREATE TABLE #MinPartitionNumberToKeep(Id SMALLINT)
    INSERT INTO #MinPartitionNumberToKeep EXEC sp_ExecuteSQL @pfStmt, N'@MinCSId BIGINT', @MinCSId
    SELECT TOP 1 @MinPartitionNumberToKeep = Id FROM #MinPartitionNumberToKeep
    IF @MinPartitionNumberToKeep IS NULL
        RAISERROR('There is no minimum partition number',16,1)
    PRINT 'Min Partition number not to prune: ' + CONVERT(VARCHAR,@MinPartitionNumberToKeep)

    DECLARE @Stmt NVARCHAR(MAX) = '', @tableList NVARCHAR(MAX) = ''
    SELECT @Stmt = @Stmt + Stmt, @tableList = @tableList + ',' + TableName
    FROM (
             SELECT DISTINCT TableName, '
		TRUNCATE TABLE dbo.'+QUOTENAME(TableName)+' WITH (PARTITIONS ( '+CONVERT(NVARCHAR(16),partition_number)+'));' AS Stmt
             FROM dbo.IndexPartition
             WHERE SchemaName = @SchemaName AND TableName = @TableName
               AND partition_number < @MinPartitionNumberToKeep
               AND row_count > 0
         ) SUB
    DROP TABLE IF EXISTS #FKDependencies
    CREATE TABLE #FKDependencies(ScriptType VARCHAR(32) NOT NULL, Script NVARCHAR(MAX) NOT NULL, OrderId SMALLINT NOT NULL)
    INSERT INTO #FKDependencies (OrderId, ScriptType, Script)
        EXEC dbo.FkScript @TableList = @tableList -- 'ConsumerSession,ConsumerSessionProperty,Item,ItemProperty,ConsumerSessionPayoutReceiverProperty,ConsumerSessionPayoutMethodProperty,ClearingReport_ConsumerSession'

    DECLARE @StmtFkDrop NVARCHAR(MAX) = ''
    SELECT @StmtFkDrop = @StmtFkDrop + Script FROM #FKDependencies WHERE ScriptType = 'DROP'
    --PRINT @StmtFkDrop
    DECLARE @StmtFkCreate NVARCHAR(MAX) = ''
    SELECT @StmtFkCreate = @StmtFkCreate + Script FROM #FKDependencies WHERE ScriptType = 'CREATE'
    --PRINT @StmtFkCreate
    DECLARE @StmtFkCheck NVARCHAR(MAX) = ''
    SELECT @StmtFkCheck = @StmtFkCheck + Script FROM #FKDependencies WHERE ScriptType = 'CHECK'
    --PRINT @StmtFkCheck

    SET @Stmt =
            'BEGIN TRY
            BEGIN TRANSACTION'
                + @StmtFkDrop
                + @Stmt
                + @StmtFkCreate
                + @StmtFkCheck
                +
            '
            COMMIT TRANSACTION
            END TRY
            BEGIN CATCH
            ROLLBACK TRANSACTION
            DECLARE @ErrMsg NVARCHAR(MAX) = ERROR_MESSAGE()
            RAISERROR(@ErrMsg,16,1)
            END CATCH
            '
    PRINT @Stmt
    IF @Debug = 0
        EXEC sp_ExecuteSQL @Stmt

END TRY
BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(MAX) = ERROR_MESSAGE()
    RAISERROR(@ErrorMessage,16,1)
END CATCH
GO