Twitter iPhone pliant OnePlus 11 PS5 Disney+ Orange Livebox Windows 11

fractionnement de page et index cluster sur colonne identity

2 réponses
Avatar
r.fauchatre
Bonjour,
De nombreuse publications techniques affirment que les fractionnements de
page sous SQL Server peuvent être évités si l'index cluster porte sur une
colonne toujours croissante (par exemple une colonne ayant la propriété
identity).

Le script fourni ci-dessous semble montrer l'inverse : non seulement lors
d'insertions dans la table ayant une clé primaire identity en cluster, on
observe des fractionnements de pages via l'analyseur de performances (objet
de performance = "SQLServer:AccessMethods" compteur = "Page Splits/sec") ,
mais leur niveau demeure relativement constant lorsque l'on reconstruit
l'index avec un fillfactor décroissant (respectivement 100 , 80, 60, 40, 20)
et que l'on poursuit les insertions (par lots de 10000)

La même expérience avec un cluster sur une colonne de type varchar
aléatoire (seconde partie du script) montre un taux de fractionnements de
pages nettement plus fluctuant, atteignant un niveau plus élevé, mais qui
décroit de façon concordante à l'évolution du fillfactor pour devenir
négligeable avec un fillfactor de 40%, ce qui est plus conforme aux attentes.

Comment expliquer ce phénomène de fractionnements de page observé avec un
cluster sur une colonne identity ???

y a t'il un moyen de compter le nombre de fractionnements de pages de façon
absolue et non pas sous forme de ratio/s fluctuant selon l'activité du
serveur ?

merci d'avance pour vos réponses,

R.Fauchatre

PS : ces tests ont été effectués sur SQL Server 2000 SP4 Developer Edition
(1 seul utilisateur actif)

====================================
script
====================================
--
=============================================================================
-- Création de la base si nécessaire
--=============================================================================

USE master
IF (SELECT name FROM sysdatabases WHERE name='TEST_INDEX' ) IS NULL CREATE
DATABASE TEST_INDEX
go

USE TEST_INDEX

-- ========================================================
-- procédures de remplissage de la table
-- ========================================================

-- ----------------------------------------------------------
-- génération de chaine (longueur et contenu aléatoire)
-- ----------------------------------------------------------
print 'Création des procédures'

if OBJECT_ID('generate_string') is not null DROP PROC generate_string
go
CREATE PROCEDURE generate_string
@string varchar(20) OUTPUT
AS
BEGIN
DECLARE @limit int
DECLARE @curr_iteration int
SELECT @limit = round((rand() * 20) + 3, 0)
SELECT @curr_iteration = 0
SELECT @string = ''
WHILE @curr_iteration < @limit
BEGIN
SELECT @string = @string + char(round((rand() * 25) + 1, 0) + 64)
SELECT @curr_iteration = @curr_iteration + 1
END
IF SUBSTRING(@string,1,1) = ' '
BEGIN
SELECT @string = SUBSTRING(@string,2,16)
END
END
go
-- ----------------------------------------------------------
-- remplissage de la table de test (10000 lignes à la fois)
-- ----------------------------------------------------------

if OBJECT_ID('FillTable') is not null DROP PROC FillTable
go

CREATE PROC FillTable (@Chaine bit = 0)
AS
BEGIN
SET NOCOUNT ON
DECLARE @string varchar(20)
DECLARE @Compteur int
SET @compteur = 0
WHILE @compteur < 10000
BEGIN
SET @compteur = @compteur + 1
IF @chaine = 1
BEGIN
EXEC generate_string @string output
SET @string = @string + '_' + CAST(@compteur as varchar(5))
INSERT dbo.DemoCluster (col2) values (@string)
END
ELSE
BEGIN
INSERT dbo.DemoCluster DEFAULT VALUES
END
END
END
go
-- ========================================================
-- Test1 : index cluster sur clé primaire int identity
-- ========================================================

print 'Création dela table de test pour index cluster sur identity'

IF OBJECTPROPERTY(object_id('dbo.DemoCluster'), 'IsUserTable') = 1
DROP TABLE dbo.DemoCluster
go

CREATE TABLE dbo.DemoCluster
(
col1 int identity CONSTRAINT PK_DemoClusterCol1 PRIMARY KEY ,
col2 varchar(20) CONSTRAINT DemoClusterCol2Default
DEFAULT current_timestamp,
col3 datetime CONSTRAINT DemoClusterCol3Default
DEFAULT getdate(),
col4 char(30) CONSTRAINT DemoClusterCol4Default
DEFAULT suser_name(),
col5 char(30) CONSTRAINT DemoClusterCol5Default
DEFAULT user_name(),
col6 char(100) CONSTRAINT DemoClusterCol6Default
DEFAULT 'valeur longue longue longue longue longue longue longue
longue longue longue longue longue longue ',
col7 varchar(200) CONSTRAINT DemoClusterCol7Default
DEFAULT 'valeur compacte'
)
go
EXEC sp_helpindex 'dbo.DemoCluster'

dbcc dropcleanbuffers
go
-- remplissage initial
print 'remplissage initial'
EXEC FillTable
EXEC sp_spaceused 'dbo.DemoCluster', true
go

-- redéfinition de l'index avec FillFactor = 100 et PAD_INDEX
print 'définition du fillfactor à 100'
CREATE UNIQUE CLUSTERED Index PK_DemoCLusterCol1 ON DemoCLuster(Col1) WITH
FILLFACTOR = 100,PAD_INDEX, DROP_EXISTING
select indid, OrigFillFactor, rowcnt, used from sysindexes WHERE name =
'PK_DemoClustercol1'
go
EXEC FillTable
EXEC sp_spaceused 'dbo.DemoCluster', true
go
-- redéfinition de l'index avec FillFactor = 80 et PAD_INDEX
print 'définition du fillfactor à 80'
CREATE UNIQUE CLUSTERED Index PK_DemoCLusterCol1 ON DemoCLuster(Col1) WITH
FILLFACTOR = 80,PAD_INDEX, DROP_EXISTING
select indid, OrigFillFactor, rowcnt, used from sysindexes WHERE name =
'PK_DemoClustercol1'
go
EXEC FillTable
EXEC sp_spaceused 'dbo.DemoCluster', true
go
-- redéfinition de l'index avec FillFactor = 60 et PAD_INDEX
print 'définition du fillfactor à 60'
CREATE UNIQUE CLUSTERED Index PK_DemoCLusterCol1 ON DemoCLuster(Col1) WITH
FILLFACTOR = 60,PAD_INDEX, DROP_EXISTING
select indid, OrigFillFactor, rowcnt, used from sysindexes WHERE name =
'PK_DemoClustercol1'
go
EXEC FillTable
EXEC sp_spaceused 'dbo.DemoCluster', true
go
-- redéfinition de l'index avec FillFactor = 40 et PAD_INDEX
print 'définition du fillfactor à 40'
CREATE UNIQUE CLUSTERED Index PK_DemoCLusterCol1 ON DemoCLuster(Col1) WITH
FILLFACTOR = 40,PAD_INDEX, DROP_EXISTING
select indid, OrigFillFactor, rowcnt, used from sysindexes WHERE name =
'PK_DemoClustercol1'
go
EXEC FillTable
EXEC sp_spaceused 'dbo.DemoCluster', true
go
-- redéfinition de l'index avec FillFactor = 20 et PAD_INDEX
print 'définition du fillfactor à 20'
CREATE UNIQUE CLUSTERED Index PK_DemoCLusterCol1 ON DemoCLuster(Col1) WITH
FILLFACTOR = 20,PAD_INDEX, DROP_EXISTING
select indid, OrigFillFactor, rowcnt, used from sysindexes WHERE name =
'PK_DemoClustercol1'
go
EXEC FillTable
EXEC sp_spaceused 'dbo.DemoCluster', true
go

-- ========================================================
-- Test2 : index cluster sur chaine aléatoire
-- ========================================================

print 'Création dela table de test pour index cluster sur chaine aléatoire'

IF OBJECTPROPERTY(object_id('dbo.DemoCluster'), 'IsUserTable') = 1
DROP TABLE dbo.DemoCluster
go

CREATE TABLE dbo.DemoCluster
(
col1 int identity CONSTRAINT PK_DemoClusterCol1 PRIMARY KEY NONCLUSTERED,
col2 varchar(20) CONSTRAINT UK_DemoClusterCol2 UNIQUE CLUSTERED,
col3 datetime CONSTRAINT DemoClusterCol3Default
DEFAULT getdate(),
col4 char(30) CONSTRAINT DemoClusterCol4Default
DEFAULT suser_name(),
col5 char(30) CONSTRAINT DemoClusterCol5Default
DEFAULT user_name(),
col6 char(100) CONSTRAINT DemoClusterCol6Default
DEFAULT 'valeur longue longue longue longue longue longue longue
longue longue longue longue longue longue ',
col7 varchar(200) CONSTRAINT DemoClusterCol7Default
DEFAULT 'valeur compacte'
)
go
EXEC sp_helpindex 'dbo.DemoCluster'

dbcc dropcleanbuffers
go
-- remplissage initial
print 'remplissage initial'
EXEC FillTable 1
EXEC sp_spaceused 'dbo.DemoCluster', true
go

-- redéfinition de l'index avec FillFactor = 100 et PAD_INDEX
print 'définition du fillfactor à 100'
CREATE UNIQUE CLUSTERED Index UK_DemoClusterCol2 ON DemoCLuster(Col2) WITH
FILLFACTOR = 100,PAD_INDEX, DROP_EXISTING
select indid, OrigFillFactor, rowcnt, used from sysindexes WHERE name =
'UK_DemoClusterCol2'
go
EXEC FillTable 1
EXEC sp_spaceused 'dbo.DemoCluster', true
go
-- redéfinition de l'index avec FillFactor = 80 et PAD_INDEX
print 'définition du fillfactor à 80'
CREATE UNIQUE CLUSTERED Index UK_DemoClusterCol2 ON DemoCLuster(Col2) WITH
FILLFACTOR = 80,PAD_INDEX, DROP_EXISTING
select indid, OrigFillFactor, rowcnt, used from sysindexes WHERE name =
'UK_DemoClusterCol2'
go
EXEC FillTable 1
EXEC sp_spaceused 'dbo.DemoCluster', true
go
-- redéfinition de l'index avec FillFactor = 60 et PAD_INDEX
print 'définition du fillfactor à 60'
CREATE UNIQUE CLUSTERED Index UK_DemoClusterCol2 ON DemoCLuster(Col2) WITH
FILLFACTOR = 60,PAD_INDEX, DROP_EXISTING
select indid, OrigFillFactor, rowcnt, used from sysindexes WHERE name =
'UK_DemoClusterCol2'
go
EXEC FillTable 1
EXEC sp_spaceused 'dbo.DemoCluster', true
go
-- redéfinition de l'index avec FillFactor = 40 et PAD_INDEX
print 'définition du fillfactor à 40'
CREATE UNIQUE CLUSTERED Index UK_DemoClusterCol2 ON DemoCLuster(Col2) WITH
FILLFACTOR = 40,PAD_INDEX, DROP_EXISTING
select indid, OrigFillFactor, rowcnt, used from sysindexes WHERE name =
'UK_DemoClusterCol2'
go
EXEC FillTable 1
EXEC sp_spaceused 'dbo.DemoCluster', true
go
-- redéfinition de l'index avec FillFactor = 20 et PAD_INDEX
print 'définition du fillfactor à 20'
CREATE UNIQUE CLUSTERED Index UK_DemoClusterCol2 ON DemoCLuster(Col2) WITH
FILLFACTOR = 20,PAD_INDEX, DROP_EXISTING
select indid, OrigFillFactor, rowcnt, used from sysindexes WHERE name =
'UK_DemoClusterCol2'
go
EXEC FillTable 1
EXEC sp_spaceused 'dbo.DemoCluster', true
go
--
=============================================================================
-- Suppression de la base
--
=============================================================================
USE master
IF (SELECT name FROM sysdatabases WHERE name='TEST_INDEX' ) IS NOT NULL DROP
DATABASE TEST_INDEX
go

2 réponses

Avatar
Christian Robert
Bonjour,

J'ai fait et refait les scripts en opérant des modifications, en vidant plus
fréquement le cache.

J'ai fait le test sur un SQL Server Express.

Dans le cas d'un champ Identity avec un index clustered (le premier), je
n'ai constaté aucune fragmentation interne.

Dans la seconde partie le résulat est faussé du fait que la taille insérée
dans le champ est de taille variable, et de longueur plus importante que le
simple int. D'autre part il y a 2 index sur cette table ce qui fausse
énormément la donne.
J'ai modifié l'ago de génération de la chaîne et supprimé l'index de la clef
primaire, il y a fragmentation interne décroissante en fonction du Fillfactor
(elle devient nulle à 40%), même si effectivement les résultats sont
fluctuant de part la nature aléatoire du champ, il faudrait un plus gros jeu
de test pour se faire une idée.

De plus il ne faut pas confondre fragmentation interne et externe, il y a
obligatoirement de la fragmentation externe qui se produit, car la base de
données n'est pas créée avec une taille initiale suffisante, et aussi une
fragmentation externe de page ou d'extention de part les essais multiples
d'insert / delete fait sur la base de données.

Les résultats ici expriment essentiellement la fragmentation interne, c'est
à dire les coupures de page.

En tout cas c'est une très bonne idée d'avoir fait le test, je trouve que
c'est la meilleure manière de se faire une idée...

--
Cordialement,
Christian Robert http://blogs.developpeur.org/christian/
MCT - Database Development / Database Administration


"r.fauchatre" a écrit :

Bonjour,
De nombreuse publications techniques affirment que les fractionnements de
page sous SQL Server peuvent être évités si l'index cluster porte sur une
colonne toujours croissante (par exemple une colonne ayant la propriété
identity).

Le script fourni ci-dessous semble montrer l'inverse : non seulement lors
d'insertions dans la table ayant une clé primaire identity en cluster, on
observe des fractionnements de pages via l'analyseur de performances (objet
de performance = "SQLServer:AccessMethods" compteur = "Page Splits/sec") ,
mais leur niveau demeure relativement constant lorsque l'on reconstruit
l'index avec un fillfactor décroissant (respectivement 100 , 80, 60, 40, 20)
et que l'on poursuit les insertions (par lots de 10000)

La même expérience avec un cluster sur une colonne de type varchar
aléatoire (seconde partie du script) montre un taux de fractionnements de
pages nettement plus fluctuant, atteignant un niveau plus élevé, mais qui
décroit de façon concordante à l'évolution du fillfactor pour devenir
négligeable avec un fillfactor de 40%, ce qui est plus conforme aux attentes.

Comment expliquer ce phénomène de fractionnements de page observé avec un
cluster sur une colonne identity ???

y a t'il un moyen de compter le nombre de fractionnements de pages de façon
absolue et non pas sous forme de ratio/s fluctuant selon l'activité du
serveur ?

merci d'avance pour vos réponses,

R.Fauchatre

PS : ces tests ont été effectués sur SQL Server 2000 SP4 Developer Edition
(1 seul utilisateur actif)

=================================== > script
=================================== > --
============================================================================ > -- Création de la base si nécessaire
--============================================================================ >
USE master
IF (SELECT name FROM sysdatabases WHERE name='TEST_INDEX' ) IS NULL CREATE
DATABASE TEST_INDEX
go

USE TEST_INDEX

-- ======================================================= > -- procédures de remplissage de la table
-- ======================================================= >
-- ----------------------------------------------------------
-- génération de chaine (longueur et contenu aléatoire)
-- ----------------------------------------------------------
print 'Création des procédures'

if OBJECT_ID('generate_string') is not null DROP PROC generate_string
go
CREATE PROCEDURE generate_string
@string varchar(20) OUTPUT
AS
BEGIN
DECLARE @limit int
DECLARE @curr_iteration int
SELECT @limit = round((rand() * 20) + 3, 0)
SELECT @curr_iteration = 0
SELECT @string = ''
WHILE @curr_iteration < @limit
BEGIN
SELECT @string = @string + char(round((rand() * 25) + 1, 0) + 64)
SELECT @curr_iteration = @curr_iteration + 1
END
IF SUBSTRING(@string,1,1) = ' '
BEGIN
SELECT @string = SUBSTRING(@string,2,16)
END
END
go
-- ----------------------------------------------------------
-- remplissage de la table de test (10000 lignes à la fois)
-- ----------------------------------------------------------

if OBJECT_ID('FillTable') is not null DROP PROC FillTable
go

CREATE PROC FillTable (@Chaine bit = 0)
AS
BEGIN
SET NOCOUNT ON
DECLARE @string varchar(20)
DECLARE @Compteur int
SET @compteur = 0
WHILE @compteur < 10000
BEGIN
SET @compteur = @compteur + 1
IF @chaine = 1
BEGIN
EXEC generate_string @string output
SET @string = @string + '_' + CAST(@compteur as varchar(5))
INSERT dbo.DemoCluster (col2) values (@string)
END
ELSE
BEGIN
INSERT dbo.DemoCluster DEFAULT VALUES
END
END
END
go
-- ======================================================= > -- Test1 : index cluster sur clé primaire int identity
-- ======================================================= >
print 'Création dela table de test pour index cluster sur identity'

IF OBJECTPROPERTY(object_id('dbo.DemoCluster'), 'IsUserTable') = 1
DROP TABLE dbo.DemoCluster
go

CREATE TABLE dbo.DemoCluster
(
col1 int identity CONSTRAINT PK_DemoClusterCol1 PRIMARY KEY ,
col2 varchar(20) CONSTRAINT DemoClusterCol2Default
DEFAULT current_timestamp,
col3 datetime CONSTRAINT DemoClusterCol3Default
DEFAULT getdate(),
col4 char(30) CONSTRAINT DemoClusterCol4Default
DEFAULT suser_name(),
col5 char(30) CONSTRAINT DemoClusterCol5Default
DEFAULT user_name(),
col6 char(100) CONSTRAINT DemoClusterCol6Default
DEFAULT 'valeur longue longue longue longue longue longue longue
longue longue longue longue longue longue ',
col7 varchar(200) CONSTRAINT DemoClusterCol7Default
DEFAULT 'valeur compacte'
)
go
EXEC sp_helpindex 'dbo.DemoCluster'

dbcc dropcleanbuffers
go
-- remplissage initial
print 'remplissage initial'
EXEC FillTable
EXEC sp_spaceused 'dbo.DemoCluster', true
go

-- redéfinition de l'index avec FillFactor = 100 et PAD_INDEX
print 'définition du fillfactor à 100'
CREATE UNIQUE CLUSTERED Index PK_DemoCLusterCol1 ON DemoCLuster(Col1) WITH
FILLFACTOR = 100,PAD_INDEX, DROP_EXISTING
select indid, OrigFillFactor, rowcnt, used from sysindexes WHERE name =
'PK_DemoClustercol1'
go
EXEC FillTable
EXEC sp_spaceused 'dbo.DemoCluster', true
go
-- redéfinition de l'index avec FillFactor = 80 et PAD_INDEX
print 'définition du fillfactor à 80'
CREATE UNIQUE CLUSTERED Index PK_DemoCLusterCol1 ON DemoCLuster(Col1) WITH
FILLFACTOR = 80,PAD_INDEX, DROP_EXISTING
select indid, OrigFillFactor, rowcnt, used from sysindexes WHERE name =
'PK_DemoClustercol1'
go
EXEC FillTable
EXEC sp_spaceused 'dbo.DemoCluster', true
go
-- redéfinition de l'index avec FillFactor = 60 et PAD_INDEX
print 'définition du fillfactor à 60'
CREATE UNIQUE CLUSTERED Index PK_DemoCLusterCol1 ON DemoCLuster(Col1) WITH
FILLFACTOR = 60,PAD_INDEX, DROP_EXISTING
select indid, OrigFillFactor, rowcnt, used from sysindexes WHERE name =
'PK_DemoClustercol1'
go
EXEC FillTable
EXEC sp_spaceused 'dbo.DemoCluster', true
go
-- redéfinition de l'index avec FillFactor = 40 et PAD_INDEX
print 'définition du fillfactor à 40'
CREATE UNIQUE CLUSTERED Index PK_DemoCLusterCol1 ON DemoCLuster(Col1) WITH
FILLFACTOR = 40,PAD_INDEX, DROP_EXISTING
select indid, OrigFillFactor, rowcnt, used from sysindexes WHERE name =
'PK_DemoClustercol1'
go
EXEC FillTable
EXEC sp_spaceused 'dbo.DemoCluster', true
go
-- redéfinition de l'index avec FillFactor = 20 et PAD_INDEX
print 'définition du fillfactor à 20'
CREATE UNIQUE CLUSTERED Index PK_DemoCLusterCol1 ON DemoCLuster(Col1) WITH
FILLFACTOR = 20,PAD_INDEX, DROP_EXISTING
select indid, OrigFillFactor, rowcnt, used from sysindexes WHERE name =
'PK_DemoClustercol1'
go
EXEC FillTable
EXEC sp_spaceused 'dbo.DemoCluster', true
go

-- ======================================================= > -- Test2 : index cluster sur chaine aléatoire
-- ======================================================= >
print 'Création dela table de test pour index cluster sur chaine aléatoire'

IF OBJECTPROPERTY(object_id('dbo.DemoCluster'), 'IsUserTable') = 1
DROP TABLE dbo.DemoCluster
go

CREATE TABLE dbo.DemoCluster
(
col1 int identity CONSTRAINT PK_DemoClusterCol1 PRIMARY KEY NONCLUSTERED,
col2 varchar(20) CONSTRAINT UK_DemoClusterCol2 UNIQUE CLUSTERED,
col3 datetime CONSTRAINT DemoClusterCol3Default
DEFAULT getdate(),
col4 char(30) CONSTRAINT DemoClusterCol4Default
DEFAULT suser_name(),
col5 char(30) CONSTRAINT DemoClusterCol5Default
DEFAULT user_name(),
col6 char(100) CONSTRAINT DemoClusterCol6Default
DEFAULT 'valeur longue longue longue longue longue longue longue
longue longue longue longue longue longue ',
col7 varchar(200) CONSTRAINT DemoClusterCol7Default
DEFAULT 'valeur compacte'
)
go
EXEC sp_helpindex 'dbo.DemoCluster'

dbcc dropcleanbuffers
go
-- remplissage initial
print 'remplissage initial'
EXEC FillTable 1
EXEC sp_spaceused 'dbo.DemoCluster', true
go

-- redéfinition de l'index avec FillFactor = 100 et PAD_INDEX
print 'définition du fillfactor à 100'
CREATE UNIQUE CLUSTERED Index UK_DemoClusterCol2 ON DemoCLuster(Col2) WITH
FILLFACTOR = 100,PAD_INDEX, DROP_EXISTING
select indid, OrigFillFactor, rowcnt, used from sysindexes WHERE name =
'UK_DemoClusterCol2'
go
EXEC FillTable 1
EXEC sp_spaceused 'dbo.DemoCluster', true
go
-- redéfinition de l'index avec FillFactor = 80 et PAD_INDEX
print 'définition du fillfactor à 80'
CREATE UNIQUE CLUSTERED Index UK_DemoClusterCol2 ON DemoCLuster(Col2) WITH
FILLFACTOR = 80,PAD_INDEX, DROP_EXISTING
select indid, OrigFillFactor, rowcnt, used from sysindexes WHERE name =
'UK_DemoClusterCol2'
go
EXEC FillTable 1
EXEC sp_spaceused 'dbo.DemoCluster', true
go
-- redéfinition de l'index avec FillFactor = 60 et PAD_INDEX
print 'définition du fillfactor à 60'
CREATE UNIQUE CLUSTERED Index UK_DemoClusterCol2 ON DemoCLuster(Col2) WITH
FILLFACTOR = 60,PAD_INDEX, DROP_EXISTING
select indid, OrigFillFactor, rowcnt, used from sysindexes WHERE name =
'UK_DemoClusterCol2'
go
EXEC FillTable 1
EXEC sp_spaceused 'dbo.DemoCluster', true
go
-- redéfinition de l'index avec FillFactor = 40 et PAD_INDEX
print 'définition du fillfactor à 40'
CREATE UNIQUE CLUSTERED Index UK_DemoClusterCol2 ON DemoCLuster(Col2) WITH
FILLFACTOR = 40,PAD_INDEX, DROP_EXISTING
select indid, OrigFillFactor, rowcnt, used from sysindexes WHERE name =
'UK_DemoClusterCol2'
go
EXEC FillTable 1
EXEC sp_spaceused 'dbo.DemoCluster', true
go
-- redéfinition de l'index avec FillFactor = 20 et PAD_INDEX
print 'définition du fillfactor à 20'
CREATE UNIQUE CLUSTERED Index UK_DemoClusterCol2 ON DemoCLuster(Col2) WITH
FILLFACTOR = 20,PAD_INDEX, DROP_EXISTING
select indid, OrigFillFactor, rowcnt, used from sysindexes WHERE name =
'UK_DemoClusterCol2'
go
EXEC FillTable 1
EXEC sp_spaceused 'dbo.DemoCluster', true
go
--
============================================================================ > -- Suppression de la base
--
============================================================================ > USE master
IF (SELECT name FROM sysdatabases WHERE name='TEST_INDEX' ) IS NOT NULL DROP
DATABASE TEST_INDEX
go




Avatar
Fred BROUARD
r.fauchatre a écrit :
Bonjour,
De nombreuse publications techniques affirment que les fractionnements de
page sous SQL Server peuvent être évités si l'index cluster porte sur une
colonne toujours croissante (par exemple une colonne ayant la propriété
identity).

Le script fourni ci-dessous semble montrer l'inverse : non seulement lors
d'insertions dans la table ayant une clé primaire identity en cluster, on
observe des fractionnements de pages via l'analyseur de performances (objet
de performance = "SQLServer:AccessMethods" compteur = "Page Splits/sec") ,
mais leur niveau demeure relativement constant lorsque l'on reconstruit
l'index avec un fillfactor décroissant (respectivement 100 , 80, 60, 40, 20)
et que l'on poursuit les insertions (par lots de 10000)



Un index cluster c'EST LA TABLE....

Un index non cluter est une structure complémentaire à la table.

Ce que vous observez comme fragmentation sur l'index cluster, c'est la
fragmentation de la table, et non celle de la colonne indexée.

Changez les tailles variables de vos objets avec des taille fixe (plus
de VARCHAR, NVARCHAR, etc...)?. Vous n'aurez alors plus de fragmentation.


La même expérience avec un cluster sur une colonne de type varchar
aléatoire (seconde partie du script) montre un taux de fractionnements de
pages nettement plus fluctuant, atteignant un niveau plus élevé, mais qui
décroit de façon concordante à l'évolution du fillfactor pour devenir
négligeable avec un fillfactor de 40%, ce qui est plus conforme aux attentes.

Comment expliquer ce phénomène de fractionnements de page observé avec un
cluster sur une colonne identity ???

y a t'il un moyen de compter le nombre de fractionnements de pages de façon
absolue et non pas sous forme de ratio/s fluctuant selon l'activité du
serveur ?

merci d'avance pour vos réponses,



Je fais ce genre de démo dans mon cours d'optimisation à Orsys si cela
vous intéresse.

A +


R.Fauchatre

PS : ces tests ont été effectués sur SQL Server 2000 SP4 Developer Edition
(1 seul utilisateur actif)

=================================== > script
===================================



--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************