avg_fragmentation_in_percent trotz rebuild und reorganize

[werner008 10]

moin,

ein frohes neues jahr euch allen als erste einmal.

 

ich hätte eine frage an die sql erfahrenen unter euch. ich habe eine ca 1gb große datenbank mit ca 16.000 tabellen (nav sei dank...) und habe die indexfragmentierung mit

 

SELECT
DB_NAME(IXStats.database_id) AS DatabaseName,
OBJECT_NAME (IXStats.[object_id]) AS TabellenName,
SIX.[Name] AS IndexName,
IXStats.avg_fragmentation_in_percent,
IXStats.index_type_desc
FROM
sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) IXStats
JOIN
sys.indexes SIX ON IXStats.[object_id] = SIX.[object_id] AND IXStats.index_id = SIX.index_id
WHERE
IXStats.database_id = DB_ID()
ORDER BY
IXStats.avg_fragmentation_in_percent DESC

 

bzw.

 

SELECT 
   DB_NAME(DB_ID()) AS DatabaseName,
   O.[name] AS TabellenName,
   SIX.[Name] AS IndexName,
   IXStats.avg_fragmentation_in_percent,
   SIX.type_desc AS Indextyp
FROM 
   sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) IXStats
   JOIN 
   sys.indexes SIX ON IXStats.[object_id] = SIX.[object_id] AND IXStats.index_id = SIX.index_id
   JOIN
   sys.objects O ON SIX.[object_id] = O.[object_id]
WHERE 
   IXStats.database_id = DB_ID()
ORDER BY 
   IXStats.avg_fragmentation_in_percent DESC

 

überprüft. anfangs hatte ich hunderte oder tausende tabellen mit werten über 80 oder 90% indexfragmentierung (avg_fragmentation_in_percent).

 

ich habe dann einen rebuild index wartungsauftrag für die DB erstellt und habe jetzt immernoch hunderte tabellen mit werten von 87,5% indexfragmentierung und tausende mit mehr als 50%. auch wenn es mir sinnlos erschien, habe ich noch einmal reorganize index drüber gefahren, gleiches ergebnis. ein zweites rebuild änderte nichts an der situation.

 

nun meine fragen:

- warum sinkt meine indexfragmentierung nicht?

- rein interessehalber und nicht so wichtig: der index rebuild dauert ca. 45min und braucht 100% cpu auf einem kern. ist das so korrekt? ich hätte mehr I/O erwartet, sehe am am SAN nur ca 50-100 I/Os pro sekunde mit ausnahme der endphase wo es auf ca 1000-1500 I/Os ansteigt.

 

danke

werner

[werner008 10]

niemand eine idee? nach einem rebuild dürfte es doch keine fragmentierung geben!?

 

danke

werner

[phoenixcp 10]

nach einem rebuild dürfte es doch keine fragmentierung geben!?

Ach, und warum nicht?

 

Das mit der Fragmentierung ist unter SQL immer so eine Sache: Wo liegen denn deine DB-Files? Lokal auf Platten auf dem Server? Im SAN?

 

Zum Thema Fragementierung und SAN hat Lichi Shea einen netten 6-teiligen Artikel geschrieben:

Part 1: Linchi Shea : Performance Impact: file fragmentation and SAN -- Part I

Part 2: Linchi Shea : Performance impact: file fragmentation and SAN ? Part II

Part 3: Linchi Shea : Performance Impact: file fragmentation and SAN ? Part III

Part 4: Linchi Shea : Performance impact: file fragmentation and SAN ? Part IV

Part 5: Linchi Shea : Performance impact: file fragmentation and SAN ? Part V

Part 6: Linchi Shea : Performance impact: file fragmentation and SAN - Part VI

 

warum sinkt meine indexfragmentierung nicht?

wird durch die reindizierung eine defragmentierung der Indizes durchgeführt oder lediglich ein Neuaufbau der Indizes?

[werner008 10]

moin,

danke für die links. werde morgen hoffentlich zeit finden, sie im detail zu lesen, kurzes überfliegen scheint interessant.

 

Ach, und warum nicht?

 

weil ich angenommen hätte, dass wenn man etwas komplett neu aufbaut, dass es dann nicht fragmentiert ist. nachdem ich rebuild und reorganize gemacht habe, hätte ich erwartet, dass nix mehr fragmentiert ist - anscheindend ein trugschluss.

 

reorganize sollte den index defragmentieren und rebuild externe fragmentierung (also in der dateistruktur) beheben, oder nicht? (zumindest verstehe ich das 70-431 buch so). wobei ich den fill factor beim reorganize nicht verstanden hab.

 

 

zu der frage

Wo liegen denn deine DB-Files?

 

sie liegen auf einer lokalen platte. diese lokale platte befindet sich im allerdings SAN, da der sql server virtualisiert ist.

 

 

danke und schönen abend

werner

[phoenixcp 10]

Ok, dann versuchen wir der Sache mal etwas strukturierter auf den Grund zu gehen. Dazu benötige ich allerdings noch die folgenden Informationen von dir:

1. Von welcher SQL Server Version mit welchem Patchlevel sprechen wir?

das kannst du per

select @@version[/Code]

herausfinden.

2. Mit welchen konkreten Statements führst du das Rebuild bzw. Reorganize aus?

 

 

 

Für SQL Server 2005 habe ich grade mal die Books Online gewälzt:

Indexfragementierung <= 30 % Alter Index REORGANIZE

Indexfragmentierung > 30 % Alter Index REBUILD With (Online = ON)

 

Der Name des Artikels in den Books Online, falls von Interesse: Reorganizing and Rebuilding Indexes

 

Gleichfalls schönen Abend von einem leicht genervten Carsten, der grade auf chkdsk /F /R seiner externen Festplatte mit allen relevanten Projektdaten wartet :mad:

[werner008 10]

moin,

select @@version gibt

 

Microsoft SQL Server 2005 - 9.00.3294.00 (X64) Oct 3 2008 00:51:23 Copyright 1988-2005 MS Standard Edition (64-bit) on Windows NT 5.2 (Build 37990: Service Pack 2)

 

(ja ich weiß, letze securityupdates bzw. sp3 ist noch nicht drauf...)

 

ich habe je einen maintanance plan: "rebuild index task" und "reorganize index task". das sql statement kann ich leider nicht posten, da er beim "view t-sql" ewig nichts anzeigt (schätze das liegt an den 19.000 tabellen) .

 

die tasks sind beide auf die betroffene datenbank und "tables and views" eingestellt.

 

rebuild index: "reorganize with the default amount of free space" und "sort in tempdb" (online mach ich nicht, weil es eh derweil kein produktivsystem ist, dass verfügbar sein muss)

 

reorganize index: der haken bei "compact lage objects" ist gesetzt (standard, hab da nix geändert)

 

 

das mit der 30% stand auch so in dem schulungshandbuch drin. ich hab einfach beides ausgeführt, weil das ergebnis nicht wie gewünscht war.

 

gruß

werner

[phoenixcp 10]

ja ich weiß, letze securityupdates bzw. sp3 ist noch nicht drauf...

deswegen muss sich keiner schämen, meiner hier beim Kunden läuft auf 9.00.3215, weil seitens der Produktentwicklung noch kein höheres Release freigegeben wurde.

 

Es gibt je nach Release immer wieder "Schwankungen" gerade was die Rebuild / Reorganize und auch die Shrink-Geschichten angeht. U.U. hast du aktuell so ein "schwammiges" SRP installiert. Man müsste mal die Fix-Liste von den folgenden intensiv auseinandernehmen und schauen, ob da nochmal was an dem Thema Fragementierung / defragmentierung gemacht wurde.

 

Etwas weiteres über das ich grade gestolpert bin und dessen Auswirkung ich mir nicht wirklich im klaren bin:

sie liegen auf einer lokalen platte. diese lokale platte befindet sich im allerdings SAN, da der sql server virtualisiert ist.

Ich arbeite nur äusserst ungern und wenn überhaupt für Testzwecke mit virtualisierten SQL Servern. Von daher kann ich dir für dein Konstrukt leider keine Einschätzung geben ob die Virtualisierung zusätzlich zur weiteren Fragmentierung beiträgt.

 

Evtl. noch ein Denkansatz: Datenbank-, Log- und Systempartition sind getrennte Platten?

[werner008 10]

moin,

ja die system, log und db daten sind je auf eigener partition und eigenem vmdk. ich werds dann jetzt dabei belassen. ich denke, dass es bei der größe der datenbank nicht wirklich eine rolle spielt ob die indices fragmentiert sind oder nicht. es hätte mich einfach interessiert.

 

was mich halt gewundert hat, ist der sicher nicht zufällige wert von 87,5 % fragmentation. die zahl klingt irgendwie komisch um zufällig zu sein.

 

danke jedenfalls für deine unterstützung

werner

[phoenixcp 10]

was mich halt gewundert hat, ist der sicher nicht zufällige wert von 87,5 % fragmentation. die zahl klingt irgendwie komisch um zufällig zu sein.

 

Rein aus Interesse: Was an dieser Zahl ist so komisch, als das es kein Zufall sein kann?