MS SQL Server - Architektur

Wir haben die Architektur von SQL Server zum besseren Verständnis in die folgenden Teile unterteilt:

• Allgemeine Architektur
• Speicherarchitektur
• Datendatei-Architektur
• Protokolldatei-Architektur

Allgemeine Architektur

Client - Wo die Anfrage initiiert wurde.

Query - SQL-Abfrage, die Hochsprache ist.

Logical Units - Schlüsselwörter, Ausdrücke und Operatoren usw.

N/W Packets - Netzwerkbezogener Code.

Protocols - In SQL Server haben wir 4 Protokolle.

• Gemeinsamer Speicher (für lokale Verbindungen und zur Fehlerbehebung).

• Named Pipes (für Verbindungen mit LAN-Konnektivität).

• TCP / IP (für Verbindungen mit WAN-Konnektivität).

• VIA-Virtual Interface Adapter (erfordert spezielle Hardware, die vom Hersteller eingerichtet werden muss und auch von der SQL 2012-Version veraltet ist).

Server - Wo SQL Services installiert wurden und sich Datenbanken befinden.

Relational Engine- Hier erfolgt die eigentliche Ausführung. Es enthält Query Parser, Query Optimizer und Query Executor.

Query Parser (Command Parser) and Compiler (Translator) - Dadurch wird die Syntax der Abfrage überprüft und die Abfrage in die Maschinensprache konvertiert.

Query Optimizer - Der Ausführungsplan wird als Ausgabe vorbereitet, indem Abfrage, Statistik und Algebrizer-Baum als Eingabe verwendet werden.

Execution Plan - Es ist wie eine Roadmap, die die Reihenfolge aller Schritte enthält, die im Rahmen der Abfrageausführung ausgeführt werden sollen.

Query Executor - Hier wird die Abfrage mit Hilfe des Ausführungsplans Schritt für Schritt ausgeführt und auch die Speicher-Engine kontaktiert.

Storage Engine - Es ist verantwortlich für das Speichern und Abrufen von Daten auf dem Speichersystem (Festplatte, SAN usw.), die Datenmanipulation, das Sperren und die Verwaltung von Transaktionen.

SQL OS- Dies liegt zwischen dem Host-Computer (Windows-Betriebssystem) und SQL Server. Alle im Datenbankmodul ausgeführten Aktivitäten werden von SQL OS ausgeführt. SQL OS bietet verschiedene Betriebssystemdienste, z. B. Speicherverwaltung für Pufferpool, Protokollpuffer und Deadlock-Erkennung mithilfe der Blockierungs- und Sperrstruktur.

Checkpoint Process- Checkpoint ist ein interner Prozess, der alle fehlerhaften Seiten (geänderten Seiten) vom Puffercache auf die physische Festplatte schreibt. Abgesehen davon schreibt es auch die Protokolldatensätze aus dem Protokollpuffer in die physische Datei. Das Schreiben schmutziger Seiten aus dem Puffercache in eine Datendatei wird auch als Härten schmutziger Seiten bezeichnet.

Es ist ein dedizierter Prozess und wird in bestimmten Intervallen automatisch von SQL Server ausgeführt. SQL Server führt den Prüfpunktprozess für jede Datenbank einzeln aus. Checkpoint hilft, die Wiederherstellungszeit für SQL Server im Falle eines unerwarteten Herunterfahrens oder eines Systemabsturzes \ Failure zu verkürzen.

Prüfpunkte in SQL Server

In SQL Server 2012 gibt es vier Arten von checkpoints - -

• Automatic - Dies ist der häufigste Prüfpunkt, der als Prozess im Hintergrund ausgeführt wird, um sicherzustellen, dass die SQL Server-Datenbank innerhalb des durch das Wiederherstellungsintervall - Serverkonfigurationsoption festgelegten Zeitlimits wiederhergestellt werden kann.

• Indirect- Dies ist neu in SQL Server 2012. Dies wird auch im Hintergrund ausgeführt, jedoch um eine benutzerdefinierte Zielwiederherstellungszeit für die spezifische Datenbank einzuhalten, für die die Option konfiguriert wurde. Sobald die Target_Recovery_Time für eine bestimmte Datenbank ausgewählt wurde, überschreibt dies das für den Server angegebene Wiederherstellungsintervall und vermeidet den automatischen Prüfpunkt in einer solchen Datenbank.

• Manual- Diese Anweisung wird wie jede andere T-SQL-Anweisung ausgeführt. Sobald Sie den Befehl checkpoint ausgeben, wird sie vollständig ausgeführt. Der manuelle Prüfpunkt wird nur für Ihre aktuelle Datenbank ausgeführt. Sie können auch die optionale Checkpoint_Duration angeben. Diese Dauer gibt die Zeit an, in der Ihr Checkpoint abgeschlossen werden soll.

• Internal- Als Benutzer können Sie den internen Prüfpunkt nicht steuern. Ausgestellt für bestimmte Operationen wie

  • Durch das Herunterfahren wird eine Prüfpunktoperation für alle Datenbanken gestartet, außer wenn das Herunterfahren nicht sauber ist (Herunterfahren mit nowait).

  • Wenn das Wiederherstellungsmodell von "Vollständig \ Massenprotokolliert" in "Einfach" geändert wird.

  • Während der Sicherung der Datenbank.

  • Wenn sich Ihre Datenbank in einem einfachen Wiederherstellungsmodell befindet, wird der Prüfpunktprozess automatisch ausgeführt, entweder wenn das Protokoll zu 70% voll ist, oder basierend auf dem Wiederherstellungsintervall der Serveroption.

  • Durch Ändern des Datenbankbefehls zum Hinzufügen oder Entfernen einer Daten- / Protokolldatei wird auch ein Prüfpunkt initiiert.

  • Der Prüfpunkt findet auch statt, wenn das Wiederherstellungsmodell der Datenbank massenprotokolliert ist und eine minimal protokollierte Operation ausgeführt wird.

  • DB-Snapshot-Erstellung.

• Lazy Writer Process- Lazy Writer überträgt schmutzige Seiten aus einem ganz anderen Grund auf die Festplatte, da Speicher im Pufferpool freigegeben werden muss. Dies geschieht, wenn der SQL Server unter Speicherdruck gerät. Soweit mir bekannt ist, wird dies durch einen internen Prozess gesteuert und es gibt keine Einstellung dafür.

SQL Server überwacht ständig die Speichernutzung, um den Ressourcenkonflikt (oder die Verfügbarkeit) zu bewerten. Seine Aufgabe ist es, sicherzustellen, dass jederzeit eine bestimmte Menge an freiem Speicherplatz verfügbar ist. Als Teil dieses Prozesses löst Lazy Writer, wenn er einen solchen Ressourcenkonflikt bemerkt, einige Seiten im Speicher frei, indem er schmutzige Seiten auf die Festplatte schreibt. Es verwendet den LRU-Algorithmus (Least Recent Used), um zu entscheiden, welche Seiten auf die Festplatte geleert werden sollen.

Wenn Lazy Writer immer aktiv ist, kann dies auf einen Speicherengpass hinweisen.

Speicherarchitektur

Im Folgenden sind einige der wichtigsten Merkmale der Speicherarchitektur aufgeführt.

• Eines der Hauptentwurfsziele aller Datenbanksoftware ist die Minimierung der Festplatten-E / A, da das Lesen und Schreiben von Festplatten zu den ressourcenintensivsten Vorgängen gehört.

• Der Speicher in Windows kann mit dem virtuellen Adressraum aufgerufen werden, der vom Kernelmodus (Betriebssystemmodus) und vom Benutzermodus (Anwendung wie SQL Server) gemeinsam genutzt wird.

• SQL Server "Benutzeradressraum" ist in zwei Regionen unterteilt: MemToLeave und Buffer Pool.

• Die Größe von MemToLeave (MTL) und Buffer Pool (BPool) wird beim Start von SQL Server festgelegt.

• Buffer managementist eine Schlüsselkomponente für die Erzielung einer hohen E / A-Effizienz. Die Pufferverwaltungskomponente besteht aus zwei Mechanismen: dem Puffermanager für den Zugriff auf und die Aktualisierung von Datenbankseiten und dem Pufferpool für die Reduzierung der E / A-Datenbanken.

• Der Pufferpool ist weiter in mehrere Abschnitte unterteilt. Die wichtigsten sind der Puffercache (auch als Datencache bezeichnet) und der Prozedurcache.Buffer cacheHält die Datenseiten im Speicher, sodass Daten, auf die häufig zugegriffen wird, aus dem Cache abgerufen werden können. Die Alternative wäre das Lesen von Datenseiten von der Festplatte. Das Lesen von Datenseiten aus dem Cache optimiert die Leistung, indem die Anzahl der erforderlichen E / A-Vorgänge minimiert wird, die von Natur aus langsamer sind als das Abrufen von Daten aus dem Speicher.

• Procedure cacheBehält die gespeicherten Prozedur- und Abfrageausführungspläne bei, um die Häufigkeit zu minimieren, mit der Abfragepläne generiert werden müssen. Informationen zur Größe und Aktivität im Prozedurcache finden Sie mit der Anweisung DBCC PROCCACHE.

Andere Teile des Pufferpools umfassen -

• System level data structures - Enthält Daten auf SQL Server-Instanzebene zu Datenbanken und Sperren.

• Log cache - Reserviert zum Lesen und Schreiben von Transaktionsprotokollseiten.

• Connection context- Jede Verbindung zur Instanz verfügt über einen kleinen Speicherbereich, in dem der aktuelle Status der Verbindung aufgezeichnet wird. Diese Informationen umfassen gespeicherte Prozeduren und benutzerdefinierte Funktionsparameter, Cursorpositionen und mehr.

• Stack space - Windows weist jedem von SQL Server gestarteten Thread Stapelspeicherplatz zu.

Datendatei-Architektur

Die Datendatei-Architektur besteht aus folgenden Komponenten:

Dateigruppen

Datenbankdateien können zu Zuordnungs- und Verwaltungszwecken in Dateigruppen zusammengefasst werden. Keine Datei kann Mitglied mehrerer Dateigruppen sein. Protokolldateien sind niemals Teil einer Dateigruppe. Der Protokollbereich wird getrennt vom Datenbereich verwaltet.

In SQL Server gibt es zwei Arten von Dateigruppen: Primär und Benutzerdefiniert. Die primäre Dateigruppe enthält die primäre Datendatei und alle anderen Dateien, die nicht speziell einer anderen Dateigruppe zugeordnet sind. Alle Seiten für die Systemtabellen werden in der primären Dateigruppe zugeordnet. Benutzerdefinierte Dateigruppen sind alle Dateigruppen, die mit dem Schlüsselwort Dateigruppe in Datenbank erstellen oder Datenbankanweisung ändern angegeben werden.

Eine Dateigruppe in jeder Datenbank fungiert als Standarddateigruppe. Wenn SQL Server einer Seite oder einem Index eine Seite zuweist, für die beim Erstellen keine Dateigruppe angegeben wurde, werden die Seiten aus der Standarddateigruppe zugewiesen. Um die Standarddateigruppe von einer Dateigruppe in eine andere Dateigruppe zu wechseln, sollte die Datenbankrolle db_owner festgelegt sein.

Standardmäßig ist die primäre Dateigruppe die Standarddateigruppe. Der Benutzer sollte über eine feste Datenbankrolle für db_owner verfügen, um Dateien und Dateigruppen einzeln sichern zu können.

Dateien

Datenbanken haben drei Arten von Dateien: Primärdatendatei, Sekundärdatendatei und Protokolldatei. Die primäre Datendatei ist der Startpunkt der Datenbank und zeigt auf die anderen Dateien in der Datenbank.

Jede Datenbank verfügt über eine Primärdatendatei. Wir können eine beliebige Erweiterung für die primäre Datendatei angeben, die empfohlene Erweiterung lautet jedoch.mdf. Die sekundäre Datendatei ist eine andere Datei als die primäre Datendatei in dieser Datenbank. Einige Datenbanken können mehrere sekundäre Datendateien enthalten. Einige Datenbanken verfügen möglicherweise nicht über eine einzige sekundäre Datendatei. Empfohlene Erweiterung für sekundäre Datendatei ist.ndf.

Protokolldateien enthalten alle Protokollinformationen, die zum Wiederherstellen der Datenbank verwendet werden. Die Datenbank muss mindestens eine Protokolldatei enthalten. Wir können mehrere Protokolldateien für eine Datenbank haben. Die empfohlene Erweiterung für die Protokolldatei lautet.ldf.

Der Speicherort aller Dateien in einer Datenbank wird sowohl in der Masterdatenbank als auch in der Primärdatei für die Datenbank aufgezeichnet. In den meisten Fällen verwendet das Datenbankmodul den Speicherort der Datei aus der Masterdatenbank.

Dateien haben zwei Namen - Logisch und Physisch. Der logische Name wird verwendet, um in allen T-SQL-Anweisungen auf die Datei zu verweisen. Der physische Name ist der OS-Dateiname. Er muss den Regeln des Betriebssystems entsprechen. Daten- und Protokolldateien können entweder auf FAT- oder NTFS-Dateisystemen abgelegt werden, jedoch nicht auf komprimierten Dateisystemen. Eine Datenbank kann bis zu 32.767 Dateien enthalten.

Ausmaße

Extents sind Grundeinheiten, in denen Tabellen und Indizes Speicherplatz zugewiesen wird. Ein Umfang beträgt 8 zusammenhängende Seiten oder 64 KB. SQL Server verfügt über zwei Arten von Extents: Uniform und Mixed. Einheitliche Ausmaße bestehen nur aus einem einzigen Objekt. Gemischte Ausmaße werden von bis zu acht Objekten gemeinsam genutzt.

Seiten

Es ist die grundlegende Einheit der Datenspeicherung in MS SQL Server. Die Größe der Seite beträgt 8 KB. Der Anfang jeder Seite ist ein 96-Byte-Header, in dem Systeminformationen wie Seitentyp, freier Speicherplatz auf der Seite und Objekt-ID des Objekts gespeichert werden, dem die Seite gehört. In SQL Server gibt es 9 Arten von Datenseiten.

• Data - Datenzeilen mit allen Daten außer Text-, Text- und Bilddaten.

• Index - Indexeinträge.

• Tex\Image - Text-, Bild- und Textdaten.

• GAM - Informationen zu zugewiesenen Ausdehnungen.

• SGAM - Informationen zu zugewiesenen Extents auf Systemebene.

• Page Free Space (PFS) - Informationen zum freien Speicherplatz auf den Seiten.

• Index Allocation Map (IAM) - Informationen zu den von einer Tabelle oder einem Index verwendeten Extents.

• Bulk Changed Map (BCM) - Informationen zu Extents, die durch Massenvorgänge seit der letzten Sicherungsprotokollanweisung geändert wurden.

• Differential Changed Map (DCM) - Informationen zu Extents, die sich seit der letzten Sicherungsdatenbankanweisung geändert haben.

Protokolldatei-Architektur

Das SQL Server-Transaktionsprotokoll arbeitet logisch so, als wäre das Transaktionsprotokoll eine Zeichenfolge von Protokolldatensätzen. Jeder Protokolldatensatz wird durch die Protokollsequenznummer (LSN) identifiziert. Jeder Protokolldatensatz enthält die ID der Transaktion, zu der er gehört.

Protokolldatensätze für Datenänderungen zeichnen entweder die durchgeführte logische Operation auf oder sie zeichnen die Vorher- und Nachher-Bilder der geänderten Daten auf. Das Vorher-Bild ist eine Kopie der Daten, bevor die Operation ausgeführt wird. Das Nachbild ist eine Kopie der Daten, nachdem der Vorgang ausgeführt wurde.

Die Schritte zum Wiederherstellen eines Vorgangs hängen von der Art des Protokolldatensatzes ab.

• Logischer Vorgang protokolliert.
  • Um die logische Operation vorwärts zu rollen, wird die Operation erneut ausgeführt.
  • Um die logische Operation zurückzusetzen, wird die umgekehrte logische Operation ausgeführt.
• Vor und nach dem Bild protokolliert.
  • Um den Vorgang vorwärts zu rollen, wird das Nachbild angewendet.
  • Um den Vorgang zurückzusetzen, wird das vorherige Bild angewendet.

Verschiedene Arten von Vorgängen werden im Transaktionsprotokoll aufgezeichnet. Diese Operationen umfassen -

• Beginn und Ende jeder Transaktion.

• Jede Datenänderung (Einfügen, Aktualisieren oder Löschen). Dies umfasst Änderungen durch gespeicherte Systemprozeduren oder DDL-Anweisungen (Data Definition Language) an einer Tabelle, einschließlich Systemtabellen.

• Jeder Umfang und jede Seitenzuweisung oder Zuweisung.

• Erstellen oder Löschen einer Tabelle oder eines Index.

Rollback-Vorgänge werden ebenfalls protokolliert. Jede Transaktion reserviert Speicherplatz im Transaktionsprotokoll, um sicherzustellen, dass genügend Protokollspeicherplatz vorhanden ist, um ein Rollback zu unterstützen, das entweder durch eine explizite Rollback-Anweisung oder durch Auftreten eines Fehlers verursacht wird. Dieser reservierte Speicherplatz wird freigegeben, wenn die Transaktion abgeschlossen ist.

Der Abschnitt der Protokolldatei vom ersten Protokolldatensatz, der für ein erfolgreiches datenbankweites Rollback zum zuletzt geschriebenen Protokolldatensatz vorhanden sein muss, wird als aktiver Teil des Protokolls oder aktives Protokoll bezeichnet. Dies ist der Abschnitt des Protokolls, der für eine vollständige Wiederherstellung der Datenbank erforderlich ist. Kein Teil des aktiven Protokolls kann jemals abgeschnitten werden. Die LSN dieses ersten Protokolldatensatzes wird als minimale Wiederherstellungs-LSN (Min. LSN) bezeichnet.

Das SQL Server-Datenbankmodul unterteilt jede physische Protokolldatei intern in mehrere virtuelle Protokolldateien. Virtuelle Protokolldateien haben keine feste Größe und es gibt keine feste Anzahl virtueller Protokolldateien für eine physische Protokolldatei.

Das Datenbankmodul wählt die Größe der virtuellen Protokolldateien dynamisch aus, während Protokolldateien erstellt oder erweitert werden. Das Datenbankmodul versucht, eine kleine Anzahl virtueller Dateien zu verwalten. Die Größe oder Anzahl der virtuellen Protokolldateien kann von Administratoren nicht konfiguriert oder festgelegt werden. Virtuelle Protokolldateien wirken sich nur dann auf die Systemleistung aus, wenn die physischen Protokolldateien durch kleine Werte und Werte für das Wachstumsinkrement definiert sind.

Der Größenwert ist die Anfangsgröße für die Protokolldatei und der Wert für grow_increment ist der Speicherplatz, der der Datei jedes Mal hinzugefügt wird, wenn neuer Speicherplatz benötigt wird. Wenn die Protokolldateien aufgrund vieler kleiner Schritte zu einer großen Größe werden, verfügen sie über viele virtuelle Protokolldateien. Dies kann den Datenbankstart verlangsamen und auch Sicherungs- und Wiederherstellungsvorgänge protokollieren.

Wir empfehlen, dass Sie Protokolldateien einen Größenwert zuweisen, der nahe an der erforderlichen Endgröße liegt, und einen relativ großen Wert für das Wachstum inkrementieren. SQL Server verwendet ein Write-Ahead-Protokoll (WAL), das garantiert, dass keine Datenänderungen auf die Festplatte geschrieben werden, bevor der zugehörige Protokolldatensatz auf die Festplatte geschrieben wird. Dadurch werden die ACID-Eigenschaften für eine Transaktion beibehalten.