Moving Average Sql Server 2012

SQL Server 2012 Analysis Services (SSAS) DMVs Von: Scott Murray Lesen Sie Kommentare (6) Verwandte Tipps: Analysis Services Administration Was sind die SQL Server Analysis Services (SSAS) 2012 DMVs und wie können sie verwendet werden In meinem vorherigen Tipp auf XMLA sowohl die Execute - und Discover-Methoden wurden im Zusammenhang mit der Ausführung von XMLA-Abfragen diskutiert. Die Entdeckungsmethode macht Metadaten über eine SSAS-Datenbank aus, die Daten werden jedoch in XML-Form zurückgegeben. Als Alternative zur Discover-Methode bietet SSAS eine Gruppe von dynamischen Managementansichten (kurz DMVs) an, die abgefragt werden können, um dieselben Daten wie die Discover-Methode zurückzugeben. Durch die Verwendung dieser DMVs werden die Daten jedoch in einem tabellarischen Format zurückgegeben, das im Allgemeinen einfacher zu lesen und als Grundlage für Berichte zu verwenden ist. Die Abfragen sind DMX, aber sie haben das Aussehen und das Gefühl von SQL mit einigen Einschränkungen. Die DMVs können in zwei Hauptkategorien zerlegt werden: die DBSCHEMAMDSCHEMA DMVs, die Metadaten über die SSAS-Datenbank abrufen, wie zB Cube-Struktur und Dimensionsstruktur und die Discover DMVs, die Überwachungsdaten wie aktuelle Verbindungen und Sperren abrufen. SSAS MDSCHEMA und DBSCHEMA DMVs Die SSAS DMVs handeln in vielerlei Hinsicht wie normale SQL DMVs und geben Daten in einem Tabellenformat zurück, zumindest in den meisten Fällen. Die Informationen, die abgefragt werden können, decken alles von den Verbindungen ab, die derzeit aktiv sind, um die Menge an Speicher zu verwenden, welche Dimensionen Teil des Würfels sind. Darüber hinaus kannst du sogar eine Dimension abfragen, um ihre Mitglieder zu bekommen. Auch wenn die Abfragen SQL-like sind, können Sie nicht die folgenden verwenden: Letztes und wahrscheinlich gleichermaßen wichtig, um Abfragen gegen die SSAS-Datenbank auszuführen, sind Systemadministrator-Berechtigungen erforderlich. Der beste Weg, um zu zeigen, was die Ansichten tun können, ist, eine Reihe von Beispielen zu überprüfen, die wiederum die Einschränkungen vermitteln werden, die mit einigen der DMVs ins Spiel kommen. Wahrscheinlich ist der beste Platz zum Starten mit einer Abfrage, um eine Liste der DMVs zur Abfrage zu erhalten. In den folgenden MDX-Abfragen wird die Liste der gesendeten Ansichten angezeigt. Beachten Sie, dass die Wortansichten sehr lose verwendet werden, da diese DMVs SQL-like aber nicht reines SQL sind. Um diese Abfragen auszuführen, öffnen Sie SSMS und stellen eine Verbindung zu Ihrer SSAS-Datenbank her, wie unten dargestellt. Für unsere Beispiele werden wir die AdventureWorks 2012 DataWarehouse Beispieldatenbank verwenden, die auf CodePlex, msftdbprodsamples. codeplexreleasesview55330 verfügbar ist. Achten Sie darauf, die AdventureWorksDW2012-Datenbank auszuwählen und zu überprüfen, ob der Abfragetyp auf MDX gesetzt ist. Wenn MDX nicht ausgewählt ist, können Sie den Abfragetyp festlegen, indem Sie in der Symbolleiste auf die Schaltfläche MDX klicken. Die Abfrageergebnisse werden im obigen Bildschirmdruck teilweise angezeigt. Diese Tabellenliste entspricht etwa den DMVs, die zur Abfrage gegen die SSAS-Datenbank zur Verfügung stehen. Für Details zu jedem dieser Zeilensätze verfügt MSDN über ein DMV-Referenzblatt unter: msdn. microsoften-uslibraryhh230820.aspxbkmkref. Als nächstes können wir die folgende Abfrage ausführen, um eine Liste von Cubes in einer bestimmten Datenbank zu erhalten. Natürlich gibt es ein paar Einschränkungen mit den unten aufgeführten Abfrage-Ergebnissen. Zuerst werden Sie feststellen, dass nur zwei Würfel im objektiven Explorer angezeigt werden (linke Seite des obersten Bildschirmdrucks), während die Abfrageergebnisse sieben Zeilen zeigen. Der Grund für diese Diskrepanz ist, dass die Ergebnisse Perspektiven zusätzlich zu den regelmäßigen Würfeln gehören. So ist ein einfacher Weg, um nur die Würfel zurückzukehren und nicht die Perspektiven, um unsere Abfrage anzupassen, wie in der nächsten Abbildung gezeigt. In diesem Fall sind die Kriterien in der where-Klausel ein bisschen Cheat, dass es nach einem leeren Wert für den BaseCubeName sucht, indem er den weniger als Operator verwendet. Als nächstes erhalten wir eine Liste der Dimensionen mit dem MDSCHEMADIMENSIONS DMV. Natürlich gibt es ein paar Einschränkungen mit den unten aufgeführten Abfrage-Ergebnissen. Die Dimensionen werden zum Beispiel mehrfach aufgeführt, die Account-Dimension ist siebenmal aufgeführt. Eine Dimension existiert für jede Würfelmessgruppe zusätzlich, eine Dimension, diejenige, deren Würfelname mit dem beginnt, ist die Würfelpegelabmessung. Außerdem zeigt die Dimensionsbeschriftung den Namen an, den die Endbenutzer sehen. Bohren in die Dimension, können wir als nächstes die MDSCHEMAMEASUREGROUPDIMENSIONS DMV verwenden, wie unten dargestellt. Diese DMV bricht die Dimensionen auf der Maße der Körperschaftsebene ab. Beachten Sie, dass ich zwei Items in der Order By-Klausel enthalten habe. Leider wird mehr als ein Element in der Bestellklausel nicht unterstützt und erzeugt den folgenden Fehler. Mit nur einem Auftrag nach Feld und dem Hinzufügen des Würfelnnamens zu der where-Klausel ergeben sich bessere Ergebnisse, die unten dargestellt sind. Wieder bemerken wir, dass wir mit den MDSCHEMADIMENSIONS DMV Duplikate haben. Bevor Sie zu einigen der Überwachungs-DMVs wechseln, können Sie die Maßnahme MDSCHEMAMEASURES DMV überprüfen. Wie unten dargestellt, vermittelt das MDSCHEMAMEASURES DMV zusätzlich zu den üblichen Namen - und Sichtbarkeitsinformationen, die aus der Dimension DMVs zur Verfügung stehen, das für die Maßnahme verwendete Format und die Aggregationsmethode, die angibt, wie die Maßnahme wie Summe, Durchschnitt oder Benutzerdefiniert ( Siehe msdn. microsoften-uslibraryms126250.aspx). Das DMV zeigt auch die Formel an, die in einem berechneten Maß im Expression-Feld verwendet wird, das bei der Überprüfung mehrerer berechneter Werte zu einem Zeitpunkt nicht berechnete Maßnahme keine Daten im Ausdrucksfeld gibt. SSAS Discover DMVs Die SSAS Discover DMVs ruft Daten ab, die zur Überwachung einer SSAS-Datenbank verwendet werden. Einige Datenpunkte, die abgerufen werden können, umfassen die Abfrageausführungszeiten, aktuelle Sperren, CPU - und Speicherverwendung und aktuelle Verbindungen. Wir beginnen mit dem DISCOVERCONNECTIONS DMV. Diese DMV bietet uns eine Fülle von Details über die aktuellen Sitzungen einschließlich der letzten Befehl ausgeführt werden, wie lange der Befehl zu laufen, wie viel Speicher die Sitzung verwendet, und wie viele Lese-und Schreibvorgänge verwendet wurden. Diese DISCOVERCOMMANDS DMV gibt uns ähnliche Informationen auf Befehlsebene. Sie werden auch feststellen, dass Sie eine Auswahl treffen können, um alle Spalten aus dem DMV abzurufen, aber wie bei anderen select-Anweisungen würde ich nur das Abrufen der benötigten Spalten empfehlen. Einige der Discover DMVs erfordern die Verwendung von SYSTEMRESTRICTSCHEMA und verlangen auch zusätzliche Parameter. Im Wesentlichen bedeutet dies, dass anstatt ein direktes Auswählen direkt gegen das DMV zu schreiben, das SYSTEMRESTRICTSCHEMA abzufragen und dann das DMV und alle anderen Parameter anzugeben, die beim Schreiben der Abfrage erforderlich sind. Zum Beispiel integriert die untere Abfrage die DISCOVERINSTANCES DMV. In diesem Fall ist nur ein Parameter erforderlich, INSTANCENAME. Beachten Sie, wie die von der SYSTEMRESTRICTSCHMEA verwendet wird. Die DISCOVERINSTANCES DMV gibt folgende Daten zur Instanz zurück. Mit dem SYSTEM. DBSCHEMATABLES DMV können Sie viele der anderen Discover SSAS DMVs erkunden. Schlussfolgerung Mit dem SSAS DMVs kann eine Fülle von Informationen über eine SSAS Cubes Metadaten und ein Cubes aktuellen Zustand, von einem Monitoring Standpunkt. Diese DMVs sind einfacher zu verwenden als XMLA-bezogene Abfragen, da die Daten im tabellarischen Format zurückgegeben werden und die Abfragen in den meisten Fällen in einer SQL-ähnlichen Weise geschrieben werden. Die Ergebnisse, abhängig von der DMV, können einen Großteil der Informationen liefern, die für einen SSAS-Administrator benötigt werden, um die SSAS-Cubes zu profilieren und zu überwachen. Nächste Schritte Letzte Aktualisierung: 7262013Mehr Leute sind mit der Phrase vertraut, quotthis wird zwei Vögel mit einem Steinbruch töten. Wenn es nicht geht, bezieht sich die Phase auf einen Ansatz, der zwei Ziele in einer Handlung anspricht. (Leider ist der Ausdruck selbst ziemlich unangenehm, da die meisten von uns es nicht wollen, Steine ​​auf unschuldige Tiere zu werfen) Heute I39m, um einige Grundlagen auf zwei großartige Features in SQL Server zu decken: der Columnstore-Index (nur in SQL Server Enterprise verfügbar) und Der SQL Query Store. Microsoft hat tatsächlich den Columnstore-Index in SQL 2012 Enterprise implementiert, obwohl sie es in den letzten beiden Releases von SQL Server erweitert haben. Microsoft hat den Query Store in SQL Server 2016 eingeführt. Also, was sind diese Features und warum sind sie wichtig Nun, ich habe eine Demo, die beide Features vorstellen und zeigen, wie sie uns helfen können. Bevor ich noch weiter gehe, decke ich auch diese (und andere SQL 2016 Features) in meinem CODE Magazin Artikel über neue Features SQL 2016. Als Grundeinführung kann der Columnstore Index dazu beitragen, Abfragen zu beschleunigen, die über große Datenmengen scanaggregieren und Der Abfrage-Store verfolgt Abfragen Ausführungen, Ausführungspläne und Laufzeitstatistiken, die Sie normalerweise manuell erfassen müssen. Vertrauen Sie mir, wenn ich sage, das sind tolle Features. Für diese Demo werde ich die Microsoft Contoso Retail Data Warehouse Demo Datenbank verwenden. Lose gesprochen, Contoso DW ist wie Quota wirklich große AdventureWorksquot, mit Tischen mit Millionen von Zeilen. (Die größte AdventureWorks-Tabelle enthält höchstens 100.000 Zeilen). Sie können die Contoso DW Datenbank hier herunterladen: microsoften-usdownloaddetails. aspxid18279. Contoso DW arbeitet sehr gut, wenn man die Leistung bei Abfragen gegen größere Tische testen möchte. Contoso DW enthält eine Standard-Data Warehouse Fact-Tabelle namens FactOnLineSales mit 12,6 Millionen Zeilen. Das ist sicherlich nicht der größte Data-Warehouse-Tisch der Welt, aber es geht auch nicht um Kinder. Angenommen, ich möchte die Produktverkäufe für 2009 zusammenfassen und die Produkte bewerten. Ich könnte die Faktentabelle abfragen und sich der Produktdimensionstabelle anschließen und eine RANK-Funktion verwenden, wie folgt: Hier ist ein Teilergebnissatz der Top 10 Zeilen, bei Total Sales. Auf meinem Laptop (i7, 16 GB RAM), nimmt die Abfrage überall von 3-4 Sekunden zu laufen. Das scheint vielleicht nicht wie das Ende der Welt zu sein, aber manche Benutzer könnten nahezu sofortige Ergebnisse erwarten (wie du bei der Verwendung von Excel gegen einen OLAP-Cube nahezu sofortige Ergebnisse sehen kannst). Der einzige Index, den ich derzeit auf dieser Tabelle habe, ist ein gruppierter Index auf einem Umsatzschlüssel. Wenn ich den Ausführungsplan betrachte, macht SQL Server einen Vorschlag, einen Deckelindex der Tabelle hinzuzufügen: Nun, nur weil SQL Server einen Index vorschlägt, bedeutet das nicht, dass du blindlings Indizes für jede quotmissing Indexquot-Nachricht erstellen solltest. In diesem Fall erkennt SQL Server jedoch, dass wir basierend auf dem Jahr filtern und den Produktschlüssel und den Verkaufsbetrag verwenden. So schlägt SQL Server einen Deckungsindex vor, mit dem DateKey als Indexschlüsselfeld. Der Grund, warum wir dies einen quotcoveringquot-Index nennen, liegt daran, dass SQL Server die Nicht-Schlüsselfelder quittiert, die wir in der Abfrage verwendet haben, für den Ridequot. Auf diese Weise muss SQL Server nicht die Tabelle oder den gruppierten Index verwenden, die Datenbank-Datenbank kann einfach den Deckungsindex für die Abfrage verwenden. Covering-Indizes sind in bestimmten Data-Warehousing und Reporting-Datenbank-Szenarien beliebt, obwohl sie zu einem Preis der Datenbank-Engine beibehalten sie kommen. Anmerkung: Die Bedeckungsindizes sind schon lange her, also habe ich noch den Columnstore-Index und den Query Store abgedeckt. Also, ich werde den Deckungsindex hinzufügen: Wenn ich die gleiche Abfrage erneut ausführen würde, lief ich vor kurzem (derjenige, der die Verkaufsmenge für jedes Produkt aggregierte), die Abfrage scheint manchmal um eine Sekunde schneller zu laufen, und ich bekomme ein Ein anderer Ausführungsplan, der einen Index-Suchvorgang anstelle eines Index-Scans verwendet (mit dem Datumsschlüssel auf dem Deckungsindex, um den Umsatz für 2009 abzurufen). Also, vor dem Columnstore Index, könnte dies eine Möglichkeit sein, diese Abfrage in viel älteren Versionen von SQL Server zu optimieren. Es läuft ein wenig schneller als das erste, und ich bekomme einen Ausführungsplan mit einem Index Seek anstelle eines Index-Scans. Allerdings gibt es einige Probleme: Die beiden Ausführungsoperatoren quotIndex Seekquot und quotHash Match (Aggregate) beide betreiben im Wesentlichen Zitat durch rowquot. Stellen Sie sich das in einem Tisch mit Hunderten von Millionen von Reihen vor. Verwandte, über den Inhalt einer Faktentabelle nachdenken: In diesem Fall kann ein einziger Datumsschlüsselwert und ein einzelner Produktschlüsselwert über Hunderte von Tausenden von Zeilen wiederholt werden (erinnern Sie sich, dass die Faktentabelle auch Schlüssel für Geographie, Promotion, Verkäufer hat , Etc.) Also, wenn die quotIndex Seekquot und quotHash Matchquot Zeile für Zeile arbeiten, tun sie dies über Werte, die möglicherweise über viele andere Zeilen wiederholt werden. Dies ist normalerweise, wo I39d zu dem SQL Server Columnstore Index, der ein Szenario bietet, um die Leistung dieser Abfrage auf erstaunliche Weise zu verbessern. Aber bevor ich das mache, gehen wir zurück in die Zeit. Let39s gehen zurück auf das Jahr 2010, als Microsoft ein Add-In für Excel als PowerPivot bekannt. Viele Leute erinnern sich wahrscheinlich daran, Demos von PowerPivot für Excel zu sehen, wo ein Benutzer Millionen von Zeilen aus einer externen Datenquelle in Excel lesen konnte. PowerPivot würde die Daten komprimieren und eine Engine zur Verfügung stellen, um Pivot-Tabellen und Pivot-Charts zu erstellen, die mit erstaunlichen Geschwindigkeiten gegen die komprimierten Daten durchgeführt wurden. PowerPivot verwendete eine In-Memory-Technologie, die Microsoft als quotVertiPaqquot bezeichnete. Diese In-Memory-Technologie in PowerPivot würde grundsätzlich doppelte Business Keyforeign Schlüsselwerte und komprimieren sie auf einen einzigen Vektor. Die In-Memory-Technologie würde diese Werte auch parallel in Blöcken von mehreren hundert zu einer Zeit scanaggregieren. Die Quintessenz ist, dass Microsoft eine große Menge an Leistungsverbesserungen in die VertiPaq In-Memory-Funktion für uns zu verwenden, direkt aus der sprichwörtlichen Box gebacken. Warum gehe ich diesen kleinen Spaziergang in die Erinnerungsspur Weil in SQL Server 2012 Microsoft eine der wichtigsten Features in der Geschichte ihrer Datenbank-Engine implementiert hat: den Columnstore-Index. Der Index ist wirklich ein Index im Namen nur: Es ist ein Weg, um eine SQL Server-Tabelle und erstellen Sie eine komprimierte, in-Memory-Spalte Store, die doppelte Fremdschlüssel Werte bis hin zu einzelnen Vektor-Werte komprimiert. Microsoft hat auch einen neuen Pufferpool erstellt, um diese komprimierten Vektorwerte parallel zu lesen und so das Potenzial für große Leistungssteigerungen zu schaffen. Also, ich werde einen Spaltenladen-Index auf dem Tisch erstellen und ich sehe, wie viel besser (und effizienter) die Abfrage läuft, im Vergleich zu der Abfrage, die gegen den Deckungsindex läuft. Also schaffe ich eine doppelte Kopie von FactOnlineSales (I39ll nennen es FactOnlineSalesDetailNCCS), und ich erstelle einen columnstore Index auf der duplizierten Tabelle, wie ich mich mit der ursprünglichen Tabelle und dem Cover-Index in irgendeiner Weise interferiere. Als nächstes erstelle ich einen columnstore-Index auf der neuen Tabelle: Notiere mehrere Dinge: I39ve spezifiziert mehrere Fremdschlüsselspalten sowie den Verkaufsbetrag. Denken Sie daran, dass ein columnstore-Index nicht wie ein traditioneller row-store-Index ist. Es gibt kein quotkeyquot. Wir geben einfach an, welche Spalten SQL Server komprimieren und in eine Spalte im Speicher speichern soll. Um die Analogie von PowerPivot für Excel zu verwenden, wenn wir einen columnstore-Index erstellen, erzählen wir SQL Server im Wesentlichen, dasselbe, was PowerPivot tat, als wir 20 Millionen Zeilen in Excel importierten, mit PowerPivot So, I39ll die Abfrage erneut ausführen, diesmal mit Die duplizierte FactOnlineSalesDetailNCCS-Tabelle, die den columnstore-Index enthält. Diese Abfrage läuft sofort in weniger als einer Sekunde. Und ich kann auch sagen, dass selbst wenn der Tisch Hunderte von Millionen von Reihen hatte, würde es immer noch auf dem sprichwörtlichen Quatschen eines Wimperns laufen. Wir könnten uns den Ausführungsplan anschauen (und in wenigen Augenblicken werden wir), aber jetzt ist es Zeit, die Query Store zu decken. Stellen Sie sich für einen Moment vor, dass wir beide Fragen über Nacht geführt haben: die Abfrage, die die reguläre FactOnlineSales-Tabelle (mit dem Cover-Index) und dann die Abfrage verwendet hat, die die doppelte Tabelle mit dem Columnstore-Index verwendet hat. Wenn wir uns am nächsten Morgen anmelden, sehen wir gern den Ausführungsplan für beide Abfragen, wie sie stattgefunden haben, sowie die Ausführungsstatistik. Mit anderen Worten, wir sehen gern die gleichen Statistiken, die wir sehen können, ob wir beide Abfragen interaktiv in SQL Management Studio geleitet haben, in TIME und IO Statistics gedreht und den Ausführungsplan direkt nach dem Ausführen der Abfrage angesehen haben. Nun, das, was der Abfrage-Shop ermöglicht es uns, können wir einschalten (aktivieren) Query Store für eine Datenbank, die SQL Server auslösen wird, um Abfrageausführung zu speichern und Planstatistiken zu speichern, damit wir sie später sehen können. Also, um den Query Store auf der Contoso-Datenbank mit dem folgenden Befehl zu aktivieren (und I39ll auch aus irgendeinem Caching herauszuholen): Dann kommst du die beiden Abfragen aus (und quotpretendquot, dass ich sie vor Stunden lief): Jetzt lassen wir sie vor, dass sie Stunden laufen ließen vor. Nach dem, was ich sagte, wird der Query Store die Ausführungsstatistiken erfassen. Also, wie sehe ich sie Glücklicherweise ist das ganz einfach. Wenn ich die Contoso DW Datenbank erweitere, sehe ich einen Query Store Ordner. Der Query Store hat enorme Funktionalität und ich versuche, viel davon in den nachfolgenden Blogeinträgen zu decken. Aber für jetzt möchte ich die Ausführungsstatistiken auf die beiden Abfragen anzeigen und die Ausführungsoperatoren für den Spaltenstartindex genau untersuchen. So klicken Sie mit der rechten Maustaste auf die Top Resource Consuming Queries und führen diese Option aus. Das gibt mir ein Diagramm wie das unten, wo ich die Ausführungsdauer (in Millisekunden) für alle abgefragten Abfragen sehen kann. In diesem Fall war Abfrage 1 die Abfrage gegen die ursprüngliche Tabelle mit dem Deckungsindex, und Query 2 war gegen die Tabelle mit dem Spaltenstartindex. Die Zahlen, die der Säulenständerindex liegen, übertrafen den ursprünglichen Tischdeckungsindex um einen Faktor von fast 7 zu 1. Ich kann die Metrik ändern, um den Speicherverbrauch zu betrachten. In diesem Fall ist zu beachten, dass die Abfrage 2 (die Spaltenstart-Indexabfrage) weit mehr Speicher verwendet hat. Dies zeigt deutlich, warum der columnstore-Index die quotin-memoryquot-Technologie darstellt. SQL Server lädt den gesamten columnstore-Index im Speicher und verwendet einen völlig anderen Pufferpool mit erweiterten Ausführungsoperatoren, um den Index zu verarbeiten. OK, also haben wir einige Graphen, um die Ausführungsstatistiken zu sehen, können wir den Ausführungsplan (und Ausführungsoperatoren), die mit jeder Ausführung verknüpft sind, sehen. Ja, wir können Wenn Sie auf die vertikale Leiste für die Abfrage klicken, die den Spaltenladenindex verwendet hat, sehen Sie die Ausführung Plan unten Das erste, was wir sehen, ist, dass SQL Server eine Spalte Stores Index-Scan durchgeführt, und das stellte fast 100 der Kosten der Abfrage. Sie könnten sagen, quotWait eine Minute, die erste Abfrage verwendet einen Deckungsindex und führte einen Index suchen, so wie kann ein Spaltenstart-Index-Scan schneller sein. Das ist eine legitime Frage, und zum Glück gibt es eine Antwort. Sogar wenn die erste Abfrage eine Indexsuche durchgeführt hat, hat sie immer noch einen Satz durch rowquot ausgeführt. Wenn ich die Maus über den Spaltenstart-Index-Scan-Operator stecke, sehe ich eine Tooltip (wie die unten unten) mit einer wichtigen Einstellung: Der Execution Mode ist BATCH (im Gegensatz zu ROW, was wir mit der ersten Abfrage mit dem Deckungsindex). Dieser BATCH-Modus sagt uns, dass SQL Server die komprimierten Vektoren (für alle Fremdschlüsselwerte, die dupliziert werden, wie zB Produktschlüssel und Datumsschlüssel) in Chargen von fast 1.000 parallel verarbeiten. So ist SQL Server immer noch in der Lage, den columnstore Index viel effizienter zu verarbeiten. Darüber hinaus, wenn ich die Maus über die Hash Match (Aggregate) Aufgabe platziere, sehe ich auch, dass SQL Server aggregiert den columnstore Index mit Batch-Modus (obwohl der Betreiber selbst stellt so ein winziges Prozent der Kosten der Abfrage) Endlich, Sie Könnte gefragt werden, quotOK, so dass SQL Server die Werte in den Daten komprimiert, behandelt die Werte als Vektoren und liest sie in Blöcken von fast tausend Werten parallel, aber meine Abfrage wollte nur Daten für 2009. So ist SQL Server Scannen über die Ganzer Satz von Datenquot Wieder eine gute Frage. Die Antwort ist, quotNot wirklich quot. Glücklicherweise für uns, der neue columnstore Index Puffer Pool führt eine andere Funktion namens quotsegment eliminationquot. Grundsätzlich wird SQL Server die Vektorwerte für die Datumsschlüsselspalte im Spaltenstartindex untersuchen und Segmente eliminieren, die außerhalb des Geltungsbereichs des Jahres 2009 liegen. In den darauffolgenden Blogposten decken Sie den Spaltenstartindex und den Query Store detaillierter ab. Im Wesentlichen, was wir heute hier gesehen haben, ist, dass der Columnstore-Index erheblich beschleunigen kann Abfragen, die über große Datenmengen scanaggregieren, und der Query Store wird Abfrage-Ausführungen erfassen und erlauben uns, Ausführungs - und Leistungsstatistiken später zu untersuchen. Am Ende werden wir gerne eine Ergebnismenge produzieren, die folgendes zeigt. Beachten Sie drei Sachen: Die Spalten verschwinden im Wesentlichen alle möglichen Rückgabegründe, nachdem Sie den Verkaufsbetrag angelegt haben. Die Ergebnismenge enthält Zwischensummen nach Wochenende (Sonntag) Datum über alle Clients (wo der Client NULL ist) Die Ergebnismenge enthält eine Gesamtsumme Zeile (wo der Client und das Datum sind beide NULL) Erstens, bevor ich in das SQL-Ende gelangen, könnten wir die dynamische Pivotmatrix-Fähigkeit in SSRS nutzen. Wir müssten einfach die beiden Ergebnismengen um eine Spalte kombinieren und dann könnten wir die Ergebnisse der SSRS-Matrixsteuerung zuführen, die die Rückgabegründe über die Spaltenachse des Berichts verbreiten wird. Allerdings nutzt nicht jeder SSRS (obwohl die meisten Leute sollten). Aber auch damals müssen die Entwickler die Ergebnismengen in etwas anderes als ein Reporting-Tool verbrauchen. Also für dieses Beispiel gehen wir davon aus, dass wir die Ergebnismenge für eine Web-Raster-Seite generieren wollen und evtl. der Entwickler die Zwischensummen-Zeilen (wo ich einen ResultSetNum-Wert von 2 und 3 habe) ausgeben und sie in ein zusammenfassendes Raster stellen möchte. Also Grundlinie, wir müssen die Ausgabe direkt aus einer gespeicherten Prozedur generieren. Und als eine weitere Wendung nächste Woche könnte es geben Rückkehr X und Y und Z. Also wir don39t wissen, wie viele Rückkehr Gründe dort sein könnte. Wir wollen einfach die Abfrage auf die möglichen unterschiedlichen Werte für Return Reason platzieren. Hier ist, wo die T-SQL PIVOT hat eine Beschränkung müssen wir ihm die möglichen Werte. Da wir wissen, dass bis zur Laufzeit, müssen wir die Abfrage Zeichenfolge dynamisch mit dem dynamischen SQL-Muster zu generieren. Das dynamische SQL-Muster beinhaltet das Erzeugen der Syntax, Stück für Stück, das Speichern in einem String und das Ausführen des Strings am Ende. Dynamic SQL kann schwierig sein, da wir die Syntax in einen String einbetten müssen. Aber in diesem Fall ist es unsere einzige wahre Option, wenn wir eine variable Anzahl von Rückgabegründen behandeln wollen. Ich habe immer herausgefunden, dass der beste Weg, um eine dynamische SQL-Lösung zu schaffen, ist, herauszufinden, was die quittale Quotie-Abfrage am Ende sein würde (in diesem Fall angesichts der Rückkehr Gründe, die wir kennen) und dann Reverse-Engineering es durch Piecing Es zusammen ein Teil zu einer Zeit. Und so ist hier das SQL, das wir brauchen, wenn wir wüssten, dass die Rückkehrgründe (A bis D) statisch waren und sich nicht ändern würden. Die Abfrage führt die folgenden aus: Kombiniert die Daten aus SalesData mit den Daten von ReturnData, wo wir das Wort Sales als Aktionstyp aus der Verkaufstabelle quonarddraht und dann den Rückgabegrund aus den Rückgabedaten in dieselbe ActionType-Spalte verwenden. Das gibt uns eine saubere ActionType Spalte, auf der man sich drehen kann. Wir kombinieren die beiden SELECT-Anweisungen in einen gemeinsamen Tabellenausdruck (CTE), der grundsätzlich eine abgeleitete Tabellenunterabfrage ist, die wir später in der nächsten Anweisung (zu PIVOT) verwenden. Eine PIVOT-Anweisung gegen den CTE, die die Dollars für den Aktionstyp summiert In einem der möglichen Aktionstyp-Werte liegen. Beachten Sie, dass dies die endgültige Ergebnismenge ist. Wir stellen diese in einen CTE, der aus dem ersten CTE liest. Der Grund dafür ist, dass wir am Ende mehrere Gruppierungen machen wollen. Die endgültige SELECT-Anweisung, die von der PIVOTCTE liest und sie mit einer nachfolgenden Abfrage gegen dieselbe PIVOTCTE kombiniert, aber wo wir auch zwei Gruppierungen in der GROUPING SETS-Funktion in SQL 2008 implementieren: GROUPING nach dem Wochenenddatum (dbo. WeekEndingDate) GRUPPE für alle Zeilen () Also, wenn wir mit Sicherheit wüssten, dass wir niemals mehr Rückgaberecht-Codes haben, dann wäre das die Lösung. Allerdings müssen wir andere Ursachencodes berücksichtigen. Also müssen wir diese ganze Abfrage als eine große Zeichenkette generieren, wo wir die möglichen Rückkehrgründe als eine durch Kommas getrennte Liste konstruieren. I39m zeigt den gesamten T-SQL-Code zu generieren (und ausführen) die gewünschte Abfrage. Und dann brechen sie in Teile und erklären jeden Schritt. Also zuerst, hier ist der ganze Code, um dynamisch zu generieren, was ich oben bekommen habe. Es gibt grundsätzlich fünf Schritte, die wir abdecken müssen. Schritt 1 . Wir wissen, dass irgendwo in der Mischung, müssen wir einen String für diese in der Abfrage zu generieren: SalesAmount, Grund A, Grund B, Grund C, Grund D0160016001600160 Was wir tun können, ist ein temporärer gemeinsamer Tabellenausdruck gebaut, der die hart verdrahteten Quoten kombiniert Anzahl der Spalten mit der eindeutigen Liste der möglichen Ursachencodes. Sobald wir das in einem CTE haben, können wir den netten kleinen Trick von FOR XML PATH (3939) benutzen, um diese Zeilen in eine einzelne Saite zu kollabieren, ein Komma vor jeder Zeile zu setzen, die die Abfrage liest und dann STUFF verwenden, um zu ersetzen Die erste Instanz eines Kommas mit einem leeren Raum. Dies ist ein Trick, den Sie in Hunderten von SQL-Blogs finden können. Also dieser erste Teil baut einen String namens ActionString, den wir weiter unten verwenden können. Schritt 2 . Wir wissen auch, dass wir die generierten pivotierten Grundspalten, zusammen mit der Standard-Verkaufsspalte, sortieren möchten. Also brauchen wir dafür einen separaten String, den ich SUMSTRING nenne. Ich benutze einfach den originalen ActionString und schalte dann die äußeren Klammern mit SUM-Syntax und den originalen Klammern wieder auf. Schritt 3: Jetzt beginnt die eigentliche Arbeit. Wenn wir diese Originalabfrage als Modell verwenden, wollen wir die ursprüngliche Abfrage generieren (beginnend mit der UNION der beiden Tabellen), aber das Ersetzen von Referenzen auf verschwenkte Spalten mit den Strings, die wir dynamisch oben erstellt haben. Auch, während nicht unbedingt erforderlich, habe ich auch eine Variable zu einfach jedem Wagen Rücklauf Feed Kombinationen, die wir in die generierte Abfrage (für Lesbarkeit) eingebettet wollen. So konstruieren wir die gesamte Abfrage in eine Variable namens SQLPivotQuery. Schritt 4 . Wir fortsetzen, die Abfrage erneut zu konstruieren und die Syntax zu verknüpfen, die wir mit dem ActionSelectString quothard-wirequot verknüpfen können (die wir dynamisch generiert haben, um alle möglichen Rückgabewerte zu halten) Schritt 5. Schließlich werden wir den letzten Teil der Pivot-Abfrage generieren, der aus dem 2. gemeinsamen Tabellenausdruck (PIVOTCTE, aus dem obigen Modell) liest und das endgültige SELECT aus dem PIVOTCTE liest und es mit einem 2. gelesenen PIVOTCTE kombiniert Implementiere die gruppierungssätze. Schließlich können wir quotexecutequot die Zeichenfolge mit dem SQL-System gespeichert proc spexecuteSQL So hoffentlich können Sie sehen, dass der Prozess, um für diese Art von Aufwand zu bestimmen ist festzustellen, was die endgültige Abfrage wäre, basierend auf Ihrem aktuellen Satz von Daten und Werte (dh gebaut Ein Abfrage-Modell) Schreiben Sie den notwendigen T-SQL-Code, um dieses Abfrage-Modell als String zu generieren. Der wichtigste Teil ist die Bestimmung des einzigartigen Satzes von Werten, auf denen du POLOTSPOLOTT, und dann kollabierst du sie in eine Zeichenkette mit der STUFF-Funktion und dem FOR XML PATH (3939) Trick Also was ist mir in den Kopf Nun, mindestens 13 Artikel Zwei Vor kurzem habe ich einen BDR geschrieben, der sich zum Teil auf die Rolle der Bildung und den Wert eines guten liberalen Kunsthintergrundes konzentrierte, nicht nur für die Softwareindustrie, sondern auch für andere Branchen. Einer der Themen dieses BDR betonte einen entscheidenden und erleuchteten Standpunkt des renommierten Softwarearchitekten Allen Holub in Bezug auf freie Kunst. Ill (treu) paraphrasieren seine Botschaft: er hob die Parallelen zwischen Programmierung und Studium der Geschichte, indem sie alle daran erinnern, dass die Geschichte liest und schreibt (und krank hinzufügen, identifizieren Muster), und Software-Entwicklung ist auch Lesen und Schreiben (und wieder, Identifizierung von Mustern ). Und so schrieb ich eine Meinung Stück, das auf diese und andere verwandte Themen konzentriert. Aber bis heute habe ich mich nie dazu veranlasst, es zu veröffentlichen. Jeder so oft Id denke daran, es zu überarbeiten, und Id sogar sitzen für ein paar Minuten und machen einige Anpassungen an sie. Aber dann würde das Leben im Allgemeinen in den Weg gehen und Id nie beenden. Also, was hat sich vor ein paar Wochen, Kollegen CoDe Magazine Kolumnist und Branchenführer Ted Neward schrieb ein Stück in seiner regulären Spalte, Managed Coder, die meine Aufmerksamkeit gefangen. Der Titel des Artikels ist On Liberal Arts. Und ich empfehle, dass jeder es liest. Ted diskutiert den Wert eines liberalen Kunsthintergrundes, die falsche Dichotomie zwischen einem liberalen Kunsthintergrund und Erfolg in der Softwareentwicklung und die Notwendigkeit, gut zu schreiben. Er spricht über einige seiner eigenen vergangenen Begegnungen mit HR Personalmanagement in Bezug auf seinen pädagogischen Hintergrund. Er unterstreicht auch die Notwendigkeit, sich an Veränderungen in unserer Branche anzupassen und anzupassen, sowie die Kennzeichen eines erfolgreichen Software-Profis (zuverlässig, vorausschauend und lernen, anfänglichen Konflikt mit anderen Teammitgliedern zu kommen). So ist es ein tolles Lesen, wie sind Teds andere CoDe Artikel und Blog-Einträge. Es hat mich auch dazu gebracht, über meine Ansichten zu diesem (und anderen Themen) nachzudenken und mich schließlich zu motivieren, meine eigene Redaktion zu beenden. Also, besser spät als nie, hier sind meine aktuellen Bäcker Dutzend von Reflexionen: Ich habe ein Sprichwort: Wasser friert bei 32 Grad ein. Wenn du in einer Trainingsrolle bist, kannst du dich vielleicht in der Welt alles tun, um jemanden zu helfen, der in der Tat nur eine Temperatur von 34 Grad fühlt und deshalb die Dinge für sie verfestigen. Manchmal dauert es nur ein bisschen mehr Aufwand oder einen anderen ideachemischen Katalysator oder eine neue Perspektive, die bedeutet, dass die mit vorheriger Ausbildung auf unterschiedliche Quellen zurückgreifen können. Wasser friert bei 32 Grad ein. Manche Menschen können ein hohes Maß an Konzentration auch mit einem Raum voller lärmender Menschen beibehalten. Im nicht einer von ihnen gelegentlich brauche ich etwas Privatsphäre, um durch eine kritische Frage zu denken. Manche Leute beschreiben dies, wie Sie lernen müssen, weg von ihm zu gehen. Ein anderer Weg, es ist eine Suche nach der verdünnten Luft. In der vergangenen Woche verbrachte ich stundenlang in halb beleuchtetem, ruhigem Zimmer mit einem Whiteboard, bis ich ein Problem völlig verstanden habe. Erst dann konnte ich mit anderen Entwicklern über eine Lösung sprechen. Die Botschaft hier ist nicht zu predigen, wie Sie über Ihr Geschäft der Lösung von Problemen gehen sollten, sondern für alle, ihre Stärken zu kennen und was funktioniert, und nutzen sie zu Ihrem Vorteil so viel wie möglich. Einige Phrasen sind wie Fingernägel an einer Tafel für mich. Verwenden Sie es als Unterricht Moment ist eins. (Warum ist es wie Fingernägel an einer Tafel Denn wenn du in einer Mentoring-Rolle bist, dann solltest du in der Regel den Moment-Modus unterrichten, aber subtil). Heres ein anderes kann ich nicht wirklich erklären, es in Worte, aber ich verstehe es. Das klingt ein bisschen kalt, aber wenn eine Person wirklich nicht etwas in Worte erklären kann, vielleicht versteht sie es nicht. Sicher, eine Person kann einen unscharfen Sinn haben, wie etwas funktioniert, kann ich bluffen meinen Weg durch die Beschreibung, wie eine Digitalkamera funktioniert, aber die Wahrheit ist, dass ich nicht wirklich verstehen, dass alles so gut. Es gibt ein Feld der Studie bekannt als Erkenntnistheorie (das Studium des Wissens). Eine der grundlegenden Grundlagen des Verständnisses, ob es sich um eine Kamera oder ein Designmuster handelt - ist die Fähigkeit, den Kontext zu etablieren, die Kette der verwandten Ereignisse zu identifizieren, die Attribute aller Komponenten auf dem Weg usw. Ja, das Verständnis ist manchmal sehr harte Arbeit , Aber tauchen in ein Thema und brechen es auseinander ist die Mühe wert. Even those who eschew certification will acknowledge that the process of studying for certification tests will help to fill gaps in knowledge. A database manager is more likely to hire a database developer who can speak extemporaneously (and effortlessly) about transaction isolation levels and triggers, as opposed to someone who sort of knows about it but struggles to describe their usage. Theres another corollary here. Ted Neward recommends that developers take up public speaking, blogging, etc. I agree 100. The process of public speaking and blogging will practically force you to start thinking about topics and breaking down definitions that you might have otherwise taken for granted. A few years ago I thought I understood the T-SQL MERGE statement pretty well. But only after writing about it, speaking about, fielding questions from others who had perspectives that never occurred to me that my level of understanding increased exponentially. I know a story of a hiring manager who once interviewed an authordeveloper for a contract position. The hiring manager was contemptuous of publications in general, and barked at the applicant, So, if youre going to work here, would you rather be writing books or writing code Yes, Ill grant that in any industry there will be a few pure academics. But what the hiring manager missed was the opportunities for strengthening and sharpening skill sets. While cleaning out an old box of books, I came across a treasure from the 1980s: Programmers at Work. which contains interviews with a very young Bill Gates, Ray Ozzie, and other well-known names. Every interview and every insight is worth the price of the book. In my view, the most interesting interview was with Butler Lampson. who gave some powerful advice. To hell with computer literacy. Its absolutely ridiculous. Study mathematics. Learn to think. Read. Write. These things are of more enduring value. Learn how to prove theorems: A lot of evidence has accumulated over the centuries that suggests this skill is transferable to many other things. Butler speaks the truth . Ill add to that point learn how to play devils advocate against yourself. The more you can reality-check your own processes and work, the better off youll be. The great computer scientistauthor Allen Holub made the connection between software development and the liberal arts specifically, the subject of history. Here was his point: what is history Reading and writing. What is software development Among other things, reading and writing . I used to give my students T-SQL essay questions as practice tests. One student joked that I acted more like a law professor. Well, just like Coach Donny Haskins said in the movie Glory Road, my way is hard. I firmly believe in a strong intellectual foundation for any profession. Just like applications can benefit from frameworks, individuals and their thought processes can benefit from human frameworks as well. Thats the fundamental basis of scholarship. There is a story that back in the 1970s, IBM expanded their recruiting efforts in the major universities by focusing on the best and brightest of liberal arts graduates. Even then they recognized that the best readers and writers might someday become strong programmersystems analysts. (Feel free to use that story to any HR-type who insists that a candidate must have a computer science degree) And speaking of history: if for no other reason, its important to remember the history of product releases if Im doing work at a client site thats still using SQL Server 2008 or even (gasp) SQL Server 2005, I have to remember what features were implemented in the versions over time. Ever have a favorite doctor whom you liked because heshe explained things in plain English, gave you the straight truth, and earned your trust to operate on you Those are mad skills . and are the result of experience and HARD WORK that take years and even decades to cultivate. There are no guarantees of job success focus on the facts, take a few calculated risks when youre sure you can see your way to the finish line, let the chips fall where they may, and never lose sight of being just like that doctor who earned your trust. Even though some days I fall short, I try to treat my client and their data as a doctor would treat patients. Even though a doctor makes more money There are many clichs I detest but heres one I dont hate: There is no such thing as a bad question. As a former instructor, one thing that drew my ire was hearing someone criticize another person for asking a supposedly, stupid question. A question indicates a person acknowledges they have some gap in knowledge theyre looking to fill. Yes, some questions are better worded than others, and some questions require additional framing before they can be answered. But the journey from forming a question to an answer is likely to generate an active mental process in others. There are all GOOD things. Many good and fruitful discussions originate with a stupid question. I work across the board in SSIS, SSAS, SSRS, MDX, PPS, SharePoint, Power BI, DAX all the tools in the Microsoft BI stack. I still write some code from time to time. But guess what I still spend so much time doing writing T-SQL code to profile data as part of the discovery process. All application developers should have good T-SQL chops. Ted Neward writes (correctly) about the need to adapt to technology changes. Ill add to that the need to adapt to clientemployer changes. Companies change business rules. Companies acquire other companies (or become the target of an acquisition). Companies make mistakes in communicating business requirements and specifications. Yes, we can sometimes play a role in helping to manage those changes and sometimes were the fly, not the windshield. These sometimes cause great pain for everyone, especially the I. T. people. This is why the term fact of life exists we have to deal with it. Just like no developer writes bug-free code every time, no I. T. person deals well with change every single time. One of the biggest struggles Ive had in my 28 years in this industry is showing patience and restraint when changes are flying from many different directions. Here is where my prior suggestion about searching for the rarified air can help. If you can manage to assimilate changes into your thought process, and without feeling overwhelmed, odds are youll be a significant asset. In the last 15 months Ive had to deal with a huge amount of professional change. Its been very difficult at times, but Ive resolved that change will be the norm and Ive tried to tweak my own habits as best I can to cope with frequent (and uncertain) change. Its hard, very hard. But as coach Jimmy Duggan said in the movie A League of Their Own: Of course its hard. If it wasnt hard, everyone would do it. The hard, is what makes it great . A powerful message. Theres been talk in the industry over the last few years about conduct at professional conferences (and conduct in the industry as a whole). Many respected writers have written very good editorials on the topic. Heres my input, for what its worth. Its a message to those individuals who have chosen to behave badly: Dude, it shouldnt be that hard to behave like an adult. A few years ago, CoDe Magazine Chief Editor Rod Paddock made some great points in an editorial about Codes of Conduct at conferences. Its definitely unfortunate to have to remind people of what they should expect out of themselves. But the problems go deeper. A few years ago I sat on a five-person panel (3 women, 2 men) at a community event on Women in Technology. The other male stated that men succeed in this industry because the Y chromosome gives men an advantage in areas of performance. The individual who made these remarks is a highly respected technology expert, and not some bozo making dongle remarks at a conference or sponsoring a programming contest where first prize is a date with a bikini model. Our world is becoming increasingly polarized (just watch the news for five minutes), sadly with emotion often winning over reason. Even in our industry, recently I heard someone in a position of responsibility bash software tool XYZ based on a ridiculous premise and then give false praise to a competing tool. So many opinions, so many arguments, but heres the key: before taking a stand, do your homework and get the facts . Sometimes both sides are partly rightor wrong. Theres only one way to determine: get the facts. As Robert Heinlein wrote, Facts are your single clue get the facts Of course, once you get the facts, the next step is to express them in a meaningful and even compelling way. Theres nothing wrong with using some emotion in an intellectual debate but it IS wrong to replace an intellectual debate with emotion and false agenda. A while back I faced resistance to SQL Server Analysis Services from someone who claimed the tool couldnt do feature XYZ. The specifics of XYZ dont matter here. I spent about two hours that evening working up a demo to cogently demonstrate the original claim was false. In that example, it worked. I cant swear it will always work, but to me thats the only way. Im old enough to remember life at a teen in the 1970s. Back then, when a person lost hisher job, (often) it was because the person just wasnt cutting the mustard. Fast-forward to today: a sad fact of life is that even talented people are now losing their jobs because of the changing economic conditions. Theres never a full-proof method for immunity, but now more than ever its critical to provide a high level of what I call the Three Vs (value, versatility, and velocity) for your employerclients. I might not always like working weekends or very late at night to do the proverbial work of two people but then I remember there are folks out there who would give anything to be working at 1 AM at night to feed their families and pay their bills. Always be yourselfyour BEST self. Some people need inspiration from time to time. Heres mine: the great sports movie, Glory Road. If youve never watched it, and even if youre not a sports fan I can almost guarantee youll be moved like never before. And Ill close with this. If you need some major motivation, Ill refer to a story from 2006. Jason McElwain, a high school student with autism, came off the bench to score twenty points in a high school basketball game in Rochester New York. Heres a great YouTube video. His mother said it all . This is the first moment Jason has ever succeeded and is proud of himself. I look at autism as the Berlin Wall. He cracked it. To anyone who wanted to attend my session at todays SQL Saturday event in DC I apologize that the session had to be cancelled. I hate to make excuses, but a combination of getting back late from Detroit (client trip), a car thats dead (blown head gasket), and some sudden health issues with my wife have made it impossible for me to attend. Back in August, I did the same session (ColumnStore Index) for PASS as a webinar. You can go to this link to access the video (itll be streamed, as all PASS videos are streamed) The link does require that you fill out your name and email address, but thats it. And then you can watch the video. Feel free to contact me if you have questions, at kgoffkevinsgoff November 15, 2013 Getting started with Windows Azure and creating SQL Databases in the cloud can be a bit daunting, especially if youve never tried out any of Microsofts cloud offerings. Fortunately, Ive created a webcast to help people get started. This is an absolute beginners guide to creating SQL Databases under Windows Azure. It assumes zero prior knowledge of Azure. You can go to the BDBI Webcasts of this website and check out my webcast (dated 11102013). Or you can just download the webcast videos right here: here is part 1 and here is part 2. You can also download the slide deck here. November 03, 2013 Topic this week: SQL Server Snapshot Isolation Levels, added in SQL Server 2005. To this day, there are still many SQL developers, many good SQL developers who either arent aware of this feature, or havent had time to look at it. Hopefully this information will help. Companion webcast will be uploaded in the next day look for it in the BDBI Webcasts section of this blog. October 26, 2013 Im going to start a weekly post of T-SQL tips, covering many different versions of SQL Server over the years Heres a challenge many developers face. Ill whittle it down to a very simple example, but one where the pattern applies to many situations. Suppose you have a stored procedure that receives a single vendor ID and updates the freight for all orders with that vendor id. create procedure dbo. UpdateVendorOrders update Purchasing. PurchaseOrderHeader set Freight Freight 1 where VendorID VendorID Now, suppose we need to run this for a set of vendor IDs. Today we might run it for three vendors, tomorrow for five vendors, the next day for 100 vendors. We want to pass in the vendor IDs. If youve worked with SQL Server, you can probably guess where Im going with this. The big question is how do we pass a variable number of Vendor IDs Or, stated more generally, how do we pass an array, or a table of keys, to a procedure Something along the lines of exec dbo. UpdateVendorOrders SomeListOfVendors Over the years, developers have come up with different methods: Going all the way back to SQL Server 2000, developers might create a comma-separated list of vendor keys, and pass the CSV list as a varchar to the procedure. The procedure would shred the CSV varchar variable into a table variable and then join the PurchaseOrderHeader table to that table variable (to update the Freight for just those vendors in the table). I wrote about this in CoDe Magazine back in early 2005 (code-magazinearticleprint. aspxquickid0503071ampprintmodetrue. Tip 3) In SQL Server 2005, you could actually create an XML string of the vendor IDs, pass the XML string to the procedure, and then use XQUERY to shred the XML as a table variable. I also wrote about this in CoDe Magazine back in 2007 (code-magazinearticleprint. aspxquickid0703041ampprintmodetrue. Tip 12)Also, some developers will populate a temp table ahead of time, and then reference the temp table inside the procedure. All of these certainly work, and developers have had to use these techniques before because for years there was NO WAY to directly pass a table to a SQL Server stored procedure. Until SQL Server 2008 when Microsoft implemented the table type. This FINALLY allowed developers to pass an actual table of rows to a stored procedure. Now, it does require a few steps. We cant just pass any old table to a procedure. It has to be a pre-defined type (a template). So lets suppose we always want to pass a set of integer keys to different procedures. One day it might be a list of vendor keys. Next day it might be a list of customer keys. So we can create a generic table type of keys, one that can be instantiated for customer keys, vendor keys, etc. CREATE TYPE IntKeysTT AS TABLE ( IntKey int NOT NULL ) So Ive created a Table Typecalled IntKeysTT . Its defined to have one column an IntKey. Nowsuppose I want to load it with Vendors who have a Credit Rating of 1..and then take that list of Vendor keys and pass it to a procedure: DECLARE VendorList IntKeysTT INSERT INTO VendorList SELECT BusinessEntityID from Purchasing. Vendor WHERE CreditRating 1 So, I now have a table type variable not just any table variable, but a table type variable (that I populated the same way I would populate a normal table variable). Its in server memory (unless it needs to spill to tempDB) and is therefore private to the connectionprocess. OK, can I pass it to the stored procedure now Well, not yet we need to modify the procedure to receive a table type. Heres the code: create procedure dbo. UpdateVendorOrdersFromTT IntKeysTT IntKeysTT READONLY update Purchasing. PurchaseOrderHeader set Freight Freight 1 FROM Purchasing. PurchaseOrderHeader JOIN IntKeysTT TempVendorList ON PurchaseOrderHeader. VendorID Te mpVendorList. IntKey Notice how the procedure receives the IntKeysTT table type as a Table Type (again, not just a regular table, but a table type). It also receives it as a READONLY parameter. You CANNOT modify the contents of this table type inside the procedure. Usually you wont want to you simply want to read from it. Well, now you can reference the table type as a parameter and then utilize it in the JOIN statement, as you would any other table variable. So there you have it. A bit of work to set up the table type, but in my view, definitely worth it. Additionally, if you pass values from , youre in luck. You can pass an ADO data table (with the same tablename property as the name of the Table Type) to the procedure. For developers who have had to pass CSV lists, XML strings, etc. to a procedure in the past, this is a huge benefit. Finally I want to talk about another approach people have used over the years. SQL Server Cursors. At the risk of sounding dogmatic, I strongly advise against Cursors, unless there is just no other way. Cursors are expensive operations in the server, For instance, someone might use a cursor approach and implement the solution this way: DECLARE VendorID int DECLARE dbcursor CURSOR FASTFORWARD FOR SELECT BusinessEntityID from Purchasing. Vendor where CreditRating 1 FETCH NEXT FROM dbcursor INTO VendorID WHILE FETCHSTATUS 0 EXEC dbo. UpdateVendorOrders VendorID FETCH NEXT FROM dbcursor INTO VendorID The best thing Ill say about this is that it works. And yes, getting something to work is a milestone. But getting something to work and getting something to work acceptably are two different things. Even if this process only takes 5-10 seconds to run, in those 5-10 seconds the cursor utilizes SQL Server resources quite heavily. Thats not a good idea in a large production environment. Additionally, the more the of rows in the cursor to fetch and the more the number of executions of the procedure, the slower it will be. When I ran both processes (the cursor approach and then the table type approach) against a small sampling of vendors (5 vendors), the processing times where 260 ms and 60 ms, respectively. So the table type approach was roughly 4 times faster. But then when I ran the 2 scenarios against a much larger of vendors (84 vendors), the different was staggering 6701 ms versus 207 ms, respectively. So the table type approach was roughly 32 times faster. Again, the CURSOR approach is definitely the least attractive approach. Even in SQL Server 2005, it would have been better to create a CSV list or an XML string (providing the number of keys could be stored in a scalar variable). But now that there is a Table Type feature in SQL Server 2008, you can achieve the objective with a feature thats more closely modeled to the way developers are thinking specifically, how do we pass a table to a procedure Now we have an answer Hope you find this feature help. Feel free to post a comment. 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