Guida all'elaborazione delle query per le tabelle con ottimizzazione per la memoriaA Guide to Query Processing for Memory-Optimized Tables

In questo argomento si applica a: SìSQL ServerSìDatabase SQL di AzurenonAzure SQL Data Warehouse non Parallel Data WarehouseTHIS TOPIC APPLIES TO: yesSQL ServeryesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Con OLTP in memoria sono state introdotte le tabelle ottimizzate per la memoria e le stored procedure compilate in modo nativo in SQL ServerSQL Server.In-Memory OLTP introduces memory-optimized tables and natively compiled stored procedures in SQL ServerSQL 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.This article gives an overview of query processing for both memory-optimized tables and natively compiled stored procedures.

Nel documento viene illustrato come compilare ed eseguire query sulle tabelle ottimizzate per la memoria, tra cui:The document explains how queries on memory-optimized tables are compiled and executed, including:

  • La pipeline di elaborazione delle query in SQL ServerSQL Server per le tabelle basate su disco.The query processing pipeline in SQL ServerSQL Server for disk-based tables.

  • 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.Query optimization; the role of statistics on memory-optimized tables as well as guidelines for troubleshooting bad query plans.

  • L'uso del codice Transact-SQLTransact-SQL interpretato per accedere alle tabelle ottimizzate per la memoria.The use of interpreted Transact-SQLTransact-SQL to access memory-optimized tables.

  • Considerazioni sull'ottimizzazione query per l'accesso alle tabelle ottimizzate per la memoria.Considerations about query optimization for memory-optimized table access.

  • Compilazione ed elaborazione di stored procedure compilate in modo nativo.Natively compiled stored procedure compilation and processing.

  • Statistiche utilizzate per la stima dei costi in Query Optimizer.Statistics that are used for cost estimation by the optimizer.

  • Modi per correggere piani di query errati.Ways to fix bad query plans.

Query di esempioExample Query

L'esempio seguente verrà utilizzato per illustrare i concetti di elaborazione delle query descritti in questo articolo.The following example will be used to illustrate the query processing concepts discussed in this article.

Vengono considerate due tabelle, Customer e Order.We consider two tables, Customer and Order. Il seguente script di Transact-SQLTransact-SQL contiene le definizioni di queste due tabelle e gli indici associati, nel formato basato su disco (tradizionale):The following Transact-SQLTransact-SQL script contains the definitions for these two tables and associated indexes, in their (traditional) disk-based form:

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).For constructing the query plans shown in this article, the two tables were populated with sample data from the Northwind sample database, which you can download from Northwind and pubs Sample Databases for 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:Consider the following query, which joins the tables Customer and Order and returns the ID of the order and the associated customer information:

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

Il piano di esecuzione stimato visualizzato in SQL Server Management StudioSQL Server Management Studio è illustrato di seguito:The estimated execution plan as displayed by SQL Server Management StudioSQL Server Management Studio is as follows

Piano di query per il join di tabelle basate su disco.Query plan for join of disk-based tables.
Piano di query per il join di tabelle basate su disco.Query plan for join of disk-based tables.

Informazioni su questo piano di query:About this query plan:

  • Le righe della tabella Customer vengono recuperate dall'indice cluster, che è la struttura dei dati primaria e contiene i dati completi della tabella.The rows from the Customer table are retrieved from the clustered index, which is the primary data structure and has the full table data.

  • I dati della tabella Order vengono recuperati utilizzando l'indice non cluster della colonna CustomerID.Data from the Order table is retrieved using the non-clustered index on the CustomerID column. L'indice contiene sia la colonna CustomerID, utilizzata per il join, sia la colonna chiave primaria OrderID, che viene restituita all'utente.This index contains both the CustomerID column, which is used for the join, and the primary key column OrderID, which is returned to the user. La restituzione di colonne aggiuntive dalla tabella Order richiederebbe ricerche nell'indice cluster della tabella stessa.Returning additional columns from the Order table would require lookups in the clustered index for the Order table.

  • L'operatore logico Inner Join viene implementato dall'operatore fisico Merge join.The logical operator Inner Join is implemented by the physical operator Merge Join. Gli altri tipi di join fisico sono Cicli annidati e Hash join.The other physical join types are Nested Loops and Hash Join. L'operatore Merge Join consente di sfruttare il fatto che entrambi gli indici sono ordinati in base alla colonna di join CustomerID.The Merge Join operator takes advantage of the fact that both indexes are sorted on the join column 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:Consider a slight variation on this query, which returns all rows from the Order table, not only OrderID:

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:The estimated plan for this query is:

Piano di query per il join di tabelle basate su disco.Query plan for a hash join of disk-based tables.
Piano di query per un hash join di tabelle basate su disco.Query plan for a hash join of disk-based tables.

In questa query le righe della tabella Order vengono recuperate utilizzando l'indice cluster.In this query, rows from the Order table are retrieved using the clustered index. L'operatore fisico Hash Match viene ora usato per l'operatore Inner Join.The Hash Match physical operator is now used for the Inner Join. L'indice cluster di Order non è ordinato in base a CustomerID, quindi per un operatore Merge Join sarebbe necessario un operatore di ordinamento, che influirebbe sulle prestazioni.The clustered index on Order is not sorted on CustomerID, and so a Merge Join would require a sort operator, which would affect performance. Si noti il costo relativo dell'operatore Hash Match (75%) rispetto al costo dell'operatore Merge Join nell'esempio precedente (46%).Note the relative cost of the Hash Match operator (75%) compared with the cost of the Merge Join operator in the previous example (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.The optimizer would have considered the Hash Match operator also in the previous example, but concluded that the Merge Join operator gave better performance.

SQL ServerSQL Server Elaborazione delle query per tabelle basate su disco Query Processing for Disk-Based Tables

Nel diagramma seguente viene illustrato il flusso di elaborazione delle query ad hoc in SQL ServerSQL Server :The following diagram outlines the query processing flow in SQL ServerSQL Server for ad hoc queries:

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

In questo scenario:In this scenario:

  1. L'utente esegue una query.The user issues a query.

  2. Il parser e il normalizzatore creano un albero della query con operatori logici basati sul testo Transact-SQLTransact-SQL inviato dall'utente.The parser and algebrizer construct a query tree with logical operators based on the Transact-SQLTransact-SQL text submitted by the user.

  3. Query Optimizer crea un piano di query ottimizzato che contiene operatori fisici, ad esempio join a cicli annidati.The optimizer creates an optimized query plan containing physical operators (for example, nested-loops join). Dopo l'ottimizzazione, il piano può essere archiviato nella cache dei piani.After optimization, the plan may be stored in the plan cache. Questo passaggio viene ignorato se la cache dei piani già contiene un piano per la query.This step is bypassed if the plan cache already contains a plan for this query.

  4. Un'interpretazione del piano di query viene elaborato dal motore di esecuzione delle query.The query execution engine processes an interpretation of the query plan.

  5. 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.For each index seek, index scan, and table scan operator, the execution engine requests rows from the respective index and table structures from Access Methods.

  6. 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.Access Methods retrieves the rows from the index and data pages in the buffer pool and loads pages from disk into the buffer pool as needed.

    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.For the first example query, the execution engine requests rows in the clustered index on Customer and the non-clustered index on Order from Access Methods. Access Methods attraversa le strutture di indice ad albero B per recuperare le righe richieste.Access Methods traverses the B-tree index structures to retrieve the requested rows. In questo caso vengono recuperate tutte le righe poiché il piano richiede le analisi complete degli indici.In this case all rows are retrieved as the plan calls for full index scans.

Accesso del codice Transact-SQLTransact-SQL interpretato alle tabelle con ottimizzazione per la memoriaInterpreted Transact-SQLTransact-SQL Access to Memory-Optimized Tables

Transact-SQLTransact-SQL ai batch ad hoc e alle stored procedure si fa riferimento anche con l'espressione " Transact-SQLTransact-SQLinterpretato". ad hoc batches and stored procedures are also referred to as interpreted Transact-SQLTransact-SQL. L'interpretazione si riferisce al fatto che ogni operatore nel piano di query viene interpretato dal motore di esecuzione delle query.Interpreted refers to the fact that the query plan is interpreted by the query execution engine for each operator in the query plan. Il motore di esecuzione legge l'operatore e i relativi parametri ed esegue l'operazione.The execution engine reads the operator and its parameters and performs the operation.

Il codice Transact-SQLTransact-SQL interpretato può essere utilizzato per accedere sia a tabelle ottimizzate per la memoria che a tabelle basate su disco.Interpreted Transact-SQLTransact-SQL can be used to access both memory-optimized and disk-based tables. Nella figura seguente viene illustrata l'elaborazione delle query per l'accesso del codice Transact-SQLTransact-SQL interpretato alle tabelle ottimizzate per la memoria:The following figure illustrates query processing for interpreted Transact-SQLTransact-SQL access to memory-optimized tables:

Pipeline di elaborazione delle query per tsql interpretato.Query processing pipeline for interpreted tsql.
Pipeline di elaborazione delle query per l'accesso del codice Transact-SQL interpretato alle tabelle ottimizzate per la memoria.Query processing pipeline for interpreted Transact-SQL access to memory-optimized tables.

Come illustrato nella figura, la pipeline di elaborazione delle query rimane per lo più invariata:As illustrated by the figure, the query processing pipeline remains mostly unchanged:

  • Il parser e il normalizzatore creano l'albero della query.The parser and algebrizer construct the query tree.

  • Query Optimizer crea il piano di esecuzione.The optimizer creates the execution plan.

  • Il piano di esecuzione viene interpretato dal motore di esecuzione delle query.The query execution engine interprets the execution plan.

    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.The main difference with the traditional query processing pipeline (figure 2) is that rows for memory-optimized tables are not retrieved from the buffer pool using Access Methods. Al contrario, le righe vengono recuperate dalle strutture dei dati in memoria tramite il motore di OLTP in memoria.Instead, rows are retrieved from the in-memory data structures through the In-Memory OLTP engine. 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.Differences in data structures cause the optimizer to pick different plans in some cases, as illustrated by the following example.

    Lo script Transact-SQLTransact-SQL riportato di seguito contiene versioni ottimizzate per la memoria delle tabelle Order e Customer, con indici hash:The following Transact-SQLTransact-SQL script contains memory-optimized versions of the Order and Customer tables, using hash indexes:

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:Consider the same query executed on memory-optimized tables:

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

Il piano stimato è il seguente:The estimated plan is as follows:

Piano di query per il join di tabelle ottimizzate per la memoria.Query plan for join of memory optimized tables.
Piano di query per il join di tabelle ottimizzate per la memoria.Query plan for join of memory-optimized tables.

Osservare le seguenti differenze del piano per la stessa query su tabelle basate su disco (figura 1):Observe the following differences with the plan for the same query on disk-based tables (figure 1):

  • Questo piano contiene un'analisi di tabella anziché un'analisi di indice cluster per la tabella Customer:This plan contains a table scan rather than a clustered index scan for the table Customer:

    • La definizione della tabella non include un indice cluster.The definition of the table does not contain a clustered index.

    • Gli indici cluster non sono supportati con le tabelle ottimizzate per la memoria.Clustered indexes are not supported with memory-optimized tables. 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.Instead, every memory-optimized table must have at least one nonclustered index and all indexes on memory-optimized tables can efficiently access all columns in the table without having to store them in the index or refer to a clustered index.

  • Questo piano contiene un Hash Match anziché un Merge Join.This plan contains a Hash Match rather than a Merge Join. Sia gli indici della tabella Order che quelli della tabella Customer sono indici hash, quindi non ordinati.The indexes on both the Order and the Customer table are hash indexes, and are thus not ordered. Un Merge Join richiederebbe un operatore di ordinamento, che ridurrebbe le prestazioni.A Merge Join would require sort operators that would decrease performance.

Stored procedure compilate in modo nativoNatively Compiled Stored Procedures

Le stored procedure compilate in modo nativo sono stored procedure Transact-SQLTransact-SQL compilate nel codice macchina, piuttosto che interpretate dal motore di esecuzione delle query.Natively compiled stored procedures are Transact-SQLTransact-SQL stored procedures compiled to machine code, rather than interpreted by the query execution engine. Lo script seguente crea una stored procedure compilata in modo nativo che esegue la query di esempio (della sezione Query di esempio).The following script creates a natively compiled stored procedure that runs the example query (from the Example Query section).

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 esecuzioneNatively compiled stored procedures are compiled at create time, whereas interpreted stored procedures are compiled at first execution time. (una parte della compilazione, specificamente l'analisi e la normalizzazione, avviene al momento della creazione;(A portion of the compilation, particularly parsing and algebrization, take place at create. tuttavia, per le stored procedure interpretate, l'ottimizzazione dei piani di query ha luogo alla prima esecuzione). La logica di ricompilazione è simile.However, for interpreted stored procedures, optimization of the query plans takes place at first execution.) The recompilation logic is similar. Le stored procedure compilate in modo nativo vengono ricompilate alla prima esecuzione della procedura se il server viene riavviato.Natively compiled stored procedures are recompiled on first execution of the procedure if the server is restarted. Le stored procedure interpretate vengono ricompilate se il piano non si trova più nella cache dei piani.Interpreted stored procedures are recompiled if the plan is no longer in the plan cache. Nella tabella seguente vengono riepilogati i casi di compilazione e di ricompilazione per le stored procedure compilate in modo nativo e interpretate:The following table summarizes compilation and recompilation cases for both natively compiled and interpreted stored procedures:

Compilate in modo nativoNatively compiled Accesso del codiceInterpreted
Compilazione inizialeInitial compilation Al momento della creazione.At create time. Alla prima esecuzione.At first execution.
Ricompilazione automaticaAutomatic recompilation Alla prima esecuzione della procedura dopo il riavvio del database o del server.Upon first execution of the procedure after a database or server restart. Al riavvio del server.On server restart. In alternativa, eliminazione dalla cache dei piani, in genere in base alle modifiche di schema o di statistiche o a utilizzo elevato di memoria.Or, eviction from the plan cache, usually based on schema or stats changes, or memory pressure.
Ricompilazione manualeManual recompilation Usare sp_recompile.Use sp_recompile. Usare sp_recompile.Use sp_recompile. È possibile eliminare manualmente il piano dalla cache, ad esempio tramite DBCC FREEPROCCACHE.You can manually evict the plan from the cache, for example through DBCC FREEPROCCACHE. È inoltre possibile creare la stored procedure specificando WITH RECOMPILE affinché venga ricompilata a ogni esecuzione.You can also create the stored procedure WITH RECOMPILE and the stored procedure will be recompiled at every execution.

Compilazione ed elaborazione delle queryCompilation and Query Processing

Nel diagramma seguente viene illustrato il processo di compilazione per le stored procedure compilate in modo nativo:The following diagram illustrates the compilation process for natively compiled stored procedures:

Compilazione nativa delle stored procedure.Native compilation of stored procedures.
Compilazione nativa delle stored procedure.Native compilation of stored procedures.

Il processo è il seguente:The process is described as,

  1. L'utente esegue un'istruzione CREATE PROCEDURE in SQL ServerSQL Server.The user issues a CREATE PROCEDURE statement to SQL ServerSQL Server.

  2. Il parser e il normalizzatore creano il flusso di elaborazione per la stored procedure, oltre ad alberi per le query Transact-SQLTransact-SQL nella stored procedure.The parser and algebrizer create the processing flow for the procedure, as well as query trees for the Transact-SQLTransact-SQL queries in the stored procedure.

  3. Query Optimizer crea piani di esecuzione ottimizzati per tutte le query nella stored procedure.The optimizer creates optimized query execution plans for all the queries in the stored procedure.

  4. 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.The In-Memory OLTP compiler takes the processing flow with the embedded optimized query plans and generates a DLL that contains the machine code for executing the stored procedure.

  5. La DLL generata viene caricata in memoria.The generated DLL is loaded into memory.

    La chiamata di una stored procedure compilata in modo nativo viene convertita in chiamata a una funzione nella DLL.Invocation of a natively compiled stored procedure translates to calling a function in the DLL.

    Esecuzione di stored procedure compilate in modo nativo.Execution of natively compiled stored procedures.
    Esecuzione di stored procedure compilate in modo nativo.Execution of natively compiled stored procedures.

    La chiamata di una stored procedure compilata in modo nativo viene descritta nel modo riportato di seguito.Invocation of a natively compiled stored procedure is described as follows:

  6. L'utente esegue un'istruzione EXECusp_myproc .The user issues an EXECusp_myproc statement.

  7. Il parser estrae il nome e i parametri della stored procedure.The parser extracts the name and stored procedure parameters.

    Se l'istruzione è stata preparata, ad esempio usando sp_prep_exec, non è necessario che il parser estragga il nome e i parametri della procedura in fase di esecuzione.If the statement was prepared, for example using sp_prep_exec, the parser does not need to extract the procedure name and parameters at execution time.

  8. Il runtime di OLTP in memoria individua il punto di ingresso della DLL per la stored procedure.The In-Memory OLTP runtime locates the DLL entry point for the stored procedure.

  9. Viene eseguito il codice della macchina nella DLL e i risultati vengono restituiti al client.The machine code in the DLL is executed and the results of are returned to the client.

    Sniffing dei parametriParameter sniffing

    Le stored procedure Transact-SQLTransact-SQL interpretate vengono compilate alla prima esecuzione, contrariamente alle stored procedure compilate in modo nativo che vengono compilate al momento della creazione.Interpreted Transact-SQLTransact-SQL stored procedures are compiled at first execution, in contrast to natively compiled stored procedures, which are compiled at create time. 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.When interpreted stored procedures are compiled at invocation, the values of the parameters supplied for this invocation are used by the optimizer when generating the execution plan. Questo utilizzo dei parametri durante la compilazione viene chiamato sniffing dei parametri.This use of parameters during compilation is called parameter sniffing.

    Lo sniffing dei parametri non viene utilizzato per la compilazione delle stored procedure compilate in modo nativo.Parameter sniffing is not used for compiling natively compiled stored procedures. Tutti i parametri della stored procedure vengono considerati con valore UNKNOWN.All parameters to the stored procedure are considered to have UNKNOWN values. Analogamente alle stored procedure interpretate, anche le stored procedure compilate in modo nativo supportano l'hint OPTIMIZE FOR.Like interpreted stored procedures, natively compiled stored procedures also support the OPTIMIZE FOR hint. Per altre informazioni, vedere Hint per la query (Transact-SQL).For more information, see Query Hints (Transact-SQL).

Recupero di un piano di esecuzione di query per le stored procedure compilate in modo nativoRetrieving a Query Execution Plan for Natively Compiled Stored Procedures

Il piano di esecuzione di query per una stored procedure compilata in modo nativo può essere recuperato usando Piano di esecuzione stimato in Management StudioManagement Studioo tramite l'opzione SHOWPLAN_XML in Transact-SQLTransact-SQL.The query execution plan for a natively compiled stored procedure can be retrieved using Estimated Execution Plan in Management StudioManagement Studio, or using the SHOWPLAN_XML option in Transact-SQLTransact-SQL. Esempio:For example:

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.The execution plan generated by the query optimizer consists of a tree with query operators on the nodes and leaves of the tree. La struttura dell'albero determina l'interazione (il flusso di righe da un operatore a un altro) tra operatori.The structure of the tree determines the interaction (the flow of rows from one operator to another) between the operators. Nella visualizzazione grafica di SQL Server Management StudioSQL Server Management Studio, la direzione del flusso è da destra a sinistra.In the graphical view of SQL Server Management StudioSQL Server Management Studio, the flow is from right to left. Ad esempio, il piano di query nella figura 1 contiene due operatori Index Scan che forniscono le righe a un operatore Merge Join.For example, the query plan in figure 1 contains two index scan operators, which supplies rows to a merge join operator. L'operatore Merge Join fornisce le righe a un operatore Select.The merge join operator supplies rows to a select operator. L'operatore Select, infine, restituisce le righe al client.The select operator, finally, returns the rows to the client.

Operatori di query nelle stored procedure compilate in modo nativoQuery Operators in Natively Compiled Stored Procedures

Nella tabella seguente vengono riepilogati gli operatori di query supportati nelle stored procedure compilate in modo nativo:The following table summarizes the query operators supported inside natively compiled stored procedures:

OperatoreOperator Query di esempioSample query NoteNotes
SELECTSELECT SELECT OrderID FROM dbo.[Order]
INSERTINSERT INSERT dbo.Customer VALUES ('abc', 'def')
UPDATEUPDATE UPDATE dbo.Customer SET ContactName='ghi' WHERE CustomerID='abc'
DELETEDELETE DELETE dbo.Customer WHERE CustomerID='abc'
Compute ScalarCompute Scalar SELECT OrderID+1 FROM dbo.[Order] Questo operatore viene utilizzato sia per le funzioni intrinseche che per le conversioni dei tipi.This operator is used both for intrinsic functions and type conversions. Non tutte le funzioni e conversioni dei tipi sono supportate nelle stored procedure compilate in modo nativo.Not all functions and type conversions are supported inside natively compiled stored procedures.
Join a cicli annidatiNested Loops Join SELECT o.OrderID, c.CustomerID FROM dbo.[Order] o INNER JOIN dbo.[Customer] c Nested Loops è l'unico operatore di join supportato nelle stored procedure compilate in modo nativo.Nested Loops is the only join operator supported in natively compiled stored procedures. In tutti i piani che contengono join viene utilizzato l'operatore Nested Loops, anche se il piano per la stessa query eseguita come codice Transact-SQLTransact-SQL interpretato contiene un hash join o un merge join.All plans that contain joins will use the Nested Loops operator, even if the plan for same query executed as interpreted Transact-SQLTransact-SQL contains a hash or merge join.
OrdinaSort SELECT ContactName FROM dbo.Customer ORDER BY ContactName
Torna all'inizioTop SELECT TOP 10 ContactName FROM dbo.Customer
Top-sortTop-sort SELECT TOP 10 ContactName FROM dbo.Customer ORDER BY ContactName L'espressione TOP (il numero di righe da restituire) non può superare le 8.000 righe.The TOP expression (the number of rows to be returned) cannot exceed 8,000 rows. Meno se nella query sono presenti anche operatori di aggregazione e di join.Fewer if there are also join and aggregation operators in the query. I join e le aggregazioni in genere riducono il numero di righe da ordinare, rispetto al numero di righe delle tabelle di base.Joins and aggregation do typically reduce the number of rows to be sorted, compared with the row count of the base tables.
Stream AggregateStream Aggregate SELECT count(CustomerID) FROM dbo.Customer Si noti che l'operatore Hash Match non è supportato per l'aggregazione.Note that the Hash Match operator is not supported for aggregation. Pertanto, in tutte le aggregazioni nelle stored procedure compilate in modo nativo viene utilizzato l'operatore Stream Aggregate, anche se il piano per la stessa query nel codice Transact-SQLTransact-SQL interpretato utilizza l'operatore Hash Match.Therefore, all aggregation in natively compiled stored procedures uses the Stream Aggregate operator, even if the plan for the same query in interpreted Transact-SQLTransact-SQL uses the Hash Match operator.

Statistiche di colonna e joinColumn Statistics and Joins

SQL ServerSQL Server mantiene statistiche sui valori nelle colonne chiave di indice per facilitare la stima del costo di determinate operazioni, quali l'analisi e le ricerche sugli indici. maintains statistics on values in index key columns to help estimate the cost of certain operations, such as index scan and index seeks. Con SQL ServerSQL Server è inoltre possibile creare statistiche sulle colonne chiave non di indice, se create in modo esplicito dall'utente o create da Query Optimizer in risposta a una query con un predicato. La principale metrica nella stima dei costi è il numero di righe elaborate da un singolo operatore.( SQL ServerSQL Server also creates statistics on non-index key columns if you explicitly create them or if the query optimizer creates them in response to a query with a predicate.) The main metric in cost estimation is the number of rows processed by a single operator. Si noti che, per le tabelle basate su disco, il numero di pagine a cui accede un operatore specifico è significativo nella stima dei costi.Note that for disk-based tables, the number of pages accessed by a particular operator is significant in cost estimation. Tuttavia, poiché il conteggio delle pagine non è importante per le tabelle ottimizzate per la memoria (è sempre zero), questa descrizione è incentrata sul conteggio delle righe.However, as page count is not important for memory-optimized tables (it is always zero), this discussion focuses on row count. 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.The estimation starts with the index seek and scan operators in the plan, and is then extended to include the other operators, like the join operator. 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.The estimated number of rows to be processed by a join operator is based on the estimation for the underlying index, seek, and scan operators. Per l'accesso del codice Transact-SQLTransact-SQL interpretato alle tabelle ottimizzate per la memoria, è possibile osservare il piano di esecuzione effettivo per vedere la differenza tra il numero di righe stimato ed effettivo per gli operatori nel piano.For interpreted Transact-SQLTransact-SQL access to memory-optimized tables, you can observe the actual execution plan to see the difference between the estimated and actual row counts for the operators in the plan.

Per l'esempio nella figura 1:For the example in figure 1,

  • L'operatore Clustered Index Scan su Customer presenta un valore stimato di 91 ed effettivo di 91.The clustered index scan on Customer has estimated 91; actual 91.

  • L'operatore Nonclustered Index Scan su CustomerID presenta un valore stimato di 830 ed effettivo di 830.The nonclustered index scan on CustomerID has estimated 830; actual 830.

  • L'operatore Merge Join presenta un valore stimato di 815 ed effettivo di 830.The Merge Join operator has estimated 815; actual 830.

    Le stime per le analisi di indice sono accurate.The estimates for the index scans are accurate. SQL ServerSQL Server mantiene il conteggio delle righe per le tabelle basate su disco. maintains the row count for disk-based tables. Le stime per le analisi complete di tabelle e indici sono sempre accurate.Estimates for full table and index scans are always accurate. Anche la stima per il join è ragionevolmente accurata.The estimate for the join is fairly accurate, too.

    Se le stime cambiano, cambiano anche le considerazioni sui costi per le diverse alternative di piano.If these estimates change, the cost considerations for different plan alternatives change as well. 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.For example, if one of the sides of the join has an estimated row count of 1 or just a few rows, using a nested loops joins is less expensive.

    Di seguito è riportato il piano della query:The following is the plan for the 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:After deleting all rows but one in the table Customer:

Statistiche di colonna e join.Column statistics and joins.

Rispetto a questo piano di query:Regarding this query plan:

  • L'operatore Hash Match è stato sostituito con un operatore fisico di join a cicli annidati.The Hash Match has been replaced with a Nested Loops physical join operator.

  • L'analisi completa dell'indice su IX_CustomerID è stata sostituita con una ricerca nell'indice.The full index scan on IX_CustomerID has been replaced with an index seek. In questo modo sono state analizzate 5 righe, anziché le 830 righe richieste per l'analisi completa dell'indice.This resulted in scanning 5 rows, instead of the 830 rows required for the full index scan.

Vedere ancheSee Also

Tabelle con ottimizzazione per la memoriaMemory-Optimized Tables