SQL – Fragen und Antworten: Verbesserung der SQL-Leistung

  • Artikel

Es gibt immer mehrere Möglichkeiten, den SQL Server-Leistung Umschlag schieben, und es kostet oft Geld zu tun.

Paul S. Randal

Der neue Normal

F: Wir sind dabei, ein Projekt zu starten, die unsere Datenbankschema umgestalten wird, und wir sind besorgt über wie viel Dinge zu normalisieren. Haben Sie irgendwelche Ratschläge?

**A.**Seien Sie vorsichtig in die Normalisierung zu weit treibt. Das übliche Ziel einer Normalisierung Übung ist dritte Normal Form oder 3NF, wo helfen alle nicht-Schlüssel-Attribute des Schlüssels definieren. Es ist hilfreich, Wortgruppe, die diese Regel beschreibt: "Die Attribute zu definieren, den Schlüssel, den gesamten Schlüssel und nichts, aber der Schlüssel, so mir helfen Codd." (E.F. Ursprünglich definiert Codd 3NF zurück in 1971; finden Sie unter diesen Link Weitere Informationen. Die Phrase auf den Eid, den man in einem Gerichtssaal schwört basiert.)

Das Problem mit dem Druck der 3NF ist, dass viele Menschen zu weit gehen — nenne ich Übernormalisieren. Dadurch kann enorme Performance-Probleme unter Last, wenn Abfragen zu verknüpfen zahlreiche Tabellen zu sinnvolle Beziehungen zu materialisieren. Eine Abfolge von Joins ist dann die einzige Möglichkeit, die verschiedenen Einheiten zu erreichen.

Wir haben beispielsweise einen Client, dessen Schema vor 10 Jahren von einem C#-Entwickler geschrieben wurde war nicht besonders versierte SQL Server. Jede mögliche Person in seiner eigenen Tabelle gespeichert und hat eine ID. Alle Entity-Attribute werden in eigenen Tabellen, mit deren eigenen IDs gespeichert. Die Beziehungen durch die Entitäten zu anderen Entitäten gehören werden in eigenen Tabellen gespeichert. Die Geschichte von wie jedes Attribut für jede Entität geändert hat in seiner eigenen Tabelle gespeichert wird, mit einer eigenen ID

So kann eine bestimmten Entität Tabelle Fremdschlüssel-Referenzen auf viele andere Tabellen haben. Dies ist ein stark zu stark normalisierte Schema Verknüpfen von 10 oder mehr Tabellen erfordert fast etwas zu tun.

Noch schlimmer ist, dass es gibt keine Archivierungsstrategien. Es gibt viele Jahre von Daten in der Datenbank, wodurch Verknüpfungen, die große Datenmengen verarbeiten müssen. Mehrere Dinge müssen getan werden, um dieses Problem zu beheben:

  • Entfernen Sie alte Daten verringern die Größe der Daten und Implementieren einer automatischen Archivierungsstrategie, des aktiven Datasets klein zu halten.
  • Sollten Sie mit Indizes und Abfrageprädikaten zu reduzieren die Menge der Daten in Operationen gefiltert.
  • Betrachten Sie die de-normalizing Teile des Schemas erlauben einige Tabellen zusammen reduzieren, um Join Komplexität zu verringern.

Hier ist die goldene Regel im Auge zu behalten, wenn Sie neues Schema entwerfen: Denken Sie an die T-SQL-Vorgängen benötigt, um effektiv die Daten Abfragen und versuchen übermäßige Verknüpfungen zu minimieren oder unnötig auf sehr großen Datenmengen tätig. Sie sollten auch Ihr Design Prototyp und führen Sie einige repräsentativen Abfragen an einer hohen Arbeitsauslastung zu sehen, ob es offensichtliche Gestaltung Engpässe gibt.

Wenn Sie ein Schema in Betrieb genommen haben wird es unglaublich schwer und teuer, Änderungen vorzunehmen. Dies bedeutet, dass Sie verbringen viel Zeit Feinabstimmung Abfragen und Indizierung Strategien, um die Leistungseinbußen des Schemas, zu stark normalisierte ausgleichen können.

Leistung-Rätsel

F: Unsere Haupt-Tabelle hat einen gruppierten Index für eine eindeutige, ganzzahlige Identitätsspalte. Tabellenzeilen werden nie aktualisiert, nachdem sie eingelegt haben und ich habe die Zeilengröße relativ klein gehalten. Ich finde, dass mit einer höheren Anzahl gleichzeitiger Verbindungen, Abfrage-Leistung verlangsamt wird. Haben Sie irgendeine Idee, was das Problem sein könnte?

**A.**Sie haben Ihre gruppierten Index mit unseren Best-Practice-Richtlinien konzipiert, wo Schlüssel des gruppierten Indexes eindeutige, statische, engen und immer sein sollte. Das funktioniert perfekt, bis Sie in einer High-End-Arbeitsauslastung erhalten.

Zum Beispiel mit mehreren tausend gleichzeitige Verbindungen (oder vielleicht sogar Hunderte) alle in die Tabelle eingefügt, es blockiert werden. Betrachtet man die Sperren mit der sys. dm_tran_locks (Dynamic Management View, DMV), zeigt es nicht etwas nicht stimmt. Alle Threads werden Seitensperren IX (auf eine der wenigen Seiten) und Key X-Sperren (auf Records auf diesen Seiten) halten. Dinge normal aussehen, aber es ist auf jeden Fall einige blockierende los. Die Blockierung nur beinhalten nicht sperren. Finden Sie weitere Informationen zu sperren und Sperren in SQL Server in Books Online.

Wenn Sie in der DMV sys. dm_os_waiting_tasks suchen, sehen Sie, dass die meisten Threads auf ein paar Seiten mit einem Wartetyp der PAGELATCH_EX gewartet werden. Dies ist da eine Sperre nicht ausreichen, um eine Kopie einer Seite Daten-Datei ändern können. Die sys. dm_os_waiting_tasks ist eines der nützlichsten DMVs, übrigens, weil es zeigt, was alle "Threads" in SQL Server warten.

Kopie der Seite im Speicher ist wirklich eine Datenstruktur, wie weit SQL Server geht. Sie können eine Datenstruktur mit nur einer relationalen Sperre nicht ändern. Sie müssen die Integrität der Partitionsdatenstruktur von mehreren Threads zugreifen auf und ändern es auf einmal schützen. Dies ist eine grundlegende grundlegenden Informatik. Sie können exklusiven Zugriff zum Ändern der Datenstruktur innerhalb von SQL Server verwenden einen leichten Synchronisierungsmechanismus bezeichnet eine Verriegelung, die ich hier vorher besprochen haben, verwalten.

Der gruppierte Index für eine Identitätsspalte ist, werden Einfügungen ein Muster nur Anhängen-Insert mit vielen Zeilen auf jeder Seite. Das ist gut so. Dies bedeutet jedoch, dass viele Threads versuchen, gleichzeitig auf der gleichen Seite der Daten-Datei Zeilen einfügen. Die erforderlichen Sperren verursachen nicht blockieren, da jeder Thread verfügt über kompatible Seitensperren und Zeilensperren einzelner Zeilen von. Jedoch Verriegelung die Threads, die alle erwirbt eine exklusive müssen, auf der Seite. Gleichzeitig ist dies nicht möglich. Wenn Ihre Arbeitsauslastung zunimmt, kann dies zu großen blockieren und Abfrage-Performance-Problemen führen.

Es gibt eine Vielzahl von Lösungen, einschließlich:

  • Mithilfe von Partitionierung die einfügen-Arbeitsauslastung verteilt auf mehrere Partitionen einer Tabelle (oder sogar mehrere Tabellen)
  • Wechseln zu einer zusammengesetzten Clusterschlüssel, die natürlich die Einfügungen über mehrere Punkte in der Tabelle verteilt werden (auf Kosten der Fragmentierung verursacht)

Das Endergebnis ist jedoch, dass das blockierende Symptom nicht immer bedeutet, dass das Problem ist.

Nachdenklich Leistung

F: Wir haben mit unserem SQL Server Performance-Probleme und unsere Entwickler sagen, wir brauchen einen leistungsfähigeren Server zur Ausführung unsere Arbeitsbelastung. Wir sind die klassischen "unfreiwillige" DBAs. Können Sie uns irgendwelche Tipps, was sonst noch helfen kann, außer Kauf mehr Hardware?

**A.**Menschen erkennen nicht oft, dass schlechter Leistung ein Symptom für ein grundlegendes Problem mit der Art und Weise Sie SQL Server verwenden. Die Annahme ist, Sie sind bis an die Fähigkeit Grenzen Ihrer aktuellen Hardware und es ist Zeit zu investieren in einen Server mit mehr und schnellere CPUs. Dies kann eine kostspielige Annahme sein, da mehr Timing-Fenster öffnet und zu noch höheren Konflikte und weniger Durchsatz der Arbeitsauslastung führt verschieben auf einen Server mit mehr CPUs.

Wir haben tatsächlich das passiert ein paar Mal wegen der schlechten T-SQL-Programmiertechniken gesehen.

Wir haben immer wieder gezeigt, wie es wäre weit billiger und günstiger zugrunde liegenden Leistungsprobleme anstelle von Hinzufügen von Hardware zu lösen. Bevor Sie den Sprung mit neuer Hardware nutzen, es gibt viele Dinge, die Sie betrachten können, ob es ein Problem lösbar ist. Dazu einige Beispiele:

  • Suchen Sie in der sys. dm_os_waiting_tasks DMV sehen verbrachte Zeit warten auf Ressourcen. Wenn die Zeit ist warten auf Client-Anwendungen, Daten zu verarbeiten, wird nicht bis der Server-Hardware steht einen Unterschied machen. Sie können dieses Muster sehen, wenn viele Zeilen in der DMV Liste ASYNC_NETWORK_IO als der Wartetyp auszugeben.
  • Prüfen Sie I/O Subsystem Schreib-/Wartezeiten unter Verwendung der DMV sys. dm_io_virtual_file_stats um zu sehen, ob das i/O-Subsystem zu hart gefahren wird. Dies deutet auf die Notwendigkeit einer besseren Indizierung Strategie. Tempdb könnte auch ein Engpass, der Übernutzung von temporären Tabellen hinweisen könnte.
  • Blick auf die Index DMVs fehlt zu sehen, ob High-Impact-Indizes vorhanden sind.
  • Sehen Sie in sys. dm_db_index_usage_stats zu sehen, wenn Sie Pflege sind — aber nicht mit — eine Tonne von Indizes.

Einfache Änderungen können oft erhebliche Auswirkungen auf die Leistung von SQL Server haben. Manchmal ist das Datenvolumen einfach gewachsen, und Sie müssen den Druck aus dem Pufferpool nehmen mehr Serverspeicher. Das i/O-Subsystem braucht manchmal wirklich einen Schub. Es ist nicht sehr häufig, aber, dass Sie einen größeren, schnelleren Server kaufen müssen.

Parallele Linien

F: Wir haben einige Abfragen, die Probleme verursachen, wenn sie parallel laufen und wir sie auf Singlethread-werden zu begrenzen möchten. Wie können wir dies ohne alles Single-Thread durch Festlegen der MAXDOP-Option auf einen?

**A.**Als Hintergrund bedeutet MAXDOP "maximalen Grad an Parallelität." Das ist im Grunde, wie viele gleichzeitige parallele Threads beim Ausführen eine Abfrage verwenden kann.

Es ist gut, dass Sie nicht "Knie-Ruck" und alles auf MAXDOP, festlegen, wie Parallelität eines der besten Performance-Funktionen von SQL Server ist. Viele Menschen tun das, vor allem auf den schlechten Rat besagt, dass die Prävalenz der CXPACKET wartet (davon warten Statistikanalyse) behoben werden sollten, durch Beenden von Parallelität.

Eines der Probleme bei der Verwendung der Sp_configure-Option zum Deaktivieren Parallelität (abgesehen von der Tatsache, dass es alles auf dem Server auswirkt) ist, dass jemand mit einer Privilegstufe diese Einstellung überschreiben kann, mithilfe eines MAXDOP-Abfragehinweis in ihren Abfragen. Sie könnten eine eines Abfragehinweises immer auf alle Abfragen angeben, die Sie einschränken möchten. Unpraktisch, aber mit Hunderten oder Tausenden von Abfragen beteiligt sein kann.

Zwei Methoden sind sehr viel effektiver Parallelität einschränken:

  • Erhöhen Sie die serverweiten "Cost Threshold for Parallelism." Dies ist eine beliebige Anzahl erzeugt während der Abfragekompilierung. Es wird verwendet, zu entscheiden, ob Sie einen Abfrageplan erzeugen, den Sie parallel oder nicht ausführen können. Durch das erhöhen dieser Anzahl, können Sie einige Abfragen effektiv verhindern, die parallel tun sollte nicht ausgeführt werden. Sie können diese Einstellung unter Verwendung eines Abfragehinweises entweder nicht überschreiben. Erfahren Sie mehr über diese Einstellung und wie Sie es in einem Blogbeitrag von Jonathan Kehayias ändern hier.
  • Sie können auch das Feature Ressourcenkontrolle (in der Enterprise Edition nur). Dadurch können Sie Abfragen in "Eimer" (Arbeitsauslastungsgruppen genannt) und dann jeden Eimer ein MAXDOP zuweisen. Sie können die MAXDOP-Option von Sp_configure, aber nicht die Ressourcenkontrolle überschreiben. Können Sie was auch immer Sie entscheiden, welche Abfragen in die Eimer gehen möchten filtern. Sie können mehrere Eimer mit unterschiedlichen MAXDOP-Einstellungen als auch haben. Mehr Menschen nutzen diese Methode eine präzise Steuerung der Parallelität zu gewinnen.

Paul S. Randal

Paul S. Randal ist der Geschäftsführer von SQLskills.com, Microsoft regional Director und ein SQL Server-MVP. Er arbeitete an der SQL Server Storage Engine Team bei Microsoft von 1999 zu 2007. Er schrieb DBCC CHECKDB/Repair für SQL Server 2005 und war verantwortlich für das Kernspeichermodul während SQL Server 2008-Entwicklung. Paul Randal ist Experte für Notfallwiederherstellung, hohe Verfügbarkeit und Datenbankwartung und regelmäßiger Referent bei Konferenzen in aller Welt. Er Blogs auf SQLskills.com/blogs/paul, und Sie finden ihn auf Twitter bei twitter.com/PaulRandal.

Verwandter Inhalt