Vous cherchez à maximiser l’utilisation de l’espace de vos tables SQL Server ? Dans cet article, nous explorerons un scénario où un utilisateur devait identifier les colonnes qui étaient définies avec des types de données plus grands que nécessaire, et comment il a pu optimiser l’utilisation de l’espace en modifiant ces colonnes en types de données plus appropriés.

Le problème

Imaginez une situation où vous avez une table SQL Server avec des millions de lignes, et chaque octet de stockage compte. Vous voulez identifier les colonnes qui sont définies comme des types de données plus grands, tels que bigint ou int, mais qui stockent en réalité des valeurs beaucoup plus petites dans chaque ligne. En modifiant ces colonnes en types de données plus appropriés, vous pouvez potentiellement économiser une quantité significative d’espace.

La solution

Avant de plonger dans la solution, il est important de noter que l’optimisation de l’utilisation de l’espace des tables ne devrait pas être votre première approche. Il existe d’autres avenues à explorer, telles que la suppression des index inutilisés, l’ajout de compression ou la considération des index de colonnes regroupées. Cependant, dans les scénarios où ces options ne sont pas disponibles, l’optimisation des types de données des colonnes peut être une solution viable.

Pour identifier les colonnes candidates à l’optimisation, nous pouvons examiner les métadonnées et les tables directement. Par exemple, si toutes les valeurs d’une colonne bigint peuvent tenir dans une colonne int, nous pouvons modifier la colonne en int et économiser 4 octets par ligne. De même, si elles tiennent toutes dans un smallint, nous pouvons économiser 6 octets par ligne. Bien que ces économies puissent sembler petites, elles peuvent s’accumuler de manière significative lorsque vous avez plusieurs colonnes sur des millions de lignes.

Dans cet article, nous nous concentrerons sur les types basés sur les entiers : bigint, int, smallint, tinyint et bit. Il convient de noter que les colonnes bit peuvent regrouper jusqu’à 8 colonnes en un seul octet, tandis que chaque colonne tinyint occupera toujours son propre octet.

Génération des types de données

Avant de pouvoir analyser les colonnes, nous devons rassembler les différents types et leurs plages de valeurs prises en charge. Au lieu de copier-coller à partir de la documentation, nous pouvons les générer de manière programmée. Voici un exemple de requête qui génère les types de données :

DECLARE @src bigint = 2;
SELECT [type], minval, maxval FROM 
(VALUES
  (N'bit',      0, 1),
  (N'tinyint',  0, 255),
  (N'smallint', -(POWER(@src,15)),       (POWER(@src,15)-1)),
  (N'int',      -(POWER(@src,31)),       (POWER(@src,31)-1)),
  (N'bigint',   -(POWER(@src,62)-1)*2-2, (POWER(@src,62)-1)*2+1)
) AS v([type], minval, maxval);

Cette requête nous fournit la plage de valeurs prise en charge pour chaque type de données. Nous pouvons utiliser cela comme base pour identifier les colonnes qui peuvent être optimisées.

Identification des colonnes à optimiser

Maintenant que nous avons les types de données et leurs plages, nous pouvons les joindre aux vues de catalogue pour les tables et les colonnes afin de trouver toutes les colonnes candidates qui peuvent potentiellement être optimisées. Nous devons également prendre en compte des informations contextuelles supplémentaires, telles que le classement des types, s’ils peuvent être une “source” ou une “cible”, l’ID du type et le nombre d’octets que chaque type occupe.

En élargissant notre requête initiale et en la joignant aux vues de catalogue pertinentes, nous pouvons créer une table temporaire qui contient toutes les colonnes qui pourraient répondre à nos critères d’optimisation :

DECLARE @src bigint = 2;
WITH types AS 
(
  SELECT * FROM 
  (VALUES
    (1,0,1, 104, N'bit',      1, 0, 1),
    (2,1,1, 48,  N'tinyint',  1, 0, 255),
    (3,1,1, 52,  N'smallint', 2, -(POWER(@src,15)),       (POWER(@src,15)-1)),
    (4,1,1, 56,  N'int',      4, -(POWER(@src,31)),       (POWER(@src,31)-1)),
    (5,1,0, 127, N'bigint',   8, -(POWER(@src,62)-1)*2-2, (POWER(@src,62)-1)*2+1)
  ) AS v(seq, src, trg, type_id, [type], bytes, minval, maxval)
),
cols AS
(
  SELECT t.[object_id],
         [schema] = s.name, 
         [table]  = t.name, 
         [column] = QUOTENAME(c.name), 
         [type]   = styp.name + COALESCE(' (alias: ' + utyp.name + ')', ''),
         c.is_nullable, 
         trgtyp.seq,
         trgtyp.type_id, 
         trgtype = trgtyp.[type],
         savings = srctyp.bytes - trgtyp.bytes, 
         trgtyp.minval,
         trgtyp.maxval,
         [rowcount] = (SELECT SUM([rows]) FROM sys.partitions
           WHERE object_id = t.object_id AND index_id IN (0,1))
  FROM sys.tables AS t
  INNER JOIN sys.schemas AS s
  ON s.[schema_id] = t.[schema_id]
  INNER JOIN sys.columns AS c
  ON t.[object_id] = c.[object_id]
  INNER JOIN sys.types AS styp
  ON c.system_type_id = styp.system_type_id
  AND c.system_type_id = styp.user_type_id
  LEFT OUTER JOIN sys.types AS utyp
  ON c.user_type_id = utyp.user_type_id
  AND utyp.user_type_id <> utyp.system_type_id
  INNER JOIN types AS srctyp
  ON srctyp.type_id = c.system_type_id
  INNER JOIN types AS trgtyp
  ON trgtyp.seq < srctyp.seq
  WHERE srctyp.src = 1
  AND trgtyp.trg = 1
)
SELECT * INTO #cols FROM cols;

SELECT * FROM #cols;

Cette requête génère une table temporaire, #cols, qui contient toutes les informations pertinentes sur les colonnes qui peuvent potentiellement être optimisées. Elle inclut des détails tels que le schéma, la table, le nom de la colonne, le type de données actuel, le type de données potentiel, les économies d’espace et la plage de valeurs pour chaque colonne.

Évaluation des candidats à l’optimisation

Maintenant que nous avons identifié les colonnes qui peuvent potentiellement être optimisées, nous pouvons générer du SQL dynamique pour récupérer les valeurs minimales et maximales de chaque colonne, les comparer aux plages possibles et évaluer nos options. Voici un exemple de génération du SQL dynamique :

DECLARE @sql nvarchar(max) = N';WITH x([object_id],[column],minval,maxval) 
AS (',
        @core nvarchar(max) = N'
    SELECT $oid, ''$c'', MIN($c), MAX($c) FROM $obj UNION ALL';    
SELECT @sql += REPLACE(REPLACE(REPLACE(@core,'$oid',RTRIM(object_id)),
  '$c',[column]),'$obj',QUOTENAME([schema]) + '.' + QUOTENAME([table]))
  FROM (SELECT [schema],[table],[column],object_id FROM #cols
  GROUP BY [schema],[table],[column],object_id) AS x;
SET @sql += N' 
    SELECT NULL,NULL,NULL,NULL
  ) 
  SELECT c.[schema],c.[table],c.[column],c.is_nullable,
    current_type = c.[type],potential_type=c.trgtype,
    space_savings=c.savings*c.[rowcount],x.minval,x.maxval,
    range = RTRIM(c.minval) + '' -> '' + RTRIM(c.maxval) 
  FROM x
  INNER JOIN #cols AS c 
  ON x.[object_id] = c.[object_id]
  AND x.[column] = c.[column]
  AND x.minval >= c.minval
  AND x.maxval <= c.maxval;';
PRINT @sql;
-- EXEC sys.sp_executesql @sql;

Ce code génère la requête SQL dynamique qui récupère les valeurs minimales et maximales de chaque colonne, les compare aux plages possibles et calcule les économies d’espace potentielles. La requête résultante fournit une liste complète des types de données cibles potentiels, ainsi que le type actuel, les économies d’espace et la plage de valeurs pour chaque colonne.

Considérations et étapes suivantes

Il est important de noter que ce script est destiné à identifier les colonnes et à estimer les économies d’espace dans la table de base. Il ne tient pas compte des complications potentielles, telles que les colonnes calculées, les contraintes, les colonnes nulles ou les colonnes utilisées dans les index. De plus, modifier une colonne de largeur fixe pour la réduire peut être une opération de taille de données, ce qui peut avoir des implications sur la taille du journal des transactions et les performances.

Avant de faire des modifications, il est crucial d’évaluer minutieusement l’impact potentiel et de prendre en compte des facteurs tels que la plage de données, les économies d’espace et l’utilisation future de la colonne. Ce script ne génère aucune commande ALTER TABLE, car l’intention est d’encourager une réflexion approfondie avant de faire des modifications.

Résumé

En analysant les types de données et les plages, et en utilisant du SQL dynamique, vous pouvez rapidement identifier les colonnes qui peuvent potentiellement être optimisées pour économiser de l’espace dans vos tables SQL Server. Bien que cet article se concentre sur les colonnes basées sur les entiers, vous pouvez étendre cette approche pour couvrir également les colonnes basées sur les chaînes de caractères. N’oubliez pas d’évaluer minutieusement l’impact potentiel et de prendre en compte tous les facteurs pertinents avant de faire des modifications.

Restez à l’écoute pour d’autres conseils sur l’optimisation de l’utilisation de l’espace des tables SQL Server !