Unique-Constraints und Performance

Ein Unique-Constraint wird in einer Tabelle definiert, wenn in einer Spalte nur eindeutige Werte (inklusive NULL) möglich sind. Das ist der einzige Constraint, der durch eine Index-Erstellung umgesetzt wird. Um einen Unique-Constraint zu erstellen, muss man einen UNIQUE Index anlegen. Constraints sind in erster Linie eine Garantie für Datenintegrität. Man vergisst aber oft, dass diese auch den SQL Server Optimizer bei der Ausführungsplan-Generierung unterstützen.

Code-Beispiel

Als Beispiel erstellen wir eine Tabelle (dbo.OrderTest). Die Tabelle hat nur drei Spalten, die für unser Problem ausreichen:

--Tabelle OrderTest
IF OBJECT_ID('dbo.OrderTest') IS NOT NULL DROP TABLE dbo.OrderTest
GO
CREATE TABLE dbo.OrderTest(
order_id int NOT NULL,
order_date date NOT NULL,
status_id tinyint NOT NULL
CONSTRAINT PK_OrderTest PRIMARY KEY CLUSTERED(order_id)
)
GO

Mit diesem Skript füllen wir die Tabelle mit Order-Details. Nach der Ausführung befinden sich in der Tabelle eine Million Orders, mit dem Datum zwischen dem 1.1.2007 und 31.12.2010 und einer Zahl zwischen 1 und 5 als Status ID.

Aufgabe

Unsere Aufgabe ist einfach: Alle Order-Details anzeigen, wobei die Status ID durch die entsprechenden Bezeichnung ersetzt werden soll.

Diese zusätzliche Anforderung wird mit einer sogenannten Lookup-Tabelle umgesetzt. Erstellen wir nun die Lookup-Tabelle und füllen wir sie mit fünf Einträgen:

--Tabelle StatusLookup anlegen
IF OBJECT_ID('dbo.StatusLookup') IS NOT NULL
DROP TABLE dbo.StatusLookup
GO
CREATE TABLE dbo.StatusLookup(
id tinyint not null,
text varchar(20) not null)
GO
--Tabelle StatusLookup füllen
INSERT INTO dbo.StatusLookup(id, text)
VALUES(1, 'eingelangt'),(2, 'in Bearbeitung'),(3, 'Rückgabe'),(4, 'geschlossen'),(5, 'gelöscht')
GO

Die Tabelle dbo.StatusLookup ist eine ganz einfache Tabelle mit nur fünf Einträgen. Deshalb ist es üblich, dass eine solche Tabelle als Heap Tabelle (ohne Clustered Index) umgesetzt wird.

Ergebnis

Jetzt haben wir beide Tabellen und können die gewünschte Abfrage erstellen:


SELECT o.order_id,o.order_date,
(SELECT s.text FROM dbo.StatusLookup s WHERE s.id=o.status_id) status
FROM dbo.OrderTest o

Wir listen die ID und das Datum des Orders aus und fügen die Statusbezeichnung mit einer Subquery hinzu. Auf meinem Notebook ist die Ausführungszeit für diese Abfrage 10.9 Sekunden. Hier sind die Ausführungsinformationen bzw. der Ausführungsplan:

Ergebnisanalyse

Was kann man hier sehen? Die Tabelle dbo.StatusLookup wurde 1.000.000 mal gescannt! Auf jede Zeile aus der OrdeTest Tabelle wurden drei Operatoren angewandt (Table Scan, Stream Aggregate und Assert). Warum?

Eine Subquery kann nur einen skalaren Wert liefern. Deshalb muss der SQL Server überprüfen, ob die innere Abfrage für jede äußere Zeile nur einen Datensatz zurückliefert. Falls es mehrere Datensätze gibt, wird ein Fehler ausgelöst. Mit den Operatoren Table Scan und Stream Aggregate wird die Anzahl von inneren Datensätzen festgelegt und dann der Assert-Operator überprüft dann, ob diese Anzahl größer als 1 ist. Wie gesagt, wird das für jede Zeile durchgeführt – d.h. 1 Mio Ausführungen!

Diese Überprüfung limitiert den SQL Optimizer bei der Auswahl der Join-Strategie und somit bleibt ihm nur Nested Loops Join als einzige Option. Diese Option ist aber ineffizient, wenn die äußere Tabelle zu groß ist. Die Tabelle dbo.StatusLookup enthält nur fünf Zeilen, ohne ID-Duplikate. Aber Duplikate sind nicht explizit verboten. Daher muss immer überprüft werden, ob es zwei Zeilen mit gleicher ID gibt. Bei der Tabellenerstellung haben wir nicht gesagt, dass ein Duplikat nicht möglich ist.

So, was können wir hier tun? Dem SQL Server Optimizer sagen, dass er diese Überprüfung nicht machen muss! Das sagen wir mit einem Unique Constraint. Erstellen wir noch eine Lookup-Tabelle:

Unique Constraint als Performanceverbesserungsfaktor

IF OBJECT_ID('dbo.StatusLookup2') IS NOT NULL DROP TABLE dbo.StatusLookup2
GO
CREATE TABLE dbo.StatusLookup2(
id tinyint not null,
text varchar(20) not null)
GO
CREATE UNIQUE INDEX ix_StatusLookup2_id on dbo.StatusLookup2(id)
go
INSERT INTO dbo.StatusLookup2(id, text)
VALUES(1, 'eingelangt'),(2, 'in Bearbeitung'),(3, 'Rückgabe'),(4, 'geschlossen'),(5, 'gelöscht')
GO

Diesmal haben wir einen Unique Index hinzugefügt und damit die Werte für die Spalte id beschränkt. Führen wir unsere Abfrage erneut aus – diesmal mit der neuen Lookup-Tabelle:

SELECT o.order_id,o.order_date,
(SELECT s.text FROM dbo.StatusLookup2 s WHERE s.id=o.status_id) status
FROM dbo.OrderTest o

Schauen wir jetzt die Ausführungsinfo an:

Analyse

Die Ausführungszeit ist um 2 Sekunden reduziert und die CPU Zeit von 9.079 auf 749 ms! Anhand des Plans können wir sehen, dass der Optimizer aufgrund des hinzugefügten Unique Constraint nicht mehr Duplikatten in der Lookup-Tabelle überprüft und dass er finden konnte, dass diese Subquery-Abfrage äquivalent zu einem Left Join ist. Deshalb hat er einen optimalen Plan generiert, bei dem die Tabelle dbo.StatusLookup2 nur einmal gelesen werden musste! Wenn wir diese zwei Pläne im SQL Server Management Studio direkt miteinander vergleichen, sehen wir, dass die Version mit dem Unique Constraint nur 8% der gesamten Ressourcen verwendet – d.h. ganz grob gesagt ist dieser Plan mehr als 10 mal besser als die Nested Loop Join Variante.

So, eine Kleinigkeit, die man oft vergißt, da Lookup-Tabellen klein sind und Indizierung dort keine Rolle spielt.

Nun kann man sich fragen, warum die Ausführungszeit mit dem zweiten Plan “nur” zwei Sekunden besser ist, wenn der Plan selbst von SSMS als 10-mal besser bezeichnet wurde?

Abfrageperformance Vergleichen

Die Ergebnisliste wird zunächst generiert und dann noch dem Client (SSMS) gesendet. Die gesamte Zeit ist in diesem Fall keine gute Auswahl für einen Performance-Vergleich, da dem Client 1 Mio Zeilen_gesendet werden sollen. Wenn wir hingegen die CPU-Zeiten vergleichen (9.079 ms vs. 749 ms), bekommen wir ein realistisches Bild. Wenn wir aber die zweite Zeitkomponente annullieren möchten, können wir die Abfragen im SSMS so ausführen, dass die Ergebnisse nicht dem Client gesendet werden. Das können wir mit der Option “Discards results after execution” im Screen “Query/Query Options/Results/Grid” einstellen.

Wenn wir beide Abfragen mit dieser Option ausführen, bekommen wir für die Ausführungszeit 3.613 ms bzw. 573 ms.

Fazit

Eine Lookup-Tabelle kann freilich als Heap-Tabelle implementiert werden, aber dann darf nicht auf ein Unique Constraint auf die ID-Spalte vergessen werden. Und ganz generell: Den SQL Server mit allen relevanten Informationen füttern! Richtige und relevante Informationen können nur zur Generierung eines besseren Ausführungplans führen!

Den Beispiel-Code können Sie hier herunterladen.



SQL Server 2008 R2 Licensing Quick Reference Guide Update: Version 1.1

Für alle SQL Spezialisten die sich nicht nur mit der Technologie sondern auch mit der Lizenzierung beschäftigen wollen oder müssen Zwinkerndes Smiley gibt es einen schnellen, aber fundierten Einstieg in die SQL Server 2008 R2 Lizenzierung (auch in virtuellen Umgebungen!). Natürlich auch wunderbar zur Nachlese geeignet: der SQL Server Licensing Quick Reference Guide. Der Guide wurde jetzt aktualisiert und unter anderem um die SQL Server Parallel Data Warehouse Edition sowie um weitere Infos ergänzt.

Ein Auszug aus dem Inhalt:

image

Download: SQL Server 2008 R2 Licensing Quick Reference Guide – Microsoft

Happy licensing, Martin
martin.poeckl@microsoft.com

Check-Constraints und Performance

Ein CHECK-Constraint wird in einer Tabelle definiert, wenn in einer Spalte nur bestimmte Werte möglich sind. Er ermöglicht, einen beliebigen Ausdruck anzugeben, den jeder Wert einer Spalte erfüllen muss. Constraints sind in erster Linie eine Garantie für Datenintegrität. Man vergisst aber oft, dass Informationen aus den Constraints auch die Entscheidungen des SQL Server Optimizers beeinflussen können.

Code-Beispiel

Als Beispiel nehmen wir die Tabelle Sales.SalesOrderHeader aus der AdventureWorks Datenbank. In dieser Tabelle soll ein CHECK-Constraint definiert sein. Wenn dies in Ihrer Datenbank noch nicht der Fall ist, können Sie ihn mit dem folgenden Code anlegen:

ALTER TABLE Sales.SalesOrderHeader WITH CHECK ADD CONSTRAINT CK_SalesOrderHeader_Status CHECK ((Status>=(0) AND Status<=(8)))

Der Constraint beschränkt die Werte für die Spalte Status auf eine Zahl zwischen 0 und 8. Und was hat diese Information mit dem Optimizer zu tun?

Aufgabe

In der Tabelle Sales.SalesOrderHeader gibt es keine Zeile mit dem Wert 8 für die Spalte Status. Wenn wir eine Abfrage ausführen, um alle Orders mit dem Status 8 anzuzeigen, bekommen wir eine leere Liste:

SELECT * FROM Sales.SalesOrderHeader WHERE Status = 8

Um diese leere Liste liefern zu können, musste SQL Server die ganze Tabelle lesen. Jede Zeile könnte den Wert 8 haben und SQL Server hat alle Zeilen überprüfen müssen. Hier sind die Ausführungsinformationen:

Ausführungsplan

Wiederholen wir jetzt diese Aufgabe mit einem Wert, der außerhalb des definierten Bereichs liegt:

SELECT * FROM Sales.SalesOrderHeader WHERE Status = 9

Selbstverständlich ist die Liste wieder leer, die Ausführungsinformationen sehen aber komisch aus:

Keine einzige Datenseite wurde gelesen! Aufgrund von der CHECK-Constraint Definition hat der SQL Server beschlossen, dass keine Zeile in der Tabelle SalesOrderHeader mit dem Status 9 existieren kann und hat das Ergebnis zurückgeliefert, ohne die Tabelle berühren zu müssen!

Fazit

Der SQL Server Optimizer liest die Informationen aus Constraints-Definitionen. Diese Informationen können die Ausführungsplan-Generierung beeinflussen.

SolidQ Journal

Liebe Gruppenmitglieder!

Ich würde Sie die neue Ausgabe des SolidQ Journals präsentiereren. Die ist zum Online-Lesen und Download auf http://www.solidq.com/sqj verfügbar. Für diese ist lediglich eine einfache, kostenlose Registrierung erforderlich. Autoren sind sowohl die global agierenden Datenbankexperten von SolidQ als auch weitere hervorragende Mitglieder der SQL Community. Hier ein Überblick über die Artikel der aktuellen Ausgabe:

Integration Services Key Feature in BI Projects (Part 2)
by Salvador Ramos

PowerShell with SQL Server Data Mining
by Mark Tabladillo

Logging with SQL Server Agent
by Herbert Albert, Gianluca Hotz

Parameter Sniffing with SQL Server Stored Procedures
by Milos Radivojevic

Encryption in SQL Server
by Dr. Greg Low

Configuring SharePoint 2010 Forms Based Authentication
by Guillermo Bas

Danke und viel Spaß beim Lesen!

LG,
Milos

SSMA: Migration von Oracle/Sybase/MySQL und Access auf SQL Server

Der bewährte SSMA (SQL Server Migration Wizard) wurde ein weiteres mal verbessert und erweitert und liegt jetzt in der Version 5 vor. Der SSMA unterstützt und erleichtert erheblich die Datenbankmigration von Oracle, Sybase, MySQL oder Microsoft Access auf Microsoft SQL Server. Der kostenfreie Assistent verfügt dazu über ausführliche Analyse-, Migrations-, und Testfunktionalitäten die sowohl die Datenbank Struktur (Tabellen, Views, Stored Procedures, usw.) sowie die Daten analysieren und migrieren können.

SQL Server Migration Assistant (SSMA) v5.0 is now available. SSMA simplifies database migration process from Oracle/Sybase/MySQL and Microsoft Access to SQL Server and SQL Azure. SSMA automates all aspects of migration including migration assessment analysis, schema and SQL statement conversion, data migration as well as migration testing to reduce cost and reduce risk of your database migration project.

Die Erweiterungen im Detail:

  • Support for migration to SQL Server “Denali”
  • Multi-thread data migration for improved scale and performance.
  • Globalization support for migrating non-English databases to SQL Server.
  • Support for installation for SSMA Extension Pack on clustered SQL Server environments.
  • Sybase migration enhancement support:
    • Support for migration to SQL Azure
    • Extended data access connectivity to Sybase ASE ADO.NET and ASE ODBC providers
    • Support for proper conversion of case sensitive Sybase database to case sensitive SQL Server
    • Extended support for conversion of Non-ANSI joins for DELETE and UPDATE statements
    • Removed dependency on separate SYSDB database on target SQL Server
  • Report use of Oracle’s User Defined Type in the database object and inside PL/SQL.

Weitere Infos und Download des SSMA: http://www.microsoft.com/sqlserver/en/us/product-info/migration.aspx

Martin
martin.poeckl@microsoft.com

SQL Server 2008 R2 SP1 jetzt testen!

Ab sofort kann die CTP1 (Community Technology Preview) von SQL Server 2008 R2 SP1 getestet werden. Das Service Pack enthält die kumulativen Updates 1-6, weitere Problembehebungen und einige Erweiterungen auf Basis von SQL Server Kundenrückmeldungen (unter anderem über das Windows Error Reporting).

Nachfolgend einige Erweiterungen im Originaltext:

  • Dynamic Management Views for increased supportability
  • ForceSeek for improved querying performance
  • Data-tier Application Component Framework (DAC Fx) for improved database upgrades, on premises and for SQL Azure
  • Disk space control for PowerPivot

Customers running SQL Server 2008 R2 can download and test the SP1 CTP and send feedback to Microsoft for continuous product improvement. We look forward to your feedback!

​Viel Spaß beim Testen,
Martin
martin.poeckl@microsoft.com

Wir suchen Themenvorschläge für 26.05.!

Wir suchen Themenvorschläge für unser nächstes Treffen am 26.05. um 18:00 (bei Microsoft Österreich, Euro Platz 3, 1120 Wien)!

Bitte schickt uns euere Wunschthemen per Mail an sqlserverug@live.at!

Die ersten 6 Vorschläge werden von uns mit drei SQL Server T-Shirts und drei Microsoft Business Intelligence Tassen belohnt 🙂

T-SQL Performance Recommendations – Slides & Codebeispiele

Vielen Dank für Eure Teilnahme an der gestrigen SQL User Group! Hier sind die Unterlagen vom Vortrag T-SQL Performance Recommendations:

TSQL Performance Recommendations Codebeispiele (bitte einfach rauskopieren das File hat die .docx Endung da die Blog-SW keine .sql unterstützt)

TSQL Performance Recommendations Präsentationsfolien

Herzlichen Dank auch an Milos Radivojevic für seinen interessanten Vortrag!!

Nächstes Treffen findet am 26.05.2011 um 18:00 bei Microsoft Österreich statt! Details folgen…

Agenda zum Vortrag T-SQL Performance Recommendations am 14. April 2011

Hier ist die detaillierte Agenda von Milos Radivojevic, der ja im Rahmen seines Vortrages beim nächsten SQL Server User Group Treffen über das Thema T-SQL Performance Recommendations reden wird:

  • General Recommendation
  • Functions in WHERE clause
  • Arithmetical Operations in WHERE clause
  • Data Type Conversions in WHERE Clause
  • Non-SARGable WHERE clause
  • Local Variables
  • Database Constraints and Performance
  • Other Recommendations
  • UDFs: Scalar vs. Inline Table
  • Network and Storage Recommendations

Zur Erinnerung: das nächste Treffen der SQL Server User Group ist am 14.04.2011 ab 18:00 bei Microsoft Österreich (Am Euro Platz 3, 1120 Wien). Die Teilnahme ist kostenfrei, eine formlose Anmeldung aber bitte unter der E-Mail Adresse sqlserverug@live.at notwendig.

cu there!
Martin Pöckl
martin.poeckl@microsoft.com

Nächstes Treffen der SQL Server User Group Austria am 14.April 2011

Die SQL Server User Group Österreich lädt am 14.April alle Interessierten nach Wien mit einem spannenden Vortrag von Milos Radivojevic: T-SQL Performance Recommendations.

Wann: 14. April 2011 ab 18:00 bis ca. 20:00
Wo: Microsoft Österreich, Am Euro Platz 3, 1120 Wien
Kosten: Keine!

Bitte unter der E-Mail Adresse sqlserverug@live.at formlos registrieren – wir haben diesmal nur begrenzte Plätze zur Verfügung!

SQL User Group auf Facebook: SQL Server Group AUSTRIA

Wir freuen uns auf zahlreiche Teilnehmer!
Martin Pöckl & Petra Korica-Pehserl