Umzugsdurchschnitt Tsql

SQL Server IO Leistung 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, so dass Workload 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, sehen Sie auch Leistungsvorteile. 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 sich das Protokoll auf die Festplatte gehärtet hat, verlassen Sie sich auf diese schnellen Schriften 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. Ich schrieb ursprünglich ein paar Absätze darüber, wie und warum dies geschieht und was Sie tun können, um es zu verhindern, aber dann fand ich diesen Beitrag, der es viel besser erklärt - Verstehen Hash, Sort und Exchange Spill Events. Version Store Die dritte Verwendung von tempdb ist für den Versionsladen. Dies wird für die Zeilenversionierung verwendet. Row-Versionen werden erstellt, wenn Snapshot-Isolation oder Lese-Snapshot-Option verwendet wird. Sie werden auch bei Online-Index-Neuerstellungen für Updates und Löschungen erstellt, die während des Wiederaufbaus gemacht wurden, und zum Umgang mit Datenänderungen auf mehrere aktive Ergebnismengen (MARS). Eine schlecht geschriebene Anwendung (oder Schurkenbenutzer), die eine große Aktualisierung durchführt, die viele Tausende von Zeilen beeinflusst, wenn eine auf der Serie laufende Versionierungsbasierte Isolationsstufe verwendet wird, kann ein schnelles Wachstum in tempdb verursachen und die IO-Leistung für andere Benutzer nachteilig beeinflussen. Tabellen - und Index-Scans Ein Tabellen-Scan ist ein Scan eines Haufens. Ein Index-Scan ist ein Scan eines gruppierten oder nicht gruppierten Index. Beide können die beste Option sein, wenn ein Deckungsindex nicht existiert und viele Zeilen wahrscheinlich abgerufen werden. Ein Cluster-Index-Scan führt besser als ein Tabellen-Scan - noch ein weiterer Grund für die Vermeidung von Haufen Aber was bewirkt, dass ein Scan an erster Stelle verwendet wird und wie können Sie eine Suche nach wahrscheinlicher machen Out of date Statistiken Vor der Überprüfung von Indizes und Code, machen Sicher, dass die Statistiken aktuell sind. Aktivieren Sie den Quellstatus. Wenn noauto update statisticsquot nicht aktiviert ist, stellen Sie sicher, dass Sie regelmäßig ein manuelles Statistik-Update ausführen. Dies ist eine gute Idee, auch wenn das Update-Statistikstatistik aktiviert ist, da die Schwelle von etwa 20 der geänderten Zeilen, bevor die automatische Aktualisierung eintritt, oft nicht genug ist, besonders wenn neue Zeilen mit einer aufsteigenden Taste hinzugefügt werden. Indexauswahl Manchmal wird ein vorhandener Index nicht verwendet. Schauen Sie sich die Selektivität an, eventuell durch Hinzufügen zusätzlicher Spalten oder Ändern der Spaltenreihenfolge. Überlegen Sie, ob ein Deckungsindex erstellt werden könnte. Eine Suche wird eher durchgeführt, wenn keine Lesezeichen-Lookups benötigt werden. Sehen Sie diese Beiträge auf dem Quottipping Pointquot von Kimberly Tripp. Der Wendepunkt . Ineffiziente TSQL Die Art, wie eine Abfrage geschrieben wird, kann auch zu einem Scan führen, auch wenn ein nützlicher Index existiert. Einige der Gründe dafür sind: Nicht-sargable Ausdrücke in der WHERE-Klausel. Quotsargquot bedeutet einfaches ARGument. Bewegen Sie also die Berechnungen von den Spalten weg und auf die Konstanten statt. So wird zum Beispiel der Index auf OrderDate nicht verwendet: WHERE DATEADD (DAY 1. OrderDate) gt GETDATE () Dabei wird ein Index verwendet, wenn er existiert (und es ist selektiv genug): WHERE OrderDate gt DATEADD (DAY. - 1. GETDATE ()) Implizite Konvertierungen in einer Abfrage können auch zu einem Scan führen. Sehen Sie diesen Beitrag von Jonathan Kehayias Implizite Umwandlungen, die Index-Scans verursachen. Schlechte Parameter Sniffing Parameter Sniffing ist eine gute Sache. Es ermöglicht Plan Wiederverwendung und verbessert die Leistung. Aber manchmal führt es zu einem weniger effizienten Ausführungsplan für einige Parameter. Indexpflege Jeder Index muss beibehalten werden. Im nicht reden über Wartungspläne, sondern über die Tatsache, dass, wenn Zeilen eingefügt, gelöscht und aktualisiert werden, müssen die nicht gruppierten Indizes auch geändert werden. Dies bedeutet zusätzliches IO für jeden Index auf einer Tabelle. So ist es ein Fehler, mehr Indizes zu haben als du brauchst. Prüfen Sie, ob alle Indizes verwendet werden. Überprüfen Sie auf Duplikate und redundante Indizes (wobei die Spalten in einer eine Teilmenge der Spalten in einer anderen sind). Überprüfen Sie auf Indizes, in denen die erste Spalte identisch ist, aber der Rest ist nicht - manchmal können diese zusammengeführt werden. Und natürlich testen, testen, testen Index Fragmentierung Index Fragmentierung beeinflusst IO Leistung auf mehrere Arten. Range-Scans sind weniger effizient und weniger in der Lage, die Verwendung von Read-Ahead-Lesungen zu verwenden Leerraum, der auf den Seiten erzeugt wird, reduziert die Dichte der Daten, dh mehr lesen IO ist notwendig Die Fragmentierung selbst wird durch Seitenaufteilungen verursacht, was bedeutet, dass mehr IO geschrieben wird Es gibt eine Reihe von Dingen, die getan werden können, um die Auswirkungen der Fragmentierung zu reduzieren oder die Menge an Fragmentierung zu reduzieren. Rekonstruieren oder Reorganisieren von Indizes regelmäßig Geben Sie einen niedrigeren Füllfaktor an, so dass Seitenaufteilungen seltener auftreten (wenn auch nicht zu niedrig, siehe unten) Ändern Sie den gruppierten Index, um einen aufsteigenden Schlüssel zu verwenden, damit neue Zeilen an das Ende angehängt werden, anstatt in einen eingefügt zu werden Zufälliger Platz in der Mitte Forwarded Records Wenn eine Zeile in einem Heap aktualisiert wird und mehr Platz benötigt, wird sie auf eine neue Seite kopiert. Aber nicht gruppierte Indizes werden nicht aktualisiert, um auf die neue Seite zu zeigen. Stattdessen wird ein Zeiger auf die ursprüngliche Seite hinzugefügt, um zu zeigen, wo sich die Zeile bewegt hat. Dies wird als Weiterleitungszeiger bezeichnet, und es könnte potenziell eine lange Kette dieser Zeiger sein, um zu überqueren, um die eventuellen Daten zu finden. Natürlich bedeutet das mehr IO. Ein Haufen kann nicht durch den Wiederaufbau des Index defragmentiert werden (es gibt keinen). Der einzige Weg, dies zu tun, ist, einen geclusterten Index auf dem Heap zu erstellen und dann danach zu löschen. Seien Sie sich bewusst, dass dies dazu führt, dass alle nicht gruppierten Indizes zweimal umgebaut werden - einmal für den neuen gruppierten Index und wieder, wenn es gelöscht wird. Wenn es eine Menge davon gibt, ist es eine gute Idee, die nicht gruppierten Indizes zuerst zu löschen und sie danach neu zu erstellen. Besser ist es immer, Haufen zu vermeiden, wo es möglich ist. Ich nehme an, dass es Fälle geben kann, in denen sie die effizientere Wahl sind (zB in Archivtabellen einfügen), aber immer überlegen, ob ein gruppierter Index eine bessere Option wäre - das ist in der Regel. Vergeudeter Raum In einer idealen Welt wäre jede Datenseite auf Festplatte (und im Speicher) 100 voll. Dies würde bedeuten, dass das Minimum von IO benötigt wird, um die Daten zu lesen und zu schreiben. In der Praxis gibt es Platz in fast allen Seiten - manchmal ein sehr hoher Prozentsatz - und es gibt viele Gründe, warum dies geschieht. Niedriger Füllfaktor Ive erwähnte den Füllfaktor bereits. Wenn es zu hoch ist und Seitensplits auftreten, wenn Zeilen eingefügt oder aktualisiert werden, ist es sinnvoll, den Index mit einem niedrigeren Füllfaktor neu zu erstellen. Allerdings, wenn der Füllfaktor zu niedrig ist, können Sie eine Menge von verschwendete Platz in den Datenbank-Seiten, was zu mehr IO und Speicher verwenden. Dies ist einer von diesen quotsuck it und seequot Szenarien. Manchmal ist ein Kompromiss erforderlich. Page splits Dies wird auch oben diskutiert. Aber auch bei der Fragmentierung können Seitenaufteilungen auch zu einem verschwendete Raum führen, wenn der Leerraum nicht wiederverwendet wird. Die Lösung besteht darin, die Indizes regelmäßig zu rekonstruieren oder neu zu ordnen. Abweichende Wahl der Datentypen Verwenden Sie die kleinsten Datentypen, die Sie können. Und versuchen Sie, die Datentypen mit fester Länge zu vermeiden, wie CHAR (255), es sei denn, Sie aktualisieren regelmäßig die längste Länge und wollen Seitenaufteilungen vermeiden. Die Begründung ist einfach. Wenn Sie nur 20 Zeichen von 200 verwenden, das ist 90 verschwendet Platz, und mehr IO als Ergebnis. Je höher die Daten pro Seite, desto besser. Lazy Denken könnte Entwickler erstellen AdresseLine1, AddressLine2, etc als CHAR (255), weil sie nicht wirklich wissen, was die längste sein sollte. In diesem Fall, entweder einige Forschung, herauszufinden, dass die längste ist 50 Zeichen (zum Beispiel) und reduzieren sie auf CHAR (50), oder verwenden Sie einen variablen Länge Datentyp. Schema Design Ive bereits erwähnt Auswahl von Datentypen oben, aber es gibt andere Schema Design Entscheidungen, die die Menge an IO durch eine Anwendungsdatenbank beeinflussen beeinflussen können. Die häufigste ist das Entwerfen von Tabellen, die zu breit sind. Manchmal sehe ich einen Tisch mit 20, 30, 50, sogar 100 Spalten. 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. Window Functions (OVER Clause)Help Make a Difference If I had to name one concept in standard SQL that I thought was the most important one, and that is worth Microsoftrsquos investment for future versions of SQL Server, Irsquod say window functions, hands down, without a doubt. Window functions are a subset of what the standard calls set functions, meaning, functions that are applied to a set of rows. The term window is used to describe the set of rows that the function operates on, and the language provides a clause called OVER where you provide the window specification. So whatrsquos the big deal, and what makes window functions more important than other features that are missing in SQL Server There are so many reasonshellip But first Irsquoll give a bit more background about window functions, and then Irsquoll get to the reasons and demonstrate use caseshellip First, to clarify, SQL Server 2005 already introduced some support for window functionsmdashthe ranking calculations: ROWNUMBER, RANK, DENSERANK and NTILE, and partial support for window aggregate functions with only the partitioning part implemented. SQL Server 2005 was a great release for developers with so many cool and practical T-SQL features. The number of solutions that I simplified and optimized just with the ROWNUMBER function and CTEs is amazing. Still, there are many standard features related to window functions that SQL Server didnrsquot yet implement (as of SQL Server 2008 R2) and that can help address quite a wide variety of business problems with simpler and more efficient solutions. These days the next major release of Microsoft SQL Servermdashversion 11mdashis being developed. These are pivotal days for candidate features where decisions are made whether they will or will not make it to the final release. And even though I think that more complete support for window functions is so important to developers and to the success of SQL Server, Irsquom not sure at all that we will see those in the product. This is time for us as part of the SQL Server community to express our strong opinion. Hopefully Microsoft will realize how important it is for us to have those features in the product, as well as to show that the SQL Server communityrsquos opinion matters. In this article I will explain some of the key features that are missing in SQL Server and why itrsquos important to add support for such features. If you share my opinion, and havenrsquot done so already, you can cast your vote in the following feature request items: Like with any thing in life that yoursquore not aware of, you donrsquot know how it can help you if you donrsquot know that it exists. My feeling is that many developers are not really aware of the capabilities of the standard window functions and therefore Microsoft doesnrsquot see a lot of demand for it. Education and raising the topic to peoplersquos awareness is therefore key to the realization of the benefits, and as a consequence, encourage people to ask Microsoft for more support. The unfortunate part is that all of SQL Serverrsquos leading competitors including Oracle, DB2 and Teradata for some time now already have a far more complete support for window functions. So even though my focus and passion is for SQL Server, I sometimes find myself in the awkward situation of demoing standard SQL window functions on Oracle when teaching or presenting. So whatrsquos missinghellip The most important missing features are probably ordering and framing options for window aggregate functions. Other key features that are still missing are distribution and offset functions, and reusability of window definitions. More details shortly. Why are window functions so powerful SQL is often referred to as a set-based language. The reason is that the language is based on the relational model, which in turn is based, in part, on mathematical set theory. When writing SQL queries yoursquore supposed to deal with a table (or relation, which is a set) as a whole, as opposed to the tablersquos individual rows. Also, since sets have no order, yoursquore not supposed to make any assumptions in regards to the physical ordering of the data. The reality is that for many developers set-based thinking is far from being intuitive, and it can take a few good years to truly think in SQL terms. This is why often developers tend to use cursorsmdashbecause using those feel like an extension to what they already know. Cursors allow you to deal with one row at a time, and also rely on specified order of the data. Window functions have an ingenious design. They do operate on sets, or windows, while allowing you to indicate ordering as part of the calculation where relevant. Not to confuse with cursors, window functions allow defining ordering for the calculation without making any expectations in regards to ordering of the input data given to the query or the output coming out of the query. In other words, no relational concepts are violated. Ordering is only part of the specification of the calculation. Similarly, other common elements in querying problems, like partitioning, framing of applicable rows, are all intuitive parts of the window specification. So in a sense, I see window functions as bridging the big gap that exists between cursoriterative and set-based thinking. Now, thatrsquos a lot of words before showing even one example. So letrsquos look at a few more concrete examples of some of the missing featureshellip Most of the examples Irsquoll show are against a database called InsideTSQL2008. You can find the script creating it here: InsideTSQLbookssourcecodeInsideTSQL2008.zip. In addition, the following view will be used in some of the examples: SET NOCOUNT ON USE InsideTSQL2008 GO IF OBJECTID ( 39Sales. EmpOrders39. 39V39 ) IS NOT NULL DROP VIEW Sales. EmpOrders GO CREATE VIEW Sales. EmpOrders WITH SCHEMABINDING AS SELECT O. empid , DATEADD ( month. DATEDIFF ( month. 0. O. orderdate ), 0 ) AS ordermonth. SUM (OD. qty ) AS qty , CAST ( SUM (OD. qty OD. unitprice (1 - discount )) AS NUMERIC (12. 2 )) AS val , COUNT () AS numorders FROM Sales. Orders AS O JOIN Sales. OrderDetails AS OD ON OD. orderid O. orderid GROUP BY empid. DATEADD ( month. DATEDIFF ( month. 0. O. orderdate ), 0 ) GO Ordering and Framing for Window Aggregate Functions As mentioned, currently window aggregate functions support only a partitioning element. Whatrsquos missing are ordering and framing options. The standard supports an ORDER BY clause to define ordering in the window and ROWS and RANGE clauses that frame the window based on the defined ordering. A classic example that would benefit from ordering and framing is running totals. Consider the following Accounts table definition: CREATE TABLE dbo. Accounts ( actid INT NOT NULL, -- partitioning column tranid INT NOT NULL, -- ordering column val MONEY NOT NULL -- measure CONSTRAINT PKAccounts PRIMARY KEY (actid. tranid ) ) The table represents deposit (positive value) and withdrawal (negative value) transactions in bank accounts. You need to calculate at each point what the account balance was. Like with many querying problems therersquos a partitioning element (actid), ordering element (tranid), and a measure that the calculation applies to (val). Window aggregate functions in standard SQL support all three elements. Herersquos how you would express the query calculating the balance at each point for each account: SELECT actid. tranid. val , SUM (val ) OVER ( PARTITION BY actid ORDER BY tranid ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS balance FROM dbo. Accounts You can achieve such calculations today in SQL Server using a subquery or a join: -- Set-Based Solution Using Subqueries SELECT actid. tranid. val , ( SELECT SUM (S2.val ) FROM dbo. Accounts AS S2 WHERE S2.actid S1.actid AND S2.tranid lt S1.tranid ) AS balance FROM dbo. Accounts AS S1 -- Set-Based Solution Using Joins SELECT S1.actid. S1.tranid. S1.val , SUM (S2.val ) AS balance FROM dbo. Accounts AS S1 JOIN dbo. Accounts AS S2 ON S2.actid S1.actid AND S2.tranid lt S1.tranid GROUP BY S1.actid. S1.tranid. S1.val But besides the fact that these solutions are not as straightforward and intuitive as the one using a window function, therersquos a big problem with the way SQL Server currently optimizes the subquery and join solutions. Assuming you defined a covering index on the partitioning column, followed by the ordering column, and including the aggregated measure, for each row SQL Server will scan all rows with the same partitioning value and an ordering value that is less than or equal to the current. Given p partitions with r rows in average, and fairly even distribution of rows in partitions, the total number of rows processed in such a plan is pr p(r r2)2. This means that in respect to the partition size, the algorithmic complexity, or scaling, of the solution s quadratic (N2). Thatrsquos bad. The window function form lends itself to good optimization, especially with the fast track case like the above (rows between unbounded preceding and current row). It should be straightforward to the optimizer to optimize this query with one ordered scan of the index, translating to simply pr rows being scanned. Another example for running totals is querying a table called EmpOrders with a row for each employee and month, and calculating the cumulative performance for each employee and month in other words, the total value for the employee from the beginning of hisher activity until the current month. Herersquos how you would express it with a window aggregate: SELECT empid. ordermonth. qty , SUM (qty ) OVER ( PARTITION BY empid ORDER BY ordermonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS runqty FROM Sales. EmpOrders empid ordermonth qty runqty ----------- ----------------------- ----------- ----------- 1 2006-07-01 00:00:00.000 121 121 1 2006-08-01 00:00:00.000 247 368 1 2006-09-01 00:00:00.000 255 623 1 2006-10-01 00:00:00.000 143 766 1 2006-11-01 00:00:00.000 318 1084 . 2 2006-07-01 00:00:00.000 50 50 2 2006-08-01 00:00:00.000 94 144 2 2006-09-01 00:00:00.000 137 281 2 2006-10-01 00:00:00.000 248 529 2 2006-11-01 00:00:00.000 237 766 . There are many business examples where ordering and framing options can be useful besides calculating account balances. Those include inventory, running totals for reporting, moving averages, and so on. Herersquos an example for a query calculating the average of the last three recorded periods: SELECT empid. ordermonth , AVG (qty ) OVER ( PARTITION BY empid ORDER BY ordermonth ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS avglastthree FROM Sales. EmpOrders There are also various temporal querying problems where running totals serve part of the solution. For simplicity I showed examples where framing is based on the ROWS clause where you indicate an offset in terms of number of rows. The standard also supports a RANGE clause that allows indicating an offset in terms of values, such as time intervals, as in the following example returning the average of the last three months: SELECT empid. ordermonth. qty , SUM (qty ) OVER ( PARTITION BY empid ORDER BY ordermonth RANGE INTERVAL 39239 MONTH PRECEDING ) AS sum3mqty FROM Sales. EmpOrders ORDER BY empid. ordermonth The SQL standard defines several offset functions that would make developersrsquo life so much easier compared to the tools available today for similar needs. Among the missing offset functions are LAG and LEAD, returning a value from a row in a given offset from the current row based on specified ordering. For example, the following query will return, for each current order, also the order date of the previous and next orders: SELECT custid. orderdate. orderid , LAG (orderdate ) OVER ( PARTITION BY custid ORDER BY orderdate. orderid ) AS prvod , LEAD (orderdate ) OVER ( PARTITION BY custid ORDER BY orderdate. orderid ) AS nxtod FROM Sales. Orders custid orderdate orderid prvod nxtod ------- ----------- -------- ----------- ----------- 1 2007-08-25 10643 NULL 2007-10-03 1 2007-10-03 10692 2007-08-25 2007-10-13 1 2007-10-13 10702 2007-10-03 2008-01-15 1 2008-01-15 10835 2007-10-13 2008-03-16 1 2008-03-16 10952 2008-01-15 2008-04-09 1 2008-04-09 11011 2008-03-16 NULL 2 2006-09-18 10308 NULL 2007-08-08 2 2007-08-08 10625 2006-09-18 2007-11-28 2 2007-11-28 10759 2007-08-08 2008-03-04 2 2008-03-04 10926 2007-11-28 NULL . Notice how elegant and intuitive this form is. The default offset is one row, but you can also be explicit if you need an offset that is other than one row, e. g. three rows: SELECT custid. orderdate. orderid , LAG (orderdate. 3 ) OVER ( PARTITION BY custid ORDER BY orderdate. orderid ) AS prv3od FROM Sales. Orders There are lots of business examples for the usefulness of these functions, like recency calculations, trend analysis, and others. Herersquos an example for a query addressing recency calculations, returning the difference in terms of days between the current and previous orders: SELECT custid. orderdate. orderid , DATEDIFF ( day , LAG (orderdate ) OVER ( PARTITION BY custid ORDER BY orderdate. orderid ), orderdate ) AS diff FROM Sales. Orders Other missing offset functions are FIRSTVALUE, LASTVALUE, returning the value from the first or last rows in the partition based on specified ordering. Herersquos an example returning the value of the first and last orders per customer with each order: -- FIRSTVALUE, LASTVALUE SELECT custid. orderdate. orderid. val , FIRSTVALUE (val ) OVER ( PARTITION BY custid ORDER BY orderdate. orderid ) AS valfirstorder , LASTVALUE (val ) OVER ( PARTITION BY custid ORDER BY orderdate. ordered ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS vallastorder FROM Sales. OrderValues custid orderdate orderid val valfirstorder vallastorder ------- ----------- -------- ------- --------------- -------------- 1 2007-08-25 10643 814.50 814.50 933.50 1 2007-10-03 10692 878.00 814.50 933.50 1 2007-10-13 10702 330.00 814.50 933.50 1 2008-01-15 10835 845.80 814.50 933.50 1 2008-03-16 10952 471.20 814.50 933.50 1 2008-04-09 11011 933.50 814.50 933.50 2 2006-09-18 10308 88.80 88.80 514.40 . And herersquos an example calculating the difference between the current order value and the first and last: SELECT custid. orderdate. orderid. val , val - FIRSTVALUE (val ) OVER ( PARTITION BY custid ORDER BY orderdate. orderid ) AS difffirst , val - LASTVALUE (val ) OVER ( PARTITION BY custid ORDER BY orderdate. ordered ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS difflast FROM Sales. OrderValues Standard SQL supports window distribution functions that performing statistical calculations. Specifically it supports the PERCENTRANK and CUMDIST functions, calculating a percentile rank and cumulative distribution. These functions give you a relative rank of a row in respect to other rows in the window partition, expressed as ratiopercent. The specific formulas used by the two variants are: PERCENTRANK: (RK-1)(NR-1), where RK rank, NR number of rows in partition CUMEDIST: NPNR, where NP number of rows preceding or peer with current row (same as next rank - 1) Herersquos an example using these functions: SELECT custid. COUNT () AS numorders , PERCENTRANK () OVER ( ORDER BY COUNT ()) AS percentrank , CUMEDIST () OVER ( ORDER BY COUNT ()) AS cumedist FROM Sales. Orders GROUP BY custid custid numorders percentrank cumedist ------- ---------- ------------ --------- 13 1 0.0000 0.0112 33 2 0.0114 0.0337 43 2 0.0114 0.0337 42 3 0.0341 0.1124 53 3 0.0341 0.1124 . 37 19 0.9545 0.9663 24 19 0.9545 0.9663 63 28 0.9773 0.9775 20 30 0.9886 0.9888 71 31 1.0000 1.0000 Reuse of Window Definition using WINDOW Clause Suppose you need to write several window functions that rely on the same window definition (or part of it). You will end up with a lot of repetition of code. Standard SQL has a clause called WINDOW that allows naming a window definition or part of it, making it reusable. For example, instead of: SELECT empid. ordermonth. qty , SUM (qty ) OVER ( PARTITION BY empid ORDER BY ordermonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS runsumqty , AVG (qty ) OVER ( PARTITION BY empid ORDER BY ordermonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS runavgqty , FROM Sales. EmpOrders SELECT empid. ordermonth. qty , SUM (qty ) OVER W1 AS runsumqty , AVG (qty ) OVER W1 AS runavgqty , FROM Sales. EmpOrders WINDOW W1 AS ( PARTITION BY empid ORDER BY ordermonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) As you can see, with the WINDOW clause the code is shorter, more readable, and easier to maintain. I showed just part of the standard support for window functions that SQL Server is still missing. Therersquos more, like window frame exclusion. There are also other set functions not implemented, like ordered set functions, and so on. But here I wanted to make a point in hope that Microsoft will realize how important it is to add such support in SQL Server 11. If you feel so as well, help make a difference by voting for the items, write about the topic, talk about it, increasing peoplersquos awareness. Hopefully this request will find open ears. As a reminder, here are the open items for some of the requests for enhancements: Thank you for expressing so well what I would haveshould have written a long time ago. Personally a SQLSErver fan, I have had to become expert at Oracle to support our platform for the many customers who still think Oracle is better. While my gripes with Oracle would take many pages to begin to express, I DO like its full support of windows functions, especially LEAD and LAG. No reason to add to what has been already so eloquently said in this post. If there is a polling place anywhere to encourage Microsoft to fully support windows function in a future release, please direct me to it Is it too late for 2011 Looking at the OVER clause in BOL at the moment the enhancements seem entirely under whelming msdn. microsoften-uslibraryms18946128SQL.11029.aspx Please Log In or Register to post comments. 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