La parte più interessante del mio lavoro è l'ottimizzazione delle prestazioni e l'ottimizzazione in T-SQL. Il fulcro dell'ottimizzazione delle prestazioni in un server SQL è l'indicizzazione corretta e utilizzabile sulle tabelle mediante l'utilizzo di query Sargable.
A volte, lo Sviluppatore database senior al lavoro mi dice solo di aggiungere un indice a una colonna xyz perché viene utilizzato in più clausole Where all'interno di più query SQL. Questo è quando ho bisogno di calmare la mente. Se l'aggiunta di un indice sulla colonna xyz potesse risolvere ogni problema di prestazioni, allora migliaia di libri non sarebbero stati pubblicati sull'ottimizzazione delle prestazioni del T-SQL e sarei andato a cercare anaconda nella foresta pluviale amazzonica.
Query Sargable (argomento di ricerca)
In termini semplici, le query Sargable sono quelle che sono in grado di utilizzare indici creati su di esse per ricerche più veloci ed esecuzione di una query.
Una ricerca più veloce significa che un indice efficace cerca un numero elevato di righe ed evita costose analisi dell'indice.
Ricerca indice - Le query sono in grado di utilizzare gli indici in modo efficace e individuare le righe con meno sforzo da Query Optimizer.
Scansione dell'indice - scansione dell'intera tabella per individuare le righe per soddisfare i criteri di ricerca
Cosa rende una query non sargibile (non è in grado di utilizzare gli indici creati in modo efficace)?
1. utilizzo delle funzioni nelle condizioni della clausola Where (poiché una funzione viene valutata rispetto a ciascuna riga che forza l'ottimizzazione della query a non utilizzare l'indice)
2. utilizzando LIKE '% Proposal%' nelle query di ricerca con jolly
3. esecuzione del calcolo aritmetico su una colonna dell'indice in una clausola Where
Creiamo e popoliamo una tabella con 0,1 milioni di righe per vedere come rendere le query Sargable.
Questo script richiede tempo per creare dati di esempio in base alla configurazione hardware (3-5 minuti).
- Crea una tabella con la chiave primaria CREATE TABLE EmployeeTest (ID INT IDENTITY (1, 1) PRIMARY KEY, Salary INT, DateOfBirth DATETIME, EmployeeName VARCHAR (80)); GO - Inserisci righe con valori casuali DECLARE @row INT; DECLARE @string VARCHAR (80), @length INT, @code INT; SET @row = 0; WHILE @row <100000 BEGIN SET @row = @row + 1; IF @row = 10000 PRINT 'Righe inserite:' + CONVERT (VARCHAR (20), @ row); IF @row = 20000 PRINT 'Righe inserite:' + CONVERT (VARCHAR (20), @ row); IF @row = 30000 PRINT 'Righe inserite:' + CONVERT (VARCHAR (20), @ row); IF @row = 40000 PRINT 'Righe inserite:' + CONVERT (VARCHAR (20), @ row); IF @row = 50000 PRINT 'Righe inserite:' + CONVERT (VARCHAR (20), @ row); IF @row = 60000 PRINT 'Righe inserite:' + CONVERT (VARCHAR (20), @ row); IF @row = 70000 PRINT 'Righe inserite:' + CONVERT (VARCHAR (20), @ row); IF @row = 80000 PRINT 'Righe inserite:' + CONVERT (VARCHAR (20), @ row); IF @row = 90000 PRINT 'Righe inserite:' + CONVERT (VARCHAR (20), @ row); IF @row = 100000 PRINT 'Fatto, Righe inserite:' + CONVERT (VARCHAR (20), @ row); - Costruisci la stringa casuale SET @length = ROUND (80 * RAND (), 0); SET @string = "; WHILE @length> 0 BEGIN SET @length = @length - 1; SET @code = ROUND (32 * RAND (), 0) - 6; IF @code TRA 1 E 26 SET @string = @ string + CHAR (ASCII ('a') + @ code-1); ELSE SET @string = @string + "; FINE - Pronto per la registrazione SET NOCOUNT ON; INSERISCI IN VALORI Dipendenti Test (ROUND (2000000 * RAND () + 10000,0), CONVERT (DATETIME, ROUND (60000 * RAND () - 30000, 9)), @string) END GO Creiamo un indice non cluster su ogni colonna.
CREA INDICE NONCLUSTERED [NCI_EmployeeTest_Salary] ON [dbo]. [EmployeeTest] ([Salary] ASC) GO CREATE INDICE NONCLUSTERED [NCI_EmployeeTest_DateOfBirth] ON [dbo]. [EmployeeTest] ([DateOfBirth] ASC) GO CREATE INDICE NONCLUSTERED [NCI_EmployeeTest_EmployeeName] ON [dbo]. [EmployeeTest] ([EmployeeName] ASC) GO
Vediamo alcune query di esempi per vedere la differenza tra le query sargable e non-sargable.
1. Filtro del risultato in base ai nomi dei dipendenti che iniziano con A
SET STATISTICS IO ON - Query non-Sargable a causa della funzione utilizzata nella clausola Where SELECT EmployeeName FROM EmployeeTest WHERE LEFT (EmployeeName, 1) = 'A'; - Query modificabile SELECT EmployeeName FROM EmployeeTest WHERE EmployeeName LIKE 'A%'; SET STATISTICS IO OFF
Le statistiche seguenti mostrano che la prima query non sargabile ha richiesto 680 letture logiche, mentre la query sargable con una ricerca con caratteri jolly ha eseguito solo 25 letture logiche.
(3115 righe interessate) Tabella "EmployeeTest". Numero di scansioni 1, letture logiche 680, letture fisiche 1, letture read-ahead 688, lob letture logiche 0, lob letture fisiche 0, lob read-ahead letture 0. (3115 righe (s) interessate) Tabella 'EmployeeTest'. Numero di scansioni 1, letture logiche 25, letture fisiche 0, letture read-ahead 0, lob letture logiche 0, lob letture fisiche 0, letture read-ahead lob 0.I piani di esecuzione di seguito mostrano la prima richiesta Non-Sargable 97% di costo, mentre la query Sargable richiede 3% con Index Seek.
2. Risultati di filtraggio specifici per anno
SET STATISTICS IO ON - Query non-Sargable a causa della funzione utilizzata nella clausola Where SELECT DateOfBirth FROM EmployeeTest WHERE YEAR (DateOfBirth) = '1952'; - Query selezionabile SELECT DateOfBirth FROM EmployeeTest WHERE DateOfBirth> = '19520101' AND DateOfBirth <'19530101'; SET STATISTICS IO OFF
Le statistiche seguenti mostrano che la prima query non Sargable ha richiesto 226 letture logiche, mentre la query Sargable ha completato solo quattro letture logiche.
(628 righe interessate) Tabella "EmployeeTest". Numero di scansioni 1, letture logiche 226, letture fisiche 0, letture read-ahead 0, lob letture logiche 0, lob letture fisiche 0, letture read-ahead lob 0.(628 righe interessate) Tabella "EmployeeTest". Numero di scansioni 1, letture logiche 4, letture fisiche 0, letture read-ahead 0, lob letture logiche 0, lob letture fisiche 0, letture read-ahead lob 0.I piani di esecuzione riportati di seguito mostrano la prima query Non-Sargable 98% del costo del lotto con Index Scan, mentre la query Sargable richiede 2% con Index Seek.
3. Calcoli su una colonna indice in una clausola Where
SET STATISTICS IO ON - Query non-Sargable a causa del calcolo eseguito nella colonna indice - in Dove clausola SELECT Salary FROM EmployeeTest WHERE Salary / 2 = 50147; - Query selezionabile SELECT Salario FROM EmployeeTest WHERE Salary = (50147 * 2); SET STATISTICS IO OFF
Le statistiche seguenti mostrano che la prima query non sargibile ha richiesto 178 letture logiche, mentre la query sargable ha completato solo due letture logiche.
(3 righe interessate) Tabella "EmployeeTest". Numero di scansioni 1, letture logiche 178, letture fisiche 0, letture read-ahead 0, lob letture logiche 0, lob letture fisiche 0, lob read-ahead letture 0. (3 righe interessate) Tabella 'EmployeeTest'. Numero di scansioni 1, letture logiche 2, letture fisiche 0, letture read-ahead 0, lob letture logiche 0, lob letture fisiche 0, letture read-ahead lob 0.I piani di esecuzione riportati di seguito mostrano la prima query Non sargabile 99% del costo in lotti con scansione indice, mentre la query Sargable richiede 1% con Index Seek.
4. Utilizzo della funzione ISNULL in una clausola Where
SET STATISTICS IO ON - Query non-Sargable a causa della funzione ISNULL nella colonna indice - in Where Clausola select EmployeeName FROM EmployeeTest dove ISNULL (EmployeeName, 'Vru') = 'Vru'; - Query selezionabile selezionare EmployeeName FROM EmployeeTest dove EmployeeName = 'Vru' O EmployeeName È NULL; SET STATISTICS IO OFF
Le statistiche seguenti mostrano che la prima query non sillababile ha richiesto 680 letture logiche, mentre la query sargable ha completato solo sei letture logiche.
(1 riga (e) interessata) Tabella 'EmployeeTest'. Numero di scansioni 1, letture logiche 680, letture fisiche 0, letture read-ahead 0, lob letture logiche 0, lob letture fisiche 0, lob read-ahead letture 0. (1 riga (s) interessate) Tabella 'EmployeeTest'. Numero di scansioni 2, letture logiche 6, letture fisiche 0, letture read-ahead 0, lob letture logiche 0, lob letture fisiche 0, letture read-ahead lob 0.I piani di esecuzione riportati di seguito mostrano la prima query Non sargabile 99% del costo in lotti con scansione indice, mentre la query Sargable richiede 1% con Index Seek.
