SQL Server - La FAQConsultez toutes les FAQ

Nombre d'auteurs : 13, nombre de questions : 119, dernière mise à jour : 31 mai 2011 Ajouter une question

Question / réponses à tout ce que vous avez toujours voulu savoir sur Microsoft SQL Server sans jamais oser le demander

Sommaire →Administration de la base de données →Performances (2)

Comment réindexer une base ?
Extensions uniformes ou extensions mixtes ?

Comment réindexer une base ?

La gestion des index par SQL Server peut entraîner la fragmentation de ceux-ci et une dégradation des performances. Si le volume d'informations de votre base évolue beaucoup, il est nécessaire de réindexer votre base. Plus votre base change, plus les index perdent en efficacité, il faut donc les recréer. On peut reconstruire les index avec la commande DBCC REINDEX.

Code tsql :

Sélectionner tout

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
CREATE PROCEDURE REINDEXATION AS 
DECLARE @table_name sysname 
DECLARE @TSQL nvarchar(4000) 
DECLARE @Err int 
DECLARE @Mess varchar(400) 
SET @Mess = 'Début réindexation' 
RAISERROR(@Mess, 10, 1) WITH LOG 
DECLARE table_list CURSOR FAST_FORWARD FOR SELECT name FROM sysobjects WHERE type = 'u' 
OPEN table_list 
FETCH NEXT FROM table_list INTO @table_name 
WHILE @@fetch_status = 0 
 BEGIN 
 SET @TSQL = N'DBCC DBREINDEX(' + @table_name + N')'  
  
 EXECUTE sp_executesql @TSQL 
 SET @Err = @@error 
 IF (@Err != 0) 
  BEGIN 
  SET @Mess = 'Echec réindexation table ' + @table_name + ' suite erreur ' + CONVERT(varchar, @Err)  
  RAISERROR(@Mess, 16, 1) WITH LOG 
  END 
 FETCH NEXT FROM table_list INTO @table_name 
 END 
CLOSE table_list 
DEALLOCATE table_list 
SET @Mess = 'Fin réindexation' 
RAISERROR(@Mess, 10, 1) WITH LOG 
GO

Mis à jour le 28 avril 2006 HULK

Extensions uniformes ou extensions mixtes ?

SQL Server stocke les pages des fichier de données (tables, index, blobs...) dans des blocs de 8 pages appelés "extents" (extensions en français). Autant une page ne peut appartenir qu'à un seul objet, autant une extension peut comporter des pages de différents objets. Est-il possible de force la mise en place d'extension ne contenant qu'un seul et même type d'objet ? La réponse est oui, mais n'est pas sans conséquences !

Les fichiers de données des bases MS SQL Server, sont constitués de pages de 8 Ko organisées par blocs contigus de 8 appelées extensions. A l'intérieur d'une page ne figure que les information d'un seul et même objet (ligne de table, ligne d'index ou blob). En revanche et par défaut, une extensions peut contenir des pages abritant différents objets. On appelle cela une extension mixte (mixed extent). Bien entendu au sein d'une même base, il est possible que certaines extension soient uniformes.

Dans certains cas, il y aurait intérêt à forcer le moteur de stockage à utiliser uniquement des extensions uniformes.

Ces cas sont rares mais intéressants :

le cas de la VLDB ayant essentiellement de très grosse table ;
le cas de tempdb si l'on a pensé à la répartir sur autant de gros fichier qu'il y a de CPU.

Pour ce faire, il faut utiliser le drapeau de trace 1118.

Voici un petit script qui montre l'effet de la mise en place de ce paramétrage :

Code sql :

Sélectionner tout

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
CREATE DATABASE DB_TEST_EXTENT;  
GO  
  
USE DB_TEST_EXTENT;  
GO  
  
CREATE TABLE dbo.T_LONGUE_LIGNE_1_LGL  
(LGL_ID    int identity,  
 LGL_DATA  char(8000) default 'blabla');  
GO  
  
WHILE NOT EXISTS(SELECT *   
                 FROM   dbo.T_LONGUE_LIGNE_1_LGL   
                 WHERE  LGL_ID = 8)  
    INSERT INTO dbo.T_LONGUE_LIGNE_1_LGL  DEFAULT VALUES;  
GO  
  
DBCC TRACEON(1118);  
GO  
  
CREATE TABLE dbo.T_LONGUE_LIGNE_2_LGL  
(LGL_ID    int identity,  
 LGL_DATA  char(8000) default 'blabla');  
GO  
  
WHILE NOT EXISTS(SELECT *   
                 FROM   dbo.T_LONGUE_LIGNE_2_LGL   
                 WHERE  LGL_ID = 8)  
    INSERT INTO dbo.T_LONGUE_LIGNE_2_LGL  DEFAULT VALUES;  
GO  
  
DBCC EXTENTINFO(DB_TEST_EXTENT, 'dbo.T_LONGUE_LIGNE_1_LGL', -1);  
DBCC EXTENTINFO(DB_TEST_EXTENT, 'dbo.T_LONGUE_LIGNE_2_LGL', -1);

Code sql :

Sélectionner tout

1
2
3
4
5
6
7
8
9
10
file_id     page_id     pg_alloc    ext_size    object_id   index_id    partition_number partition_id         iam_chain_type       pfs_bytes  
----------- ----------- ----------- ----------- ----------- ----------- ---------------- -------------------- -------------------- ------------------  
1           21          1           1           2105058535  0           1                72057594038779904    In-row data          0x6400000000000000  
1           55          1           1           2105058535  0           1                72057594038779904    In-row data          0x6400000000000000  
1           77          1           1           2105058535  0           1                72057594038779904    In-row data          0x6400000000000000  
1           78          1           1           2105058535  0           1                72057594038779904    In-row data          0x6400000000000000  
1           79          1           1           2105058535  0           1                72057594038779904    In-row data          0x6400000000000000  
1           80          1           1           2105058535  0           1                72057594038779904    In-row data          0x6400000000000000  
1           89          1           1           2105058535  0           1                72057594038779904    In-row data          0x6400000000000000  
1           90          1           1           2105058535  0           1                72057594038779904    In-row data          0x6400000000000000

Code sql :

Sélectionner tout

1
2
3
file_id     page_id     pg_alloc    ext_size    object_id   index_id    partition_number partition_id         iam_chain_type       pfs_bytes  
----------- ----------- ----------- ----------- ----------- ----------- ---------------- -------------------- -------------------- ------------------  
1           176         8           8           2137058649  0           1                72057594038845440    In-row data          0x4444444444444444

Comme on le voit, ces deux tables contiennent les mêmes données. L'une a donné lieu à la description de 8 pages dans la même extension, l'autre la description globale des 8 pages dans l'extension uniforme.

Bien tester les effets de ce paramétrage, car le remède peut s'avérer dans certaines circonstances, pire que le mal.

Réglage du paramètre

En exécutant :

Code sql :

Sélectionner tout

DBCC TRACEON(1118);

Vous forcez la session en cours à générer des extensions uniformes. Cela peut être intéressant par exemple si vous voulez insérer un lot important de lignes dans une table sans générer d'extensions mixtes. Par exemple lors de la création d'une base de données, pour l'alimentation du référentiel.

Code sql :

Sélectionner tout

DBCC TRACEON(1118, -1);

Vous forcez le moteur pour toutes les sessions à venir à générer des extensions uniforme.

Code sql :

Sélectionner tout

1
2
DBCC TRACEOFF(1118) ; 
DBCC TRACEOFF(1118, -1) ;

Vous revenez au comportement normal du moteur de stockage.

Mis à jour le 24 mai 2009 SQLpro

Proposer une nouvelle réponse sur la FAQ

Ce n'est pas l'endroit pour poser des questions, allez plutôt sur le forum de la rubrique pour ça

Titre de la question

Réponse à la question

Liens sous la question

Les sources présentées sur cette page sont libres de droits et vous pouvez les utiliser à votre convenance. Par contre, la page de présentation constitue une œuvre intellectuelle protégée par les droits d'auteur. Copyright © 2024 Developpez Developpez LLC. Tous droits réservés Developpez LLC. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents et images sans l'autorisation expresse de Developpez LLC. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.