Discussion :

[Badplayer1603]

Bonjour,

suite à une formation suivie, je m'interroge sur la compression des données.
Il a été dit que c'était un gain d'IO au détriment de la consommation CPU.
Mais qu'étant données la charge CPU des serveurs acutels, ce n'était pas un soucis et que du coup la compression c'était "tout benef".

je pensais donc faire quelques test de compression mais après avoir lu cet article, je reste perplexe:
http://www.sqlpac.com/referentiel/do...tm#.VJQ63cOAGY

Je vois que selon les cas on perd en temps de traitement mais on gagne en IO=> quels intérêts?

J'aimerais avoir quelques retours d’expériences sur le sujet.

je vois qu'il est préconisé la compression de type ROW, est ce juste?

Quand doit on compresser? y a-t-il des requêtes qui permettent d'identifier ou d'orienter ces choix?
quand doit on compresser index+table? juste table? ...?

Bref, je prendrais tout ce que vous avez à me dire.

Merci d'avance pour vos retours.

[SQLpro]

Bonjour,

suite à une formation suivie, je m'interroge sur la compression des données.
Il a été dit que c'était un gain d'IO au détriment de la consommation CPU.
Mais qu'étant données la charge CPU des serveurs acutels, ce n'était pas un soucis et que du coup la compression c'était "tout benef".

Ce formateur visiblement manque de pratique !!!

je pensais donc faire quelques test de compression mais après avoir lu cet article, je reste perplexe:
http://www.sqlpac.com/referentiel/do...tm#.VJQ63cOAGY

Je vois que selon les cas on perd en temps de traitement mais on gagne en IO=> quels intérêts?

J'aimerais avoir quelques retours d’expériences sur le sujet.

Effectivement d'un point de vu général, la compression fait perdre pour les mises à jour et fait gagner pour les lectures.
Le gain sera d'autant plus intéressant en mode PAGE s'il y a de nombreuses valeurs identiques...

Démonstration ...

1) les tables et les données

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TABLE TT1 (DATA VARCHAR(50))
GO
 
INSERT INTO TT1
SELECT CAST(NEWID() AS VARCHAR(50))
GO 10000
 
 
CREATE TABLE TT2 (DATA VARCHAR(50))
GO
 
INSERT INTO TT2
SELECT 'B68C39C0-D178-4623-BBBB-98A75D9E8A1A'
GO 10000

2) les index

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
CREATE INDEX X11 ON TT1(DATA);
CREATE INDEX X12 ON TT1(DATA) WITH (DATA_COMPRESSION = PAGE);
CREATE INDEX X21 ON TT2(DATA);
CREATE INDEX X22 ON TT2(DATA) WITH (DATA_COMPRESSION = PAGE);
GO

3) les métriques

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
SELECT i.name, page_count
FROM   sys.indexes AS i 
       CROSS APPLY sys.dm_db_index_physical_stats(DB_ID(), object_id, index_id, NULL, NULL) AS ips 
WHERE  name LIKE 'X%'
  AND  i.object_id IN (OBJECT_ID('TT1'), OBJECT_ID('TT2'));

4) les résultats

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
name       page_count
---------- -------------
X11        68   (pas de compression)
X12        66   (compression mode page)
X21        68   (pas de compression)
X22        15   (compression mode page)

L'index X12 est très faiblement compressé
L'index X22 est très largement compressé...

CQFD

je vois qu'il est préconisé la compression de type ROW, est ce juste?

Non ! cela dépend des cas... PAGE => ROW + PAGE

Quand doit on compresser? y a-t-il des requêtes qui permettent d'identifier ou d'orienter ces choix?
quand doit on compresser index+table? juste table? ...?

Bref, je prendrais tout ce que vous avez à me dire.

Merci d'avance pour vos retours.

La compression est intéressant s'il y a beaucoup de lecture et peu de mises à jour. Vous pouvez auditer le ratio lecture/écriture via :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
SELECT OBJECT_NAME(i.object_id) AS NOM_TABLE, i.name AS NOM_INDEX,
       user_seeks, user_lookups, user_scans,user_updates 
FROM   sys.dm_db_index_usage_stats AS ius
       JOIN sys.indexes AS i
	        ON ius.object_id = i.object_id
			AND ius.index_id = i.index_id
WHERE  database_id = DB_ID()
  AND  i.object_id IN (OBJECT_ID('TT1'), OBJECT_ID('TT2'));

De plus amples informations figure dans notre livre sur SQL Server


A +

[Badplayer1603]

Bonjour,

merci de votre retour.

J'ai votre livre. Malheureusement, le chapitre sur la compression ne fait qu'une page et ne m'a pas plus aidé que les articles trouvés sur le net. En effet, il ne donne pas de méthode pour orienter le type de compression.
Par contre, il y a un lien vers msdn, il me semble, qui serait une aide à la décision.
Malheureusement, je n'ai pas noté le lien et je ne peux regarder cet article dans l’immédiat.
Peut être y a t'il un autre chapitre en complement, mais je ne l'ai pas encore lu.
Le forum étant peuplé d'expert, je me suis dis qu'il serait plus instructif d'avoir vos avis et retour d'experience


en complément de votre réponse, je suis tombé sur cet article:
https://technet.microsoft.com/en-us/...QL.100%29.aspx

qui préconise les requêtes suivantes:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 SELECT o.name AS [Table_Name], x.name AS [Index_Name],
       i.partition_number AS [Partition],
       i.index_id AS [Index_ID], x.type_desc AS [Index_Type],
       i.leaf_update_count * 100.0 /
           (i.range_scan_count + i.leaf_insert_count
            + i.leaf_delete_count + i.leaf_update_count
            + i.leaf_page_merge_count + i.singleton_lookup_count
           ) AS [Percent_Update]
FROM sys.dm_db_index_operational_stats (db_id(), NULL, NULL, NULL) i
JOIN sys.objects o ON o.object_id = i.object_id
JOIN sys.indexes x ON x.object_id = i.object_id AND x.index_id = i.index_id
WHERE (i.range_scan_count + i.leaf_insert_count
       + i.leaf_delete_count + leaf_update_count
       + i.leaf_page_merge_count + i.singleton_lookup_count) != 0
AND objectproperty(i.object_id,'IsUserTable') = 1
ORDER BY [Percent_Update] ASC

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT o.name AS [Table_Name], x.name AS [Index_Name],
       i.partition_number AS [Partition],
       i.index_id AS [Index_ID], x.type_desc AS [Index_Type],
       i.range_scan_count * 100.0 /
           (i.range_scan_count + i.leaf_insert_count
            + i.leaf_delete_count + i.leaf_update_count
            + i.leaf_page_merge_count + i.singleton_lookup_count
           ) AS [Percent_Scan]
FROM sys.dm_db_index_operational_stats (db_id(), NULL, NULL, NULL) i
JOIN sys.objects o ON o.object_id = i.object_id
JOIN sys.indexes x ON x.object_id = i.object_id AND x.index_id = i.index_id
WHERE (i.range_scan_count + i.leaf_insert_count
       + i.leaf_delete_count + leaf_update_count
       + i.leaf_page_merge_count + i.singleton_lookup_count) != 0
AND objectproperty(i.object_id,'IsUserTable') = 1
ORDER BY [Percent_Scan] DESC

pour mesurer les lectures et ecritures.


Que ce soit avec votre requête ou celle de l'article, quel est le ratio à partir duquel il est intéressant de pratiquer les differents types de compression?

de plus , une phrase de l'article joint me fait un peu peur:

Databases with compressed tables or indexes cannot be restored, attached, or in any way used on other editions

pouvez vous m'en dire plus?

[bvesan]

Bonjour,
L'article "Data Compression" sur technet que vous citez est une très bonne référence pour la compression de données.

Il est expliqué la différence entre la compression de lignes (transformation des colonne de longueur fixe en colonne de longueur variable) et la compression de pages(remplacement des redondances par une entrée de dictionnaire)
La compression de ligne est donc très légère en terme de calcul (lors de la compression ET de la décompression), et ne sera efficace que si vous avez de nombreuses colonnes surévaluées (bigint, char(beaucoup), ...)
La compression de pages est nécessairement plus consommatrice en terme de calcul (principalement lors de la compression), mais plus efficace.

Dans les deux cas, on est bien sur une idée de gain de pages sur disque (le gain dépendant complètement de la redondance des données), et donc de gain en terme de données brassées sur disque lors de remonté d'information.
A noter que l'article mentionne la procédure stockée sp_estimate_data_compression_savings pour avoir une idée du gain en terme de compression.

Pour ce qui est du ratio lecture/modification à partir duquel il est intéressant d'utiliser la compression, il n'y a pas à mon avis de valeur fixe; tout dépend de l'activité sur disque de votre instance. Si l'instance passe la majeur partie de son temps à lire des données sur disque, alors il est clair que la compression va réduire les attentes sur disque, a priori avec un gain global sur les performances. Même si pour certaines requêtes, le temps de traitement sera rallongé à cause de la consommation CPU engendrée par la décompression.
(C'est pour cela qu'il est bon de tester la compression de certaines d'une base en rejouant les traitements sur les tables nom compressées puis sur les mêmes tables compressées).

En gros, plutôt que d'utiliser le ratio lectures/modifications, j'opterai pour une observation des attentes sur l'instance.

• si votre instance n'attend pas du tout sur disque, inutile de vous intéresser au sujet COMPRESSION
• Si, par contre, vous constatez des attentes sur les lectures disques, et que celles-ci sont prépondérantes, alors la compression des grosses tables sur lesquelles ces lectures massives sont effectuées est clairement à étudier. (si vous voulez absolument un seuil, disons que lorsque les attentes sur des lectures en base représentent 80% des attentes totales, vous pouvez regarder la compression).

Databases with compressed tables or indexes cannot be restored, attached, or in any way used on other editions

La phrase qui vous inquiète ne veut rien dire d'autre que "C'est une fonctionnalité de l'édition Enterprise, vous ne pourrez pas l'utiliser sur autre chose qu'une édition Enterprise"...

[SQLpro]

...
Pour ce qui est du ratio lecture/modification à partir duquel il est intéressant d'utiliser la compression, il n'y a pas à mon avis de valeur fixe; tout dépend de l'activité sur disque de votre instance. ...

Pas seulement, cela dépend aussi des ressources du serveur... En effet si votre serveur est surdimensionné en terme de CPU et trop léger en terme d'organisation du storage (cas très fréquent) alors la compression aura un effet assez bénéfique dans bien des cas....

C'est pourquoi je vous invite plus a tester et comparer en vraie grandeur, plus qu'à purement auditer avec de savantes formules !

A +

[Badplayer1603]

Bonjour,

merci pour votre réponse détaillée, je commence à y voir plus clair dans la démarche.
Pourriez vous me dire comment identifier les attentes sur lectures disque?

en effet, j'ai regardé les différentes attentes possible de la vu sys.dm_os_wait_stats mais j'ai bien peur de ne pas savoir lesquels sont à ignorer et quelles sont celles à considérer comme attente sur lectures disques.


cdt.