svenguenter
Goto Top

Löschen eines Indexes in einer MSSQL Datenabnk ist nicht mehr möglich

Hallo Zusammen,

ich betreue seit neuestem einen MSSQL 2012 Server. Nach eingehender Prüfung des Servers habe ich festegellt das hier die INdexe nie gepflegt wurden. Ich habe mir ein Script gebaut welches alle Indexe mit einer Fragmentierung zwischen 5 und 20% Neu Reorganisiert und alle die über 20 % liegen neu baut. ( Fragmentierungsgrad lag teilweise bei 99,8% und mehr )

Es bleibt aber nun ein Bodensatz über. Diesen kann ich teilweise nicht mal mehr löschen ( drop Index ) und wenn ich den gelöscht bekomme und neu anlege ist er direkt wieder massiv fragmentiert.

Ich habe einmal über das Studio gearbeitet und mit folgenden Scripten

USE xxx;
GO

EXEC sp_updatestats;

GO

DECLARE @DBID int
SELECT @DBID = DB_ID() 
DBCC FLUSHPROCINDB (@DBID)




USE xxx;
GO

truncate table  DBADMIN.DBO.indexreorg;
truncate table DBADMIN.DBO.index_reorg_cmd;
 



 insert into DBADMIN.DBO.indexreorg select
        OBJECT_NAME(ps.object_id) AS Tab,
        i.name AS Idx,
       ps.avg_fragmentation_in_percent AS Required 
    FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'limited') AS ps   
    INNER JOIN sys.indexes AS i ON i.index_id = ps.index_id AND i.object_id = ps.object_id
    WHERE  (OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1);  

insert into DBADMIN.DBO.index_reorg_cmd SELECT 'alter index ' + QUOTENAME(Idx) + ' on ' + 'xxx.DBO.' + QUOTENAME(Tab) + ' ' + ' REORGANIZE '  as befehl  
FROM DBADMIN.DBO.IndexReorg 
WHERE Required > 5 and Required < 31 AND (Tab LIKE 'T_%');  


insert into DBADMIN.DBO.index_reorg_cmd 
select 'alter index ' + QUOTENAME(Idx) + ' on ' + 'xxx.DBO.' + QUOTENAME(Tab) + ' ' + ' REBUILD '   
from DBADMIN.DBO.IndexReorg where Required > 30 AND (Tab LIKE 'T_%');   

DECLARE @cmd nvarchar(max);
DECLARE index_remake CURSOR FOR
select * from DBADMIN.DBO.index_reorg_cmd
OPEN index_remake
fetch next from index_remake into @cmd;

while @@FETCH_STATUS = 0

	begin
	print @cmd;
	exec(@cmd);
	SET @cmd = '';  
	fetch next from index_remake into @cmd;
	end
	

close index_remake;
deallocate index_remake;

Eine echte Replikation gibt es noch nciht. Aktuell habe ich nur noch die Idee eine Datenreplikation aufzubauen und die Indexe dann auf der Abonnentenseite neu anzulegen. Hat einer von euhc noch eine Idee?

Danke und Gruss

Sven

Content-ID: 347718

Url: https://administrator.de/contentid/347718

Ausgedruckt am: 16.11.2024 um 23:11 Uhr

MadMax
Lösung MadMax 30.08.2017 um 12:53:03 Uhr
Goto Top
Hallo Sven,

das wird Dir auch nichts nützen. Die Lösung bzw. die Information, daß es nicht geht, findest Du in der SQL Server Hilfe bei "ALTER INDEX":
Hinweis:
Das erneute Erstellen oder Reorganisieren von kleinen Indizes verringert oft nicht die Fragmentierung. Die Seiten kleiner Indizes werden in gemischten Blöcken gespeichert. Gemischte Blöcke sind für bis zu acht Objekte freigegeben, sodass die Fragmentierung in einem kleinen Index durch die Reorganisation oder das erneute Erstellen des Index möglicherweise nicht verringert wird. Weitere Informationen zu gemischten Blöcken finden Sie unter Grundlegendes zu Seiten und Blöcken.


Gruß, Mad Max
SvenGuenter
SvenGuenter 30.08.2017 um 13:03:03 Uhr
Goto Top
Hallo MadMax,

erst mal Danke.
Das mit den kleinen Indexen ist mir bekannt. Wir reden hier aber von Indexen auf Tabellen mit > 1 mio Datensätze.
Weiterhin sollte es doch möglich sein Indexe zu löschen. Aber auch dies ist nicht möglich. Also nicht bei allen sondern nur noch bei 13 von 178.

Gruss

Sven
AndreasHoster
Lösung AndreasHoster 30.08.2017 aktualisiert um 13:05:54 Uhr
Goto Top
Diesen kann ich teilweise nicht mal mehr löschen ( drop Index )
Was kommt den als Fehler?

Ein weiterer Punkt ist die Große der Indizes, sind die Indizes eventuell sehr sehr klein, bei denen sich der Fragmentierungsgrad nicht senken lässt?
Das scheint normal zu sein beim SQL Server: https://dba.stackexchange.com/questions/18372/why-index-rebuild-does-not ...

Zur Index Maintenance kann ich Ola Hallgrens SQL Maintenance Solution empfehlen: https://ola.hallengren.com/
Das sind (kostenlose) SQL Skripte zum Einplanen von Backups, Index/Statistik Updates etc.

Edit: Ich brauche einfach zu lange zum Tippen.
MadMax
Lösung MadMax 30.08.2017 um 13:34:11 Uhr
Goto Top
Hallo Sven,

daß sich ein Index nicht löschen läßt, kann daran liegen, daß er noch für einen Fremdschlüssel benötigt wird. Ansonsten gibt es natürlich eine Fehlermeldung, die näheres dazu sagen könnte.

Das mit der Fragmentierung bei größeren Indices macht mich jetzt neugierig, das prüfe ich mal bei uns.

Gruß, Mad Max
SvenGuenter
SvenGuenter 30.08.2017 um 13:55:49 Uhr
Goto Top
PK FK ist schonmal ein Teil der Lösung. Hier habe ich wirklich die Beziehungen zu anderen Tabellen. Was mich halt wundert ist das ich hier nicht droppen kann. Ich kann doch auch einen PK nachträglich erweitern. Aber trotzdem danke für diese Info. Nun muss ich nur noch herausfinden was mit einem Index los ist.

Wie gesagt über 1 mio Datensätze Fragmentiert zu 86,75 Prozent. Es ist ein reiner IDX also kein Pk. Ich kann ihn weder Reorganisieren oder Neu bauen und löschen geht auch nicht. Fehlermeldung hierzu ist leider nichts sagend da ich nur die Info bekomme das der MSSQL Server nicht in der Lage ist alle Inforamtionen zu sammeln. Kann es sein das die Tabellenstruktur korrupt ist? Gibt es hier einen dbcc checktable oder so was in der Richtung?


Gruss

Sven
SvenGuenter
SvenGuenter 31.08.2017 aktualisiert um 16:20:40 Uhr
Goto Top
Hallo Zusammen nach längerem suchen und fragen ist die Lösung mal wieder total einfach, Man muss den Rebuild Job mit demSchalter Online=ON ausführen.

also wie folgt:

insert into DBADMIN.DBO.index_reorg_cmd  
select 'alter index ' + QUOTENAME(Idx) + ' on ' + 'xxx.DBO.' + QUOTENAME(Tab) + ' ' + ' REBUILD  WITH (ONLINE = ON)'    
from DBADMIN.DBO.IndexReorg where Required > 30 AND (Tab LIKE 'T_%')