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…
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 * * * * *