sql-scripts/OCTPDBA-931 - Evaluate databases sizes for TPH consolidation in Azure SQL Hyperscale/compression estimate.sql

/*
24.12.2024, TSC
*/
USE [Arizona]
;
SET XACT_ABORT ON
;
SET NOCOUNT ON
;

--#region temp table
IF OBJECT_ID('tempdb..#temptable') IS NOT NULL
        BEGIN
            DROP TABLE [#temptable];
        END
CREATE TABLE [#temptable]
(
    [db_name]                                            VARCHAR(255) CONSTRAINT [df_db_name] DEFAULT DB_NAME(),
    [object_name]                                        NVARCHAR(128),
    [schema_name]                                        NVARCHAR(128),
    [index_id]                                           INT,
    [partition_number]                                   INT,
    [size_with_current_compression_setting(KB)]          BIGINT,
    [size_with_requested_compression_setting(KB)]        BIGINT,
    [sample_size_with_current_compression_setting(KB)]   BIGINT,
    [sample_size_with_requested_compression_setting(KB)] BIGINT
)
;
--#endregion temp table

--#region create table in hciTools
IF NOT EXISTS (
              SELECT *
              FROM [HCITools].[sys].[tables] [t]
                  JOIN [HCITools].[sys].[schemas] [s] ON [s].[schema_id] = [t].[schema_id]
              WHERE [s].[name] = 'tmp'
                AND [t].[name] = 'compression_estimate'
              )
        BEGIN
            CREATE TABLE [HCITools].[tmp].[compression_estimate]
            (
                [rowId]                           BIGINT IDENTITY NOT NULL,
                [database_name]                   VARCHAR(255)    NOT NULL,
                [schema_name]                     VARCHAR(50)     NOT NULL,
                [table_name]                      VARCHAR(255)    NOT NULL,
                [compute_date]                    DATETIME        NOT NULL,
                [current_size_KB]                 BIGINT          NOT NULL,
                [estimated_compressed_size_in_kb] BIGINT          NULL,
                CONSTRAINT [pk_compression_estimate] PRIMARY KEY ([rowId])
            );
            --DROP TABLE hciTools.tmp.compression_estimate
            --TRUNCATE TABLE hciTools.tmp.compression_estimate
            PRINT 'table compression_estimate created';
        END;
GO
--#endregion create table in hciTools

/* declare variables */
DECLARE @dbName VARCHAR(255)
;

DECLARE [csrDb] CURSOR FAST_FORWARD READ_ONLY FOR
    SELECT [Name]
    FROM [sys].[databases]
    WHERE [Name] LIKE 'activePos%'
       OR [Name] IN ('arizona', 'arizonaCust', 'arizonaLd')
    ORDER BY [NAME] DESC
;

OPEN [csrDb]
;

FETCH NEXT FROM [csrDb] INTO @dbName
;

WHILE @@FETCH_STATUS = 0 BEGIN
    PRINT '---------------------------------------';
    PRINT '---- Switching to ' + @dbName;
    PRINT '---------------------------------------';
    EXECUTE ('
USE '+@dbName+'
DECLARE @schema_name VARCHAR(100), @table_name VARCHAR(500), @iterCnt INT = 0, @expectedIter INT = 0, @tstamp VARCHAR(20);
SELECT @expectedIter = COUNT(1)
FROM sys.[tables] t;

TRUNCATE TABLE [#temptable];

DECLARE csrCompressionEstimate CURSOR FAST_FORWARD READ_ONLY FOR 
    SELECT --TOP 10
        SCHEMA_NAME(t.[schema_id]) AS [schema_name]
        , t.[name] AS [table_name]
    FROM sys.tables t

OPEN csrCompressionEstimate

FETCH NEXT FROM csrCompressionEstimate INTO @schema_name, @table_name

WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO [#temptable]
    (
        [object_name],
        [schema_name],
        [index_id],
        [partition_number],
        [size_with_current_compression_setting(KB)],
        [size_with_requested_compression_setting(KB)],
        [sample_size_with_current_compression_setting(KB)],
        [sample_size_with_requested_compression_setting(KB)]
    )
    
    EXEC sys.sp_estimate_data_compression_savings @schema_name, @table_name, NULL, NULL, ''PAGE'';

    SET @iterCnt = @iterCnt + 1;

    IF @iterCnt%3 = 0
    BEGIN
        SET @tstamp = CONVERT(VARCHAR(20), CURRENT_TIMESTAMP, 114)
        RAISERROR(''%s - Iterration %i / %i'', 0, 0, @tstamp, @iterCnt, @expectedIter) WITH NOWAIT;
    END 
    FETCH NEXT FROM csrCompressionEstimate INTO @schema_name, @table_name
END

CLOSE csrCompressionEstimate
DEALLOCATE csrCompressionEstimate

MERGE [HCITools].[tmp].[compression_estimate] AS trg
USING (
    SELECT 
        [db_name]
        ,[schema_name]
        ,[object_name]
        , SUM([size_with_current_compression_setting(KB)]) AS [size_with_current_compression_setting(KB)]
        , SUM([size_with_requested_compression_setting(KB)]) AS [size_with_requested_compression_setting(KB)]
    FROM #temptable
    GROUP BY [object_name], [schema_name], [db_name]
) AS src ON src.[schema_name] = trg.[schema_name] AND src.[object_name] = trg.[table_name] AND DB_NAME() = trg.[database_name]
WHEN MATCHED 
    THEN UPDATE 
        SET [trg].[compute_date] = CURRENT_TIMESTAMP
          , [trg].[current_size_KB] = src.[size_with_current_compression_setting(KB)]
          , [trg].[estimated_compressed_size_in_kb] = src.[size_with_requested_compression_setting(KB)]
WHEN NOT MATCHED
    THEN INSERT([database_name], [schema_name], [table_name], [compute_date], [current_size_KB], [estimated_compressed_size_in_kb])
    VALUES(src.[db_name], src.[schema_name], src.[object_name], CURRENT_TIMESTAMP, src.[size_with_current_compression_setting(KB)], src.[size_with_requested_compression_setting(KB)])
;

    ');

    FETCH NEXT FROM [csrDb] INTO @dbName;
END;

CLOSE [csrDb]
;
DEALLOCATE [csrDb]
;

SELECT [e].[database_name],
       [e].[schema_name],
       [e].[table_name],
       REPLACE(REPLACE(CONVERT(VARCHAR(100), CONVERT(MONEY, [e].[current_size_KB]), 1), ',', ''''), '.00', '')                 AS [current_size_KB],
       REPLACE(REPLACE(CONVERT(VARCHAR(100), CONVERT(MONEY, [e].[estimated_compressed_size_in_kb]), 1), ',', ''''), '.00', '') AS [current_size_KB],
       CASE
           WHEN [current_size_KB] = 0 THEN 0
           ELSE CAST((([current_size_KB] - [estimated_compressed_size_in_kb]) * 100.0 / [current_size_KB]) AS NUMERIC(25, 2))
       END                                                                                                                     AS [percentage_difference]
FROM [HCITools].[tmp].[compression_estimate] [e]
WHERE [e].[current_size_KB] > 0
  AND [e].[estimated_compressed_size_in_kb] > 0
ORDER BY [percentage_difference] DESC
;