Laden...

SQL-Server und SQL-Server-Express: Indizes und Performance

Erstellt von m.grauber vor 4 Jahren Letzter Beitrag vor 4 Jahren 3.795 Views
M
m.grauber Themenstarter:in
343 Beiträge seit 2010
vor 4 Jahren
SQL-Server und SQL-Server-Express: Indizes und Performance

Hallo,

Meine Fragen bezieht sich auf SQL-Server (gekauft) und die kostenlose SQL-Server-Express-Edition und dienen zur Performance-Steigerung beim Lesen von Daten aus einem C# Projekt heraus.

Ich kann die Performance bei den wenigen Datensätzen leider nicht testen. Sicher habt ihr schon viel Erfahrung damit.

1.) Durch die Prozessor-Beschränkung beim SQL-Server-Express könnte es sein, dass sich die weiteren Fragen dann anders beantworten lassen, weil sich die Arbeitslast ändert und z. B. die Express-Editions dann mit weniger Indizes klar kommt. - Ist dies so? (Diese Frage bitte erst am Schluss beantworten)

Allgemeine Infos:

  • Alle Tabellen haben bereits einen Primary Key, daher kann ich zur Performancesteigerung immer nur noch Non-Clustered-Indizes anlegen
  • Die Erhöhung des Speicherplatzes durch neue Indizes ist problemlos möglich. Es wird keine Platzprobleme geben. Die Performancesteigerungen sind das Wichtigste.
  • Die Geschwindigkeit der Datensatzerstellung ist ebenfalls vernachlässigbar und kann durch eine bessere Indizierung auch gerne etwas länger dauern.
  • Zugriff ist aus C# heraus teils über T-SQL-Anweisungen und teils über Linq.
  • Es sollen einmal sehr viele Datensätzen in die Kunden-Tabelle und in zugehörige relationale Tabellen.
  • In möglichen Foreign-Keys sollen keine automatischen Regeln etc. genutzt werden.

Damit es einfache Antworten geben kann, hier ein einfaches Bsp.: eine Tabelle Kunden mit den Feldern

 PKKunden int NOT NULL (Primary Key)
 Nachname nvarchar(70) NOT NULL
 Vorname nvarchar(30) NOT NULL
 Kundenart int NOT NULL
 Kundeseit datetime NOT NULL (Standardwert: 1.1.1900)
 Kundeaktiv bit NOT NULL
 Kundenid uniqueidentifier NOT NULL

Ich würde nun gerne folgende Indizes erstellen:
"CREATE INDEX IX_Kunden_Nachname ON Kunden(Nachname ASC)" "CREATE INDEX IX_Kunden_Vorname ON Kunden(Vorname ASC)" "CREATE INDEX IX_Kunden_Kundenart ON Kunden(Kundenart ASC)" "CREATE INDEX IX_Kunden_Kundeseit ON Kunden(Kundeseit ASC)" "CREATE INDEX IX_Kunden_Kundeaktiv ON Kunden(Kundeaktiv ASC)" "CREATE INDEX IX_Kunden_Kundenid ON Kunden(Kundenid ASC)"

2.) Ich würde für alle Felder per "CREATE INDEX" Indizes erstellen, nach denen ich filtere. Gibt es beim SQL-Server eine Obergrenze, ab der durch die vielen Indizes dann die Abfrage evtl. sogar noch länger dauert als ohne oder mit weniger Indizes? (Ich kenne das von anderen Datenbanken, dass dann Abfragen ab z. B. 7 Indizes in einer Tabelle langsamer werden.)

3.) Sind die oberen Indizes die performanteste Art, wenn man dann auf die Tabellenfelder per T-SQL und WHERE-Klausel (z. B. "WHERE Kundenart=2") zugreift?

4.) Sind die oberen Indizes auch die performanteste Art, wenn man auf die Tabellenfelder per Linq-Where zugreift?

5.)
a) Ist "CREATE INDEX" die performanteste Art, wenn man auf dieses Feld später per JOIN zugreift ODER ist es performanter per Foreign-Key (z. B. "ALTER TABLE [dbo].[Kunden] WITH NOCHECK ADD CONSTRAINT [FK_Kunden_Kundenart] FOREIGN KEY([Kundenart]) REFERENCES [dbo].[Kundenart] ([PKKundenart]) NOT FOR REPLICATION")?
b) Oder verlangsamt evtl. sogar der Foreign-Key?

6.) Soll trotz Foreign-Key dennoch auch immer ein Index erstellt werden, damit die Abfragen schneller sind?

7.) Gelten die oberen Antworten dann wirklich für alle Feldtypen (z. B. nvarchar, bit, tinyint, int, datetime etc. (außer z. B. Memofeldern)

8.) Wie sieht es mit GUID's in uniqueidentifier-Feldern (NOT NULL) aus? Gilt das auch dafür?

9.) Mit welcher Art Index kann man die Performance steigern, wenn man z. B. ein nvarchar-Feld mit "WHERE Nachname LIKE %müller%" abfragt?

Vielen Dank im Voraus!!!

Mit freundlichem Gruß

Mfg
Michael

PS: Ich stelle nur Fragen, wenn ich in Büchern, im Web und in Foren nichts gefunden habe. Dumme Fragen bitte ich zu entschuldigen!

:] VISUAL STUDIO 2017 + .NET FRAMEWORK 4.5 + SQL-Server 2012 :]

16.807 Beiträge seit 2008
vor 4 Jahren

Schau Dir mal die Basics vom SQL Server an, damit sind die meisten Fragen grundgelend beantwortet:

  • Man kann keine multiplen clustered indexes auf eine Tabelle setzen. Ein clustered index beeinflusst die Reihenfolge, wie Daten gespeichert werden. Kann halt nur eine Reihenfolge geben. Die restlichen Fragen ergeben sich daher.
  • Die performanteste Art auf Daten zuzugreifen sind Indexed Views, die starke Limits haben
T
2.219 Beiträge seit 2008
vor 4 Jahren

Nebenbei solltest du auch deine Indizierung überdenken.
Es macht keinen Sinn, bei einer Kunden Tabelle so viele Spalten einzeln zu indizieren.
Jeder Index kostet dich bei Insert, Update und Delete entsprechende Performance, wenn die Spalten im Index betroffen sind.
Wenn du viele Indizies brauchst um performant Daten abzufragen, dann machst du grundlegend was falsch.
Dann solltest du auch überlegen ob eine Relationale Datenbank überhaupt der richtige Ansatz ist.
Auch machen Indizies nur Sinn, wenn du entsprechend viele Daten hast.
Eine Tabelle mit 100 Einträgen braucht man nicht so krass zu indizieren.

Reden wir aber von einigen Mio. Einträgen, die dann immer wieder gelesen werden mit unterscheidlichen Einzelbedingungen, dann würde ich überlegen ob die Abfrage gegen eine DB dann noch sinnvoll ist.

Nachtrag:
Alternativ solltest du auch mal mit der Developer Edition vom SQL Server deine Tests fahren.
Dann hast du einen SQL Server mit allen Features nur mit der Einschränkung, dass dieser rein zum testen ist und nicht produktiv verwendet werden darf!
Genueres kannst du den Lizenzbestimmungen entnehmen!

Nachtrag2:
Deine Spalten immer mit Prefixen wie Kunde/Kunden bei der Tabelle Kunden einzufügen ist nicht sinnvoll.
Nutzte direkte Bezeichnungen wie Art, Aktiv o.ä.
Das ist sehr unansehnlich, wenn die Tabelle X Spalten mit dem Tabellennamen als Prefix hat.
In deinem Kunden Objekt in C# wirst du auch nicht schreiben wollen, dass das "Name" Feld z.B. KundenName heißt.

Beim Thema SQL und Performance kann ich dir das Buch "SQL Performance Explained" ans Herz legen.
Wenn du es gelesen und verstanden hast, erübrigen sich deine Performance Fragen ebenfalls.

Buch

T-Virus

Developer, Developer, Developer, Developer....

99 little bugs in the code, 99 little bugs. Take one down, patch it around, 117 little bugs in the code.

16.807 Beiträge seit 2008
vor 4 Jahren

Es macht keinen Sinn, bei einer Kunden Tabelle so viele Spalten einzeln zu indizieren.
Jeder Index kostet dich bei Insert, Update und Delete entsprechende Performance, wenn die Spalten im Index betroffen sind.

Es gibt keine pauschale Aussage, wann wie viele Indexes schlecht sind.
Daher ist es pauschal auch nicht schlecht alles zu indizieren, wenn es dafür Gründe gibt.

Es ist meist ja immer noch so, dass von relationalen Datenbanken sehr viel mehr gelesen als geschrieben wird - gerade sowas wie Kundendaten oder Shops etc.

Auch machen Indizies nur Sinn, wenn du entsprechend viele Daten hast.
Eine Tabelle mit 100 Einträgen braucht man nicht so krass zu indizieren.

Indexe wirken sich vor allem auf die Geschwindigkeit beim Lesen aus - und das ist eben auch bei wenigen Daten der Fall.
Daher kann sich ein Index bei 100 Einträgen sehr schnell lohnen, wenn es eine sehr hohe Performance-Anforderung beim Lesen gibt.

M
m.grauber Themenstarter:in
343 Beiträge seit 2010
vor 4 Jahren

Hallo Abt,

Entschuldige, das war ein wirklich dummer Schreibfehler. 😦 Ich meine NON-Clustered. Ich haben den Text oben gleich geändert. Ich habe ja bereits einen Primary Key, der auch die Sortierung bestimmt!

Diese Tabelle ist wie gesagt nur für das Verständnis beim Fragen. Natürlich füge ich nur für die Felder einen Index ein, die ich in den größeren "SELECT …."-Anweisungen auch abfrage und diese Indizes sind nur Beispiele für Indizes bei den verschiedenen Feldtypen an den verschiedensten Orten. Das kommt um Himmelswillen so nicht in die Datenbank!

Indexed View's kommen allerdings nicht in Frage.

Ich habe mich natürlich schon vorher damit etwas befasst und das waren die Fragen, die noch übrig sind.

Könntet ihr sie bitte dennoch ganz knapp (j/n) beantworten?

Das würde wirklich sehr weiterhelfen!

An T-Virus: Danke auch Dir für die Antwort!

Natürlich ist die Datenbank relational und muss es bei den Daten auch sein. Leider ist in meinem Beispiel die Tabelle und die Felder etwas dumm gewählt, aber es soll einfach verständlich sein.

Also soll ich den Index nicht mit "IX_" beginnen? - Beim Anlegen des Index über das Managementstudio und auch im Internet lese ich das immer wieder.

  • Wie soll ich dann den Index benennen? (IX hinten anstellen wie z. B. "Kunden_Nachname_IX"?

Danke!

Mfg
Michael

PS: Ich stelle nur Fragen, wenn ich in Büchern, im Web und in Foren nichts gefunden habe. Dumme Fragen bitte ich zu entschuldigen!

:] VISUAL STUDIO 2017 + .NET FRAMEWORK 4.5 + SQL-Server 2012 :]

16.807 Beiträge seit 2008
vor 4 Jahren

Deine Fragen sind entweder bereits geklärt oder pauschal nicht mit Ja/Nein beantwortbar.
Performance-Verhalten von Datentypen ist Micro Optimization und sollte man sich gut überlegen; Performance-Werte kann man ergooglen.
Die Performance hier unterscheidet sich prinzipiell nicht am Typ, sondern wie viel Bytes der Typ hat. Das entscheidet wie viel durchsucht werden muss (Page based indexes).

Ansonsten:

  • Ja, FK mit Index macht in 99,9% der Fälle sinn
  • Texte sind meistens sehr performant mit Full Text Search durchsuchbar; Full Text Search hat aber viele Limits.

Optimierungen sind eben ganz viel Sachverhalt und nie nen pauschales Thema.
Damit musst Dich halt abfinden / beschäftigen.

W
955 Beiträge seit 2010
vor 4 Jahren

Warum erzeugst du nicht Testdaten und überprüfst es auf deinem System?

T
2.219 Beiträge seit 2008
vor 4 Jahren

Und zum testen kannst du dich dann mit der Developer Edition voll austoben.
Hier hast du dann auch keine größen Limitierung pro Datenbank und die Hardware Limitierung ist auch etwas besser.

@Abt
Die Aussage bezog sich primär auf das Szenario mit der Kunden Tabelle.
In den meisten Fällen macht es kaum Sinn alle Spalten einer Tabelle zu indizieren.
Natürlich gibt es Fälle wo dies sinnvoll und vielleicht auch nötig ist, da die Tabellenstruktur es auch vorgibt.

Gerade weil jeder weitere Index einer Tabelle eben auch Redundanz bedeutet sowie eben zunehmend Performance bei Insert, Update und Delete Anweisungen kostet, sollte man nicht kopflos alles indizieren.
Bei hoch frequenten Tabellen mit vielen Spalten kann dies u.U. sogar zu einer verschlechterung der Gesamtperformance führen, wenn die Datenbank mit Index Anpassungen bis hin zu einem Index Split beschäftigt ist.
Von Index Fragmentierung will ich dann gar nicht erst anfangen.
Hier hat man dann noch mehr zu tun, diese bei riesigen Tabellen im 100+ GB Bereich zu defragmentieren, wenn man nicht gerade teure SSD Lösungen einsetzt.
Gerade durch solche Schattenseiten, kann es auch mit einem Index zu neuen Problemen kommen, die man dann erstmal in den Griff kriegen muss.

Man sollte auch bei einer Relationalen Datenbank immer erst prüfen ob ein weitere Index nötig ist oder man die bestehenden nicht durch optimierte Abfragen nutzen kann.

In dem Fall würde ich abhängig von dem Durchlauf der Tabelle, der Anzahl der Einträge und eben auch den Suchanfragen mir überlegen jede Spalte mit einem Index zu zuknallen.
Je nach Hardware würde es sich bei vielleicht 1.000 Einträgen nicht mal lohnen einen Index auf die Tabelle zu setzen, da die Daten dann meistens in einem Atemzug gelesen werden und sogar von der DB im RAM gehalten werden können.
Bei schwächerer oder ausgelasteter Hardware dann schon.
Dies hängt dann von anderen Situationsbedingten Faktoren ab, aber das ist dann zu pauschal!

T-Virus

Developer, Developer, Developer, Developer....

99 little bugs in the code, 99 little bugs. Take one down, patch it around, 117 little bugs in the code.

16.807 Beiträge seit 2008
vor 4 Jahren

Die Aussage bezog sich primär auf das Szenario mit der Kunden Tabelle.

.. und ich geh nun einfach mal davon aus, dass Du nicht mehr Infos hast als wir.
Dahingehend machen die Tipps eventuell bei einer schreibintensiven Sitatuion durchaus Sinn - dazu gehören Kundendaten jedoch meistens nicht.
In den meisten Fällen ist bei einer 0815 Kundentabelle das vollständige Indizien der performantere Weg.
In der realen Welt sieht das dann auch noch so aus, dass man bei einer wirklich schreibeintensiven Tabelle ohnehin die Felder in eigene Tabellen auslagern würde, um die Performance zu erhöhen.
Daher - sorry - machen die Tipps nicht ganz so wirklich sinn.

Auch gibt es keine Redundanz, um Du Dich kümmern musst; das bekommt man alles von der Datenbank geschenkt.
Du propagierst doch sehr oft das KISS-Prinzip; dann gewähre das hier doch auch.
Du weißt nicht ob der Threadstarter eine Tabelle mit mehreren 100 GB hat. Daher brauchst ja auch eine Szenarien erfinden, die es hier eventuell gar nicht gibt 😃

M
m.grauber Themenstarter:in
343 Beiträge seit 2010
vor 4 Jahren

Hallo Abt und T-Virus!

Ersteinmal vielen Dank für die Infos. Ich war leider unterwegs.

Nein, es sollen nicht alle Felder indiziert werden, sondern natürlich wie gesagt nur die, die in in WHERE-Klauseln auch zum Vergleich abgefragt werden und die in JOIN-Verbindungen angegeben sind. Bei anderen Tabellen können das auch entsprechend mehr Felder werden. Mein Beispiel mit der Kundentabelle ist nur ein Beispiel und steht stellvertretend für alle Tabellen.

Diese werde ich nun mit "CREATE INDEX IX_Kunden_Nachname ON Kunden(Nachname ASC)" usw. indizieren.
Das können je nach Tabelle zwischen 2 und 8 Indizes sein.
Auf die Spalten mit den Foreign Keys werde ich auch Indizes setzen.

In der Lösung wird die Kundentabelle und auch alle anderen Tabellen eher seltener bearbeitet und es sollen sehr viele Einträge (z. B. auch > 100.000) gespeichert werden.

  • Zur Indizierung von Guid-Spalten habe ich im Netz sehr widersprüchliche Infos gefunden. Einige sagen sogar, dass sei Kontraproduktiv. Hier bin ich etwas verwirrt und habe leider zu wenig Datensätze, um die Differenz zu Messen. - Könnt ihr bitte zumindest dazu noch ein klares Ja oder Nein geben, wenn ihr damit Erfahrungen habt?

  • Würdet ihr für die Spalte Nachname und Vorname statt normaler Indizes aus Performancegründen lieber **Volltextindizes **verwenden?

Nochmals vielen Dank!

Mit freundlichem Gruß

Mfg
Michael

PS: Ich stelle nur Fragen, wenn ich in Büchern, im Web und in Foren nichts gefunden habe. Dumme Fragen bitte ich zu entschuldigen!

:] VISUAL STUDIO 2017 + .NET FRAMEWORK 4.5 + SQL-Server 2012 :]

16.807 Beiträge seit 2008
vor 4 Jahren

Warum erzeugst du nicht Testdaten und überprüfst es auf deinem System?

Nochmal die Frage: wieso testest Du es nicht einfach, wenn Du widersprüchliche Aussagen findest?

Und nochmal die Aussage: Pauschal ein Ja/Nein wirste nicht erhalten.

M
m.grauber Themenstarter:in
343 Beiträge seit 2010
vor 4 Jahren

Hallo Abt

In Ordnung. Ich dachte im Forum gibt es dazu Erfahrungswerte und man kann sich dazu einfach austauschen und nicht, dass jeder selbst immer von vorne anfängt. 🙁

Ich werde dann viele Daten erzeugen und dann Tests fahren.

Danke trotzdem für die Antworten.

Mit freundlichem Gruß

Mfg
Michael

PS: Ich stelle nur Fragen, wenn ich in Büchern, im Web und in Foren nichts gefunden habe. Dumme Fragen bitte ich zu entschuldigen!

:] VISUAL STUDIO 2017 + .NET FRAMEWORK 4.5 + SQL-Server 2012 :]

16.807 Beiträge seit 2008
vor 4 Jahren

Erfahrungswerte haben hier nichts mit Ja/Nein zutun, wie bereits erklärt.
Du verlangst das aber. Was soll man dann dazu sagen, ausser ehrlich zu antworten, dass das halt nicht drin ist?

M
m.grauber Themenstarter:in
343 Beiträge seit 2010
vor 4 Jahren

Hallo Abt

In Ordnung. Verstanden. Vielen Dank für die Infos. Ihr tut hier wirklich einen sehr guten Job, auf diese Fragen einzugehen! 👍

Mit freundlichem Gruß

Mfg
Michael

PS: Ich stelle nur Fragen, wenn ich in Büchern, im Web und in Foren nichts gefunden habe. Dumme Fragen bitte ich zu entschuldigen!

:] VISUAL STUDIO 2017 + .NET FRAMEWORK 4.5 + SQL-Server 2012 :]