Changer le fill factor d'une clé primaire

**StringBuilder** · 08/03/2017, 15h57

Bonjour,

J'ai une table dont la clé primaire est définie comme suit :

Code sql :

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

1
2
3
4
5
6
7
 
/****** Object:  Index [PK__CRM_ISI___3214EC27C9FE0A05]    Script Date: 08/03/2017 15:42:21 ******/
ALTER TABLE [dbo].[CRM_ISI_H0] ADD PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Dedans, on insère en masse des données, qui certes sont ordonnées, mais sont non séquentielles :

C'est à dire avec l'ordre d'insertion suivant :
1, 2, 3, 9001, 9002, 9003, 2001, 2002, 2003, 5001, 5002, 5003, 4, 5, 6, ...

De ce que je comprends de "PAD_INDEX = OFF", c'est que l'index a un fill factor de 0, et que donc, dans mon exemple, quand je dois insérer 2001 dans la table, SQL Server doit physiquement décaller 9001, 9002 et 9003, puis quand il insère 4, il doit décaller 2001, 2002, ... 9003.

Et j'ai bien l'impression que c'est ce qu'il se passe.
En effet, j'ai une procédure qui insère en masse plusieurs centaines de milliers de lignes séquentielles d'un coup, dont les valeurs se trouvent à peu près au milieu de la plage de table.

Les quelques premières dizaines de lignes se sont insérées rapidement.
Puis depuis, chaque ligne met environ une seconde à s'insérer.

Au bout de 24 heures, le traitement n'en était qu'à 50%.

Je l'ai shooté. Ça a rollbacké toute la transaction.

J'ai relancé, et en quelques dizaines de secondes, le traitement est allé à 50%, et ensuite il a repris sa cadence de une ligne par seconde.

Ça donne vraiment l'impression qu'à chaque ligne SQL Server décalle des centaines de Mo sur le disque.

Je suis donc tenté de modifier la clé primaire de façon à ce qu'elle dispose d'un fillfactor de 20% par exemple (et tant pis pour la place perdue).
Ceci devrait donc, de ce que j'en comprends, limiter la nécessité de déplacer la moitié de la table à chaque ajout de ligne.

Suis-je sur la bonne piste ?

Seulement voilà : comment faire un ALTER sur une clé primaire ?
Je vois bien comment en créer un ou comment en supprimer une, mais je ne vois pas comment la modifier.

Si possible, j'aimerais pouvoir le faire "online" : tant pis si ça fait rammer toutes les transactions pendant 30 minutes, ce sera plus acceptable pour mon client que de devoir arrêter le serveur, même pour 5 minutes.

**StringBuilder** · 08/03/2017, 16h01

Bon, désolé. Il suffisait de faire un ALTER de l'index et non de la PK ^^ (puis un rebuild)

**StringBuilder** · 08/03/2017, 16h05

Bon, la mauvaise nouvelle, c'est que ça n'a absolument rien changé à la vélocité du traitement.
Comprends pas...

-- Edit : Quoique si... ça fait pas des insert mais des update en fait (pas bu assez de café je crois). Et chaque ligne contient un flux compressé de 3 Ko. Pas la peine de chercher plus loin : il décompresse les données, les modifie, puis les recompresse avant de les mettre à jour dans la base, le tout, ligne à ligne... donc pas étonnant que ce soit si long. Le truc moyennement compréhensible, c'est le coup des 50% qui vont super vite après reprise... quoi que non, si, ok, c'est pas du tout transactionnel : la modification étant déjà faite, il ne l'a simplement pas refaite.

Quel programme de 5 lettres...

**elsuket** · 08/03/2017, 16h52

Bonjour,

De ce que je comprends de "PAD_INDEX = OFF", c'est que l'index a un fill factor de 0

Ce n'est pas cela : PAD_INDEX signifie que l'on souhaite remplir au même pourcentage que FILLFACTOR les niveaux intermédiaires de l'index.
L'option FILLFACTOR ne vaut que pour le taux de remplissage des pages feuilles de l'index.

quand je dois insérer 2001 dans la table, SQL Server doit physiquement décaller 9001, 9002 et 9003, puis quand il insère 4, il doit décaller 2001, 2002, ... 9003.

Cela coûterait trop cher. En fait, :

s'il reste de l'espace libre dans la page en quantité suffisante pour recevoir les données, il écrit les données dans cet espace, met à jour la table des emplacements pour respecter l'ordre physique, et l'offset de la ligne, et le tour est joué.
s'il n'en reste pas, il alloue une nouvelle page ... et si la page est "au milieu" de l'index, il "coupe en deux" (dans la littérature c'est un split de page) la page source pour la stocker dans la nouvelle page, puis écrit la ligne dans la page adéquate. Cette opération est coûteuse.

Dans le second cas, on peut s'en convaincre à l'aide de la DMF sys.dm_db_index_operational_stats : dans le cas présent, les colonnes dont le nom commence par leaf_ et nonleaf_ nous le donnent.

Effectivement, ALTER INDEX REBUILD est un moyen de définir un FILLFACTOR une fois que l'index a été créé.
On peut aussi utiliser CREATE INDEX avec l'option WITH (DROP_EXISTING = ON, FILLFACTOR = <FF>), ce qui, à peu de choses près, est équivalent.

@++

**elsuket** · 08/03/2017, 16h57

Et chaque ligne contient un flux compressé de 3 Ko. Pas la peine de chercher plus loin : il décompresse les données, les modifie, puis les recompresse avant de les mettre à jour dans la base, le tout, ligne à ligne... donc pas étonnant que ce soit si long

Ha, là par contre, il y a un autre effet : on stocke environ 3Ko dans la page, qui elle fait 8Ko.
Donc le moteur est sans cesse en train d'allouer des pages qui stockent ... 2 lignes seulement, les 2Ko restants étant inutilisables.
Comme en plus on les insère dans un odre non-séquentiel, un bon paquet de splits de page doivent se produire, ceci expliquant cela.

Quel est le type de la colonne qui stocke le flux compressé ? varbinary(max) ?
Peut-être devriez-vous considérer l'utilisation de FileStream

@++

**olivtone** · 08/03/2017, 17h59

je profite de ce post pour m'interroger sur les bonnes pratiques sur les insertions en SQL au niveau performance :

Personnellement sur les insertions en masse :
- Je decoupe en plusieurs lots les insertions ex: 1 million d'insertion je le decoupe 10 fois 100000 lignes
- Je desactive les Index lors des insertions sauf la PK clustered, et j'ai pu apercevoir des ameliorations pas monstrueuses mais toujours bonne a prendre

et vous quelles sont les votre de coté ?

merci a vous

**elsuket** · 08/03/2017, 22h37

Je decoupe en plusieurs lots les insertions ex: 1 million d'insertion je le decoupe 10 fois 100000 lignes

Effectivement j'ai pour habitude de procéder par lots, de façon à ne pas faire grossir trop largement le fichier du journal des transactions.
Je fais cela aussi pour des mises à jour et suppression d'un grand nombre de lignes.
C'est intéressant notamment lorsque la table cible a de nombreuses clés étrangères la désignant, ou qu'il y a des cascades.

Je desactive les Index lors des insertions sauf la PK clustered, et j'ai pu apercevoir des ameliorations pas monstrueuses mais toujours bonne a prendre

Oui, c'est une pratique effectivement commune, et tout à fait compréhensible. Il est plus rapide de reconstruire un index une fois la table complètement peuplée que de le maintenir au fur et à mesure des ajouts de ligne.
En effet cela induit les allocations et splits de page

@++

**SQLpro** · 09/03/2017, 09h47

Ce qui aurait été intéressant c'est de stocker les LOBs en dehors des données relationnelles de la table en utilisant la directive TEXTIMAGE ON pour définir un groupe de fichier spécifique pour stockées ces données là...

Arian Papilllon à publié un article intéressant pour modifier le stockage d'une table existante dans ce sens, mais cela passe par du pzrtitionnement.

A +

**elsuket** · 09/03/2017, 14h49

Article que voici ...

On peut aussi forcer ce stockage en dehors des pages de la table à l'aide de la procédure stockée sp_tableoption avec le paramètre @OptionName = 'value types out of row' positionné à 1.

@++

**StringBuilder** · 09/03/2017, 15h59

Envoyé par elsuket

Ha, là par contre, il y a un autre effet : on stocke environ 3Ko dans la page, qui elle fait 8Ko.
Donc le moteur est sans cesse en train d'allouer des pages qui stockent ... 2 lignes seulement, les 2Ko restants étant inutilisables.
Comme en plus on les insère dans un odre non-séquentiel, un bon paquet de splits de page doivent se produire, ceci expliquant cela.

Quel est le type de la colonne qui stocke le flux compressé ? varbinary(max) ?
Peut-être devriez-vous considérer l'utilisation de FileStream

@++

Ben non, c'est un type "IMAGE" (quand je dis que c'est une équipe de trisomiques dégénérés congénitaux qui ont écrit ce programme, je me base sur des faits réels ^^)
Du coup pas de FILESTREAM possible... (enfin, je crois pas)

Par contre, le coup de la page, ça m'intéresse : y'a moyen de changer la taille des pages ?
Car autant une majorité des pages fait entre 1 et 4 Ko, autant un nombre très conséquent fait beaucoup plus.
Sans aller dans des pages de 100 Mo, peut-être serait-il intéressant d'avoir une taille tournant autour de 96 Ko par exemple ?

**elsuket** · 09/03/2017, 16h26

Par contre, le coup de la page, ça m'intéresse : y'a moyen de changer la taille des pages ?

Non, c'est n'est pas possible

Mais vous pouvez tester ce que SQLPro et moi vous avons proposé

@++

**StringBuilder** · 09/03/2017, 17h00

Oups, faut que j'ouvre mes yeux ^^

Je regarde ça tout de suite !

**StringBuilder** · 09/03/2017, 17h27

Hmmm, bon, je crois que c'est mort pour faire un truc propre...

C'est SQL Server 2014 Standard : donc pas de partitionnement

Du coup j'ai fait ça :

Code sql :

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

1
2
 
CREATE UNIQUE CLUSTERED INDEX PK__TE_ISI_H__3214EC2728C9B3B8 ON TE_ISI_H0 (ID ASC) WITH (DROP_EXISTING=ON) ON SANS_LOB;

Ce qui fait exactement l'inverse de ce qu'il faudrait...
Mais au moins les données et les BLOB sont plus dans le même fichier, c'est toujours mieux que rien...

Par contre, ce qui me surprend, c'est que ça a été absolument instantané... Autant j'ai l'impression que ça a changé quelque chose au niveau des meta de la table, autant physiquement, j'ai l'impression que ça n'a rien fait du tout !

**elsuket** · 10/03/2017, 13h42

Attention à ce que disait SQLPro : soit il faut créer une table à l'identique (structure, clés, index) en utilisant la clause TEXTIMAGE_ON, soit il faut partitionner. Quelle option avez-vous choisi ?
Que donne la requête suivante ?

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT		OBJECT_SCHEMA_NAME(P.object_id) table_schema
		, OBJECT_NAME(P.object_id) table_name
		, P.index_id
		, P.partition_number
		, AU.allocation_unit_id
		, AU.type_desc
		, AU.total_pages
		, AU.used_pages
		, AU.data_pages
FROM		sys.partitions AS P
INNER JOIN	sys.system_internals_allocation_units AS AU
			ON AU.container_id = P.partition_id
WHERE		P.object_id = OBJECT_ID('monSchema.maTable')
ORDER BY	table_schema, table_name, P.index_id, P.partition_number, AU.type;

@++

**StringBuilder** · 10/03/2017, 14h12

Ni l'une ni l'autre, dans la mesure où :
- Sur une Standard, y'a pas de partitionnement possible
- Je suis en PROD, donc pas de coupure de service possible

Quant à la requête, ça donné ça (export CSV depuis Management Studio, c'est pas hyper lisible

)

Code csv :

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

1
2
3
4
5
6
 
table_schema	table_name	index_id	partition_number	allocation_unit_id	type_desc	total_pages	used_pages	data_pages
dbo	TE_ISI_H0	2	1	72057594984660992	IN_ROW_DATA	21861	21850	21511
dbo	TE_ISI_H0	3	1	72057594984726528	IN_ROW_DATA	14945	14908	14671
dbo	TE_ISI_H0	1	1	72057594985644032	IN_ROW_DATA	20433	20423	20377
dbo	TE_ISI_H0	1	1	72057594090029056	LOB_DATA	164991	164899	0

**elsuket** · 10/03/2017, 21h14

J'ai essayé de voir ce que fait un ALTER TABLE ... SWITCH :

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
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
CREATE TABLE lob_storage_direct
(
	lsd_id int NOT NULL IDENTITY
		CONSTRAINT PK_lob_storage_direct PRIMARY KEY
	, lsd_name varchar(16) NOT NULL
		CONSTRAINT UQ_lob_storage_direct__lsd_name UNIQUE
	, lsd_value varchar(max) NOT NULL
		CONSTRAINT CHK_lob_storage_direct__lsd_value CHECK (LEN(lsd_value) > 0)
)
 
-- Peuplement de la table avec quelques lignes ...
;WITH
	CTE AS
	(
		SELECT		SUBSTRING
				(
					REPLACE(CAST(NEWID() AS varchar(36)), '-', '')
					, ABS(CHECKSUM(NEWID())) % 8
					, 8 + ABS(CHECKSUM(NEWID())) % 8
				) AS lsd_name
				, REPLICATE
				(
					SUBSTRING
					(
						REPLACE(CAST(NEWID() AS varchar(36)), '-', '')
						, (ABS(CHECKSUM(NEWID())) % 8) + 1
						, 9 + (ABS(CHECKSUM(NEWID())) % 8)
					)
					, ABS(CHECKSUM(NEWID())) % 100
				) AS lsd_value
		FROM		sys.objects AS A
		CROSS JOIN	sys.objects AS B
	)
INSERT INTO dbo.lob_storage_direct
(
	lsd_name
	, lsd_value
)
SELECT	lsd_name
	, lsd_value
FROM	CTE
WHERE	LEN(lsd_value) > 0
GO
 
-- Métadonnées d'allocation de l'espace pour cette table
SELECT		OBJECT_SCHEMA_NAME(P.object_id) table_schema
		, OBJECT_NAME(P.object_id) table_name
		, P.index_id
		, P.partition_number
		, AU.allocation_unit_id
		, AU.type_desc
		, AU.total_pages
		, AU.used_pages
		, AU.data_pages
FROM		sys.partitions AS P
INNER JOIN	sys.system_internals_allocation_units AS AU
			ON AU.container_id = P.partition_id
WHERE		P.object_id = OBJECT_ID('dbo.lob_storage_direct')
ORDER BY	table_schema, table_name, P.index_id, P.partition_number, AU.type;
GO
 
-- Création d'une nouvelle table à la structure identique
CREATE TABLE dbo.lob_storage_out_of_row
(
	lsd_id int NOT NULL IDENTITY
		CONSTRAINT PK_lob_storage_out_of_row PRIMARY KEY
	, lsd_name varchar(16) NOT NULL
		CONSTRAINT UQ_lob_storage_out_of_row__lsoor_name UNIQUE
	, lsd_value varchar(max) NOT NULL
		CONSTRAINT CHK_lob_storage_out_of_row__lsoor_value CHECK (LEN(lsd_value) > 0)
)
GO
 
-- Forçage du stockage des LOB dans des pages de type LOB_DATA
EXEC sp_tableoption
	'dbo.lob_storage_out_of_row'
	, 'large value types out of row'
	, 1
GO
 
-- Echange des structures
-- Ceci est instantané puisque c'est un simple échange de métadonnées sur les numéros d'unité d'allocation
ALTER TABLE dbo.lob_storage_direct
SWITCH TO dbo.lob_storage_out_of_row
GO
 
--  Métadonnées d'allocation de l'espace pour la nouvelle table
SELECT		OBJECT_SCHEMA_NAME(P.object_id) table_schema
		, OBJECT_NAME(P.object_id) table_name
		, P.index_id
		, P.partition_number
		, AU.allocation_unit_id
		, AU.type_desc
		, AU.total_pages
		, AU.used_pages
		, AU.data_pages
FROM		sys.partitions AS P
INNER JOIN	sys.system_internals_allocation_units AS AU
			ON AU.container_id = P.partition_id
WHERE		P.object_id = OBJECT_ID('dbo.lob_storage_out_of_row')
ORDER BY	table_schema, table_name, P.index_id, P.partition_number, AU.type;

Mais bien sûr comme il s'agit d'une simple modification des métadonnées sous-jacentes aux tables, cela n'entraîne aucun changement en ce qui concerne le stockage des données.
A la suite de ce test, je me suis dit qu'avec un simple UPDATE, il devrait se passer quelque chose :

Code :

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

1
2
UPDATE	dbo.lob_storage_out_of_row
SET	lsd_value = lsd_value

Effectivement, avant l'exécution de cet ordre, nous avions 1889 pages de type IN_ROW_DATA, et zéro de type LOB_DATA.
Après, nous avons toujours 1889 pages de type IN_ROW_DATA, mais 2041 pages de type LOB_DATA.
N.B : sur reproduction de ce test, il est fort probable que l'on obtienne un comptage de page différent, simplement du aux valeurs de chaîne pseudo-aléatoirement générées.

Ce qui m'étonne, c'est que le nombre de pages de type IN_ROW_DATA reste inchangé. Le processus GHOST CLEANUP n'en a à mon avis que faire, et un CHECKPOINT n'y change rien non plus.
Voyons ce que donne un ALTER INDEX ALL ON dbo.lob_storage_out_of_row REORGANIZE WITH (LOB_COMPACTION = ON) : l'option REORGANIZE est une opération entièrement en ligne.
Maintenant, nous avons 137 pages de type IN_ROW_DATA, et toujours 2041 de type LOB_DATA.

C'est donc à essayer sur une copie de votre base de données de production.
Comme vous parliez de plusieurs centaines de milliers de lignes, il faudra procéder à l'UPDATE par lots de lignes, de 10 000 par exemple.

@++

**hmira** · 14/03/2017, 17h31

Envoyé par elsuket

Ce n'est pas cela : PAD_INDEX signifie que l'on souhaite remplir au même pourcentage que FILLFACTOR les niveaux intermédiaires de l'index.

Bonjour Eslsuket,
C'est très juste, mais dans la pratique, personnellement je rencontre que très rarement pour ne pas dire jamais ! d'index où PAD_INDEX est défini à ON lorsque bien sûr FILLFACTOR est différent de 0 et/ou 100.
En d'autres termes, dans la quasi majorité des bases de données que j'ai pu rencontré (99.9999999% des cas), la propriété PAD_INDEX n'est jamais mise en œuvre.
C.à.d, dans la pratique, il est quasiment improbable que le résultat de la requête ci-dessous ne soit pas vide !

Code SQL :

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

1
2
3
4
5
select i.fill_factor, i.is_padded, i.* 
from sys.indexes i with (nolock) 
where i.fill_factor > 0 
AND   i.fill_factor < 100 
AND   i.is_padded = 1

En effet, nombre de DBA, y compris moi-même ! n'investiguent pas assez cette propriété (PAD_INDEX) !
Quel est votre avis sur ce constat ?
PS : Je parle bien de PAD_INDEX, je ne parle pas de FILLFACTOR, cette dernière fait déjà l'objet d'une attention particulière par nombre de DBA.

Merci.
A+

**SQLpro** · 15/03/2017, 09h58

PAD_INDEX n'a d'intérêt que pour des très grosses tables ou de très gros index (des dizaines de Go)

A +

**hmira** · 15/03/2017, 11h24

Envoyé par SQLpro

PAD_INDEX n'a d'intérêt que pour des très grosses tables ou de très gros index (des dizaines de Go)

A +

Merci SQLPro pour cette précision.
Donc si je comprends bien, pour les "grosses tables (des dizaines de Go)" comme tu dis, pour lesquelles, le partitionnement et l'alignement des indexes n'a pas été mis en œuvre, il faudra, le cas échéant, se préoccuper du PAD_INDEX, dès lors que les allocations (Split) et la défragmentation liées aux nœuds intermédiaires de l'index deviennent trop importantes.
A+

**SQLpro** · 15/03/2017, 11h34

Yes !

A +

Changer le fill factor d'une clé primaire [2014]

Administration SQL Server

Discussions similaires

Partager

Partager