{"id":3268,"date":"2012-04-02T09:01:29","date_gmt":"2012-04-02T08:01:29","guid":{"rendered":"http:\/\/blog.capdata.fr\/?p=3268"},"modified":"2019-09-13T14:23:34","modified_gmt":"2019-09-13T13:23:34","slug":"production-sql-server-reorganisation-des-objets","status":"publish","type":"post","link":"https:\/\/blog.capdata.fr\/index.php\/production-sql-server-reorganisation-des-objets\/","title":{"rendered":"Production SQL Server: R\u00e9organisation des objets"},"content":{"rendered":"\"twitter\"<\/a>\"linkedin\"<\/a>\"mail\"<\/a>

Cinqui\u00e8me article de la s\u00e9rie Une approche pragmatique de la production SQL Server<\/a>, il s\u2019agit ici de la d\u00e9fragmentation des objets.<\/p>\n

Qu\u2019est-ce que la fragmentation\u00a0?<\/strong><\/p>\n

Lorsqu\u2019un objet (table ou indexe) subit des modifications, ses pages peuvent se vider partiellement (on parle de fragmentation interne) et certaines pages, voire certaines extensions (groupes de 8 pages contig\u00fces) peuvent se vider compl\u00e8tement (il s\u2019agit alors de fragmentation externe).<\/p>\n

Quel est l\u2019impact sur les performances\u00a0?<\/strong><\/p>\n

Sur disque<\/em>
\nPour lire une donn\u00e9e sur disque physique, il faut\u00a0:<\/p>\n

D\u00e9placer la t\u00eate de lecture du disque. Cette op\u00e9ration est longue, de l\u2019ordre de 5 millisecondes en moyenne<\/p>\n

Attendre que le secteur de donn\u00e9es \u00e0 lire passe sous la t\u00eate. Cette op\u00e9ration prend en moyenne entre 2 et 6 millisecondes (un demi-tour du disque, \u00e0 une vitesse de rotation entre 5000 et 15000 tr\/min)<\/p>\n

Lorsque les secteurs \u00e0 lire sont contigus (c.-\u00e0-d. plac\u00e9s physiquement \u00e0 la suite les uns des autres), la t\u00eate de lecture n\u2019a plus \u00e0 se d\u00e9placer\u00a0; lire 100 secteurs contigus prend donc \u00e0 peine plus de temps que d\u2019en lire un seul (disons 5ms pour la t\u00eate+ 4ms pour le demi tour + 1ms pour la lecture des 100 secteurs=10ms contre 100x(6 + 4)=1000ms pour des secteurs non contigus).<\/p>\n

Si l\u2019objet est fragment\u00e9, les d\u00e9placements de la t\u00eate de lecture seront plus fr\u00e9quents, les temps de lecture seront donc (beaucoup) plus longs.<\/p>\n

En m\u00e9moire<\/em>
\nLorsqu\u2019un objet n\u2019est pas fragment\u00e9, ses pages sont pleines. L\u2019objet occupe donc moins de place que s\u2019il \u00e9tait fragment\u00e9. Un objet fragment\u00e9 occupera donc plus de place en m\u00e9moire qu\u2019un objet non fragment\u00e9, au d\u00e9triment des autres objets.<\/p>\n

Pour l’Optimizer<\/em>
\nLorsque SQL Server calcule le co\u00fbt d’une op\u00e9ration, il distingue une lecture s\u00e9quentielle (consid\u00e9r\u00e9e peu co\u00fbteuse) et une lecture al\u00e9atoire (consid\u00e9r\u00e9e tr\u00e8s co\u00fbteuse). Si un objet est fortement fragment\u00e9, les lectures s\u00e9quentielles deviennent plus co\u00fbteuses que ce qu’a estim\u00e9 SQL Server, le plan d’ex\u00e9cution choisi n’est donc potentiellement plus optimal.<\/p>\n

Mesurer la fragmentation<\/strong><\/p>\n

Depuis la version 2005 de SQL Server, le taux de fragmentation est directement consultable via une vue dynamique de gestion.<\/p>\n

\r\nselect object_name(I.object_id) 'Table', I.name 'Indexe', IPS.avg_fragmentation_in_percent 'Taux de Fragmentation'\r\n\r\nFROM sys.dm_db_index_physical_stats(DB_ID(),DEFAULT, DEFAULT, DEFAULT,DEFAULT) IPS\r\n\r\ninner join sys.indexes I ON I.object_id = IPS.object_id AND I.index_id = IPS.index_id\r\n\r\nwhere IPS.index_level=0\r\n\r\nand IPS.index_id !=0\r\n\r\norder by 1,IPS.index_id,2\r\n<\/pre>\n
Comment d\u00e9fragmenter\u00a0?<\/strong><\/p>\n
D\u00e9fragmenter un objet consiste \u00e0 le compacter et \u00e0 d\u00e9placer ses pages de mani\u00e8re \u00e0 ce qu\u2019elles soient contig\u00fces. L\u2019action de d\u00e9fragmenter est la r\u00e9organisation, elle est g\u00e9r\u00e9e dans SQL Server par deux ordres\u00a0:<\/p>\n
\r\nALTER INDEX REBUILD \r\n--et \r\nALTER INDEX REORGANIZE\r\n<\/pre>\n
Lorsqu\u2019une table ne contient pas d\u2019indexe clusteris\u00e9, il faut soit l\u2019exporter, la vider puis la r\u00e9importer (attention aux contraintes r\u00e9f\u00e9rentielles qui pourraient emp\u00eacher ces op\u00e9rations), soit cr\u00e9er un indexe clusteris\u00e9 puis le supprimer.<\/p>\n
Lorsqu\u2019elle en contient un, la m\u00e9thode de d\u00e9fragmentation d\u00e9pendra du taux de fragmentation observ\u00e9.<\/p>\n
(les valeurs ci-dessous sont relativement empiriques)<\/p>\n
Taux <5%\u00a0Pas de d\u00e9fragmentation. La consommation des ressources li\u00e9e \u00e0 la d\u00e9fragmentation serait a priori sup\u00e9rieure au gain en performance r\u00e9sultant.<\/p>\n
Taux compris entre 5 et 25% ALTER INDEX <nom de l’indexe> ON <nom de la table> REORGANIZE . La d\u00e9fragmentation s\u2019op\u00e9rera uniquement au niveau des feuilles de l\u2019indexe\/la table, mais sera relativement peu consommatrice en ressources.<\/p>\n
Taux sup\u00e9rieur \u00e0 25% ALTER INDEX <nom de l’indexe> ON <nom de la table> REBUILD.<\/p>\n
Cette m\u00e9thode va reconstruire int\u00e9gralement l\u2019indexe\/la table, op\u00e9ration consommatrice mais aboutissant \u00e0 une table compl\u00e8tement d\u00e9fragment\u00e9e.<\/p>\n
A noter que\u00a0:<\/p>\n