Infos über Indizes

[Bernhard Geyer]

1, Wie bekomme ich auf einfache Art und Weise heraus wieviel Speicher ein Index benötigt.

In der Tabelleninformationsansicht bekomme ich nur den Wert für alle Indexs. Bei Zuhilfename des Indexoptimierungs-Assistenten (SQL Profiler/Extras) bekomme ich zwar die Info, jedoch benötigt man einige Vorarbeiten

2, In der Tabelleninformationsansicht werden Indexes aufgelistet, welche über "Tabelle auwählen, Popup-Menu, Alle Tasks/Indexes verwalten..." nicht aufgelistet werden. Woher kommen diese (Vermutung: Indexoptimierungs-Assistenten) und wie kann ich sehen wie diese Aufgebaut sind bzw. wie kann ich sie löschen?

Danke für jeden Tipp

Bernhard Geyer

[Andreas Kosch]

Hallo Bernhard,

beim MS SQL Server 7 oder 2000 muss man zwischen einem echten Index und einer Spaltenstatistik unterscheiden. Immer dann, wenn der Optimizer (<b>nicht</b> der Indexoptimierungs-Assistent) eine Abfrage bewerten muss, aber für eine relevante Tabellenspalte kein echter Index vorhanden ist, legt er für die Berechnung der Güte (Anzahl der eindeutigen Werte im Verhältnis zur Zeilenanzahl) eine so genannte Spaltenstatistik an (diese beginnen mit dem Präfix _WA_Sys). Über die <b>IsAutoStatistics</b>-Eigenschaft der <b> INDEXPROPERTY</b>-Funktion kann man dies nachvollziehen:

<pre>
SELECT
object_name(id) TableName, count(*) NumberOfAutoStats
FROM
sysindexes
WHERE
OBJECTPROPERTY(id, N'IsUserTable') = 1
AND INDEXPROPERTY (id,name,'IsAutoStatistics') = 1
GROUP BY
object_name(id)
ORDER BY
count(*) DESC
</pre>

&gt;..wie kann ich sie löschen?

Das wäre keine gute Idee, denn in den meisten Fällen ist allein die Existenz einer Spaltenstatistik ein Wink mit dem Zaunspfahl, dass ein echter Index sinnvoll ist.

P.S: In der Regel macht der Indexoptimierungs-Assistenten seinen Job perfekt, so dass wir die ganzen Details "vergessen" dürfen

[Bernhard Geyer]

Hallo Andreas,

erstmal danke für die Infos.

Das Problem das ich mit den Indexen habe ist, das sie schon einen gewaltigen Umfang erreichen.

Die Datenbank-Tabelle hat schon einen Umfang von ca. 800 MB und die (2 eigenen + 1 von MS-SQL-Server angelegten) Indizes belegen schon ca. 1,7 GByte. Dies führt dazu das Insert-Operationen sehr langsam durchgeführt werden (es müssen ja alle 3 Indizes aktuallisiert werden und Server hat AFAIK "nur" 1GB RAM).

Werde in den nächsten Tagen die Kundendatenbank als Backup bekommen, um mit dieser Datengröße eigene Optimierungsuntersuchungen durchführen zu können

[Bernhard Geyer]

Hallo Andreas,

hab jetzt die Datenbank des Kunden und das Indexproblem ist noch größer. Es sind 4 Indize vom Optimizer angelegt worden (Jeweils 1 Spalten-Index). Damit werden von den Indizes für diese Datenbank ca. 4 GByte benötigt. Auch müssen damit 6 Indizes (2 Eigene + 4 Optimizer-Indizes bei jeden Update/Insert/Delete aktualisiert werden).

Will ich nun einen Index ergänzen (welcher ähnlich eines der schon vorhanden Optimizer-Indizes ist), wird dieser ja nicht mehr benötigt. Wie kann ich diesen nun löschen?

Auch will ich die anderen 3 Optimizer-Indizes löschen (hab bisher aber keine Lösung gefunden), da diese evtl. nur all Monate ein Mal von Nutzen sind, aber bei 100000 Inserts/Updates nun Performance kosten. Der Optimizer kann diese Indizes ja temporär erzeugen, soll sie aber wieder löschen wenn Abfrage fertig ausgeführt ist.

Ich hoffe Du kannst mir noch mal ein paar Tipps geben

[Andreas Kosch]

Hallo Bernhard,

in der Tat ist jeder aktive Index das Ergebnis einer Abwägung zwischen Aufwand und Nutzen. Wenn ein Index eine SELECT-Abfrage beschleunigen soll, muss man zwangsläufig mit den Nebenwirkungen leben.

Die Zahl von 6 Indizes für eine Tabelle hört sich gar nicht so schlecht an. Wenn die Festplatte ausreichend groß und schnell ist (ich bevorzuge jeweils ein RAID0-Array von mindestens 2 Platten je Daten- und Log-Partition), sollte es auch bei dieser Datenbankgröße keine Probleme geben.

Ich glaube nicht, dass die Spaltenstatistiken für die Indexgröße von 4 GB verantwortlich sind, denn diese "virtuellen" Indizes belegen keinen Festplatten-Platz für die betroffenen Zeilen. Das folgende Beispiel demonstriert dies - ich kopiere dazu die Orders-Tabelle aus Northwind nach tempdb:

<pre><font color="#008080">-- Beispieltabellen in TEMPDB aufbauen</font>
<b>USE</b> tempdb
GO
<b>SELECT</b> * <b>INTO</b> tempdb.dbo.orders <b>FROM</b> northwind.dbo.orders
GO
<b>SELECT</b> * <b>FROM</b> orders <b>WHERE</b> orderid = 10248
GO

<font color="#008080">-- Prüfen, welche Indizes aktiv sind</font>
<b>SELECT</b> name, used, rowcnt <b>FROM</b> sysindexes <b>WHERE</b> id = object_id(<font color="#FF00FF">'orders'</font>)
<b>GO</b>
<font color="#008080">
</pre>
<pre>
-- Ergebnis:
-- name used rowcnt
-- ------------------------- ------- --------------------
-- orders 22 830
-- _WA_Sys_OrderID_75D7831F 0 0</font>
</pre>
<pre>
<b>CREATE</b> <b>INDEX</b> orderOrderID <b>ON</b> orders(CustomerID)
GO

<font color="#008080">-- Prüfen, welche Indizes aktiv sind</font>
<b>SELECT</b> name, used, rowcnt <b>FROM</b> sysindexes <b>WHERE</b> id = object_id(<font color="#FF00FF">'orders'</font>)
GO
</pre>
<pre>
<font color="#008080">-- Ergebnis:
-- name used rowcnt
-- ------------------------- ------- ---------
-- orders 22 830
-- _WA_Sys_OrderID_75D7831F 0 0
-- orderOrderID 5 830</font>
</pre>

Sowohl für <i>used</i> als auch für <i>rowcount</i> wird bei den Spaltenstatistiken der Wert 0 zurückgeliefert - während ein realer Index dort entsprechenden Platz belegt.

Wenn die umfangreichen INSERT-/UPDATE-Aufrufe in einer Mehrbenutzerumgebung bei gleichzeigen SELECTs von anderen Users erfolgen und es zu "Verzögerungen" kommt, würde ich mir das Sperrverhalten einmal ansehen. An dieser Stelle helfen oftmals die Hints (WITH...) weiter.

Welche Spalte wurde als CLUSTERED INDEX definiert? Bei dieser Indexart sortiert der SQL Server die Zeilen nach dem Karteikastenprinzip ein, so dass kein zusätzlicher Platz für einen Index benötigt wird. In der Voreinstellung nutzt der MS SQL Server die Primärschlüsselspalte - aber dass muss nicht immer der Idealfall sein