Der interessanteste Teil meiner Arbeit ist die Leistungsoptimierung und Optimierung in T-SQL. Der Kern der Leistungsoptimierung in einem SQL-Server ist die ordnungsgemäße und nutzbare Indexierung von Tabellen mithilfe von Sargable-Abfragen.
Manchmal sagt mir der leitende Datenbankentwickler bei der Arbeit, ich solle einfach einer xyz-Spalte einen Index hinzufügen, da er in mehreren Where-Klauseln in mehreren SQL-Abfragen verwendet wird. Dann muss ich meinen Geist beruhigen. Wenn durch das Hinzufügen eines Indexes für die xyz-Spalte jedes Leistungsproblem gelöst werden könnte, wären nicht Tausende von Büchern über T-SQL-Leistungsoptimierung veröffentlicht worden, und ich hätte im Amazonas-Regenwald nach Anaconda gesucht.
Sargable-Abfragen (Suche argumentierbar)
In einfachen Worten, Sargable-Abfragen sind solche, die erstellte Indizes für eine schnellere Suche und Ausführung einer Abfrage verwenden können.
Eine schnellere Suche bedeutet, dass ein effektiver Index eine große Anzahl von Zeilen sucht und kostspielige Index-Scans vermieden wird.
Index suchen - Abfragen sind in der Lage, Indizes effektiv zu verwenden und Zeilen mit weniger Aufwand vom Abfrageoptimierer zu lokalisieren.
Index-Scan - Scannen der gesamten Tabelle, um Zeilen zu finden, die den Suchkriterien entsprechen
Was macht eine Abfrage nicht ansteckbar (erstellte Indizes nicht effektiv verwenden können)?
1. Verwenden von Funktionen in Where-Klausel-Bedingungen (da eine Funktion für jede Zeile ausgewertet wird, wodurch der Abfrageoptimierer den Index nicht verwendet)
2. Verwendung von LIKE '% Proposal%' in Suchabfragen mit Platzhaltern
3. arithmetische Berechnung einer Indexspalte in einer Where-Klausel
Lassen Sie uns eine Tabelle mit 0,1 Millionen Zeilen erstellen und auffüllen, um zu sehen, wie Abfragen anfassbar gemacht werden.
Dieses Skript benötigt Zeit, um Beispieldaten basierend auf Ihrer Hardwarekonfiguration (3-5 Minuten) zu erstellen.
- Erstellen Sie eine Tabelle mit dem Primärschlüssel CREATE TABLE EmployeeTest (ID INT IDENTITY (1, 1)) PRIMARY KEY, Gehalt INT, DateOfBirth DATETIME, EmployeeName VARCHAR (80)); GO - Zeilen mit zufälligen Werten einfügen DECLARE @row INT; DECLARE @string VARCHAR (80), @length INT, @code INT; SET @row = 0; WO @row <100000 BEGIN SET @row = @row + 1; IF @ row = 10000 PRINT 'Eingefügte Zeilen:' + CONVERT (VARCHAR (20), @ row); IF @ row = 20000 PRINT 'Eingefügte Zeilen:' + CONVERT (VARCHAR (20), @ row); IF @row = 30000 PRINT 'Eingefügte Zeilen:' + CONVERT (VARCHAR (20), @ row); IF @ row = 40000 PRINT 'Eingefügte Zeilen:' + CONVERT (VARCHAR (20), @ row); IF @ row = 50000 PRINT 'Eingefügte Zeilen:' + CONVERT (VARCHAR (20), @ row); IF @ row = 60000 PRINT 'Eingefügte Zeilen:' + CONVERT (VARCHAR (20), @ row); IF @ row = 70000 PRINT 'Eingefügte Zeilen:' + CONVERT (VARCHAR (20), @ row); IF @ row = 80000 PRINT 'Eingefügte Zeilen:' + CONVERT (VARCHAR (20), @ row); IF @ row = 90000 PRINT 'Eingefügte Zeilen:' + CONVERT (VARCHAR (20), @ row); IF @ row = 100000 PRINT 'Done, eingefügte Zeilen:' + CONVERT (VARCHAR (20), @ row); - Erstellen Sie die Zufallszeichenfolge SET @length = ROUND (80 * RAND (), 0); SET @string = "; WHILE @length> 0 BEGIN SET @length = @length - 1; SET @code = ROUND (32 * RAND (), 0) - 6; WENN @code zwischen 1 und 26 SET @string = @ string + CHAR (ASCII ('a') + @ code-1); ELSE SET @string = @string + "; ENDE - Bereit für den Datensatz SET NOCOUNT ON; INSERT IN EmployeeTest VALUES (RUND (2000000 * RAND () + 10000,0), CONVERT (DATETIME, RUND (60000 * RAND () - 30000, 9)), @string) ENDE GO Lassen Sie uns für jede Spalte einen nicht gruppierten Index erstellen.
CREATE NONCLUSTERED INDEX [NCI_EmployeeTest_Salary] ON [dbo]. [EmployeeTest] ([Gehalt] ASC) GO CREATE NONCLUSTERED INDEX [NCI_EmployeeTest_DateOfBirth] ON [dbo]. [EmployeeTest] ([DateOfBirth] ASC) GO CREATE NONCLUSTERED INDEX [NCI_EmployeeTest_EmployeeName] ON [dbo]. [EmployeeTest] ([EmployeeName] ASC) GO
Sehen wir uns einige Beispielabfragen an, um den Unterschied zwischen sargable und nicht-sargable Abfragen zu sehen.
1. Filtern des Ergebnisses anhand der Mitarbeiternamen, die mit A beginnen
SET STATISTICS IO ON - Nicht abfragbare Abfrage wegen der in Where-Klausel verwendeten Funktion SELECT EmployeeName FROM EmployeeTest WHERE LEFT (EmployeeName, 1) = 'A'; --Akabierbare Abfrage SELECT EmployeeName FROM EmployeeTest, WO EmployeeName wie 'A%'; SET STATISTICS IO OFF
Die Statistiken unten zeigen, dass die erste nicht-sargable-Abfrage 680 logische Lesevorgänge durchführte, während die sargable-Abfrage mit einer Platzhaltersuche nur 25 logische Lesevorgänge durchführte.
(3115 betroffene Zeilen) Tabelle 'EmployeeTest'. Scananzahl 1, logisch liest 680, physikalische Lesevorgänge 1, Read-Ahead-Lesevorgänge 688, logische Lesevorgänge 0, logische Lesevorgänge 0, Lob-Read-Ahead-Lesevorgänge 0. (3115 betroffene Zeilen) Tabelle 'EmployeeTest'. Scananzahl 1, logisch liest 25, physikalische Lesevorgänge 0, Read-Ahead-Lesevorgänge 0, logische Lesevorgänge 0, logische Lesevorgänge 0, Lob-Read-Ahead-Lesevorgänge 0.In den folgenden Ausführungsplänen werden die ersten Nicht-Abfrage-Abfragen dargestellt 97% kostenwährend die Sargable-Abfrage dauert 3% bei Index Seek.
2. Ergebnisse für ein bestimmtes Jahr filtern
SET STATISTICS IO ON - Nicht abfragbare Abfrage wegen Funktion, die in Where-Klausel verwendet wird SELECT DateOfBirth FROM EmployeeTest WHERE YEAR (DateOfBirth) = '1952'; --Abstellbare Abfrage SELECT DateOfBirth FROM EmployeeTest WHERE DateOfBirth> = '19520101' AND DateOfBirth <'19530101'; SET STATISTICS IO OFF
Die Statistiken unten zeigen, dass die erste nicht-Sargable-Abfrage 226 logische Lesevorgänge durchführte, während die Sargable-Abfrage nur vier logische Lesevorgänge ausführte.
(628 Zeile (n) betroffen) Tabelle 'EmployeeTest'. Scananzahl 1, logische Lesevorgänge 226, physikalische Lesevorgänge 0, Read-Ahead-Lesevorgänge 0, logische Lesevorgänge 0, logische Lesevorgänge 0, Lob-Ahead-Lesevorgänge 0.(628 Zeile (n) betroffen) Tabelle 'EmployeeTest'. Scananzahl 1, logische Lesevorgänge 4, physikalische Lesevorgänge 0, Read-Ahead-Lesevorgänge 0, logische Lesevorgänge 0, logische Lesevorgänge 0, Lob-Ahead-Lesevorgänge 0.In den folgenden Ausführungsplänen werden die ersten Nicht-Abfrage-Abfragen dargestellt 98% Chargenkosten mit Index Scan, während die Abfrage Sargable dauert 2% bei Index Seek.
3. Berechnungen für eine Indexspalte in einer Where-Klausel
SET STATISTICS IO ON - Nicht abfragbare Abfrage wegen Berechnung in der Indexspalte --in Where Clause SELECT Gehalt aus EmployeeTest WHERE Gehalt / 2 = 50147; --Akabierbare Abfrage SELECT Gehalt aus EmployeeTest WHERE Gehalt = (50147 * 2); SET STATISTICS IO OFF
Die Statistiken unten zeigen, dass die erste nicht-sargable-Abfrage 178 logische Lesevorgänge durchführte, während die sargable-Abfrage nur zwei logische Lesevorgänge ausführte.
(3 Zeile (n) betroffen) Tabelle 'EmployeeTest'. Scananzahl 1, logisch liest 178, physikalische Lesevorgänge 0, Read-Ahead-Lesevorgänge 0, logische Lesevorgänge 0, logische Lesevorgänge 0, Lob-Read-Ahead-Lesevorgänge 0. (3 betroffene Zeilen) Tabelle 'EmployeeTest'. Scananzahl 1, logische Lesevorgänge 2, physikalische Lesevorgänge 0, Read-Ahead-Lesevorgänge 0, logische Lesevorgänge 0, logische Lesevorgänge 0, Lob-Ahead-Lesevorgänge 0.Die folgenden Ausführungspläne zeigen die ersten Nicht-Speicher-Abfragen 99% Batch-Kosten mit Index-Scanwährend die Sargable-Abfrage dauert 1% bei Index Seek.
4. Verwendung der ISNULL-Funktion in einer Where-Klausel
SET STATISTICS IO ON - Nicht abfragbare Abfrage wegen ISNULL-Funktion in der Indexspalte --in Where-Klausel Wählen Sie EmployeeName FROM EmployeeTest, wobei ISNULL (EmployeeName, 'Vru') = 'Vru'; - Abrufbare Abfrage Wählen Sie EmployeeName FROM EmployeeTest aus, wobei EmployeeName = 'Vru' oder EmployeeName NULL ist. SET STATISTICS IO OFF
Die Statistiken unten zeigen, dass die erste nicht-sargable-Abfrage 680 logische Lesevorgänge durchführte, während die sargable-Abfrage nur sechs logische Lesevorgänge ausführte.
(1 Zeile (n) betroffen) Tabelle 'EmployeeTest'. Scananzahl 1, logisch liest 680, physikalische Lesevorgänge 0, Read-Ahead-Lesevorgänge 0, logische Lesevorgänge 0, logische Lesevorgänge 0, Lob-Read-Ahead-Lesevorgänge 0. (1 betroffene Zeilen) Tabelle 'EmployeeTest'. Scananzahl 2, logisch liest 6, physikalische Lesevorgänge 0, Read-Ahead-Lesevorgänge 0, logische Lesevorgänge 0, logische Lesevorgänge 0, Lob-Ahead-Lesevorgänge 0.Die folgenden Ausführungspläne zeigen die ersten Nicht-Speicher-Abfragen 99% Batch-Kosten mit Index-Scanwährend die Sargable-Abfrage dauert 1% bei Index Seek.
