Den mest intressanta delen av mitt jobb är prestationsinställning och optimering i T-SQL. Hjärtat i prestandatunning i en SQL-server är korrekt och användbar indexering på tabeller genom användning av Sargable-frågor.
Ibland säger Senior Database Developer på jobbet att jag bara lägger till ett index i en xyz-kolumn eftersom det används i flera Where-klausuler inom flera SQL-frågor. Det är då jag måste lugna mitt sinne. Om du lägger till ett index på xyz-kolumnen kan du lösa alla prestationsproblem, då skulle tusentals böcker inte ha publicerats på T-SQL-prestandatuning, och jag skulle ha gått på jakt efter anaconda i Amazonas regnskog.
Sargable Queries (Sök Argumentable)
I enkla termer är Sargable-frågor de som kan använda skapade index på dem för snabbare sökningar och utförande av en fråga.
En snabbare sökning betyder att ett effektivt index söker upp ett stort antal rader och undviker kostnadskalkyler.
Index sök - Frågor kan använda index effektivt och hitta rader med mindre ansträngning från frågeoptimeraren.
Index Scan - skannar hela bordet för att hitta rader för att tillgodose sökkriterierna
Vad gör en fråga icke-sargabel (kan inte använda skapade index effektivt?)?
1. använder funktioner i Where-klausulförhållanden (eftersom en funktion utvärderas mot varje rad som tvingar sökningsoptimeraren att inte använda indexet)
2. Använd LIKE '% Förslag%' i Wild Card-sökfrågor
3. utföra aritmetisk beräkning på en indexkolumn i en Where-klausul
Låt oss skapa och fylla ett bord med 0,1 miljoner rader för att se hur man gör sökningar Sargable.
Detta skript skulle ta tid att skapa provdata baserat på din hårdvarukonfiguration (3-5 minuter).
- Skapa en tabell med primär nyckel SKAPA TABELL MedarbetarTest (ID INT IDENTITET (1, 1) PRIMÄR NYCKEL, Lön INT, DateOfBirth DATETIME, anställningsnamn VARCHAR (80)); GO - Infoga rader med slumpmässiga värden DECLARE @row INT; DECLARE @ sträng VARCHAR (80), @length INT, @code INT; SET @row = 0; WHILE @row <100000 BEGIN SET @row = @row + 1; IF @row = 10000 PRINT 'Rader infogade:' + CONVERT (VARCHAR (20), @ rad); IF @row = 20000 PRINT 'Rader infogade:' + CONVERT (VARCHAR (20), @ rad); IF @row = 30000 PRINT 'Rader infogade:' + CONVERT (VARCHAR (20), @ rad); IF @row = 40000 PRINT 'Rader infogade:' + CONVERT (VARCHAR (20), @ rad); IF @row = 50000 PRINT 'Rader infogade:' + CONVERT (VARCHAR (20), @ rad); IF @row = 60000 PRINT 'Rader infogade:' + CONVERT (VARCHAR (20), @ rad); IF @row = 70000 PRINT 'Rader infogade:' + CONVERT (VARCHAR (20), @ rad); IF @row = 80000 PRINT 'Rader infogade:' + CONVERT (VARCHAR (20), @ rad); IF @row = 90000 PRINT 'Rader infogade:' + CONVERT (VARCHAR (20), @ rad); IF @row = 100000 PRINT 'Klar, rader infogade:' + CONVERT (VARCHAR (20), @ rad); - Bygg den slumpmässiga strängen SET @length = ROUND (80 * RAND (), 0); SET @string = "; WHILE @ längd> 0 BEGIN SET @length = @length - 1; SET @code = RUND (32 * RAND (), 0) - 6; IF @code MELLAN 1 OCH 26 SET @string = @ sträng + CHAR (ASCII ('a') + @ code-1); ELSE SET @string = @string + "; END - Klar för posten SET NOCOUNT ON; INSERTA I MEDDELARSTESTVÄRDEN (ROUND (2000000 * RAND () + 10000,0), KONVERTERA (DATUM, RUND (60000 * RAND () - 30000, 9)), @string) END GO Låt oss skapa ett icke-grupperat index på varje kolumn.
SKAPA NONCLUSTERED INDEX [NCI_EmployeeTest_Salary] ON [dbo]. [EmployeeTest] ([Lön] ASC) GÅ CREATE NONCLUSTERED INDEX [NCI_EmployeeTest_DateOfBirth] ON [dbo]. [EmployeeTest] ([DateOfBirth] ASC) GÅ CREATE NONCLUSTERED INDEX [NCI_EmployeeTest_EmployeeName] PÅ [dbo]. [EmployeeTest] ([EmployeeName] ASC) GO
Låt oss se några exempelfrågor för att se skillnaden mellan sargable och non-sargable queries.
1. Filtrera resultatet baserat på de anställdas namn som börjar med A
SET STATISTIK IO ON - Ej-Sargable Query på grund av Funktion Används i Var Clause VÄLJ Medarbetare NAME FROM EmployeeTest WHERE LEFT (EmployeeName, 1) = 'A'; --Sargable Query SELECT Anställdnamn FRÅN EmployeeTest WHERE EmployeeName LIKE 'A%'; SET STATISTIK IO AV
I statistiken nedan visas det första icke-sargable-frågan som tog 680 logiskt, medan den sargable frågan med en vildkortsökning endast visade 25 logiska ord.
(3115 rader påverkas) Tabell "EmployeeTest". Skanräkning 1, logiskt läser 680, fysisk läser 1, läsaren läser 688, lob logisk läser 0, lob fysikalisk läser 0, lob läser framåt 0. (3115 rader påverkas) Tabell EmployeeTest. Skanräkning 1, logiskt läser 25, fysiskt läser 0, läsaren läser 0, lob logisk läser 0, lob fysikalisk läser 0, lob läser framåt 0 läser 0.Exekveringsplanerna nedan visar den första icke-sargable-frågan tar 97% kostnad, medan Sargable-frågan tar 3% med Index Search.
2. Filtrera resultat för specifikt ett år
SET STATISTIK IO ON - Ej-Sargable Query på grund av Funktion som används i Var Clause VÄLJ DateOfBirth FROM EmployeeTest VAR ÅR (DateOfBirth) = '1952'; --Sargable Query VÄLJ DateOfBirth FROM EmployeeTest VAR DatumOfBirth> = '19520101' OCH DateOfBirth <'19530101'; SET STATISTIK IO AV
I statistiken nedan visas det första icke-Sargable-frågan tog 226 logiska läsningar, medan Sargable-frågan endast slutförde fyra logiska läsningar.
(628 rader påverkas) Tabell 'EmployeeTest'. Skanräkning 1, logiskt läser 226, fysiskt läser 0, läsaren läser 0, lob logisk läser 0, lob fysikalisk läser 0, lob läser framåt 0 läser 0.(628 rader påverkas) Tabell 'EmployeeTest'. Skanräkning 1, logiskt läser 4, fysiskt läser 0, läsaren läser 0, lob logisk läser 0, lob fysikalisk läser 0, lob läser framåt 0 läser 0.Exekveringsplanerna nedan visar den första icke-sargable-frågan tar 98% satskostnad med Index Scan, medan Sargable-frågan tar 2% med Index Search.
3. Beräkningar på en indexkolumn i en Var-klausul
SET STATISTIK IO ON - Ej-Sargable Query på grund av Beräkning gjort på Index Column - In Where Clause SELECT Lön FRÅN EmployeeTest VAR Lön / 2 = 50147; --Sargable Query SELECT Lön FRÅN MedarbetareTest VAR Lön = (50147 * 2); SET STATISTIK IO AV
I statistiken nedan visas det första icke-sargable-frågan som tog 178 logiska läsningar, medan den sargable query fullbordade endast två logiska läsningar.
(3 rader påverkas) Tabell 'EmployeeTest'. Skanräkning 1, logiskt läser 178, fysiskt läser 0, läsaren läser 0, lob logiskt läser 0, lob fysisk läser 0, lob läser framåt 0. (3 rader påverkas) Tabell EmployeeTest. Skanräkning 1, logiskt läser 2, fysiskt läser 0, läsaren läser 0, lob logisk läser 0, lob fysikalisk läser 0, lob läser framåt 0 läser 0.Exekveringsplanerna nedan visar det första icke-sargable-frågan tar 99% Batchkostnad med Index Scan, medan Sargable-frågan tar 1% med Index Search.
4. Använda ISNULL-funktionen i en Var-klausul
SET STATISTIK IO ON - Ej-Sargable Query på grund av ISNULL-funktionen på Index-kolumnen - i Var Clause välj EmployeeName FROM EmployeeTest där ISNULL (EmployeeName, 'Vru') = 'Vru'; --Sargable Query välj EmployeeName FROM EmployeeTest där EmployeeName = 'Vru' ELLER EmployeeName IS NULL; SET STATISTIK IO AV
I statistiken nedan visas det första icke-sargable-frågan som tog 680 logiska läs, medan den sargable query fullbordade endast sex logiska läsningar.
(1 rad (er) drabbade) Tabell "EmployeeTest". Skanräkning 1, logiskt läser 680, fysiskt läser 0, läsaren läser 0, lob logiskt läser 0, lob fysisk läser 0, lob läses framåt 0. (1 rad (er) påverkas) Tabell EmployeeTest. Skanräkning 2, logiskt läser 6, fysiskt läser 0, läsaren läser 0, lob logisk läser 0, lob fysikalisk läser 0, lob läser framåt 0 läser 0.Exekveringsplanerna nedan visar det första icke-sargable-frågan tar 99% Batchkostnad med Index Scan, medan Sargable-frågan tar 1% med Index Search.
