Noch mehr Statistik – noch mehr Performance

Im Blogg von dieser Woche habe ich ja bereits ein paar Tipps zu Performance Steigerung mittels Index Statistiken gegeben – heute gehe ich noch einen Schritt weiter. Statistiken zu erstellen kann eine ganz aufwendige Sache sein, insbesondere wenn die Tabelle Millionen und mehr von Zeilen hat. Dies ist auch der Grund, warum der SQL Server per Default das Histogramm lediglich auf einem Subset aller Zeilen (Sample) bildet. Die Grösse des Samples bestimmt der SQL Server selbständig. Wenn du denkst, dass du smarter als der SQL Server bist, kannst du aber auch hier wiederum von Hand eingreifen:

UPDATE STATISTICS Person.Address IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode WITH SAMPLE 1 PERCENT
UPDATE STATISTICS Person.Address IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode WITH SAMPLE 1000 ROWS
UPDATE STATISTICS Person.Address IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode WITH FULLSCAN

FULLSCAN entspricht dabei einem SAMPLE 100 PERCENT.

Wie gesagt, ein Update der Statistik kann ganz schön lange dauern. Ein Query, das auf eine out-of-date Statistik stösst, muss warten bis diese Statistik aktualisiert wurde; erst dann kann das Query compiliert und ein Resultset zurückgegeben werden. Das kann zu unvorhersehbar langen Antwortzeiten führen, welchen auf Clientseite schon mal zu einem Timeout führen können. Um dem zu entgehen, kennt der SQL Server die DB Option, die Statistik asynchron zu aktualisieren:

ALTER DATABASE AdventureWorks
  SET AUTO_UPDATE_STATISTICS_ASYNC ON

In diesem Falle wir die Statistik im Hintergrund aktualisiert. Queries welche in der Zwischenzeit diese Statistik benötigen werden nicht blockiert sondern verwenden die veraltete Statistik.

Wäre es nicht toll, wenn dir der SQL Server erzählen würde, welche Indizes er gerne hätte um noch effizienter arbeiten zu können? Genau hierfür gibt es eine Dynamic Management View:

SELECT object_name(object_id, database_id) as ‘Object’
      ,equality_columns
      ,inequality_columns
      ,included_columns
FROM sys.dm_db_missing_index_details

Das Resultat kann etwa wie folgt aussehen:

Object	equality_columns	inequality_columns	included_columns
Address	[ModifiedDate]	NULL	NULL

Mit dieser Information kannst du deinen fehlenden Index leicht bilden:

CREATE INDEX NewIndex
  ON <Object>(<equality_columns>,<inequality_columns>)
  INCLUDE (<included_columns>)
  WITH DROP_EXISTING

Oder konkret:

CREATE INDEX Adress_ModDate_Index
  ON Address(ModifiedDate)
  WITH DROP_EXISTING

Aber Achtung: Diese Daten sind nur solange durch die Dynamic Management View verfügbar, bis du den SQL Server neu startest oder aber an der zugrunde liegenden Tabelle eine Schema Änderung vornimmst.

Mit aktuellen Statistiken zu mehr Performance

Nein, natürlich meine ich mit Statistiken nicht etwa das Statistische Jahrbuch der Schweiz, sondern viel mehr SQL Servers eigene Index Statistik. Index Statistiken beschreiben die Verteilung von Werten in einer Spalte. Der Query Optimizer nutzt diese statistischen Daten um den optimalen Query Plan zu bestimmen, in dem er die Zugriffskosten für die Benutzung eines bestimmten Index schätzt. Mit Hilfe von DBCC kannst du dir zum Beispiel das Histogramm der Verteilung der Werte anschauen:

DBCC SHOW_STATISTICS (‘Person.Address’,‘IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode’) WITH HISTOGRAM

Du kannst dir das Histogramm natürlich auch mit Excel grafisch darstellen lassen; das sieht dann so aus:

Wann immer du dir einen Index anlegst, legt SQL Server parallel dazu auch eine entsprechende Statistik an. Wenn sich die dem Index zugrunde liegende Daten ändern, kann sich natürlich auch das Histogramm dazu ändern. Dies kann dazu führen, dass der Query Optimizer lediglich einen Suboptimalen Query Plan erstellt, was selbstreden auf die Performance geht. SQL Server wird daher die Statistik automatisch updaten, d.h. neu berechnen, wann immer sich 20% der Zeilen – aber mindestens 500 Zeilen – der entsprechenden Tabelle geändert haben. Ob die Statistik noch aktuell ist, siehst du zum Beispiel im Actual Execution Plan: wenn Actual Number of Rows stark von Estimated Number of Rowas abweicht, dann dürfte die Statistik nicht mehr viel wert sein. In diesem Falle kannst du den Update der Statistik forcieren:

UPDATE STATISTICS Person.Address

Da der SQL Server die Statistiken sowohl selber anlegt als auch selber up-to-date hält, sollte in der Regel das Histogramm aber kaum je unbrauchbar sein – wenn da nicht noch all diese Optionen wären. Sowohl das automatische Anlegen als auch Updaten der Statistiken kannst du nämlich unterbinden. Das kann mal für grosse BULK Operationen interessant sein; sollte aber wirklich die ganz grosse Ausnahme sein. Mit folgenden Kommandos kannst du in das Verhalten der automatischen Statistik eingreifen:

• sp_autostats: Tabllen und Index weites ein-/ausschalten von AUTO UPDATE
• STATISTICS_NORECOMPUTE Klausel in einem CREATE INDEX Statement
• NORECOMPUTE Klausel in einem UPDATE STATISTICS oder CREATE STATISTICS Statement
• AUTO_CREATE_STATISTICS und AUTO_UPDATE_STATISTICS Datenbank Option (änderbar via ALTER DATABASE)

Nochmals: Überlege es dir gut, ob du wirklich einzelnen Statistiken nicht automatisch up-to-date halten willst oder für einzelne Indizes überhaupt gar keine Statistik willst. Was in Ausnahmefällen mal für eine Statistik gelten kann wird aber kaum je generell für alle Statistiken gelten. Also Hände weg von den Datenbank weiten Optionen zur Ausschaltung der automatischen Statistiken! Die Performance wird es dir danken!

Neuer TPC Record für SQL Server 2005

„SQL Server 2005 überschreitet die 1 Million Grenze im TPC-C Benchmark“ und „SQL Server 2005 setzt einen neuen TPC-H Rekord“; dies hat Microsoft vor ein paar wenigen Tagen veröffentlicht. Toll, SQL Server 2005 ist also ein Spitzenprodukt – aber was heisst das nun für unseren Alltag als IT Professional?

Fangen wir von Vorne an. Die Transaction Processing Performance Council (TPC), eine Nonprofit Organisation, wurde gegründet um Hersteller unabhängige Datenbank Performance Vergleichstests zu definieren. TPC Tests sind streng definiert und müssen von den Herstellern selbst durchgeführt werden. TPC übernimmt dabei die Rolle eines unabhängigen Auditors, der die Tests begleitet und die Ergebnisse überprüft. Die Ergebnisse selber werden in einem Bericht zusammengefasst und auf der Website der TPC publiziert (www.tpc.org).

TPC-C ist schlichtweg der Standard Test zur Messung der Performance und Skalierbarkeit von OLTP Systemen. Der durchgespielte Use-Case entspricht einem Bestellsystem in welchem parallel neue Bestellungen erfasst und bestehende abgefragt werden. Gemessen wird die Performance in Transactions per Minute (tpmC).

TPC-H ist ein weiterer Test. Diesmal ist der Use-Case aber an einem OLAP System angelehnt. Hier werden ad-hoc Abfragen parallel zu Datenupdates durchgeführt. Gemessen wird die Performance in Query per Hour (QphH@size). TPC-H Performance wird wesentlich von der Grösse der darunterliegenden Datenmenge bestimmt; daher werden die Tests zu Vergleichszwecken in verschiedene Gruppen (entsprechend der zugrundeliegenden Datenbankgrösse) zusammengefasst. Daher auch das @size.

Genug der Theorie und zurück zur Erfolgsmeldung von Microsoft. Was bedeutet nun mehr als 1 Million tpmC? Ist das viel? Warum soll das so toll sein, wenn Microsoft mit diesem Resultat lediglich an siebter Stelle der Top-10 Rangliste steht und der Spitzenreiter Oracle 10g R2 beinahe 4 mal so viel leistet? Microsofts Testkonfiguration hat sensationelle 1′231′433 tpmC geleistet. Nehmen wir an, dass diese Menge von Bestellungen Mitarbeiter in einem Backoffice erfasst haben, dann ergibt dies bei einer 40 Stunden Woche die schon fast astronomische Zahl von 153′682′838′400 Bestellungen pro Jahr. Im Vergleich dazu: Die Schweizer Luftfahrtgesellschaft SWISS hat im Jahr 2006 gerade mal 10 Millionen Passagiere befördert respektive Ticket verkauft und deren Bestellungen erfasst. Somit lässt sich mit SQL Server 2005 spielend der gesamte Ticketverkauf der SWISS abwickeln; ja wahrscheinlich sogar aller Luftfahrtgesellschaften der grossen weiten Welt zusammen. Genau dies ist die sensationelle Meldung des Tages. Geschehe was will: mein Kunde kann Forderungen bezüglich Skalierbarkeit der Lösung stellen und ich kann beruhigt antworten, dass der SQL Server 2005 dies schon mitmachen wird; Vorausgesetz der Kunde hat das hierfür notwendige Kleingeld. Solch eine Performance hat selbstverständlich seinen Preis. So besteht die Testkonfiguration zum Beispiel aus 64 Prozessoren, 1 TB RAM (ja Terabyte – nicht Gigabyte) sowie ganzen 1′742 Harddisks mit insgesamt 54.8 TB Kapazität. Dass dies auch was kostet, dürfte wohl jedem einleuchten.

Ganz ähnlich verhält es sich auch mit den TPC-H Resultaten. Hier ist SQL Server 2005 in den Kategorie 100 GB (19′323 QpH@1ooGB) und 1′000 GB (69′999QpH@1000GB) gar an Erster Stelle. Was bedeutet aber 69′999QpH@1000GB? Angenommen 1000 Business Analysten arbeiten parallel auf einem 1 TB grossen Datawarehouse, dann muss jeder von Ihnen mehr als 1 ad-hoc Abfrage pro Minute an das System stellen. Und selbstverständlich auch begutachten und bewerten, damit er in der Lage ist die nächste Abfrage zu formulieren und abzusetzen. Auch das dürfte wohl kaum eine praktische Limite für unser Daily Business darstellen.

Langer Rede kurzer Sinn: SQL Server 2005 muss sich nicht verstecken! Es wird kaum ein relevantes Szenario geben, bei welchem der SQL Server punkto Performance nicht mitkommt. In einigen Konstellationen ist der SQL Server 2005 gar die leistungsstärkste und kostengünstigste Lösung.

MERGE Statement – ein cooles Feature von Katmai

Beim Durchsehen von Katmai – dem Nachfolger von SQL Server 2005 – bin ich auf eine coole Erweiterung in der DB-Engine gestossen; dem MERGE Statement.

Das MERGE Statement vereint die drei Statements INSERT, UPDATE und DELETE. Neu kann mit nur einem einzigen Statement ein ganzes Datenset in einem Rutsch in die DB geschrieben werden, unabhängig davon, ob einzelne Records bereits erfasst (und demnach ein UPDATE benötigen) , neu sind (und somit ein INSERT benötigen) oder aber aus der DB gelöscht werden müssen (und somit ein DELETE benötigen). Natürlich erwarte ich von so einem neuen MERGE Statement auch eine erhebliche Performance Verbesserung gegenüber der alt hergebrachten Methode mit zwei oder drei separaten Statements (INSERT, UPDATE und DELETE). Ob dem so ist, werde ich dir gleich nachfolgend versuchen zu beweisen. Zuerst aber mal ein Beispiel zum MERGE Statement (dies habe ich mir aus dem BOL von Katmai ausgelehnt).

Szenario: In einem täglichen Prozess soll die ProductInventory Tabelle mit den Einträgen aus SalesOrderDetail aktualisiert werden. Die Spalte Quantity aus ProductInventory soll pro Produkt um die Anzahl der verkauften Produkte reduziert werden. Fällt ein Produkt auf eine Menge Null zurück, muss dieses Produkt aus ProductInventory gelöscht werden.

Lösung mit SQL Server 2005:

WITH Selling(ProductID, OrderQty) AS
(
    SELECT ProductID, SUM(OrderQty)
    FROM Sales.SalesOrderDetail sod
      JOIN Sales.SalesOrderHeader soh ON sod.SalesOrderID = soh.SalesOrderID
                                     AND soh.OrderDate = {d ‘2001-07-03′}
    GROUP BY ProductID
)
DELETE Production.ProductInventory
FROM Production.ProductInventory pi
  JOIN Selling src ON (pi.ProductID = src.ProductID)
WHERE pi.Quantity = src.OrderQty;

WITH Selling(ProductID, OrderQty) AS
(
    SELECT ProductID, SUM(OrderQty)
    FROM Sales.SalesOrderDetail sod
      JOIN Sales.SalesOrderHeader soh ON sod.SalesOrderID = soh.SalesOrderID
                                     AND soh.OrderDate = {d ‘2001-07-03′}
    GROUP BY ProductID
)
UPDATE Production.ProductInventory
SET Quantity = pi.Quantity - src.OrderQty
FROM Production.ProductInventory pi
  JOIN Selling src ON (pi.ProductID = src.ProductID)
WHERE pi.Quantity > src.OrderQty;

Lösung mit Katmai:

MERGE Production.ProductInventory AS pi
USING (SELECT ProductID, SUM(OrderQty)
       FROM Sales.SalesOrderDetail sod
         JOIN Sales.SalesOrderHeader soh ON sod.SalesOrderID = soh.SalesOrderID
                                        AND soh.OrderDate = {d ‘2001-07-03′}
       GROUP BY ProductID) AS src (ProductID, OrderQty)
ON (pi.ProductID = src.ProductID)
WHEN MATCHED AND pi.Quantity - src.OrderQty = 0
    THEN DELETE
WHEN MATCHED
    THEN UPDATE SET pi.Quantity = pi.Quantity - src.OrderQty;

Also, eleganter ist die Lösung von Katmai allemal; wie sieht es aber mit der Performance aus? OK, ich weiss – Performance Tests mit CTP’s sind nicht gerade besonders fair. Ich bin aber auch nicht an absoluten Zahlen interessiert, sondern vielmehr an Trends. Daher habe ich die IO’s (SET STATISTICS IO ON) der beiden Lösungen miteinander verglichen:

mit SQL Server 2005:

Table ‘ProductInventory’. Scan count 4, logical reads 8, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘SalesOrderDetail’. Scan count 5, logical reads 15, physical reads 3, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘SalesOrderHeader’. Scan count 1, logical reads 703, physical reads 2, read-ahead reads 699, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(0 row(s) affected)

Table ‘ProductInventory’. Scan count 4, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘SalesOrderDetail’. Scan count 5, logical reads 15, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘SalesOrderHeader’. Scan count 1, logical reads 703, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘ProductInventory’. Scan count 0, logical reads 16, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(8 row(s) affected)

mit Katmai:

Table ‘ProductInventory’. Scan count 4, logical reads 8, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘SalesOrderDetail’. Scan count 5, logical reads 15, physical reads 3, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘SalesOrderHeader’. Scan count 1, logical reads 703, physical reads 2, read-ahead reads 699, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘ProductInventory’. Scan count 0, logical reads 16, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(8 row(s) affected)

Der Effekt des neuen Merge Statements scheint mir offensichtlich zu sein; rund die Hälfte der IO’s. Und das ist schlicht und einfach Super!

Über den Sinn und Unsinn von UDFs

„Code duplizieren ist unschön, dilettantisch, ja fast schon unästhetisch und ist beim nächsten Refactoring zwingend zu eliminieren!“ In etwa so hört es sich bei professionellen SW-Entwicklern an, wenn diese über „schöne“ Software diskutieren.

Wenn wir in T-SQL Code duplizieren nicht möchten, greifen wir zu User Defined Functions (UDF). Dass UDFs aber nicht immer sinnvoll sind und wahre Performance Killer sein können, möchte ich dir anhand eines Erlebnisses bei einem meiner Kunden schildern:

In einem Migrationsprojekt müssen Daten aus mehreren AS400 Applikationen gelesen, validiert, transformiert und in einem SQL Server abgelegt werden. Also ein klassischer ETL-Prozess. Aus diversen Gründen haben wir uns für reine T-SQL Script entschieden, welche den ganzen ETL-Prozess abbilden.

Unter anderem besagt eine Transformationsregel, dass bei allen AS400 Strings die nachfolgenden Leerschläge abgetrennt werden müssen (RTRIM) und ein daraus resultierender Leerstring in NULL umgewandelt werden muss. Also ein klassischer Fall für eine UDF der Form:

CREATE FUNCTION dbo.CONVERT_NoString(@INPUT nvarchar(4000)) RETURNS nvarchar(4000) AS
BEGIN
  RETURN(CASE WHEN RTRIM(@INPUT) = “ THEN null
              ELSE                        RTRIM(@INPUT)
         END)
END

Ähnliche Funktionen gibt es z.B. auch für Geldbeträge oder für das Transformieren von Codes (wie z.B. Geschlecht, Beruf etc.) zwischen den Werten in den AS400 Daten und den Daten in der neu zu erstellenden SQL DB. Ein typisches UPDATE Statement sieht dann in etwa wie folgt aus:

UPDATE ExportDB.dbo.KLIBU_Tabelle1
SET BelegNummer = dbo.CONVERT_NoNumber(wvbs.BELNUM)
   ,Betrag      = dbo.CONVERT_NoMoney(wvbs.Betrag)
   ,VonDatum    = dbo.FORMDAT_Vers1(wvbs.VonDatum)
   ,BelegText   = dbo.CONVERT_NoString(wvbs.BuchungsText)
   ,StatusCode  = dbo.GetCodeElement(40, ‘1. AS400 Applikation’, wvbs.Status)
   …
FROM         ExportDB.dbo.KLIBU_Tabelle1 ExpTab1
  INNER JOIN ImportDB.dbo.AS400_Tabelle1 ImpTab1 ON ExpTab1.BasisID = ImpTab1.BasisID
WHERE bs.Herkunft = ‘1. AS400 Applikation’

Das funktioniert einwandfrei und ergibt bei Tabellen mit Einträgen von bis zu mehreren 10′000 Zeilen auch kaum Probleme mit der Performance. Ganz anders sieht es aus, wenn wie in meinem Fall eine einzige Tabelle rund 6.8 Millionen Zeilen hat und pro Zeile 14 solche und ähnliche UDFs aufgerufen werden. In meinem Fall dauerte dieser Update 6 Stunden und 20 Minuten! Und das wohlgemerkt während der Nacht, wo sonst der Server nur die Daumen dreht. Na ja, dafür hat der Server aber auch rund 95 Millionen UDFs aufgerufen; 6.8 Millionen Zeilen à 14 UDFs

Ich habe mir die Mühe gemacht und den Update Befehl umgeschrieben; diesmal ohne auch nur einem einzigen UDF Aufruf aber mit derselben Funktionalität. D.h. ich habe den UDF-Code genommen und diesen x-mal direkt in den Update Befehl dupliziert. Der Erfolg war bestechend. Neu benötigt der Update-Befehl noch lediglich 1 Stunde und 45 Minuten; diesmal jedoch unter Tags als sogar ein Team von 5 Mitarbeitern auf dem Server arbeitete!

Lesson learned: Setze UDFs wirklich nur ein, wenn zwingend notwendig und die Performance auch wirklich – aber wirklich – keine Rolle spielt!