Index Rebuild dauert plötzlich sehr viel länger

Question

Hallo,

ich habe auf einem SQL Server 2012 einen Wartungsplan, der jede Nacht die Indizes mehrerer Datenbanken neu erstellt (Index Rebuild).
Bisher lief das immer in ca. 15 Minuten durch und nun benötigt der Vorgang plötzlich drei Stunden, obwohl nicht mehr Indizes noch wesentlich mehr Daten hinzugekommen sind.
Ich habe bereits versucht, herauszubekommen, wann welcher Index neu erstellt wurde. Da es aber sehr viele gibt, und es wohl bisher keine präzise Methode gibt, diesen Zeitpunkt zu ermitteln (dazu noch über mehrere Datenbanken hinweg), konnte ich noch nicht feststellen, welcher Index evtl. das Problem ausgelöst hat. Die Zeitpunkte, die mit den Methoden aus diversen Posts ermittelt wurden, liegen bisher alle nach dem Zeitpunkt, zu dem lt. Wartungsplanprotokollfile der Wartungsplan bereits fertig war.

Nun meine Fragen:
a) Gibt es eine zuverlässige Abfragemöglichkeit um den Zeitpunkt für den letzten Rebuild für mehrere Indizes präzise zu bestimmen?
b) Wie kann ich sonst nach der Ursache für die längere Dauer forschen?

Vielen Dank schon vorab für eure Hilfe.

Gruß
Alex

Answer 1

Hallo Alex,

Du kannst es (bedingt) im nachhinein kontrollieren. Ein Index Rebuild ist ein DDL Befehl (ein ALTER) und die werden vom Standard Trace (sofern nicht verändert) mitprotokolliert.

In SSMS ein Rechte Maus Klick auf die Datenbank => Standardberichte => Schemaänderungsverlauf; hier sollten die ALTER der Indizes auftauchen. Per Rechte Maus auf den Report kannst Du diesen auch nach Excel exportieren.

Natürlich kann man den Standard-Trace auch per SQL Abfragen und enstprechend filtern, Beispiele dazu solltest Du im Netz finden. Oder lass den SQL Profiler laufen, wenn Du den Bericht abrufst, da siehst Du dann das verwendet SQL Statement.

---

Olaf Helper

Blog Xing

Answer 2

Hallo Olaf,

Vielen Dank für den Tipp.
Tatsächlich habe ich mit Hilfe von SSC folgendes Skript zusammengebastelt, das mir zeigen sollte, wo die Zeit verloren ging:

DECLARE @trace nvarchar(100)
DECLARE @datetimefrom datetime
DECLARE @datetimeto datetime
SET @datetimefrom=CONVERT(datetime, '20121108 04:57:26');
SET @datetimeto=CONVERT(datetime, '20121108 07:46:28');
SELECT @trace=CONVERT(nvarchar(100),value) FROM ::fn_trace_getinfo(0) WHERE traceid=1 AND property=2
OPTION(RECOMPILE);
PRINT @trace
SELECT 
t.StartTime
,t.DatabaseID
,t.DatabaseName
,t.ObjectID
,t.ObjectName
,t.EventClass
,e.name AS EventName
,t.EventSubClass --0=begin,1=commit,
,e.category_id
,cat.name AS [CategoryName]
,t.TextData
FROM ::fn_trace_gettable(@trace,0) AS t
INNER JOIN sys.trace_events AS e ON e.trace_event_id = t.EventClass
INNER JOIN sys.trace_categories AS cat ON e.category_id = cat.category_id
WHERE t.StartTime BETWEEN @datetimefrom AND @datetimeto
AND e.category_id = 5 --category 5 is objects
AND e.trace_event_id = 164 --trace_event_id: 46=Create Obj,47=Drop Obj,164=Alter ObjAND e.name='Object:Altered'

Jetzt muss ich mir nur noch die TabellenNamen irgendwie dazufummeln, damit's noch ein bissel einfacher auszuwerten wird.

Answer 3

Hallo Alex,

den Tabellennamen dazu zu bekommen, ist nicht schwer, die ObjektID wird ja bereits selektiert. Das setzt aber voraus, dass das Script auf der enstprechenden Datenbank ausgeführt wird; deswegen habe ich den Filter auf DB_ID hinzugefügt; die Änderungen sind in fett markiert:

SELECT 
	t.StartTime
	,t.DatabaseID
	,t.DatabaseName
	,t.ObjectID
	,t.ObjectName
	,OBJ.name AS TableName
	,t.EventClass
	,e.name AS EventName
	,t.EventSubClass --0=begin,1=commit,
	,e.category_id
	,cat.name AS [CategoryName]
	,t.TextData
FROM ::fn_trace_gettable(@trace,0) AS t
	INNER JOIN sys.trace_events AS e ON e.trace_event_id = t.EventClass
	INNER JOIN sys.trace_categories AS cat ON e.category_id = cat.category_id
	INNER JOIN sys.objects AS OBJ
	    ON t.ObjectID = OBJ.object_id
WHERE t.StartTime BETWEEN @datetimefrom AND @datetimeto
	AND e.category_id = 5 --category 5 is objects
	AND e.trace_event_id = 164 --trace_event_id: 46=Create Obj,47=Drop Obj,164=Alter ObjAND e.name='Object:Altered'
	AND t.DatabaseID = DB_ID();

---

Olaf Helper

Blog Xing

Answer 4

Hallo Olaf,

super, danke!

Gruß Alex