Dieser Artikel wurde maschinell übersetzt.
SQLServer: Festplatten-I/O minimieren
Abfrageoptimierung und Indizierung ist ein effektives Verfahren, physische und logische Festplatten-I/O zu reduzieren.
Auszug aus "SQL Server DMV Starter Pack," von Red Gate Books (2010) veröffentlicht.
Glenn Berry, Louis Davidson und Tim Ford
Es muss eine persistente logische und physische e/a zu minimieren. Die Auflistung der I/O-bezogenen Datenbank-Management-Objekte (DMOs) hilft untersuchen insbesondere physische e/a statt auf Ihrem System, wenn Daten geschrieben und vom Datenträger gelesen.
Die DMOs in dieser Kategorie bieten eine explizite Bild Plattenzugriffe aus der Sicht des Datenträgersubsystems. Sie zeigen uns, z.B. Verteilung der i/o auf verschiedene Dateien auf der Festplatte platziert, wo die i/o ist immer einen Engpass und I/O Stände und So weiter. Sie können diese Informationen verwenden, um die Datenträger-Subsystem-Architektur zu optimieren. Sie könnten auch Daten sammeln und verwenden, um die Anforderungen an Führungskräfte Einheit für mehr Speicherkapazität zu unterstützen.
Einige physische e/a ist natürlich nicht zu vermeiden. SQL Server muss die Anwendungsdaten auf Datenträger geschrieben. Es hat auch zum Schreiben in das Transaktionsprotokoll für jede INSERT-, Update- und Delete und sogar für Massenvorgänge. Vor dem Sprung zu dem Schluss, dass Sie einfach mehr Festplatten-Leistung benötigen, beachten Sie jedoch gibt es viel Sie tun können, in Bezug auf die Abfrage tuning und Indizierung um unnötige logische und physische e/a zu minimieren.
Berücksichtigen Sie die I/O Informationen aus dem DMOs hier behandelt (alle welche beginnen mit "sys.dm_io_"), sowie Daten aus andere Dynamic Management Views (DMVs), die Serverleistung auf irgendeine Weise, verweisen einschließlich:
- dm_exec_query_stats – I/O, die eine bestimmte Abfrage über das mal gekostet hat, die es ausgeführt wurde
- dm_exec_connections – I/O, die über diese Verbindung stattgefunden hat
- sys. dm_exec_sessions – I/O, die getroffen hat statt, während dieser Sitzung
- dm_os_workers – i/o ausstehende für einen gegebenen Worker-thread
Alle Abfragen in diesem Abschnitt Arbeiten mit SQL Server 2005, 2008 und 2008 R2, und alle erfordern die View Server State-Berechtigung.
Datenträgerengpässen über i/o-Ständen zu untersuchen
Die DMV benutzen wir hier ist dm_io_virtual_file_stats, die beschreibt, wie SQL Server-Onlinedokumentation: "I/O-Statistiken für Daten-und Protokolldateien zurückgegeben. Dieser dynamischen Verwaltungssicht ersetzt die Fn_virtualfilestats-Funktion."
Diese DMV akzeptiert zwei Argumente: Database_id und File_id. Sie können für entweder eine NULL angeben. In diesem Fall gibt es Informationen zu allen Datenbanken oder alle Dateien zurück.
Beachten Sie, dass diese DMV akkumulativ. Mit anderen Worten, erhöhen sich die Werte in den Datenspalten kontinuierlich vom Punkt bei dem letzten des Servers Neustart. Dies bedeutet, dass Sie einen Vergleichswert, gefolgt von der eigentlichen Messung nehmen müssen. Dann subtrahieren Sie die beiden, so dass Sie sehen können, wo I/O ansammeln, ist.
Dieses Skript können Sie die Anzahl der Lesevorgänge zu sehen und schreibt auf jede Datei Daten- und Protokolldateien für jede Datenbank auf eine Instanz von SQL Server ausgeführt wird. Es wird Zeit, durchschnittliche I/O Stand, in Millisekunden sortiert:
-- Calculates average stalls per read, per write, and per total input/output -- for each database file. SELECT DB_NAME(database_id) AS [Database Name] , file_id , io_stall_read_ms , num_of_reads , CAST(io_stall_read_ms / ( 1.0 + num_of_reads ) AS NUMERIC(10, 1)) AS [avg_read_stall_ms] , io_stall_write_ms , num_of_writes , CAST(io_stall_write_ms / ( 1.0 + num_of_writes ) AS NUMERIC(10, 1)) AS [avg_write_stall_ms] , io_stall_read_ms + io_stall_write_ms AS [io_stalls] , num_of_reads + num_of_writes AS [total_io] , CAST(( io_stall_read_ms + io_stall_write_ms ) / ( 1.0 + num_of_reads + num_of_writes) AS NUMERIC(10,1)) AS [avg_io_stall_ms]FROM sys.dm_io_virtual_file_stats(NULL, NULL)ORDER BY avg_io_stall_ms DESC ;
Diese Abfrage zeigt Ihnen die wartet die längste für Datenträger e/a-Dateien. Damit können Sie entscheiden, wo Sie einzelne Dateien, die auf der Grundlage verfügbarer Ressourcen zu finden. Sie können auch verwenden, um davon zu überzeugen, dass jemand wie ein SAN-Ingenieur, dass SQL Server sehen, ist, dass die Festplatte für bestimmte Dateien Engpässen zu helfen.
Untersuchen von Datenträgerengpässen über ausstehende e/a
Dies nimmt einen etwas anderen Ansatz auf die Untersuchung von e/a-Engpässe. Verwenden Sie die dm_io_pending_io_requests DMV, die beschreibt, wie SQL Server-Onlinedokumentation: "Eine Zeile für jede ausstehende Anforderung I/O in SQL Server zurückgegeben."
Die Daten in der DMV stellt einen "Point in Time"-Snapshot der I/O anstehende Anforderungen auf Ihrem System, in dem Moment, den Sie das Skript ausführen:
-- Look at pending I/O requests by file SELECT DB_NAME(mf.database_id) AS [Database] , mf.physical_name ,r.io_pending , r.io_pending_ms_ticks , r.io_type , fs. num_of_reads , fs. num_of_writesFROM sys.dm_io_pending_io_requests AS r INNER JOIN sys.dm_io_virtual_file_stats(NULL, NULL) AS fs ON r.io_handle = fs.file_handle INNER JOIN sys.master_files AS mf ON fs.database_id = mf.database_id AND fs.file_id = mf.file_idORDER BY r.io_pending , r.io_pending_ms_ticks DESC ;
Da diese Daten einen Snapshot zu einem bestimmten Zeitpunkt der Tätigkeit darstellt, sollten Sie diese Abfrage mehrmals ausführen, um festzustellen, ob die gleichen Dateien (und den gleichen Laufwerkbuchstaben) konsequent an der Spitze der Liste auftauchen. Wenn das passiert, ist es Beweis der I/O Engpässe für diese bestimmte Datei oder Laufwerksbuchstaben. Dies können Sie helfen, Ihr SAN davon überzeugen, dass Techniker das System I/O für eine bestimmte LUN Probleme war.
Die letzten beiden Spalten in der Abfrage die kumulative Anzahl der Lese- und Schreibvorgänge für die Datei zurück, da SQL Server gestartet wurde (oder nach der Erstellung der Datei — je nachdem, was kürzer war). Diese Informationen sind hilfreich, wenn Sie versuchen zu entscheiden, welche RAID-Level für einen bestimmten Laufwerkbuchstaben verwenden. Beispielsweise werden Dateien mit mehr Schreibaktivitäten normalerweise besser auf ein RAID 10 LUN durchführen, als sie auf einem RAID-5-LUN werden.
Zu wissen, das relative Lese-Schreib-Verhältnis für jede Datei helfen Ihnen Ihre Datenbankdateien auf einem entsprechenden LUN zu platzieren. Dies hilft wiederum, Sie Ihre Abfragen für mehr Effizienz zu optimieren.
.png)
.png)
.png)
Glenn Berry arbeitet als ein Datenbankarchitekt bei 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, die beweist, dass er gerne Tests machen.
Louis Davidson in der IT-Branche war seit 16 Jahren als Unternehmensdatenbank Entwickler und Architekt. Er ist seit sechs Jahren ein SQL Server-Microsoft-MVP und hat vier Bücher über Datenbankdesign geschrieben. Er ist derzeit die Datenarchitekt und manchmal DBA für das Christian Broadcasting Network, Unterstützung von Büros in Virginia Beach, VA., und Nashville, Tennessee
Timothy Ford Ichs SQL Server MVP und arbeitet seit mehr als 10 Jahren mit SQLServer. Er ist der primäre DBA und Fachexperten für die SQL Server-Plattform für Spektrum Gesundheit. Er hat über Technologie seit 2007 für eine Vielzahl von Websites zu schreiben und führt seinen eigenen Blog unter thesqlagentman.com, SQL als auch Telearbeit und professionelle Entwicklungsthemen abdeckt.**
Erfahren Sie mehr über "SQL Server DMV Starter Pack" bei red-gate.com/our-company/about/book-store.