SQLServer: Transaktionsverwaltung
Die Verwaltung von SQL Server-Transaktionen stellt einen wichtigen Schritt dar, um reibungslose Abläufe zu gewährleisten und Fehler in Zusammenhang mit Sperren und Blockierungen zu vermeiden.
Auszug aus "SQL Server DMV Starter Pack," veröffentlicht von Red Gate Bücher (2010).
Glenn Berry, Louis Davidson und Tim Ford
Suchen in abgestufte Ebenen der Verwaltung von SQL Server-Operationen, müssen Sie überlegen, wie Sie transaktionsbezogene Dynamic Management Objects (DMOs) verwalten. Alle dynamische Verwaltungssichten (DMVs) in der Kategorie "Transaktion Verwandte" von DMOs beginnen mit "sys.dm_tran_" als eine Eröffnungsaussage.
Schließlich ist jeder Anweisung für SQL Server ausgeführt, die transaktional. Wenn Sie eine einzelne SQL-Anweisung ausführen, wird eine implizite Transaktion im Hintergrund gestartet. Diese Anweisung startet und die automatische Vervollständigung. Verwenden Sie explizite BEGIN TRAN / COMMIT TRAN Befehle, Sie können diese zusammen als eine explizite Transaktion gruppieren — eine Reihe von Anweisungen, die fehlschlagen oder gemeinsam gelingen muss.
SQL Server implementiert verschiedene Isolationsstufen von Transaktionen, um die Eigenschaften Unteilbarkeit, Konsistenz, Isolation und Dauerhaftigkeit (Säure) aus diesen beiden Transaktionen sicherzustellen. In der Praxis bedeutet dies, dass es Sperren verwendet und Riegel zu vermitteln transaktionale Zugriff auf freigegebene Ressourcen und "Störung" zwischen den Transaktionen zu verhindern.
Im Allgemeinen werden Ihre Strategie und Prozesse für die Untersuchung und Verwalten von SQL Server-Transaktionen auf ein paar wichtige Fragen beschränkt:
- Welche Transaktionen sind aktiv, und welche Sitzungen werden sie ausgeführt? (Session_transactions, Active_transactions)
- Welche Transaktionen tun die meiste Arbeit? (Database_transactions)
- Welche Umsätze sind Sperren/blockierend Probleme verursacht? (Sperren)
Von diesen Gründen ist eine Untersuchung Sperren und blockieren bei weitem die häufigste Verwendung von diese DMVs. Eine Untersuchung, die immer häufiger werden weiterhin ist in der Aktivität generiert, wenn die SNAPSHOT-Isolationsstufe verwenden. Die Snapshot-Isolationsstufe wurde in SQL Server 2005 eingeführt. Snapshot-Isolation beseitigt Sperren und Deadlocks durch Verwendung eines Versionsspeichers in Tempdb-Datenbank zur Parallelität, anstatt zur Gründung Sperren für Datenbankobjekte. Es gibt eine Reihe von DMVs bereitgestellt, um diese Isolationsstufe zu untersuchen.
Monitor lang andauernder Transaktionen
Wollen wir nun weitergehen zu den Skripten. Sofern nicht anders angegeben, alle diese Abfragen arbeiten mit SQL Server 2005, 2008 und 2008 R2, und alle ist die VIEW SERVER STATE-Berechtigung erforderlich. Das Skript verwendet zwei DMVs. Die erste ist sys. dm_tran_database_transactions, die in SQL Server Books online (BOL) wie folgt beschrieben wird: "Gibt Informationen zu Transaktionen auf Datenbankebene zurück."
Die zweite ist sys.dm_tran_session_transactions, die einfach: "Liefert Korrelationsinformationen für damit verbundenen Transaktionen und Sitzungen."
Die knappe Beschreibung für Database_transactions widerlegt eher ihr potenzieller nutzen. Das folgende Skript enthält eine Abfrage, die pro Sitzung, welche Datenbanken sind im Einsatz durch eine offene Transaktion von dieser Sitzung, zeigt an, ob die Transaktion auf Lese-/ Schreibzugriff in einer der Datenbanken aktualisiert hat, (in der Standardeinstellung die meisten Transaktionen sind schreibgeschützt), wenn die Transaktion auf Lese-und Schreibzugriff für die Datenbank, wie viele Protokolldatensätze geschrieben und wie viele Bytes für die Protokolldatensätze verwendet wurden aktualisiert:
Wählen Sie st.session_id, DB_NAME(dt.database_id) AS Database_name, Fall, wenn dt.database_transaction_begin_time ist NULL, dann 'Read-only'
ELSE 'Read-Write' Ende AS Transaction_state, dt.database_transaction_begin_time Read_write_start_time, dt.database_transaction_log_record_count, dt.database_transaction_log_bytes_usedFROM sys.dm_tran_session_transactions als St INNER JOIN sys. dm_tran_database_transactions als dt
ON st.transaction_id = dt.transaction_idORDER BY st.session_id, database_name
Diese Art von Abfragen für Database_transactions sind sehr nützlich, wenn Sie Dinge wie überwachen:
- Sitzungen mit Lese-und Schreibzugriff Transaktionen (besonders wichtig zum schlafen Sitzungen)
- Sitzungen des Transaktionsprotokolls verursacht zu wachsen/aufblasen
- Der Fortschritt der lang andauernde Transaktionen (für nicht-Bulk protokollierte Vorgänge, jede betroffene Indexzeile produzieren etwa eine Transaktionsprotokoll-Datensatz)
Sperren und blockieren
Unser Beispielskript für die transaktionsbezogenen Kategorie der DMVs verwendet die sys. dm_tran_locks DMV, die von BOL wie folgt beschrieben wird:
"Gibt Informationen zu aktiven Sperren-Manager-Ressourcen zurück. Jede Zeile stellt eine aktive Anforderung an den Sperrenmanager für eine Sperre, die gewährt wurde oder gewährt werden wartet. Die Spalten im Resultset sind in zwei Hauptgruppen unterteilt: Ressource und Anforderung. Die Ressourcengruppe beschreibt die Ressource auf dem die Sperranforderung erfolgt, und die Anforderungsgruppe beschreibt die Sperranforderung."
Diese DMV ist nützlich bei der Sperren und blockierende Probleme auf Ihre Datenbankinstanzen zu ermitteln:
--Betrachten Sie active Sperren-Manager-Ressourcen für aktuelle Datenbank
Wählen Sie Request_session_id,
Db_name(resource_database_id) AS [Database], Resource_type, Resource_subtype, Request_type, Request_mode, Resource_description, Request_mode, request_owner_type
VON sys.dm_tran_locksWHERE Request_session_id > 50 Und Resource_database_id = DB_ID() und < Request_session_id > @@ SPIDORDER BY Request_session_id;
--Suchen Sie blockieren
Wählen Sie tl.resource_type, tl.resource_database_id, tl.resource_associated_entity_id, tl.request_mode, tl.request_session_id, wt.blocking_session_id, wt.wait_type, wt.wait_duration_msFROM sys. dm_tran_locks als tl INNER JOIN dm_os_waiting_tasks als wt ON tl.lock_owner_address = wt.resource_addressORDER BY Wait_duration_ms DESC;
Die erste Abfrage zeigt Lock-Typen und deren Status von SPID, von der aktuellen Datenbank gefiltert, und Beseitigung der aktuellen Verbindung und die System-SPIDs wiedergeben. Die zweite Abfrage enthält Informationen über alle blockieren, die möglicherweise auftretenden, instanzweite. Beachten Sie, dass diese zweite Abfrage mit der dm_os_waiting_tasks DMV verknüpft, Daten für die Dauer ein Prozesses gewartet hat, aufgrund der Blockade, und welche Ressource abrufen.
Wenn Sie schwere blockierende Probleme haben, müssen Sie in der Regel führen Sie jede dieser fragt mehrere Male, Sperrung zu fangen. Wenn Sie zwei Anweisungen zur Datenänderung identifizieren oder eine Abfrage und einer Datenänderung, die sind umfassend in schweren blockieren oder sogar Deadlocks, dann müssen Sie den SQL-Text für die Abfragen zu extrahieren, sie prüfen, führen Sie sie auf einem Testsystem — mit Profiler Ablaufverfolgung ausgeführt — und erarbeiten eine Möglichkeit um die Abfragen zu optimieren, oder fügen Sie Indizes, um das Problem zu lindern.
.png)
Glenn Berry arbeitet als ein Datenbankarchitekt NewsGator Technologies in Denver, Colorado Er ist ein SQL Server-MVP und hat eine ganze Sammlung von Microsoft-Zertifizierungen, einschließlich MCITP, MCDBA, MCSE, MCSD, MCAD und MCTS, was beweist, dass er gerne Tests machen.
.png)
Louis Davidson in der IT-Branche wurde seit 16 Jahren als Unternehmensdatenbank Entwickler und Architekten. Er ist seit sechs Jahren ein SQL Server Microsoft MVP und hat vier Bücher über Datenbankdesign geschrieben. Derzeit ist er der Datenarchitekt und manchmal DBA für Christian Broadcasting Network, Unterstützung von Büros in Virginia Beach, VA., und Nashville, Tennessee
.png)
Timothy Ford ist ein SQL Server-MVP und arbeitet seit mehr als 10 Jahren mit SQL Server. Er ist der primäre DBA und Experte für die SQL Server-Plattform für Spektrum Gesundheit. Er wurde schriftlich über Technologie seit 2007 für eine Vielzahl von Websites und verwaltet seinen eigenen Blog unter thesqlagentman.com, SQL als sowie Telearbeit und professionelle Entwicklungsthemen abdeckt.
Erfahren Sie mehr über "SQL Server DMV Starter Pack" bei red-gate.com/our-company/about/book-store.