Moving Average Sql Server

SQL Server-Datenträger-Performance-Metriken Teil 2 weitere wichtige Festplatten-Performance-Maßnahmen Im vorherigen Teil der SQL Server Performance Metrics-Serie haben wir die wichtigsten und nützlichen Datenträger-Performance-Metriken vorgestellt. Nun zeigen wir auch andere wichtige Platten-Performance-Maßnahmen Aktuelle Disk-Warteschlangen-Länge Zeigt die Anzahl der Festplatten-Anfragen an, die gerade warten, sowie Anfragen, die derzeit gewartet werden. Vorbehaltlich breiter Variationen, es sei denn, die Arbeitsbelastung hat einen stationären Zustand erreicht und Sie haben eine ausreichende Anzahl von Proben gesammelt, um ein Muster zu etablieren. 1 Die Metrik zeigt, wie viele IO-Operationen darauf warten, auf die Festplatte geschrieben zu werden oder zu lesen und wie viele derzeit verarbeitet werden. Wenn die Festplatte nicht verfügbar ist, werden diese Operationen in die Warteschlange gestellt und werden verarbeitet, wenn die Festplatte verfügbar ist. Das gesamte Festplatten-Subsystem verfügt über eine einzige Warteschlange Die aktuelle Datenträger-Warteschlangen-Länge-Metrik in Windows-Systemmonitor ist sowohl für physische als auch für logische Datenträger verfügbar. In einigen früheren Versionen von Performance Monitor wurde dieser Zähler als Datenträger-Warteschlangenlänge bezeichnet. Der aktuelle Datenträger-Warteschlangen-Längenwert sollte weniger als 2 pro Plattenspindel betragen. Beachten Sie, dass dies nicht pro logisch, sondern pro physischen Datenträger ist. Wenn größer, bedeutet dies einen potenziellen Festplatten-Engpass, so dass weitere Untersuchungen und Überwachung anderer Festplatten-Metriken empfohlen wird. Beginnen Sie mit der Überwachung der Festplattenzeit (siehe unten). Häufige Peaks sollten auch untersucht werden Disk Array-Systeme wie RAID oder SAN haben eine große Anzahl von Festplatten und Controller, die Warteschlangen auf solchen Systemen kürzer macht. Da die Metrik nicht die Warteschlange pro Festplatte anzeigt, aber für das gesamte Array, glauben einige DBAs, dass die Überwachung der aktuellen Festplatten-Warteschlangenlänge auf Festplatten-Arrays nicht benötigt wird. Ein weiteres Szenario, in dem die aktuelle Datenträger-Warteschlangenlänge irreführend sein kann, ist, wenn Daten im Datenträger-Cache gespeichert sind. Es wird als in der Warteschlange für das Schreiben gemeldet werden und damit die aktuelle Festplatte Warteschlange Länge Wert wird höher als tatsächliche durchschnittliche Festplatte Warteschlange Länge Die durchschnittliche Festplatte Warteschlange Länge Metrik zeigt die Informationen ähnlich wie aktuelle Festplatte Warteschlange Länge. Nur der Wert ist nicht aktuell, aber durchschnittlich über einen bestimmten Zeitraum. Die Schwelle ist die gleiche wie für die vorherige Metrik bis zu 2 pro Festplatte. Bei Festplattensystemen beträgt der empfohlene Wert weniger als 2 pro einzelne Festplatte in einem Array. Beispielsweise gibt es in einem 6-Platten-Array den aktuellen Datenträger-Warteschlangen-Längenwert von 12, dass die Warteschlange 2 pro Datenträger ist. Es gibt zwei weitere Metriken, die der durchschnittlichen Datenträger-Warteschlangenlänge entsprechen 8211 Durchschnittliche Festplatten-Lese-Warteschlangenlänge und durchschnittliche Datenträger-Schreib-Warteschlangenlänge. Wie ihre Namen zeigen, zeigen sie die durchschnittliche Warteschlangenlänge für Operationen, die darauf warten, dass der Datenträger gelesen oder geschrieben wird. Disk Time Dieser Zähler weist auf ein Disk-Problem hin, muss aber in Verbindung mit dem Current Disk Queue Length-Zähler beobachtet werden, um wirklich informativ zu sein. Erinnern Sie sich auch, dass die Festplatte ein Engpass sein könnte, bevor die Festplattenzeit 100 erreicht wird. Die Festplattenzeitmetrik zeigt an, wie beschäftigt der Datenträger die Lese - und Schreibanforderungen bedient, aber wie oben erwähnt, ist es kein klarer Hinweis auf ein Problem als seine Werte Kann normal sein, während theres eine ernsthafte Scheibenleistung Problem. Sein Wert ist der durchschnittliche Datenträger-Warteschlangen-Längenwert, der in Prozente dargestellt ist (d. h. multipliziert mit 100). Wenn die durchschnittliche Datenträger-Warteschlangenlänge 1 ist, ist die Festplattenzeit 100 Was kann verwirrend sein, dass die Festplatten-Zeitwerte über 100 liegen können, was nicht logisch ist. Dies geschieht, wenn der durchschnittliche Datenträger-Warteschlangen-Längenwert größer als 1 ist. Wenn die durchschnittliche Datenträger-Warteschlangenlänge 3 ist, ist die Festplattenzeit 300, was bedeutet, dass die Prozesse 3 Mal mehr Festplattenzeit verwenden als verfügbar, noch dass es einen Engpass gibt. Wenn Sie Haben Sie eine Festplatte Array, die gesamte Festplatte Zeit für alle Festplatten angezeigt wird, ohne die Angabe, wie viele Festplatten verfügbar sind und welche Festplatte hat die höchste Festplatte Zeit. Zum Beispiel könnte die Festplattenzeit gleich 500 eine gute Leistung anzeigen (falls Sie 6 Festplatten haben) oder extrem schlecht (falls Sie nur 1 Festplatte haben). Sie können nicht sagen, ohne zu wissen, die Maschine Hardware Da dieser Zähler kann irreführend sein, einige DBAs nicht verwenden, da es andere einfachere und indikative Metriken, die Platten-Performance zeigen Wenn der Wert höher als 90 pro Festplatte ist, ist zusätzliche Untersuchung erforderlich. Zuerst überprüfen Sie den aktuellen Datenträgerwarteschlangenwert. Wenn seine höher als die Schwelle (2 pro physikalische Festplatte), überwachen, wenn die hohen Werte häufig auftreten. Wenn die Maschine nicht nur für SQL Server verwendet wird, können andere ressourcenintensive Anwendungen zu Diskettenengpässen führen, so dass die SQL Server-Leistung leiden wird. Wenn dies der Fall ist, sollten Sie diese Anwendungen auf eine andere Maschine verschieben und eine dedizierte Maschine nur für SQL Server verwenden. Wenn dies nicht der Fall ist oder nicht getan werden kann, sollten Sie einige der Dateien auf andere Datenträger - oder Computerarchivdatenbanken, Datenbank und verschieben Transaktionsprotokollsicherungen, Verwendung einer schnelleren Festplatte oder Hinzufügen zusätzlicher Festplatten zu einem Array Datenträger-Lesezeit und der Festplatten-Schreibzeit Die Festplatten-Lesezeit und die Festplatten-Schreibzeit-Metriken ähneln der Festplattenzeit. Nur zeigt die Operationen gelesen oder geschrieben auf Festplatte, respectively. Sie sind eigentlich die durchschnittliche Festplatten-Lese-Warteschlangenlänge und durchschnittliche Festplatten-Schreib-Warteschlange. Die Werte, die diese Metriken zeigen, können gleichermaßen irreführend sein wie die Festplatten-Zeit Auf einem Drei-Platten-Array-System, wenn eine Festplatte 50 der Zeit liest (Festplatten-Lesezeit 50), die andere liest 85 der Zeit, und die dritte ist im Leerlauf, Festplatten-Lesezeit ist 135 und durchschnittliche Festplatten-Lese-Warteschlange Länge 1.35. Auf den ersten Blick sieht die Disk Read Time gleich 135 aus wie ein Problem, aber es ist nicht so. Es bedeutet nicht, dass Festplatten beschäftigt sind 135 der Zeit. Um einen echten Wert zu erhalten, solltest du den Wert mit der Anzahl der Festplatten teilen und youll 1363 45 erhalten, was die normale Leistung anzeigt. Leerlaufzeit Die Festplatte ist im Leerlauf, wenn sie keine Lese - und Schreibanforderungen verarbeitet. Dies misst den Prozentsatz der Zeit, in der die Festplatte im Leerlauf war Während des Stichprobenintervalls. Wenn dieser Zähler unter 20 Prozent fällt, ist das Plattensystem gesättigt. Sie können erwägen, das aktuelle Festplattensystem durch ein schnelleres Plattensystem zu ersetzen. 3 Wenn der Wert kleiner als 20 ist, ist die Festplatte nicht in der Lage, alle Lese - und Schreibanforderungen rechtzeitig zu bedienen. Bevor Sie sich für den Plattenaustausch entscheiden, überprüfen Sie, ob es möglich ist, einige Anwendungen auf eine andere Maschine zu entfernen. Free Space Neben Windows Performance Monitor ist diese Metrik im Windows Explorer auf den Registerkarten Computer und Disk Properties verfügbar. Während der Leistungsmonitor den Prozentsatz des verfügbaren freien Speicherplatzes anzeigt, zeigt der Windows Explorer den Betrag in GB an. Dies misst den Prozentsatz des freien Speicherplatzes auf dem ausgewählten logischen Laufwerk. Beachten Sie, wenn dies unter 15 Prozent fällt, da Sie Gefahr laufen, freien Speicherplatz für das Betriebssystem zu haben, um kritische Dateien zu speichern. Eine offensichtliche Lösung hier ist, um mehr Speicherplatz hinzuzufügen. 3 Wenn der Wert plötzliche Peaks ohne offensichtliche Gründe zeigt, ist eine weitere Untersuchung erforderlich. Anders als die meisten Speicher - und Prozessor-SQL Server-Performance-Metriken können Disk-Metriken ziemlich trügerisch sein. Sie könnten nicht eindeutig ein Leistungsproblem angeben, deren Werte in Ordnung sein könnten, wenn es tatsächlich eine ernsthafte Disk-Ausgabe gibt, während ihre seltsam hohen Werte eine normale Leistung zeigen können, da sie Werte für ein Array von Festplatten anzeigen. Dann kommt es zu Array-Metriken, Hardware-Konfiguration Wissen ist notwendig, um sie richtig zu lesen. Trotz dieser Disk-Metriken Downsides, sind sie notwendig für SQL Server Performance TroubleshootingSQL Server IO Performance Alles, was Sie brauchen, um zu beachten SQL Server IO Leistung ist entscheidend für die Gesamtleistung. Der Zugriff auf Daten auf der Festplatte ist viel langsamer als im Speicher, so dass das Beste aus der lokalen Festplatte und SAN ist wichtig. Es gibt eine Menge Ratschläge im Internet und in Büchern über SQL Server IO Leistung, aber ich habe nicht eine einzige Liste, die alles zu betrachten. Das ist mein Versuch, alle Informationen an einem Ort zusammenzubringen. Also hier ist eine Liste von allem, was ich denken kann, dass IO Leistung beeinflussen kann. Ich habe es angefangen, an den physischen Festplatten zu starten und den Draht zum Server zu bewegen und schließlich das Code - und Datenbankschema. Fehlgeschlagene Festplatte Wenn ein Laufwerk in einem Festplatten-Array fehlschlägt, muss es ersetzt werden. Die Auswirkungen auf die Leistung vor dem Ersatz hängt von der Speicher-Array und RAID-Konfiguration verwendet. RAID 5 und RAID 6 verwenden verteilte Parität, und diese Parität wird verwendet, um die Lesevorgänge zu berechnen, wenn ein Datenträger ausfällt. Leseleistung verliert den Vorteil des Lesens von mehreren Festplatten. Dies gilt auch, wenn auch in geringerem Maße, auf RAID 1 (gespiegelte) Arrays. Liest den Vorteil des Lesens von mehreren Streifen für Daten auf der ausgefallenen Festplatte, und schreibt kann etwas langsamer aufgrund der Erhöhung der durchschnittlichen Suchzeit. Cache schreiben Wenn eine Transaktion begangen wird, muss das Schreiben in das Transaktionsprotokoll abgeschlossen sein, bevor die Transaktion als begangen markiert ist. Dies ist wichtig, um die Transaktionsintegrität zu gewährleisten. Es war früher so, dass der Schreib-Cache nicht empfohlen wurde, aber viele der neuesten Speicher-Arrays haben batteriebetriebene Caches, die vollständig für den Einsatz mit SQL Server zertifiziert sind. Wenn Sie die Möglichkeit haben, die Speicherverteilung zwischen Lese - und Schreib-Cache zu variieren, versuchen Sie, dem Schreib-Cache so viel wie möglich zuzuordnen. Dies liegt daran, dass SQL Server ein eigenes Lese-Caching über den Pufferpool durchführt, so dass jeder zusätzliche Lese-Cache auf dem Festplattencontroller keinen Nutzen hat. Thin Provisioning Thin Provisioning ist eine Technologie, die von einigen SANs bereitgestellt wird, wobei der verwendete Festplattenspeicher gerade genug für die Daten ist, während er dem Server in voller Größe erscheint, mit viel freiem Speicherplatz. Wenn die Gesamtmenge, die allen Servern zugewiesen wird, den Betrag der physischen Speicherung übersteigt, wird dies als Über-Bereitstellung bezeichnet. Einige SAN-Anbieter versuchen zu behaupten, dass Leistung nicht betroffen ist, aber das ist nicht immer wahr. Ich habe dieses Problem vor kurzem auf einem 3PAR Array gesehen. Sequentielle Lesungen waren signifikant langsamer auf dünn versorgten LUNs. Das Umschalten auf dick versorgte LUNs verdoppelte den sequentiellen Durchlauf. Wo sind die Scheiben Sind sie wo Sie denken, dass sie sind Es ist vollkommen möglich, mit einem Speicher-Array verbunden zu sein, aber für die IO-Anfragen, um dieses Array zu einem anderen zu übergeben. Dies ist manchmal als ein billiger Weg, um Speicherplatz zu erhöhen - mit vorhandenen Hardware, die nicht ausgelastet ist weniger kostspielig als Kauf mehr Festplatten. Das Problem ist, dass dies eine weitere Komponente in den Weg einführt und die Leistung beeinträchtigt - und der DBA kann sich dessen nicht einmal bewusst sein. Stellen Sie sicher, dass Sie wissen, wie das SAN konfiguriert ist. Smart Tiering Dies wird von verschiedenen Anbietern als unterschiedliche Dinge bezeichnet. Das Speicher-Array besteht aus zwei oder mehr Arten von Festplatten, von unterschiedlicher Leistung und Kosten. Es gibt die langsameren 10K Scheiben - das sind die billigsten. Dann hast du die 15K Scheiben. Diese sind schneller, aber teurer. Und dann kann es einige super-schnelle SSDs geben. Diese sind noch teurer, obwohl der Preis nach unten kommt. Smart-Tiering migriert Daten zwischen den Ebenen, so dass häufiger zugegriffene Daten auf dem schnelleren Speicher sind, während weniger häufig verwendete Daten auf den langsameren Speicher fallen. Das ist grundsätzlich OK, aber du bist der DBA. Sie sollten schon wissen, welche Daten schnell abgerufen werden müssen und die langsamer sein können. Wollen Sie wirklich einen Algorithmus, der diese Entscheidung für Sie macht und regelmäßige Wartungsaufgaben können das Ganze trotzdem verwechseln. Betrachten Sie eine Last von Index-Wiederaufbauten, die über Nacht laufen. Nehmen wir an, dass die letzte zu verarbeitende Datenbank eine Archivdatenbank ist - wollen Sie, dass dies die SSD hoggt, wenn die Benutzer das erste Mal morgens anmelden, während die geschäftskritische Datenbank in der unteren Stufe schmachtet. Dies ist eine Vereinfachung, Na sicher. Die tiering Algorithmen sind anspruchsvoller als das, aber mein Punkt steht. Sie sollten die Prioritäten für Ihre SQL Server-Daten entscheiden. Lassen Sie sich nicht von den SAN-Anbietern (oder Speicheradministratoren) überzeugen. Storage Level Replikation Die Replikation auf Storage-Ebene ist eine Disaster Recovery-Funktion, die Block-Level-Daten vom primären SAN zu einem anderen kopiert - oft in einem separaten Rechenzentrum. Die SAN-Anbieter behaupten keinen Einfluss auf die Leistung, und das trifft zu, wenn sie richtig konfiguriert ist. Aber ich habe gesehen, schlecht konfigurierte Replikation haben einen ernsthaften Einfluss auf die Leistung. Ein Klient erlitt ein paar Jahre schlechte IO Leistung. Als ich zu ihnen kam, fragte ich, ob die Speicherreplikation verantwortlich war. Mir wurde gesagt, dass ich nicht so dumm sein sollte - der Verkäufer hat überprüft und es ist nicht das Problem - es muss SQL Server selbst sein. Ein paar Monate später wurde ich wieder kontaktiert - sie hatten die Replikation ausgeschaltet, während sie sich in einen neuen bewegten Rechenzentrum und erraten, was Schreiben Latenz verbessert um eine Größenordnung. Lassen Sie mich wiederholen, dass dies durch eine schlechte Konfiguration verursacht wurde und die meisten Speicherreplikation die Performance nicht spürbar beeinträchtigt. Aber seine andere Sache zu prüfen, ob youre kämpfen mit SQL Server IO Leistung. Host-Busadapter Überprüfen Sie, ob die SAN - und HBA-Firmware kompatibel sind. Manchmal, wenn ein SAN aktualisiert wird, werden die HBAs auf den Servern übersehen. Dies kann zu unregelmäßigen Fehlern führen oder sogar die Lagerung unzugänglich machen. Schauen Sie sich die HBA-Warteschlangen-Tiefe an. Eine gemeinsame Voreinstellung ist 32, die möglicherweise nicht optimal ist. Einige Studien haben gezeigt, dass die Erhöhung dieser auf 64 oder höher die Leistung verbessern kann. Es könnte auch die Dinge schlimmer machen, je nach Arbeitsbelastung, SAN machen und modellieren, Plattenlayout, etc. So testen Sie gründlich, wenn Sie können. Einige Speicher-Admins entmutigen die Änderung der HBA-Warteschlangen-Tiefe, da sie denken, dass jeder das gleiche auf ihren Servern wünscht und das Speicher-Array wird überschwemmt. Und sie haben recht, auch überreden sie, dass es nur für dich ist. Versprechen, niemandem etwas zu erzählen. Was auch immer. Holen Sie sich einfach Ihre zusätzliche Warteschlange Tiefe, wenn Sie denken, es wird Leistung profitieren. Zu viele Server Wenn ein Unternehmen ein kleines Vermögen auf einem Speicherbereichsnetzwerk abgibt, wollen sie Wert für Geld bekommen. Also natürlich, jeder neue Server, der kommt, wird angeschnallt, so dass er von all dem schönen Speicherplatz Gebrauch machen kann. Dies ist in Ordnung, bis ein paar Server beginnen, eine Menge von IO-Anfragen und andere Benutzer beschweren sich über eine Leistungsverlangsamung. Das ist etwas, was ich bei so vielen Kunden immer wieder sehe, und es gibt keine einfache Lösung. Das Unternehmen will nicht oder kann es sich nicht leisten, ein anderes SAN zu kaufen. Wenn Sie denken, dass dies ein Problem für Sie ist, setzen Sie einen Zeitplan zusammen von allen Jobs - über alle Server - und versuchen, einige umzusetzen, damit die Arbeitsbelastung gleichmäßiger verteilt wird. Partition Alignment und Formatierung Ich werde kurz die Partitionsausrichtung erwähnen, obwohl Windows 2008 einen Standard-Offset von 1MB verwendet, also ist dies weniger ein Problem als früher. Ich bin auch nicht davon überzeugt, dass viele moderne SANs viel von der Praxis profitieren. Ich habe einen Test auf einem EVA vor ein paar Jahren und fand nur eine 2 Verbesserung. Dennoch sind noch ein paar Prozent angestrebt. Leider musst du deine Bände zerreißen und deine Partitionen neu erstellen, wenn dies auf einem bestehenden System behoben werden soll. Dies ist wahrscheinlich nicht der Mühe wert, es sei denn, Sie streben für jeden letzten Zentimeter Leistung. Formatierung ist etwas anderes, das korrekt durchgeführt werden sollte. SQL Server speichert Daten in 8KB Seiten, aber diese werden in Blöcken von 8, genannt Extents abgerufen. Wenn die Festplatten mit 64KB-Zuweisungseinheiten formatiert sind, kann dies einen signifikanten Leistungsvorteil haben. Multipathing Wenn Sie keine lokale Festplatte verwenden, dann sollten Sie eine Redundanz in Ihr Speichersubsystem eingebaut haben. Wenn Sie ein SAN haben, haben Sie ein kompliziertes Netzwerk von HBAs, Gewebe, Switches und Controllern zwischen SQL Server und den Festplatten. Es sollten mindestens zwei HBAs, Switches etc. vorhanden sein, und diese sollten alle so miteinander verbunden werden, dass es mehrere Pfade zu den Festplatten gibt. Diese Redundanz ist in erster Linie für hohe Verfügbarkeit, aber wenn das Multipathing als aktiv konfiguriert wurde, können Sie auch Leistungsvorteile sehen. Network Attached Storage Seit SQL Server 2008 R2 ist es möglich, eine Datenbank auf einer Dateifreigabe zu erstellen, wiederherzustellen oder zu binden. Dies hat eine Reihe von möglichen Verwendungen, und vor allem für devtest Umgebungen kann es Kapazitätsmanagement einfacher machen und bewegte Datenbanken zwischen Servern viel schneller machen. Die Frage, die gefragt werden muss, ist aber, wenn du das in der Produktion willst. Die Leistung wird nicht so gut sein wie die lokalen oder SAN-Laufwerke. Es gibt zusätzliche Komponenten in der Kette, so dass die Zuverlässigkeit nicht so gut sein kann. Und mit dem Netzwerk, Ihre Daten verwendet die gleiche Infrastruktur wie alle anderen TCPIP-Verkehr, die wiederum die Leistung beeinflussen könnte. Aber theres gute Nachrichten Während die Verfügbarkeit noch eine Sorge ist, haben Verbesserungen in SMB auf Windows Server 2012 (und über ein Update zu WIndows Server 2008 R2) es deutlich schneller gemacht. Ich sah ein Zitat von einem Microsoft-Mitarbeiter irgendwo, dass 97 der Leistung der lokalen Speicherung behauptet. Ich kann das Zitat jetzt nicht finden, und ich erinnere mich nicht, ob er Latenz oder Durchsatz maß. Disk Fragmentierung Wie oft verwenden Sie das Disk Defragmenter Tool auf Ihrem PC zu analysieren und zu defragmentieren Ihr C: Laufwerk Wie oft überprüfen Sie Fragmentierung auf den Festplatten auf Ihrem SQL Server Für die meisten Menschen, die nirgendwo so nah ist, krank wetten. Doch Volumen Fragmentierung ist genauso schädlich für SQL Server Leistung wie es ist auf Ihrem PC. Sie können die Wahrscheinlichkeit der Festplattenfragmentierung in einer Reihe von Möglichkeiten reduzieren: Pre-Size-Daten und Protokolldateien, anstatt sich auf Auto-Wachstum zu setzen. Set-Auto-Wachstum-Inkremente auf sinnvolle Werte anstelle der Default 10 Vermeiden Sie schrumpfende Daten und Log-Dateien Niemals, Verwenden Sie immer die Autoshrink-Datenbank-Option Stellen Sie sicher, dass Festplatten für SQL Server gewidmet sind und nicht mit anderen Anwendungen geteilt werden Sie können Fragmentierung mit dem gleichen Tool wie auf Ihrem PC überprüfen. Disk Defragmenter ist auf allen Server-Versionen von Windows verfügbar. Eine weitere Möglichkeit zu überprüfen ist über die Win32Volume-Klasse in WMI. Dieses Bit von PowerShell meldet die Datei Prozent Fragmentierung für alle Volumes auf einem bestimmten Server. Wenn Sie eine bedeutende Fragmentierung haben, gibt es ein paar Möglichkeiten, es zu beheben. Meine bevorzugte Option ist wie folgt, erfordert aber einige Ausfallzeiten. Stoppen Sie die SQL-Dienste Sichern Sie die Dateien auf der Festplatte (vor allem mdf, ndf und ldf-Dateien - besser sicher als sorry) Führen Sie das Windows Disk Defragmenter-Tool Starten Sie die SQL-Dienste Überprüfen Sie das Fehlerprotokoll, um keine Fehler beim Start zu gewährleisten Führen Sie CHECKDB gegen alle Datenbanken ( Außer tempdb). Ich habe noch nie gesehen, dass das Defrag-Tool Korruption verursacht, aber du kannst nicht zu vorsichtig sein. Eine andere Option, die keine Ausfallzeiten erfordert, ist die Verwendung eines Drittanbieter-Tools wie Diskeeper. Dies kann sehr effektiv bei der Festsetzung und Vermeidung von Scheiben Fragmentierung, aber es kostet Geld und verwendet einen Filter-Treiber - siehe meine Kommentare unten. Filter-Treiber Ein Filter-Treiber ist ein Stück Software, die zwischen einer IO-Anfrage und dem Schreiben auf Festplatte sitzt. Es erlaubt das Schreiben geprüft und abgelehnt, modifiziert oder auditiert. Die häufigste Art von Filter-Treiber wird von Anti-Virus-Software installiert. Sie wollen nicht, dass Antiviren-Software jedes einzelne Schreiben in Ihre Datenbankdateien überprüft. Sie wollen auch nicht, dass es Ihre Backups entweder überprüft oder schreibt in das Fehlerprotokoll oder Standard-Trace. Wenn Sie AV-Software installiert haben, können Sie Ausschlüsse angeben. Schließen Sie alle von SQL Server verwendeten Ordner sowie die von Daten - und Protokolldateien verwendeten Laufwerke sowie die für Sicherungen verwendeten Ordner aus. Noch besser ist, um Online-AV-Überprüfung auszuschalten, und planen Sie einen Scan in einer ruhigen Zeit. OLTP und BI auf dem gleichen Server Es ist selten, ein System zu finden, das rein OLTP ist. Die meisten werden auch irgendeine Art von Berichtselement haben. Leider sind die beiden Arten der Arbeitsbelastung nicht immer koexistieren. Ich habe eine Menge von Artikeln von Joe Chang gelesen, und in einem Artikel erklärt er, warum dies der Fall ist. Im Wesentlichen werden OLTP-Abfragepläne Zeilen in kleinen Batches abrufen (kleiner als ein Schwellenwert von 25 Zeilen) und diese IO-Anfragen werden synchron von der Datenbank-Engine abgewickelt, was bedeutet, dass sie darauf warten, dass die Daten abgerufen werden, bevor sie fortfahren. Große BI-Workloads und Reporting-Abfragen, oft mit parallelen Plänen, stellen asynchrone IO-Anfragen aus und nutzen die HBA-Fähigkeit, Anfragen anzufordern. Infolgedessen müssen die OLTP-Anfragen hinter den BI-Anfragen in die Warteschlange treten, wodurch die OLTP-Leistung erheblich abgebaut wird. Auto-wachsen und Instant File Initialisierung Es ist gut, Auto-wachsen aktiviert, genau wie eine Vorsichtsmaßnahme, obwohl Sie auch vor-Größe Daten und Log-Dateien, so dass es selten benötigt wird. Allerdings, was passiert, wenn eine Datendatei wächst und Sie haben keine Instant-Datei-Initialisierung aktiviert Besonders wenn die Auto-Wachsen zu groß eingestellt ist. Alle IO gegen die Datei muss warten, bis das Dateigrößen abgeschlossen ist, und dies kann in den berüchtigten Ziffern gemeldet werden, die länger als 15 Sekunden dauern, um die Nachricht im Fehlerprotokoll zu vervollständigen. Instant Initialisierung wird nicht helfen, mit Log-Wachstum, so stellen Sie sicher, Log-Auto-Wachstum Inkremente sind nicht zu hoch. Weitere Informationen über die Initialisierung der Sofortdatei und die Aktivierung finden Sie unter diesem Link Datenbankdatei-Initialisierung. Und während auf dem Thema Auto-wachsen, siehe den Abschnitt über Proportional-Fill, unten. Transaktionsprotokoll Leistung Wie lange dauert Ihr Transaktionsprotokoll: Weniger als 1ms Mehr als 5ms Schauen Sie sich die virtuellen Dateistufen, Leistungszähler oder die WRITELOG Wartezeit an, um zu sehen, ob die Log-Latenz ein Problem für Sie ist. Writes an die Transaktionsprotokoll sind sequentiell, und so sollte der Schreibkopf auf der Festplatte idealerweise dort sein, wo es aus dem letzten Log-Schreiben war. Das bedeutet keine suche zeit und glänzend schnell mal schreiben Und da eine Transaktion nicht begehen kann, bis das Protokoll auf die Festplatte gehärtet ist, verlassen Sie sich auf diese schnellen Schreibvorgänge für ein performantes System. Die Beratung seit Jahren ist für das Transaktionslog für jede Datenbank auf der eigenen Festplatte. Und dieser Rat ist immer noch gut für lokale Festplatte, und für einige Speicher-Arrays. Aber jetzt, da viele SANs einen eigenen batteriegepufferten Schreib-Cache haben, ist dieser Rat nicht so kritisch wie früher. Vorausgesetzt, der Cache ist groß genug, um mit Peak-Bursts von Schreib-Aktivität zu bewältigen (und sehen Sie meine früheren Kommentare über die Zuweisung von mehr Cache zu schreiben, als zu lesen) Sie erhalten sehr niedrige Latenz. Also was, wenn du nicht den Luxus von einem Mega-Bucks-SAN und Lasten von Schreib-Cache hast Dann ist der Rat, der seit den 1990er Jahren herum ist, noch gültig: Eine Transaktionsprotokolldatei pro Datenbank auf eigenem Laufwerk RAID 1, RAID 10 oder RAID 01 Angenommen, Sie sind glücklich mit Ihrem Log-Datei-Layout, was sonst könnte verlangsamen Ihr Protokoll schreibt virtuelle Log-Dateien Obwohl ein Transaktionsprotokoll nacheinander geschrieben wird, kann die Datei selbst intern zersplittert werden. Wenn es zuerst erstellt wird, besteht es aus mehreren Chunks, die als virtuelle Protokolldateien bezeichnet werden. Jedes Mal, wenn es gewachsen ist, ob manuell oder automatisch, werden mehrere virtuelle Log-Dateien hinzugefügt. Ein Transaktionslog, das mehrmals wächst, kann mit Tausenden von virtuellen Protokolldateien enden. Mit zu vielen VLFs kann die Protokollierung verlangsamen und kann auch Log-Backups verlangsamen. Du musst auch vorsichtig sein, um VLFs zu vermeiden, die zu groß sind. Eine inaktive virtuelle Protokolldatei wird nicht gelöscht, bis das Ende erreicht ist und die nächste beginnt zu verwenden. Für das vollständige Wiederherstellungsmodell passiert dies nicht, bis das nächste Protokoll-Backup. Also ein Log-Backup wird plötzlich viel mehr Arbeit, und kann dazu führen, dass Leistungsprobleme, während es stattfindet. Die Antwort für ein großes Transaktionslog ist, eine Anfangsgröße von maximal 8000MB festzulegen und dann manuell in Stücke von 8000MB bis zur Zielgröße zu wachsen. Dies führt zu einer maximalen VLF-Größe von 512 MB, ohne eine übermäßig große Anzahl von VLFs zu erzeugen. Hinweis: Dieser Rat ist nur für manuelles Wachstum. Nicht auto wachsen um 8000MB Alle Transaktionen in der Datenbank stoppen, während der zusätzliche Platz initialisiert wird. Autogrow sollte viel kleiner sein - aber versuchen Sie, die Datei manuell zu formatieren, damit das Auto wachsen wahrscheinlich nicht benötigt wird. Log Manager Limits Die Datenbank-Engine setzt Grenzen für die Menge an Log, die im Flug zu einem beliebigen Zeitpunkt sein kann. Dies ist ein pro-Datenbank-Limit und hängt von der Version des verwendeten SQL-Servers ab. SQL Server beschränkt die Anzahl der herausragenden IOs und MB pro Sekunde. Die Grenzen variieren mit der Version und ob 32 Bit oder 64 Bit. Weitere Informationen finden Sie unter Diagnose von Transaktionslog-Leistungsproblemen und Limits des Protokollmanagers. Aus diesem Grund sollte die Schreiblatenz so gering wie möglich sein. Wenn es 20ms dauert, um in das Transaktionslog zu schreiben, und Sie sind auf 32 IOs im Flug zu einem Zeitpunkt begrenzt, das bedeutet, dass maximal 1600 Transaktionen pro Sekunde, weit unter dem, was eine Menge von hohen Volumen OLTP-Datenbanken benötigen. Dies unterstreicht auch, wie wichtig es ist, die Transaktionsgrößen klein zu halten, da eine sehr große Transaktion andere Transaktionen vorhersehen könnte, während sie sich verpflichtet hat. Wenn Sie der Meinung sind, dass diese Grenzwerte die Protokollschreibleistung in Ihren Datenbanken beeinträchtigen, gibt es mehrere Möglichkeiten, das Problem anzugehen: Arbeiten Sie mit der Erhöhung der Protokollschreibleistung Wenn Sie minimal protokollierte Operationen haben, können Sie die Datenbank umschalten, um das BULK LOGGED-Wiederherstellungsmodell zu verwenden. Vorsichtig - ein Log-Backup mit einem minimal angemeldeten Betrieb muss komplett wiederhergestellt werden. Punkt in der Zeitwiederherstellung ist nicht möglich. Aufteilen einer hochvolumigen Datenbank in 2 oder mehr Datenbanken, da die Protokollgrenzen pro Datenbank gelten Nicht sequenzielle Protokollaktivität Es gibt Aktionen, die von der Datenbank-Engine ausgeführt werden, die den Schreibkopf vom Ende der Protokolldatei weg bewegt. Wenn Transaktionen noch begangen werden, während dies geschieht, haben Sie einen Such-Overhead und Log-Performance wird schlechter. Operationen, die aus den Protokolldateien lesen, umfassen das Zurücksetzen von großen Transaktionen, Protokollsicherungen und die Replikation (der Log-Reader-Agent). Es gibt wenig, was man über die meisten von diesen tun kann, aber das Vermeiden großer Rollbacks ist etwas, das bei der Entwurfs - und Entwicklungsstufe einer Anwendung angegangen werden sollte. Proportional Fill Sehr aktive Tabellen können in einer Dateigruppe platziert werden, die mehrere Datendateien hat. Dies kann die Leseleistung verbessern, wenn sie sich auf verschiedenen physischen Festplatten befinden, und sie können die Schreibleistung verbessern, indem sie die Konflikte in den Zuweisungsseiten (insbesondere für tempdb) einschränken. Sie verlieren etwas von dem Vorteil, obwohl, wenn Sie nicht den Vorteil der proportionalen Füllung Algorithmus. Proportionalfüllung ist der Prozess, mit dem die Datenbank versucht, neue Seiten proportional zum freien Speicherplatz in jeder Datendatei in der Dateigruppe zuzuordnen. Um den maximalen Nutzen zu erhalten, stellen Sie sicher, dass jede Datei die gleiche Größe hat und wird immer durch das gleiche Inkrement gezüchtet. Dies ist sowohl für manuelle als auch für automatisches Wachstum. Eine Sache zu beachten ist, wie das Auto Wachstum funktioniert. SQL Server tut sein Bestes, um die Dateien mit der gleichen Rate zu füllen, aber man wird immer kurz vor den anderen füllen, und diese Datei wird dann automatisch auf eigene Faust wachsen. Dies bekommt dann mehr neue Seitenzuweisungen als die anderen und wird ein temporärer Hotspot, bis die anderen auch automatisch wachsen und aufholen. Dies ist unwahrscheinlich, dass Probleme für die meisten Datenbanken verursachen, obwohl für tempdb kann es mehr spürbar sein. Trace-Flag 1117 bewirkt, dass alle Datendateien in einer Dateigruppe zusammen wachsen, also lohnt es sich zu überlegen, ob dies ein Problem für Sie ist. Persönlich würde ich lieber die Dateien manuell malen, damit das Auto-Wachstum nicht notwendig ist. Tempdb-Konfiguration Beginnen wir mit einigen Dingen, die jeder einverstanden hat: tempdb-Dateien sollten auf dem schnellsten verfügbaren Speicherplatz platziert werden. Lokale SSD ist ideal, und von SQL Server 2012 ist dies sogar auf einem Cluster möglich Vor-Größe der Daten - und Protokolldateien, da das Auto-Wachstum zu Leistungsproblemen führen kann, während es auftritt Neue temporäre Objekte werden immer erstellt, also Konkurrenz im GAM , SGAM und PFS Seiten können ein Problem in einigen Umgebungen sein Und jetzt einige Meinungsverschiedenheiten: Es gibt jede Menge Ratschläge im ganzen Web, um eine tempdb-Datendatei pro Kern zu erstellen, um Zuweisungskonflikte zu reduzieren. Paul Randall nicht einverstanden (ein SQL Server DBA Mythos am Tag: (1230) tempdb sollte immer eine Datendatei pro Prozessorkern haben). Er sagt, dass zu viele Dateien tatsächlich noch schlimmer machen können. Seine Lösung ist, weniger Dateien zu erstellen und nur zu erhöhen, wenn nötig Es gibt mehr Ratschläge, oft wiederholt, um tempdb-Dateien aus anderen Datenbanken zu trennen und sie auf ihre eigenen physischen Spindeln zu stellen. Joe Chang ist nicht einverstanden und hat ein sehr gutes Argument für die Verwendung der gemeinsamen Pool von Festplatten. (Daten-, Protokoll - und Temp-Dateiplatzierung). Kranke lassen Sie entscheiden, was zu tun ist AutoShrink Die AutoShrink-Datenbank-Option ist seit jeher seit ich mit der Verwendung von SQL Server, verursacht viele Performance-Probleme für Menschen, die es ermöglicht haben, ohne zu erkennen, was es tut. Oft wird eine Drittanbieteranwendung eine Datenbank mit dieser Option aktiviert, und der DBA kann es bis später nicht bemerken. Also warum ist es schlecht Zwei Gründe: Es wird immer in Verbindung mit Auto wachsen, und der kontinuierliche Zyklus von wachsen-schrumpfen-wachsen verursacht eine riesige Menge an physischen Scheiben Fragmentierung. Ive bereits abgedeckt, dass Thema früher in diesem Artikel Während es führt die schrumpfen gibt es eine Menge zusätzlicher IO, die verlangsamt das System für alles andere Deaktivieren Sie es. Vergeben Sie genügend Speicherplatz für die Daten - und Protokolldateien und formatieren Sie sie entsprechend. Und vergessen Sie nicht, all diese Fragmentierung zu beheben, während Sie daran sind. Nicht genügend Speicher Dies ist ein Artikel über SQL Server IO Leistung, nicht Speicher. Also will ich das hier nicht ausdrücken - das ist ein Thema für einen anderen Artikel. Ich möchte nur daran erinnern, dass SQL Server das Gedächtnis liebt - je mehr desto besser. Wenn Ihre gesamte Datenbank (en) passt in den Speicher youll haben ein viel schnelleres System, umgehen all das langsame IO. Mangel an Speicher kann dazu führen, dass verschmutzte Seiten auf die Festplatte häufiger gespült werden, um Platz für mehr Seiten zu machen, die gelesen werden. Mangel an Speicher kann auch zu erhöhtem tempdb IO führen, da mehr Arbeitstische für Sortier - und Hash-Operationen auf Festplatte spulen müssen. Jedenfalls ist der Punkt dieses Abschnitts wirklich eine Aussage zu machen: Füllen Sie Ihre Server mit so viel Speicher, wie Sie es sich leisten können, und so viel wie die Ausgabe von SQL Server und Windows adressieren können. SQL Server 2014 hat eine neue Funktion, die es ermöglicht, dass einige Tabellen im Speicher beibehalten werden und über nativ kompilierte gespeicherte Prozeduren zugegriffen werden. Einige Neugestaltung von einigen Ihrer bestehenden Code kann erforderlich sein, um dies zu nutzen, aber es sieht aus wie eine große Performance-Boost für die OLTP-Systeme, die beginnen, es zu benutzen. Der hohe Einsatz von tempdb tempdb kann ein wichtiger Verbraucher von IO sein und kann die Gesamtleistung beeinflussen, wenn er übermäßig verwendet wird. Es lohnt sich, die verschiedenen Gründe für seine Verwendung zu betrachten und Ihr System zu prüfen, um sicherzustellen, dass Sie diese so weit wie möglich minimiert haben. Benutzerdefinierte temporäre Objekte Die häufigsten sind temporäre Tabellen, Tabellenvariablen und Cursor. Wenn es eine hohe Schöpfungsrate gibt, kann dies zu einer Belegungsseite führen, obwohl die Erhöhung der Anzahl der tempdb-Datendateien dies teilweise lindern kann. Prozesse, die sehr große temporäre Tabellen oder Tabellenvariablen erzeugen, sind ein großes Nein-Nein, da diese viel IO verursachen können. Interne Objekte Die Datenbank-Engine erstellt Arbeitstabellen in tempdb für die Bearbeitung von Hash-Joins, Sortierung und Spooling von Zwischen-Ergebnis-Sets. Wenn Sortieroperationen oder Hash-Joins mehr Speicher benötigen, als es gewährt wurde, verschütten sie auf Festplatte (mit tempdb) und Sie sehen Hash-Warnungen und Warnungen in der Standard-Trace sortieren. I originally wrote a couple of paragraphs about how and why this happens and what you can do to prevent it, but then I found this post that explains it much better - Understanding Hash, Sort and Exchange Spill Events . Version Store The third use of tempdb is for the version store. This is used for row versioning. Row versions are created when snapshot isolation or read committed snapshot option is used. They are also created during online index rebuilds for updates and deletes made during the rebuild and for handling data modifications to multiple active result sets (MARS). A poorly written application (or rogue user) performing a large update that affects many thousands of rows when a row versioning based isolation level is in use may cause rapid growth in tempdb and adversely impact IO performance for other users. Table and Index Scans A table scan is a scan of a heap. An index scan is a scan of a clustered or non-clustered index. Both may be the best option if a covering index does not exist and a lot of rows are likely to be retrieved. A clustered index scan performs better than a table scan - yet another reason for avoiding heaps But what causes a scan to be used in the first place, and how can you make a seek more likely Out of date statistics Before checking indexes and code, make sure that statistics are up to date. Enable quotauto create statisticsquot. If quotauto update statisticsquot is not enabled make sure you run a manual statistics update regularly. This is a good idea even if quotauto update statisticsquot is enabled, as the threshold of approximately 20 of changed rows before the auto update kicks in is often not enough, especially where new rows are added with an ascending key. Index Choice Sometimes an existing index is not used. Have a look at improving its selectivity, possibly by adding additional columns, or modifying the column order. Consider whether a covering index could be created. A seek is more likely to be performed if no bookmark lookups will be needed. See these posts on the quottipping pointquot by Kimberly Tripp. The Tipping Point . Inefficient TSQL The way a query is written can also result in a scan, even if a useful index exists. Some of the reasons for this are: Non-sargable expressions in the WHERE clause. quotsargquot means Simple ARGument. So move calculations away from the columns and onto the constants instead. So for example, this will not use the index on OrderDate: WHERE DATEADD ( DAY. 1. OrderDate ) gt GETDATE () Whereas this will use an index if it exists (and it is selective enough): WHERE OrderDate gt DATEADD ( DAY. - 1. GETDATE ()) Implicit conversions in a query may also result in a scan. See this post by Jonathan Kehayias Implicit Conversions that cause Index Scans . Bad Parameter Sniffing Parameter sniffing is a good thing. It allows plan re-use and improves performance. But sometimes it results in a less efficient execution plan for some parameters. Index Maintenance Every index has to be maintained. Im not talking about maintenance plans, but about the fact that when rows are inserted, deleted and updated, the non-clustered indexes also have to be changed. This means additional IO for each index on a table. So it is a mistake to have more indexes than you need. Check that all indexes are being used. Check for duplicates and redundant indexes (where the columns in one are a subset of the columns in another). Check for indexes where the first column is identical but the rest are not - sometimes these can be merged. And of course, test, test, test. Index Fragmentation Index fragmentation affects IO performance in several ways. Range scans are less efficient, and less able to make use of read-ahead reads Empty space created in the pages reduces the density of the data, meaning more read IO is necessary The fragmentation itself is caused by page splits, which means more write IO There are a number things that can be done to reduce the impact of fragmentation, or to reduce the amount of fragmentation. Rebuild or reorganize indexes regularly Specify a lower fill factor so that page splits occur less often (though not too low, see below) Change the clustered index to use an ascending key so that new rows are appended to the end, rather than inserted in a random place in the middle Forwarded Records When a row in a heap is updated and requires more space, it is copied to a new page. But non-clustered indexes are not updated to point to the new page. Instead, a pointer is added to the original page to show where the row has moved to. This is called a forwarding pointer, and there could potentially be a long chain of these pointers to traverse to find the eventual data. Naturally, this means more IO. A heap cannot be defragmented by rebuilding the index (there isnt one). The only way to do this is to create a clustered index on the heap, and then drop it afterwards. Be aware that this will cause all non-clustered indexes to be rebuilt twice - once for the new clustered index, and again when it is dropped. If there are a lot of these it is a good idea to drop the non-clustered indexes first, and recreate them afterwards. Better still is to avoid heaps where possible. I accept there may be cases where they are the more efficient choice (inserting into archive tables, for example), but always consider whether a clustered index would be a better option - it usually is. Wasted Space In an ideal world every data page on disk (and in memory) would be 100 full. This would mean the minimum of IO is needed to read and write the data. In practise, there is wasted space in nearly all pages - sometimes a very high percent - and there are a lot of reasons why this occurs. Low fill factor Ive mentioned fill factor already. If it is too high, and page splits are occurring when rows are inserted or updated, it is sensible to rebuild the index with a lower fill factor. However, if the fill factor is too low you may have a lot of wasted space in the database pages, resulting in more IO and memory use. This is one of those quotsuck it and seequot scenarios. Sometimes a compromise is needed. Page splits This is also discussed above. But as well as fragmentation, page splits can also result in wasted space if the empty space is not reused. The solution is to defragment by rebuilding or reorganizing indexes regularly. Wasteful Choice of Data Types Use the smallest data types you can. And try to avoid the fixed length datatypes, like CHAR(255), unless you regularly update to the longest length and want to avoid page splits. The reasoning is simple. If you only use 20 characters out of 200, that is 90 wasted space, and more IO as result. The higher density of data per page the better. Lazy thinking might make developers create AddressLine1, AddressLine2, etc as CHAR(255), because they dont actually know what the longest should be. In this case, either do some research, find out that the longest is 50 characters (for example) and reduce them to CHAR(50), or use a variable length data type. Schema Design Ive already mentioned choice of data types above, but there are other schema design decisions that can affect the amount of IO generated by an application database. The most common one is designing tables that are too wide. I sometimes see a table with 20, 30, 50, even 100 columns. This means fewer rows fit on a page, and for some extreme cases there is room for just one row per page - and often a lot of wasted space as well (if the row is just slightly wider than half a page, thats 50 wasted). If you really do need 50 columns for your Customer table, ask yourself how many of these are regularly accessed. An alternative is to split into 2 tables. Customer, with just a few of the commonly used columns, and CustomerDetail with the rest. Of course, the choice of which columns to move is important. You dont want to start joining the tables for every query as that defeats the object of the exercise. Page or Row Compression Compression is another way of compacting the data onto a page to reduce disk space and IO. Use of row or page compression can dramatically improve IO performance, but CPU usage does increase. As long as you are not already seeing CPU bottlenecks, compression may be an option to consider. Be aware that compression is an Enterprise edition feature only. Backup Compression Since SQL Server 2008 R2, backup compression has been available on Standard edition as well as Enterprise. This is major benefit and I recommend that it be enabled on all instances. As well as creating smaller backups, it is also quicker and means less write IO. The small increase in CPU usage is well worth it. Enable it by default so that if someone sets off an ad hoc backup it will have minimal IO impact. Synchronous MirroringAlwaysOn High safety mode in database mirroring, or synchronous commit mode in AlwaysOn, both emphasise availability over performance. A transaction on the mirroring principal server or primary replica does not commit until it receives a message back from the mirror or secondary replica that the transaction has been hardened to the transaction log. This increases transactional latency, particularly when the servers are in different physical locations. Resource Governor in 2014 Up until and including SQL Server 2012 resource governor has only been able to throttle CPU and memory usage. Finally the ability to include IO in a resource pool has been added to SQL Server 2014. This has obvious use as a way of limiting the impact of reports on the system from a particular user, department or application. Gathering The Evidence There are a lot of ways you can measure SQL Server IO performance and identify which areas need looking at. Most of what follows is available in SQL CoPilot in graphical and tabular form, both as averages since last service start and as snapshots of current activity. Wait Types Use sys. dmoswaitstats to check number of waits and wait times for IOCOMPLETION, LOGBUFFER, WRITELOG and PAGEIOLATCH. Use this script to focus on the IO wait types: SELECT waittype. waitingtaskscount. waittimems - signalwaittimems AS totalwaittimems , 1. ( waittimems - signalwaittimems ) CASE WHEN waitingtaskscount 0 THEN 1 ELSE waitingtaskscount END AS avgwaitms FROM sys. dmoswaitstats WHERE waittype IN ( IOCOMPLETION. LOGBUFFER. WRITELOG. PAGEIOLATCHSH. PAGEIOLATCHUP. PAGEIOLATCHEX. PAGEIOLATCHDT. PAGEIOLATCHKP ) This shows averages since the last service restart, or since the wait stats were last cleared. To clear the wait stats, use DBCC SQLPERF (sys. dmoswaitstats, CLEAR) You can also check sys. dmoswaitingtasks to see what is currently being waited for. Virtual File Stats Query sys. dmiovirtualfilestats to find out which data and log files get the most read and write IO, and the latency for each file calculated using the stall in ms. SELECT d. name AS databasename. mf. name AS logicalfilename. numofbytesread. numofbyteswritten. numofreads. numofwrites. 1. iostallreadms ( numofreads 1 ) avgreadstallms. 1. iostallwritems ( numofwrites 1 ) avgwritestallms FROM sys. dmiovirtualfilestats (NULL, NULL) vfs JOIN sys. masterfiles mf ON vfs. databaseid mf. databaseid AND vfs. FILEID mf. FILEID JOIN sys. databases d ON mf. databaseid d. databaseid Performance Counters There are two ways of looking at performance counters. Select from sys. dmosperformancecounters, which shows all the SQL Server counters, or use Windows Performance Monitor (perfmon) to see the other OS counters as well. Some counters to look at are: SQL Server:Buffer Manager Lazy writessec The number of times per second that dirty pages are flushed to disk by the Lazy Writer process. An indication of low memory, but listed here as it causes more IO. Checkpoint pagessec The number of dirty pages flushed to disk per second by the checkpoint process. Page readssec Number of physical pages read from disk per second Page writessec Number of physical pages written to disk per second Readahead pagessec Pages read from disk in advance of them being needed. Expect to see high values in BI workloads, but not for OLTP SQL Server:Access Methods Forwarded recordssec Should be as low as possible. See above for explanation of forwarded records. Full scanssec The number of unrestricted full scans. Use of UDFs and table variables can contribute to this, but concentrating on seeks will help to keep the value down Page splitssec The number of page splits per second - combining splits due to pages being added to the end of a clustered index as well as quotgenuinequot splits when a row is moved to a new page. Use the technique from the link in the section on index fragmentation, above, to get a more accurate breakdown Skipped ghosted recordssec For information about ghosted records see An In-depth Look at Ghost Records in SQL Server Workfiles createdsec A measure of tempdb activity Worktables createdsec A measure of tempdb activity SQL Server:Databases Log bytes flushedsec The rate at which log records are written to disk Log flush wait time The duration of the last log flush for each database Log flush waitssec The number of commits per second waiting for a log flush Logical Disk Avg Disk secsRead Avg Disk secsWrite Avg Disk Read bytessec Avg Disk Write bytessec Using the sys. dmosperformancecounters DMV, a lot of counters display a raw value, which has to be monitored over time to see values per second. Others have to be divided by a base value to get a percentage. This makes this DMV less useful unless you perform these calculations and either monitor over time or take an average since the last server restart. This script uses the tempdb creation date to get the number of seconds since the service started and calculates the averages for these counters. It also retrieves all other counters and calculates those that are derived from a base value. USE master SET NOCOUNT ON DECLARE upsecs bigint SELECT upsecs DATEDIFF ( second. createdate. GETDATE ()) FROM sys. databases WHERE name tempdb SELECT RTRIM ( objectname ) objectname. RTRIM ( instancename ) instancename. RTRIM ( countername ) countername. cntrvalue FROM sys. dmosperformancecounters WHERE cntrtype 65792 UNION ALL SELECT RTRIM ( objectname ), RTRIM ( instancename ), RTRIM ( countername ), 1. CAST ( cntrvalue AS bigint ) upsecs FROM sys. dmosperformancecounters WHERE cntrtype 272696576 UNION ALL SELECT RTRIM ( v. objectname ), RTRIM ( v. instancename ), RTRIM ( v. countername ), 100. v. cntrvalue CASE WHEN b. cntrvalue 0 THEN 1 ELSE b. cntrvalue END FROM ( SELECT objectname. instancename. countername. cntrvalue FROM sys. dmosperformancecounters WHERE cntrtype 537003264 ) v JOIN ( SELECT objectname. instancename. countername. cntrvalue FROM sys. dmosperformancecounters WHERE cntrtype 1073939712 ) b ON v. objectname b. objectname AND v. instancename b. instancename AND RTRIM ( v. countername ) base RTRIM ( b. countername ) UNION ALL SELECT RTRIM ( v. objectname ), RTRIM ( v. instancename ), RTRIM ( v. countername ), 1. v. cntrvalue CASE WHEN b. cntrvalue 0 THEN 1 ELSE b. cntrvalue END FROM ( SELECT objectname. instancename. countername. cntrvalue FROM sys. dmosperformancecounters WHERE cntrtype 1073874176 ) v JOIN ( SELECT objectname. instancename. countername. cntrvalue FROM sys. dmosperformancecounters WHERE cntrtype 1073939712 ) b ON v. objectname b. objectname AND v. instancename b. instancename AND REPLACE ( RTRIM ( v. countername ), (ms). ) Base RTRIM ( b. countername ) ORDER BY objectname. instancename. countername Dynamic Management Views and Functions As well as the DMVs in the above scripts, there are a number of others that are useful for diagnosing SQL Server IO performance problems. Here are all the ones I use. Ill add some sample scripts when I get the time: sys. dmoswaitstats sys. dmiovirtualfilestats sys. dmosperformancecounters sys. dmiopendingiorequests sys. dmdbindexoperationalstats sys. dmdbindexusagestats sys. dmdbindexphysicalstats sys. dmosbufferdescriptors It can also be useful to see what activity there is on the instance. Here are your options: The Profiler tool is quick and easy to use - you can start tracing in a matter of seconds. However, there is some overhead and it may impact performance itself - especially when a lot of columns are selected. A server side trace is a better option. A server-side trace has less of an impact than running Profiler. It has to be scripted using system stored procedures, but Profiler has the ability to generate the script for you. Extended Event Sessions Extended events were first introduced in SQL Server 2008, and have been considerably enhanced in SQL 2012. They are very lightweight, and the use of server-side traces and Profiler is now deprecated. Nevertheless, use of extended events may impact performance of high transaction systems if you are not careful. Use an asynchronous target and avoid complicated predicates to limit the overhead. There are a number of tools for gathering performance data from your servers. SQLIO is a simple tool that creates a file on disk and tests latency and throughput for randomsequential IO, at various block sizes and with a variable number of threads. These are all fully configurable. SQLIO is a great way of getting a baseline on a new server or storage, for future comparison. Third party tools are another option for viewing performance metrics. Some show you what is happening on the server right now. Others are built into more complex (and expensive) monitoring solutions. Performance metrics obtained on virtual servers are unreliable. Performance counters and wait stats may give the impression that everything is OK, when it is not. I recommend the use of the performance monitoring tools provided by the VM vendor. In the case of VMWare, this is very easy to use and is built into Virtual Center. This turned into a much bigger article than I expected - SQL Server IO performance is a big subject I started with everything I knew, and double checked my facts by searching the web and checking books. In the process I learnt a whole lot of new stuff and found a lot of useful links. It has been a useful exercise. Hopefully this has been useful for you too. Importing SQL Server Data Using SSIS - Which Option is Fastest By: Daniel Calbimonte Read Comments (27) Related Tips: More Integration Services Development This article is useful for SSIS developers who do not know which tasks are best to use in an SSIS projects. The main problem is that at the end of development if performance is slow then you will need to rebuild the project and change components. This article shows various ways of importing data and shows which types of components perform best within SSIS. The contest will be between the following components: ODBC Tasks ADO NET Tasks OLEDB Task SQL Server Destination T-SQL Tasks I created different SSIS packages in order to test performance. In this demo I used SSIS 2012 and the database Adventureworks 2012 . In this demo I am going to import the table AdventureWorks2012.Sales. SalesOrderDetail to the test2 database which is on the same instance of SQL Server. SalesOrderDetails is the table with more rows in AdventureWorks2012. In order to create the database test2 and the destination table dbo. OrderDetails, use this T-SQL code: Test 1 - ODBC Tasks The first example will use ODBC Source and ODBC Destination as shown below: When we run the package we notice the average time is 5 minutes 57 seconds to import the rows: Test 2 - ADO NET Tasks As noticed, ODBC is pretty slow. Lets try another approach. We are going to truncate the destination table first: Lets try ADO tasks to import the same data and verify if these components are faster: The average elapsed time in my testing was 11 seconds. This is much better. Test 3 - OLEDB Tasks This time we are going to import the same data using the OLEDB Tasks. Again we will truncate the table in the test2 database first. The average elapsed time is 5 seconds. Note that I am using the fast load option with the Table Lock option in the OLE DB Destination Task: If we do not use the fast load option, the average elapsed time was 2 minutes and 21 seconds: OK. The fast load option really improves performance. I will return to that configuration. What about the OLE DB Source. By default I am using the option Table or view in the OLE DB Source as shown below: Lets use a SQL Command instead as shown below. The average elapsed time is 2.85 seconds . Test 4 - SQL Server Destination Now, lets try to use the SQL Destination as the destination instead of OLE DB Destination: The average elapsed time is 2.5 seconds. At this point it is the best option. Test 5 - Execute T-SQL Task Finally, some people think that the best option is to use the Execute T-SQL Task: I am using a simple insert statement to import data from one source to another: The average elapsed time is 1.8 seconds Finally Ive been told that if the query runs inside a stored procedure it is even faster: Lets create a stored procedure: After creating the stored procedure we are going to call it in the Execute T-SQL Task: The average elapsed time is 2.12 seconds . The stored procedures does not improve performance. Lets review the table with the results: You may think the morale of the story is to use the Execute T-SQL Task instead of other SSIS tasks. In this example we were importing data on the same instance, but this will not always be the case. So the morale of the story is that there are many alternatives when creating a SSIS project and we have to carefully study the alternatives in different scenarios. There are great SSIS tools and we do not always use the best options. With each new version of SSIS new tasks are added and performance may be improved with existing tasks. The main changes in SSIS for SQL 2008 and 2012 are related to performance improvements. Next Steps If you are working in a SSIS project make sure you are using the best tasks and also verify if there are other SSIS tasks that can be used in your project. Also make sure you are following the best practices recommended by the experts: Last Update: 7132012 Great read and analysis, but I have one caveat to add. If you need to move a large amount of data, you need to take care of the transaction log growth. This is not a much of a concern using SSIS. For instance, I needed to move 1.3 billion rows (15 columns) and began using TSQL which quickly filled my logs. However, using OLE DB Source and Destination (Bulk Inserts) with fast load, there was little impact to the log file. Thursday, September 20, 2012 - 9:19:12 AM - vinodhkumar Its very useful. great job. Thanks Monday, August 27, 2012 - 10:54:42 AM - Orlando Colamatteo I agree with some others that the testbed is a bit contrived. If youre looking to move data from one table to another on the same instance then SSIS will rarely be a viable option. Some form of T-SQL will almost certainly outperform an SSIS operation. A more realistic scenario is moving data between two disparate data sources. It is surpising how poorly the ODBC Destination performs, especially in light of what Microsoft has publicly said in that they will be moving away from OLE DB interfaces and standardizing on ODBC in future products: In the ODBC Destination I expected Microsoft to implement the loading of data via the bulk load API as they did with the FastLoad option of the OLE DB Destination. On a separate note regarding loading data into MySQL with SSIS: In the past I did some performance tests with the Cherry City OLE DB driver for MySQL and it is horribly slow as it only inserts one row at a time. This is not to mention the fact that it crashed BIDS regularly when developing with it. Given the lack of a benefit I would stick with the tools built into SSIS and avoid the hassle of installing and configuring a third party driver. If youre using SSIS 2005 I would recommend using a Script Component as a Destination and issuing batch-inserts against a connection made using the MySQL ODBC Driver: msdn. microsoften-uslibraryms135939.aspx If youre using SSIS 2008 I would recommend using an ADO NET Destination with the MySQL ODBC Driver. In my tests it was only able to achieve about 240 rowsminute throughput to MySQL which is quite disappointing: msdn. microsoften-uslibrarybb895291(vsql.105).aspx If youre using SSIS 2012 I would recommend using an ODBC Destination with the MySQL ODBC Driver. In my tests it outperformed the ADO NET Destination over 3 to 1 but still only achieved about 800 rowsminute throughput, which was still quite disappointing: msdn. microsoften-uslibraryhh758691(vsql.110).aspx