Rebuilding Indexes

Question

Hallo,

folgendes Script führe ich aus:

USE AdventureWorksLT
GO

Print 'Selecting Index Fragmentation in the database.'

SELECT 
  DB_NAME(DPS.DATABASE_ID) AS [DatabaseName]
 ,OBJECT_NAME(DPS.OBJECT_ID) AS TableName
 ,SI.NAME AS IndexName
 ,DPS.INDEX_TYPE_DESC AS IndexType
 ,DPS.AVG_FRAGMENTATION_IN_PERCENT AS AvgPageFragmentation
 ,DPS.PAGE_COUNT AS PageCounts
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, NULL) DPS --N'LIMITED') DPS
INNER JOIN sysindexes SI 
    ON DPS.OBJECT_ID = SI.ID 
    AND DPS.INDEX_ID = SI.INDID
ORDER BY DPS.avg_fragmentation_in_percent DESC
GO

Print 'Rebuilding indexes on every table in the database.'
EXEC sp_MSforeachtable @command1="print 'Rebuilding indexes for ?' ALTER INDEX ALL ON ? REBUILD WITH (FILLFACTOR = 80)"
GO

Print 'Reorganizing indexes on every table in the database.'
EXEC sp_MSforeachtable @command1="print 'Reorganizing indexes for ?' ALTER INDEX ALL ON ? REORGANIZE"
GO

Dabei läuft das Script auf folgenden Fehler beim Rebuilding:

Meldung 1934, Ebene 16, Status 1, Zeile 1
Fehler bei ALTER INDEX, da die folgenden SET-Optionen falsche Einstellungen aufweisen: 'QUOTED_IDENTIFIER'. Überprüfen Sie, ob die SET-Optionen für die Verwendung mit indizierte Sichten und/oder Indizes für berechnete Spalten und/oder gefilterte Indizes und/oder Abfragebenachrichtigungen und/oder XML-Datentypmethoden und/oder Vorgänge für räumliche Indizes richtig sind.

Frage:
Wo liegt der Fehler und wie geht es richtig?

Danke.

 

Answer 1

Hallo Joerg_x,

die Fehlermeldung sagt Dir doch bereits worin das Problem besteht. Ändere einfach Deine Rebuild-Anweisung in:

Print 'Rebuilding indexes on every table in the database.'
EXEC sp_msforeachtable @command1="print 'Rebuilding indexes for ?' SET QUOTED_IDENTIFIER ON ALTER INDEX ALL ON ? REBUILD WITH (FILLFACTOR = 80)"
GO

Somit sollte es funktionieren. Falls Du noch nähere Informationen zu QUOTED_IDENTIFIER benötigst.

---

Gruß Falk

Answer 2

Hallo Jörg,

siehe auch MSDN ALTER INDEX => Argumente => ALL, dort ist eine Tabelle aufgeführt, wann bei der Verwendung der ALL Option welche Fehler auftreten können.

Übrigens, ein Index Rebuild und einen Reorg nacheinander auszuführen, kann man machen, ist aber nicht gerade sinnvoll. Entweder das eine oder das andere, aber beides zusammen ist überflüssig.

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 3

Hallo Falk,

Mit Deiner Anpassung funktioniert es. Mein Fehler war, dass ich

SET QUOTED_IDENTIFIER ON

vor die Anweisung geschrieben hatte.

Danke.

Answer 4

Hallo Olaf,

Du hast Recht. Ich habe inzwischen auf:

http://msdn.microsoft.com/en-us/library/ms188917.aspx

im Abschnitt D unter Examples eine Lösung gefunden, die ich gesucht habe, und genau das erledigt.

Nun habe ich noch eine abschließende Frage:

Vor und nach Durchführung von:

SELECT Database_id, avg_fragmentation_in_percent, avg_page_space_used_in_percent 
FROM sys.dm_db_index_physical_stats(DB_ID('AdventureWorksLT'), NULL, NULL, NULL , NULL);

aus:
http://blogs.msdn.com/b/jorgepc/archive/2007/12/09/how-to-check-fragmentation-on-sql-server-2005.aspx

sehe ich keine Unterschiede. Ich hatte nun erwartet, bei der

avg_fragmentation_in_percent

einen geringeren Wert nach der Durchführung zu sehen. Wo liegt mein Denkfehler?

Danke noch einmal.

Answer 5

http://msdn.microsoft.com/en-us/library/ms188917.aspx

im Abschnitt D unter Examples eine Lösung gefunden, die ich gesucht habe, und genau das erledigt.

Nun habe ich noch eine abschließende Frage:

Vor und nach Durchführung von:

SELECT Database_id, avg_fragmentation_in_percent, avg_page_space_used_in_percent 
FROM sys.dm_db_index_physical_stats(DB_ID('AdventureWorksLT'), NULL, NULL, NULL , NULL);

aus:
http://blogs.msdn.com/b/jorgepc/archive/2007/12/09/how-to-check-fragmentation-on-sql-server-2005.aspx

sehe ich keine Unterschiede. Ich hatte nun erwartet, bei der

avg_fragmentation_in_percent

einen geringeren Wert nach der Durchführung zu sehen. Wo liegt mein Denkfehler?

 

Ich antworte mir mal selbst :-)

Vermutlich ist das die Erklärung:

http://msdn.microsoft.com/en-us/library/ms189858.aspx

In general, fragmentation on small indexes is often not controllable. The pages of small indexes are stored on mixed extents. Mixed extents are shared by up to eight objects, so the fragmentation in a small index might not be reduced after reorganizing or rebuilding the index.

Answer 6

sehe ich keine Unterschiede. Ich hatte nun erwartet, bei der

avg_fragmentation_in_percent

einen geringeren Wert nach der Durchführung zu sehen. Wo liegt mein Denkfehler?

Hallo Jörg,

Siehe Alter Index => Neuerstellen von Indizes => Hinweis, dort steht es erläutert. Bei kleinen Tabellen / Indizes werden diese zusammen in "gemischten Blöcken" gespeichert und da ist der Fragmentationsgrad immer hoch; und die AdventureWorksLT ist nun mal eine kleine Datenbank mit wenig Daten.

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing