3
Erstellen von Indizes mit eingeschlossenen Spalten im MS-SQL 2012
Hallo,
ich habe eine Frage zum Thema "Indizes mit eingeschlossenen Spalten"
Microsoft schreibt hier:
Ein Index mit Nichtschlüsselspalten kann die Abfrageleistung erheblich steigern, wenn alle Spalten in der Abfrage in den Index als Schlüssel- oder Nichtschlüsselspalten eingeschlossen werden. Leistungsvorteile werden erzielt, weil der Abfrageoptimierer alle Spaltenwerte im Index finden kann; auf Daten der Tabelle oder des gruppierten Indexes wird nicht zugegriffen, sodass als Ergebnis weniger Datenträger-E/A-Vorgänge auftreten.
https://docs.microsoft.com/de-de/sql/relational-databases/indexes/create ...
Soweit verständlich.
Ich nutze ebenfalls den MS SQL Server 2012 Performance Dashboard Reports um mögliche fehlende Indexe zu finden (und dann selber zu entscheiden ob sie benötigt werden, bitte an dieser Stelle keine Grundsatzdiskussion wer besser ist, Mensch oder Maschine
)
https://www.microsoft.com/en-us/download/details.aspx?id=29063
Zwischenfrage:
Nutzt sonst jemand das Tool und wie beurteilt ihre die Qualität der Missing Index Vorschläge?
Frage:
In dem Report Missing Index werden die möglichen fehlenden Indexe angezeigt inkl. Erstellungscode.
Aber der Report schlägt mir oft ähnliche Indexe vor, vor allem bei Indizes mit eingeschlossenen Spalten:
z.B.
CREATE INDEX missing_index_28 ON [Tabellenname] ([Spalte01]) INCLUDE ([Spalte02], [Spalte03], [Spalte04])
ein weitere Index soll erstellt werden, der nur eine oder zwei Include Spalten mehr hat
CREATE INDEX missing_index_28 ON [Tabellenname] ([Spalte01]) INCLUDE ([Spalte02], [Spalte03], [Spalte04], [Spalte05], [Spalte06])
Reicht es dann ggf. nicht aus, dass man NUR den Index mit den MEISTEN Include Spalten anlegt, oder muss man generell besser mehr Indexe aber dafür exaktere Indexe anlegen.
Und wie verhält es sich wenn vor dem include mehrere Schlüsselspalte stehen?
Würde der untere Index nicht auch alle oberen Indexe abdecken?
z.B.
CREATE INDEX missing_index_28 ON [Tabellenname] ([Spalte01], [Spalte07], [Spalte08],) INCLUDE ([Spalte02], [Spalte03], [Spalte04], [Spalte05], [Spalte06])
Sind nicht zu viele Indexe für´s schreiben / ändern / löschen von Tabellenzeilen in eine Datenbank kontraproduktiv?
Kann man hierfür eine allgemeine Richtlinie / Aussage treffen?
Besten Dank,
Rene
ich habe eine Frage zum Thema "Indizes mit eingeschlossenen Spalten"
Microsoft schreibt hier:
Ein Index mit Nichtschlüsselspalten kann die Abfrageleistung erheblich steigern, wenn alle Spalten in der Abfrage in den Index als Schlüssel- oder Nichtschlüsselspalten eingeschlossen werden. Leistungsvorteile werden erzielt, weil der Abfrageoptimierer alle Spaltenwerte im Index finden kann; auf Daten der Tabelle oder des gruppierten Indexes wird nicht zugegriffen, sodass als Ergebnis weniger Datenträger-E/A-Vorgänge auftreten.
https://docs.microsoft.com/de-de/sql/relational-databases/indexes/create ...
Soweit verständlich.
Ich nutze ebenfalls den MS SQL Server 2012 Performance Dashboard Reports um mögliche fehlende Indexe zu finden (und dann selber zu entscheiden ob sie benötigt werden, bitte an dieser Stelle keine Grundsatzdiskussion wer besser ist, Mensch oder Maschine
)https://www.microsoft.com/en-us/download/details.aspx?id=29063
Zwischenfrage:
Nutzt sonst jemand das Tool und wie beurteilt ihre die Qualität der Missing Index Vorschläge?
Frage:
In dem Report Missing Index werden die möglichen fehlenden Indexe angezeigt inkl. Erstellungscode.
Aber der Report schlägt mir oft ähnliche Indexe vor, vor allem bei Indizes mit eingeschlossenen Spalten:
z.B.
CREATE INDEX missing_index_28 ON [Tabellenname] ([Spalte01]) INCLUDE ([Spalte02], [Spalte03], [Spalte04])
ein weitere Index soll erstellt werden, der nur eine oder zwei Include Spalten mehr hat
CREATE INDEX missing_index_28 ON [Tabellenname] ([Spalte01]) INCLUDE ([Spalte02], [Spalte03], [Spalte04], [Spalte05], [Spalte06])
Reicht es dann ggf. nicht aus, dass man NUR den Index mit den MEISTEN Include Spalten anlegt, oder muss man generell besser mehr Indexe aber dafür exaktere Indexe anlegen.
Und wie verhält es sich wenn vor dem include mehrere Schlüsselspalte stehen?
Würde der untere Index nicht auch alle oberen Indexe abdecken?
z.B.
CREATE INDEX missing_index_28 ON [Tabellenname] ([Spalte01], [Spalte07], [Spalte08],) INCLUDE ([Spalte02], [Spalte03], [Spalte04], [Spalte05], [Spalte06])
Sind nicht zu viele Indexe für´s schreiben / ändern / löschen von Tabellenzeilen in eine Datenbank kontraproduktiv?
Kann man hierfür eine allgemeine Richtlinie / Aussage treffen?
Besten Dank,
Rene
Share on Facebook
Share on Twitter
Share on Reddit
Share on LinkedinPlease also mark the comments that contributed to the solution of the article
Content-Key: 345688
Url: https://administrator.de/contentid/345688
Printed on: April 19, 2024 at 00:04 o'clock
3 Comments
Latest comment
Hi
Grundsätzlich sollte man wissen, was man hier macht.
Der Wichtigste Index ist immer erst mal der Clustered Index, weil dieser die Physikalische Speicherung auf dem Datenträger vorgibt.
Bei Tabellen, die viele Änderungen erfahren sollte dieser Index so angelegt sein, das der SQL Server nicht bei jeder Änderung irgendwas verschieben muss, weil er den Datensatz jetzt woanders hinschieben muss, oder weil ein neuer Datensatz dazwischen eingeschoben wurde.
Am besten hat man natürlich eine art ID, die sich mit jedem neuen Datensatz erhöht, nach dem Erstellen niemals geändert wird und die den ClusteredIndex spielt. Wichtig hierbei: Jeder non-clustered index beinhaltet IMMER ALLE im Clustered Index angegeben Spalten.
Zum Thema Include: Nimmt man den index hier:
CREATE INDEX missing_index_28 ON [Tabellenname] ([Spalte01]) INCLUDE ([Spalte02], [Spalte03], [Spalte04], [Spalte05], [Spalte06])
Heisst das auf Klartext: Mache einen Index, sortiert nach Spalte01, aber hänge hinter den Eintrag immer gleich die Werte von Spalte 02-06, damit du diese Daten gleich hast. Sinn dahinter: Der Index ist ein Verweis auf einen Datensatz. Findet der Index also einen Datensatz, der deiner Abfrage entspricht, schickt er die Abfrage an die Position des tatsächlichen Datensatzes. Das ist also eine Zusätzliche Operation, die auf dem SQL geschieht. Braucht die Abfrage aber hier nur die Werte von Spalte 02-06, dann sind die Daten schon da und können direkt übergeben werden. Keine weitere Dinge sind nötig.
Vorteil: Abfrage ist deutlich schneller
Nachteile: Braucht mehr Speicherplatz; Braucht mehr IO wenn ein Insert/Update auf der Tabelle erfolgt, weil entweder die Tabelle anhand des Clusters neu sortiert werden muss, oder weil der neue Datensatz am Feld noch zusätzlich an alle "Include-Indexe" übertragen werden muss.
Änderst du also an dem Datensatz was an "Spalte04", so muss diese neue Information an alle Indexe gegeben werden, die Spalte04 beinhalten.
Fazit: Solche Indexe sind da sinnvoll, wo VIEL gelesen wird, aber weniger geschrieben.
Ändert sich sehr viel an der Tabelle, muss der Clustered Index Weise gewählt werden und die NonClustered Indexe sollten möglichst wenig INCLUDEs haben, ausser es ist für die Performance bei den Queries ein Zugewinn.
Ist manchmal schwierig abzuwägen. idR muss man es mit einem ordentlichen Performancetest ausloten
Ich würde hier einen Schnitt über die Abfragen machen, und sehen, welche Spalte am häufigsten abgefragt werden. Diese kommen dann in den INCLUDE Part. Was nur hier und da mal abgefragt wird, nicht. Grundsätzlich gilt beim SQL: Viel hilft nicht viel. Weniger ist mehr, wenn es das richtige ist
Zwischenfrage:
Nutzt sonst jemand das Tool und wie beurteilt ihre die Qualität der Missing Index Vorschläge?
Das tool bietet zumindest eine Übersicht wo man noch was drehen kann. Ansonsten schlägt das Ding meines Wissens nach einfach nur Covering-Indexe vor, wenn die Abfrage halt mal keinen ordentlichen Index zum suchen gefunden hat. Das wäre auch die Antwort auf die Frage, warum er viele ähnliche Vorschläge mach: Weil die Abfrage ähnlich war, aber keinen Index gefunden hat. Entsprechend schlägt er einen CoverIndex passend dazu vor.Nutzt sonst jemand das Tool und wie beurteilt ihre die Qualität der Missing Index Vorschläge?
Frage:
In dem Report Missing Index werden die möglichen fehlenden Indexe angezeigt inkl. Erstellungscode.
Aber der Report schlägt mir oft ähnliche Indexe vor, vor allem bei Indizes mit eingeschlossenen Spalten:
z.B.
CREATE INDEX missing_index_28 ON [Tabellenname] ([Spalte01]) INCLUDE ([Spalte02], [Spalte03], [Spalte04])
ein weitere Index soll erstellt werden, der nur eine oder zwei Include Spalten mehr hat
CREATE INDEX missing_index_28 ON [Tabellenname] ([Spalte01]) INCLUDE ([Spalte02], [Spalte03], [Spalte04], [Spalte05], [Spalte06])
Siehe Antwort obenIn dem Report Missing Index werden die möglichen fehlenden Indexe angezeigt inkl. Erstellungscode.
Aber der Report schlägt mir oft ähnliche Indexe vor, vor allem bei Indizes mit eingeschlossenen Spalten:
z.B.
CREATE INDEX missing_index_28 ON [Tabellenname] ([Spalte01]) INCLUDE ([Spalte02], [Spalte03], [Spalte04])
ein weitere Index soll erstellt werden, der nur eine oder zwei Include Spalten mehr hat
CREATE INDEX missing_index_28 ON [Tabellenname] ([Spalte01]) INCLUDE ([Spalte02], [Spalte03], [Spalte04], [Spalte05], [Spalte06])
Reicht es dann ggf. nicht aus, dass man NUR den Index mit den MEISTEN Include Spalten anlegt, oder muss man generell besser mehr Indexe aber dafür exaktere Indexe anlegen.
Und wie verhält es sich wenn vor dem include mehrere Schlüsselspalte stehen?
Würde der untere Index nicht auch alle oberen Indexe abdecken?
z.B.
CREATE INDEX missing_index_28 ON [Tabellenname] ([Spalte01], [Spalte07], [Spalte08],) INCLUDE ([Spalte02], [Spalte03], [Spalte04], [Spalte05], [Spalte06])
Sind nicht zu viele Indexe für´s schreiben / ändern / löschen von Tabellenzeilen in eine Datenbank kontraproduktiv?
Jain. Kommt schwer auf die grösse der Tabelle und die Abfragen an.Und wie verhält es sich wenn vor dem include mehrere Schlüsselspalte stehen?
Würde der untere Index nicht auch alle oberen Indexe abdecken?
z.B.
CREATE INDEX missing_index_28 ON [Tabellenname] ([Spalte01], [Spalte07], [Spalte08],) INCLUDE ([Spalte02], [Spalte03], [Spalte04], [Spalte05], [Spalte06])
Sind nicht zu viele Indexe für´s schreiben / ändern / löschen von Tabellenzeilen in eine Datenbank kontraproduktiv?
Grundsätzlich sollte man wissen, was man hier macht.
Der Wichtigste Index ist immer erst mal der Clustered Index, weil dieser die Physikalische Speicherung auf dem Datenträger vorgibt.
Bei Tabellen, die viele Änderungen erfahren sollte dieser Index so angelegt sein, das der SQL Server nicht bei jeder Änderung irgendwas verschieben muss, weil er den Datensatz jetzt woanders hinschieben muss, oder weil ein neuer Datensatz dazwischen eingeschoben wurde.
Am besten hat man natürlich eine art ID, die sich mit jedem neuen Datensatz erhöht, nach dem Erstellen niemals geändert wird und die den ClusteredIndex spielt. Wichtig hierbei: Jeder non-clustered index beinhaltet IMMER ALLE im Clustered Index angegeben Spalten.
Zum Thema Include: Nimmt man den index hier:
CREATE INDEX missing_index_28 ON [Tabellenname] ([Spalte01]) INCLUDE ([Spalte02], [Spalte03], [Spalte04], [Spalte05], [Spalte06])
Heisst das auf Klartext: Mache einen Index, sortiert nach Spalte01, aber hänge hinter den Eintrag immer gleich die Werte von Spalte 02-06, damit du diese Daten gleich hast. Sinn dahinter: Der Index ist ein Verweis auf einen Datensatz. Findet der Index also einen Datensatz, der deiner Abfrage entspricht, schickt er die Abfrage an die Position des tatsächlichen Datensatzes. Das ist also eine Zusätzliche Operation, die auf dem SQL geschieht. Braucht die Abfrage aber hier nur die Werte von Spalte 02-06, dann sind die Daten schon da und können direkt übergeben werden. Keine weitere Dinge sind nötig.
Vorteil: Abfrage ist deutlich schneller
Nachteile: Braucht mehr Speicherplatz; Braucht mehr IO wenn ein Insert/Update auf der Tabelle erfolgt, weil entweder die Tabelle anhand des Clusters neu sortiert werden muss, oder weil der neue Datensatz am Feld noch zusätzlich an alle "Include-Indexe" übertragen werden muss.
Änderst du also an dem Datensatz was an "Spalte04", so muss diese neue Information an alle Indexe gegeben werden, die Spalte04 beinhalten.
Fazit: Solche Indexe sind da sinnvoll, wo VIEL gelesen wird, aber weniger geschrieben.
Ändert sich sehr viel an der Tabelle, muss der Clustered Index Weise gewählt werden und die NonClustered Indexe sollten möglichst wenig INCLUDEs haben, ausser es ist für die Performance bei den Queries ein Zugewinn.
Ist manchmal schwierig abzuwägen. idR muss man es mit einem ordentlichen Performancetest ausloten
Reicht es dann ggf. nicht aus, dass man NUR den Index mit den MEISTEN Include Spalten anlegt, oder muss man generell besser mehr Indexe aber dafür exaktere Indexe anlegen.
Ja prinzipiell schon. Hat halt den Nachteil, das bei grossen & stark frequentierten Tabellen die Gesamtlast auf dem System steigt.Ich würde hier einen Schnitt über die Abfragen machen, und sehen, welche Spalte am häufigsten abgefragt werden. Diese kommen dann in den INCLUDE Part. Was nur hier und da mal abgefragt wird, nicht. Grundsätzlich gilt beim SQL: Viel hilft nicht viel. Weniger ist mehr, wenn es das richtige ist
1
Hi SeaStorm,
danke für deine Antwort.
An die IO´s hab ich noch gar nicht gedacht, könnte aber in Zukunft beim Einsatz von SSD Platten eine Rolle spielen.
Andersherum gefragt:
Gibt es auch ein Tool, so wie den Performance Report, das mir auch doppelt, überflüssige, ungebrauchte, ineffiziente Indexe heraussuchen kann?
Da die DB schon über 15 Jahre in Gebrauch ist, und auch einige Dev´s und Admin herumgeschraubt haben, werden sich auch einige "Index-Leichen" gebildet haben.
Besten Dank.
Rene
danke für deine Antwort.
An die IO´s hab ich noch gar nicht gedacht, könnte aber in Zukunft beim Einsatz von SSD Platten eine Rolle spielen.
Andersherum gefragt:
Gibt es auch ein Tool, so wie den Performance Report, das mir auch doppelt, überflüssige, ungebrauchte, ineffiziente Indexe heraussuchen kann?
Da die DB schon über 15 Jahre in Gebrauch ist, und auch einige Dev´s und Admin herumgeschraubt haben, werden sich auch einige "Index-Leichen" gebildet haben.
Besten Dank.
Rene
nichts das mir bekannt wäre. Wäre für ein Programm eh schwierig zu entscheiden, was da Sinn macht. Vor allem da es nur gegen Abfragen analysieren kann, die während der Analyse gemacht wurden oder anhand des Queryplan-Caches.
Abfragen die also nicht im Cache sind oder nicht gemacht wurden, sind dann nicht dabei und deren indexe, sollten welche existieren, wären dann als Überflüssig angesehen worden und fliegen raus.
und da du sagst, das die Anwengung 15 Jahre alt ist, werden da wohl eher wenige Abfragen über Parameter, sondern gruselig über Vollständige Abfragetexte, die die Werte enthalten, abgefeuert. Von daher siehts dann idR auch eher mau aus, mit dem QueryplanCache.
Ohne die Anwendung zu kennen würde ich alles in eine DEV-Umgebung kopieren und da nach und nach die Tabellen durchgehen, deren Indexe löschen, die Anwendung belasten, und den Analyzer dabei laufen lassen. Dann gucken was der zu beanstanden hat und daraus die Sinnvollsten Indexe erstellen.
Falls du den Sourcecode des Programms hast, suche dir einen Weg um alle Queries aus dem Programm raus zu fischen.
Und dann diese Analysieren: die am häufigsten abgefragten Tabellen nehmen und schauen was da an häufigen Abfragen dazu existieren und welche Felder die Anfordern. Abhängig davon die Indexe neu erstellen.
Abfragen die also nicht im Cache sind oder nicht gemacht wurden, sind dann nicht dabei und deren indexe, sollten welche existieren, wären dann als Überflüssig angesehen worden und fliegen raus.
und da du sagst, das die Anwengung 15 Jahre alt ist, werden da wohl eher wenige Abfragen über Parameter, sondern gruselig über Vollständige Abfragetexte, die die Werte enthalten, abgefeuert. Von daher siehts dann idR auch eher mau aus, mit dem QueryplanCache.
Ohne die Anwendung zu kennen würde ich alles in eine DEV-Umgebung kopieren und da nach und nach die Tabellen durchgehen, deren Indexe löschen, die Anwendung belasten, und den Analyzer dabei laufen lassen. Dann gucken was der zu beanstanden hat und daraus die Sinnvollsten Indexe erstellen.
Falls du den Sourcecode des Programms hast, suche dir einen Weg um alle Queries aus dem Programm raus zu fischen.
Und dann diese Analysieren: die am häufigsten abgefragten Tabellen nehmen und schauen was da an häufigen Abfragen dazu existieren und welche Felder die Anfordern. Abhängig davon die Indexe neu erstellen.
