Guida all'elaborazione delle query per le tabelle con ottimizzazione per la memoria

Articolo
06/13/2017

In-Memory OLTP introduce tabelle ottimizzate per la memoria e stored procedure compilate in modo nativo in SQL Server. In questo articolo viene fornita una panoramica sull'elaborazione delle query per le tabelle ottimizzate per la memoria e le stored procedure compilate in modo nativo.

Nel documento viene illustrato come compilare ed eseguire query sulle tabelle ottimizzate per la memoria, tra cui:

Pipeline di elaborazione delle query in SQL Server per le tabelle basate su disco.
Ottimizzazione query; il ruolo delle statistiche sulle tabelle ottimizzate per la memoria e linee guida per la risoluzione dei problemi relativi a piani di query errati.
Uso di Transact-SQL interpretato per accedere alle tabelle ottimizzate per la memoria.
Considerazioni sull'ottimizzazione query per l'accesso alle tabelle ottimizzate per la memoria.
Compilazione ed elaborazione di stored procedure compilate in modo nativo.
Statistiche utilizzate per la stima dei costi in Query Optimizer.
Modi per correggere piani di query errati.

Query di esempio

L'esempio seguente verrà utilizzato per illustrare i concetti di elaborazione delle query descritti in questo articolo.

Vengono considerate due tabelle, Customer e Order. Lo script Transact-SQL seguente contiene le definizioni per queste due tabelle e gli indici associati, nel formato basato su disco (tradizionale):

CREATE TABLE dbo.[Customer] (  
  CustomerID nchar (5) NOT NULL PRIMARY KEY,  
  ContactName nvarchar (30) NOT NULL   
)  
GO  
  
CREATE TABLE dbo.[Order] (  
  OrderID int NOT NULL PRIMARY KEY,  
  CustomerID nchar (5) NOT NULL,  
  OrderDate date NOT NULL  
)  
GO  
CREATE INDEX IX_CustomerID ON dbo.[Order](CustomerID)  
GO  
CREATE INDEX IX_OrderDate ON dbo.[Order](OrderDate)  
GO

Per la creazione dei piani di query illustrati in questo articolo, le due tabelle sono state popolate con dati del database di esempio Northwind, che è possibile scaricare da Northwind and pubs Sample Databases for SQL Server 2000(Database di esempio Northwind e pubs per SQL Server 2000).

Si consideri la query seguente, che crea un join tra le tabelle Customer e Order e restituisce l'ID dell'ordine e le informazioni sul cliente associato:

SELECT o.OrderID, c.* FROM dbo.[Customer] c INNER JOIN dbo.[Order] o ON c.CustomerID = o.CustomerID

Il piano di esecuzione stimato visualizzato da SQL Server Management Studio è il seguente

Piano di query per il join di tabelle basate su disco.

Informazioni su questo piano di query:

Le righe della tabella Customer vengono recuperate dall'indice cluster, che è la struttura dei dati primaria e contiene i dati completi della tabella.
I dati della tabella Order vengono recuperati usando l'indice non cluster della colonna CustomerID. L'indice contiene sia la colonna CustomerID, utilizzata per il join, sia la colonna chiave primaria OrderID, che viene restituita all'utente. La restituzione di colonne aggiuntive dalla tabella Order richiederebbe ricerche nell'indice cluster della tabella stessa.
L'operatore logico Inner Join viene implementato dall'operatore fisico Merge Join. Gli altri tipi di join fisico sono Nested Loops e Hash Join. L'operatore Merge Join consente di sfruttare il fatto che entrambi gli indici sono ordinati in base alla colonna di join CustomerID.

Si consideri una leggera variazione in questa query, con la restituzione di tutte le righe della tabella Order e non solo di OrderID, come illustrato di seguito:

SELECT o.*, c.* FROM dbo.[Customer] c INNER JOIN dbo.[Order] o ON c.CustomerID = o.CustomerID

Il piano stimato per la query è il seguente:

Piano di query per un hash join di tabelle basate su disco.

In questa query le righe della tabella Order vengono recuperate utilizzando l'indice cluster. L'operatore fisico Hash Match viene ora utilizzato per l'Inner Join. L'indice cluster di Order non è ordinato in base a CustomerID, pertanto per un Merge Join sarebbe necessario un operatore di ordinamento, che influirebbe sulle prestazioni. Si noti il costo relativo dell'operatore Hash Match (75%) rispetto al costo dell'operatore Merge Join nell'esempio precedente (46%). In Query Optimizer l'operatore Hash Match è stato preso in considerazione anche nell'esempio precedente, con la conclusione, tuttavia, che l'operatore Merge Join avrebbe offerto prestazioni migliori.

elaborazione di query SQL Server per tabelle Disk-Based

Il diagramma seguente illustra il flusso di elaborazione delle query in SQL Server per le query ad hoc:

SQL Server pipeline di elaborazione delle query.
Pipeline di elaborazione delle query di SQL Server.

In questo scenario:

L'utente esegue una query.
Il parser e l'algebrizer costruiscono un albero di query con operatori logici basati sul testo Transact-SQL inviato dall'utente.
Query Optimizer crea un piano di query ottimizzato che contiene operatori fisici, ad esempio join a cicli annidati. Dopo l'ottimizzazione, il piano può essere archiviato nella cache dei piani. Questo passaggio viene ignorato se la cache dei piani già contiene un piano per la query.
Un'interpretazione del piano di query viene elaborato dal motore di esecuzione delle query.
Per ogni operatore Index Seek, Index Scan e Table Scan, il motore di esecuzione richiede righe delle rispettive strutture di indice e di tabella ad Access Methods.
Access Methods recupera le righe dalle pagine dell'indice e dei dati nel pool di buffer e carica le pagine dal disco nel pool di buffer in base alle esigenze.

Per la prima query di esempio, il motore di esecuzione richiede ad Access Methods le righe dell'indice cluster di Customer e dell'indice non cluster di Order. Access Methods attraversa le strutture di indice ad albero B per recuperare le righe richieste. In questo caso vengono recuperate tutte le righe poiché il piano richiede le analisi complete degli indici.

Accesso Transact-SQL interpretato alle tabelle Memory-Optimized

I batch e le stored procedure ad hoc Transact-SQL vengono definiti anche transact-SQL interpretati. L'interpretazione si riferisce al fatto che ogni operatore nel piano di query viene interpretato dal motore di esecuzione delle query. Il motore di esecuzione legge l'operatore e i relativi parametri ed esegue l'operazione.

Transact-SQL interpretato può essere usato per accedere a tabelle ottimizzate per la memoria e basate su disco. La figura seguente illustra l'elaborazione delle query per l'accesso Transact-SQL interpretato alle tabelle ottimizzate per la memoria:

Pipeline di elaborazione delle query per l'accesso del codice Transact-SQL interpretato alle tabelle ottimizzate per la memoria.

Come illustrato nella figura, la pipeline di elaborazione delle query rimane per lo più invariata:

Il parser e il normalizzatore creano l'albero della query.
Query Optimizer crea il piano di esecuzione.
Il piano di esecuzione viene interpretato dal motore di esecuzione delle query.

La differenza principale rispetto alla pipeline tradizionale di elaborazione delle query (figura 2) sta nel fatto che le righe delle tabelle ottimizzate per la memoria non vengono recuperate dal pool di buffer tramite Access Methods. Al contrario, le righe vengono recuperate dalle strutture dei dati in memoria tramite il motore di OLTP in memoria. Le differenze nelle strutture dei dati provocano in alcuni casi la selezione di piani diversi da parte di Query Optimizer, come illustrato nell'esempio seguente.

Lo script Transact-SQL seguente contiene versioni ottimizzate per la memoria delle tabelle Order e Customer, usando indici hash:

CREATE TABLE dbo.[Customer] (  
  CustomerID nchar (5) NOT NULL PRIMARY KEY NONCLUSTERED,  
  ContactName nvarchar (30) NOT NULL   
) WITH (MEMORY_OPTIMIZED=ON)  
GO  
  
CREATE TABLE dbo.[Order] (  
  OrderID int NOT NULL PRIMARY KEY NONCLUSTERED,  
  CustomerID nchar (5) NOT NULL INDEX IX_CustomerID HASH(CustomerID) WITH (BUCKET_COUNT=100000),  
  OrderDate date NOT NULL INDEX IX_OrderDate HASH(OrderDate) WITH (BUCKET_COUNT=100000)  
) WITH (MEMORY_OPTIMIZED=ON)  
GO

Si consideri la stessa query eseguita su tabelle ottimizzate per la memoria:

SELECT o.OrderID, c.* FROM dbo.[Customer] c INNER JOIN dbo.[Order] o ON c.CustomerID = o.CustomerID

Il piano stimato è il seguente:

Piano di query per il join di tabelle ottimizzate per la memoria.

Osservare le seguenti differenze del piano per la stessa query su tabelle basate su disco (figura 1):

Questo piano contiene un'analisi di tabella anziché un'analisi di indice cluster per la tabella Customer:
- La definizione della tabella non include un indice cluster.
- Gli indici cluster non sono supportati con le tabelle ottimizzate per la memoria. Ogni tabella ottimizzata per la memoria deve invece disporre di almeno un indice non cluster e tutti gli indici delle tabelle ottimizzate per la memoria possono accedere in modo efficace a tutte le colonne della tabella senza che sia necessario archiviare tali colonne nell'indice o fare riferimento a un indice cluster.
Il piano contiene un Hash Match anziché un Merge Join. Sia gli indici della tabella Order che quelli della tabella Customer sono indici hash, quindi non ordinati. Un Merge Join richiederebbe un operatore di ordinamento, che ridurrebbe le prestazioni.

stored procedure compilate in modo nativo

Le stored procedure compilate in modo nativo sono stored procedure Transact-SQL compilate nel codice del computer, anziché interpretate dal motore di esecuzione di query. Lo script seguente crea una stored procedure compilata in modo nativo che esegue la query di esempio (della sezione Query di esempio).

CREATE PROCEDURE usp_SampleJoin  
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER  
AS BEGIN ATOMIC WITH   
(  TRANSACTION ISOLATION LEVEL = SNAPSHOT,  
  LANGUAGE = 'english')  
  
  SELECT o.OrderID, c.CustomerID, c.ContactName   
FROM dbo.[Order] o INNER JOIN dbo.[Customer] c   
  ON c.CustomerID = o.CustomerID  
  
END

Le stored procedure compilate in modo nativo vengono compilate al momento della creazione, mentre le stored procedure interpretate vengono compilate alla prima esecuzione (Una parte della compilazione, in particolare l'analisi e l'algebrizzazione, avviene in fase di creazione. Tuttavia, per le stored procedure interpretate, l'ottimizzazione dei piani di query viene eseguita al primo esecuzione. La logica di ricompilazione è simile. Le stored procedure compilate in modo nativo vengono ricompilate alla prima esecuzione della procedura se il server viene riavviato. Le stored procedure interpretate vengono ricompilate se il piano non si trova più nella cache dei piani. Nella tabella seguente vengono riepilogati i casi di compilazione e di ricompilazione per le stored procedure compilate in modo nativo e interpretate:

	Compilate in modo nativo	Accesso del codice
Compilazione iniziale	Al momento della creazione.	Alla prima esecuzione.
Ricompilazione automatica	Alla prima esecuzione della procedura dopo il riavvio del database o del server.	Al riavvio del server. In alternativa, eliminazione dalla cache dei piani, in genere in base alle modifiche di schema o di statistiche o a utilizzo elevato di memoria.
Ricompilazione manuale	Non supportato. La soluzione alternativa consiste nell'eliminare e ricreare la stored procedure.	Usare `sp_recompile`. È possibile eliminare manualmente il piano dalla cache, ad esempio tramite DBCC FREEPROCCACHE. È inoltre possibile creare la stored procedure specificando WITH RECOMPILE affinché venga ricompilata a ogni esecuzione.

Compilazione ed elaborazione delle query

Nel diagramma seguente viene illustrato il processo di compilazione per le stored procedure compilate in modo nativo:

Compilazione nativa di stored procedure.
Compilazione nativa delle stored procedure.

Il processo è il seguente:

L'utente invia un'istruzione CREATE PROCEDURE per SQL Server.
Il parser e l'algebrizer creano il flusso di elaborazione per la procedura, nonché gli alberi delle query per le query Transact-SQL nella stored procedure.
Query Optimizer crea piani di esecuzione ottimizzati per tutte le query nella stored procedure.
Il compilatore di OLTP in memoria acquisisce il flusso di elaborazione con i piani di query ottimizzati incorporati e genera una DLL contenente il codice macchina per l'esecuzione della stored procedure.
La DLL generata viene caricata in memoria.

La chiamata di una stored procedure compilata in modo nativo viene convertita in chiamata a una funzione nella DLL.

Esecuzione di stored procedure compilate in modo nativo.

La chiamata di una stored procedure compilata in modo nativo viene descritta nel modo riportato di seguito.

L'utente rilascia un'istruzioneEXEC usp_myproc.
Il parser estrae il nome e i parametri della stored procedure.

Se l'istruzione è stata preparata, ad esempio utilizzando sp_prep_exec, il parser non deve estrarre il nome e i parametri della stored procedure in fase di esecuzione.
Il runtime di OLTP in memoria individua il punto di ingresso della DLL per la stored procedure.
Viene eseguito il codice della macchina nella DLL e i risultati vengono restituiti al client.

Sniffing dei parametri

Le stored procedure Transact-SQL interpretate vengono compilate al primo esecuzione, a differenza delle stored procedure compilate in modo nativo, che vengono compilate in fase di creazione. Quando le stored procedure interpretate vengono compilate al momento della chiamata, i valori dei parametri forniti per la chiamata vengono utilizzati da Query Optimizer durante la generazione del piano di esecuzione. Questo utilizzo dei parametri durante la compilazione viene chiamato sniffing dei parametri.

Lo sniffing dei parametri non viene utilizzato per la compilazione delle stored procedure compilate in modo nativo. Tutti i parametri della stored procedure vengono considerati con valore UNKNOWN. Analogamente alle stored procedure interpretate, anche le stored procedure compilate in modo nativo supportano l'hint OPTIMIZE FOR. Per altre informazioni, vedere Hint per la query (Transact-SQL).

Recupero di un piano di esecuzione di query per le stored procedure compilate in modo nativo

Il piano di esecuzione delle query per una stored procedure compilata in modo nativo può essere recuperato usando il piano di esecuzione stimato in Management Studio oppure usando l'opzione SHOWPLAN_XML in Transact-SQL. Ad esempio:

SET SHOWPLAN_XML ON  
GO  
EXEC dbo.usp_myproc  
GO  
SET SHOWPLAN_XML OFF  
GO

Il piano di esecuzione generato da Query Optimizer consiste in un albero con gli operatori di query sui nodi e sulle foglie. La struttura dell'albero determina l'interazione (il flusso di righe da un operatore a un altro) tra operatori. Nella visualizzazione grafica di SQL Server Management Studio, il flusso si trova da destra a sinistra. Ad esempio, il piano di query nella figura 1 contiene due operatori Index Scan che forniscono le righe a un operatore Merge Join. L'operatore Merge Join fornisce le righe a un operatore Select. L'operatore Select, infine, restituisce le righe al client.

Operatori di query nelle stored procedure compilate in modo nativo

Nella tabella seguente vengono riepilogati gli operatori di query supportati nelle stored procedure compilate in modo nativo:

Operatore	Query di esempio
SELECT	`SELECT OrderID FROM dbo.[Order]`
INSERT	`INSERT dbo.Customer VALUES ('abc', 'def')`
UPDATE	`UPDATE dbo.Customer SET ContactName='ghi' WHERE CustomerID='abc'`
DELETE	`DELETE dbo.Customer WHERE CustomerID='abc'`
Compute Scalar	Questo operatore viene utilizzato sia per le funzioni intrinseche che per le conversioni dei tipi. Non tutte le funzioni e conversioni dei tipi sono supportate nelle stored procedure compilate in modo nativo. `SELECT OrderID+1 FROM dbo.[Order]`
Join a cicli annidati	Nested Loops è l'unico operatore di join supportato nelle stored procedure compilate in modo nativo. Tutti i piani che contengono join useranno l'operatore Nested Loops, anche se il piano per la stessa query eseguita come interpretato transact-SQL contiene un hash o un merge join. `SELECT o.OrderID, c.CustomerID` `FROM dbo.[Order] o INNER JOIN dbo.[Customer] c`
Ordina	`SELECT ContactName FROM dbo.Customer` `ORDER BY ContactName`
TOP	`SELECT TOP 10 ContactName FROM dbo.Customer`
Top-sort	L'espressione `TOP` (il numero di righe da restituire) non può superare le 8.000 righe. Meno se nella query sono presenti anche operatori di aggregazione e di join. I join e le aggregazioni in genere riducono il numero di righe da ordinare, rispetto al numero di righe delle tabelle di base. `SELECT TOP 10 ContactName FROM dbo.Customer` `ORDER BY ContactName`
Stream Aggregate	Si noti che l'operatore Hash Match non è supportato per l'aggregazione. Pertanto, tutte le aggregazioni nelle stored procedure compilate in modo nativo usano l'operatore Stream Aggregate, anche se il piano per la stessa query in Transact-SQL interpretato usa l'operatore Hash Match. `SELECT count(CustomerID) FROM dbo.Customer`

Statistiche di colonna e join

SQL Server gestisce le statistiche sui valori nelle colonne chiave di indice per stimare il costo di determinate operazioni, ad esempio l'analisi dell'indice e la ricerca di indici. ( SQL Server crea anche statistiche sulle colonne chiave non di indice se vengono create in modo esplicito o se Query Optimizer le crea in risposta a una query con un predicato. La metrica principale nella stima dei costi è il numero di righe elaborate da un singolo operatore. Si noti che, per le tabelle basate su disco, il numero di pagine a cui accede un operatore specifico è significativo nella stima dei costi. Tuttavia, poiché il conteggio delle pagine non è importante per le tabelle ottimizzate per la memoria (è sempre zero), questa descrizione è incentrata sul conteggio delle righe. La stima inizia con gli operatori Index Seek e Index Scan nel piano e viene quindi estesa agli altri operatori, quale l'operatore di join. Il numero stimato di righe da elaborare da parte di un operatore di join è basato sulla stima per gli operatori Index Seek e Index Scan sottostanti. Per l'accesso Transact-SQL interpretato alle tabelle ottimizzate per la memoria, è possibile osservare il piano di esecuzione effettivo per visualizzare la differenza tra i conteggi delle righe stimati e effettivi per gli operatori nel piano.

Per l'esempio nella figura 1:

L'operatore Clustered Index Scan su Customer presenta un valore stimato di 91 ed effettivo di 91.
L'operatore Nonclustered Index Scan su CustomerID presenta un valore stimato di 830 ed effettivo di 830.
L'operatore Merge Join presenta un valore stimato di 815 ed effettivo di 830.

Le stime per le analisi di indice sono accurate. SQL Server gestisce il numero di righe per le tabelle basate su disco. Le stime per le analisi complete di tabelle e indici sono sempre accurate. Anche la stima per il join è ragionevolmente accurata.

Se le stime cambiano, cambiano anche le considerazioni sui costi per le diverse alternative di piano. Ad esempio, se uno dei lati del join presenta un conteggio stimato di una o solo poche righe, l'uso di join a cicli annidati è meno costoso.

Di seguito è riportato il piano della query:

SELECT o.OrderID, c.* FROM dbo.[Customer] c INNER JOIN dbo.[Order] o ON c.CustomerID = o.CustomerID

Dopo aver eliminato tutte le righe tranne una nella tabella Customer:

Statistiche di colonna e join.

Rispetto a questo piano di query:

L'operatore Hash Match è stato sostituito con un operatore fisico di join a cicli annidati.
L'analisi completa dell'indice su IX_CustomerID è stata sostituita con una ricerca nell'indice. In questo modo sono state analizzate 5 righe, anziché le 830 righe richieste per l'analisi completa dell'indice.

Statistiche e cardinalità per le tabelle con ottimizzazione per la memoria

SQL Server gestisce le statistiche a livello di colonna per le tabelle ottimizzate per la memoria. Inoltre, viene mantenuto il conteggio effettivo delle righe della tabella. Tuttavia, diversamente dalle tabelle basate su disco, le statistiche per le tabelle ottimizzate per la memoria non vengono aggiornate automaticamente. Pertanto, le statistiche devono essere aggiornate manualmente dopo aver effettuato modifiche significative nelle tabelle. Per altre informazioni, vedere Statistiche per tabelle con ottimizzazione per la memoria.

Vedere anche

Tabelle ottimizzate per la memoria

Condividi tramite