Compresser toutes les données de vos bases

Voici une procédure permettant de compresser toutes les données de vos bases. Elle est à exécuter dans le contexte de la base cible et avec le paramètre de compression qui peut être PAGE ou ROW :
 
 

USE master;
GO

DROP PROCEDURE dbo.sp__COMPRESSION_DATABASE_PAGE_ALL_OBJECTS
GO

CREATE PROCEDURE dbo.sp__COMPRESSION_DATABASE_PAGE_ALL_OBJECTS 
   @DATA_COMPRESSION VARCHAR(4)
AS

SET NOCOUNT ON;

-- vérifier si le paramètre est PAGE ou ROW
SET @DATA_COMPRESSION = UPPER(@DATA_COMPRESSION);
IF @DATA_COMPRESSION NOT IN ('PAGE' , 'ROW')
BEGIN
   RAISERROR('Valeur incorrecte (%s) pour le paramètre @DATA_COMPRESSION.',
             16, 1, @DATA_COMPRESSION);
   RETURN;
END;

-- vérifier si l'instance est au moins en version 2016 et SP1
DECLARE @OK BIT =0, 
        @VER VARCHAR(32) = 
           CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(32));
DECLARE @VERFLOAT FLOAT = 
           CAST(RIGHT(@VER, LEN(@VER) - CHARINDEX('.', @VER) - 2) AS FLOAT);
IF CAST(SERVERPROPERTY('Edition') AS VARCHAR(64)) 
      LIKE 'Enterprise Edition%'
   SET @OK = 1;
IF CAST(SERVERPROPERTY('Edition') AS VARCHAR(64)) 
       LIKE 'Developer Edition%' 
   SET @OK = 1;
IF CAST(SERVERPROPERTY('ProductMajorVersion') AS INT) > 13
   SET @OK = 1;
IF CAST(SERVERPROPERTY('ProductMajorVersion') AS INT) = 13
      AND @VERFLOAT >= 4001.0
   SET @OK = 1;
IF @OK = 0
BEGIN
   RAISERROR('Version de SQL Server incompatible pour ce process. Veuillez monter en version 2016 SP1 au minimum.', 16, 1);
   RETURN;
END;
   
-- vérifier si la base est au moins en mode de compatibilité 2016
IF CAST(SERVERPROPERTY('Edition') AS VARCHAR(64)) 
      LIKE 'Standard Edition%'
      AND NOT EXISTS(SELECT * 
                     FROM   sys.databases 
                     WHERE  name = DB_NAME() 
                       AND  compatibility_level >= 130)
BEGIN
   RAISERROR('Version de la base de données incompatible pour ce process. Veuillez monter la base en version 2016 au minimum.', 16, 1);
   RETURN;
END;

-- tout est bon, on peut compresser les tables et index
DECLARE @WORK TABLE (ID           INT IDENTITY PRIMARY KEY,
                     SQLCMD       NVARCHAR(max),
                     DONE         BIT DEFAULT 0);
INSERT INTO @WORK (SQLCMD)                      
SELECT CASE 
          WHEN i.name IS NULL 
             THEN N'ALTER TABLE [' + s.name + N'].[' + o.name + 
                  N'] REBUILD WITH (DATA_COMPRESSION = PAGE);'
             ELSE N'ALTER INDEX [' + i.name + N'] ON [' + s.name + 
                  N'].[' + o.name + N'] REBUILD WITH (DATA_COMPRESSION = ' 
                  + @DATA_COMPRESSION 
                  + N', FILLFACTOR = ' 
                  + CAST(COALESCE(NULLIF(fill_factor, 0), 100) AS NVARCHAR(16))
                  + N', PAD_INDEX = ' 
                  + CASE is_padded WHEN 1 THEN N'ON' ELSE N'OFF' END
                  + N', ALLOW_ROW_LOCKS = ' 
                  + CASE allow_row_locks WHEN 1 THEN N'ON' ELSE N'OFF' END
                  + N', ALLOW_PAGE_LOCKS = ' 
                  + CASE allow_page_locks WHEN 1 THEN N'ON' ELSE N'OFF' END
                  + N');'
       END
FROM   sys.objects AS o
       JOIN sys.indexes AS i
            ON o.object_id = i.object_id
       JOIN sys.schemas AS s
            ON o.schema_id = s.schema_id
WHERE o.type = 'U'
  AND EXISTS(SELECT * 
             FROM   sys.partitions AS p
             WHERE  i.object_id = p.object_id
               AND  i.index_id = p.index_id
               AND  p.data_compression_desc <> @DATA_COMPRESSION);
DECLARE @SQL NVARCHAR(max) = N'', 
        @ID INT, 
        @DD DATETIME2, 
        @DURATION_S FLOAT, 
        @D VARCHAR(32);
WHILE EXISTS(SELECT * FROM @WORK WHERE DONE = 0)
BEGIN
   SELECT TOP 1 @SQL = SQLCMD, @ID = ID, @DD = SYSUTCDATETIME() 
   FROM   @WORK
   WHERE  DONE = 0;
   EXEC (@SQL);
   SET @DURATION_S = DATEDIFF(ms, @DD, SYSUTCDATETIME()) / 1000.0;
   UPDATE @WORK
   SET DONE = 1
   WHERE ID = @ID;
   SET @SQL = RIGHT(@SQL, LEN(@SQL) - 6);
   SET @SQL = LEFT(@SQL, CHARINDEX(' REBUILD', @SQL)-1);
   SET @D = CAST(@DURATION_S AS VARCHAR(32));
   RAISERROR('%s, compressé en %s seconde(s).', 10, 1 , @SQL, @D) WITH NOWAIT;
END;
GO

EXEC sp_MS_marksystemobject 'sp__COMPRESSION_DATABASE_PAGE_ALL_OBJECTS';
GO

Pour l’utiliser :

USE mabase;
EXEC dbo.sp__COMPRESSION_DATABASE_PAGE_ALL_OBJECTS 'PAGE';

par exemple…

Le code de la procédure

Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
* Le site sur les SGBD relationnels et le SQL : https://sqlpro.developpez.com *
* le blog SQL, SQL Server, SGBDR... sur : https://blog.developpez.com/sqlpro/ *
* Expert Microsoft SQL Server, MVP (Most valuable Professional) depuis 14 ans *
* Entreprise SQL SPOT : modélisation, conseil, audit, optimisation, formation *
* * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *