Guida sull'architettura di elaborazione delle queryQuery Processing Architecture Guide

QUESTO ARGOMENTO SI APPLICA A: sìSQL Server (a partire dalla versione 2008)sìDatabase SQL di AzuresìAzure SQL Data Warehouse sìParallel Data Warehouse THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

Il Motore di database di SQL ServerSQL Server Database Engine consente di elaborare le query su diverse architetture di archiviazione dei dati, come tabelle locali, tabelle partizionate e tabelle distribuite su più server.The Motore di database di SQL ServerSQL Server Database Engine processes queries on various data storage architectures such as local tables, partitioned tables, and tables distributed across multiple servers. Negli argomenti seguenti viene descritta l'elaborazione delle query e l'ottimizzazione del riutilizzo delle query in SQL ServerSQL Server tramite la memorizzazione nella cache dei piani di esecuzione.The following topics cover how SQL ServerSQL Server processes queries and optimizes query reuse through execution plan caching.

Elaborazione di istruzioni SQLSQL Statement Processing

L'elaborazione di una singola istruzione SQL rappresenta la modalità più semplice di esecuzione delle istruzioni SQL in SQL ServerSQL Server.Processing a single SQL statement is the most basic way that SQL ServerSQL Server executes SQL statements. Per illustrare il processo di base, viene usata la procedura di elaborazione di una singola istruzione SELECT che fa riferimento esclusivamente a tabelle di base locali, non a viste o tabelle remote.The steps used to process a single SELECT statement that references only local base tables (no views or remote tables) illustrates the basic process.

Ottimizzazione delle istruzioni SELECTOptimizing SELECT Statements

Un'istruzione SELECT non definisce esattamente la procedura che il server di database deve eseguire per recuperare i dati richiesti.A SELECT statement is non-procedural; it does not state the exact steps that the database server should use to retrieve the requested data. Il server di database deve pertanto analizzare l'istruzione per determinare il metodo più efficace per l'estrazione dei dati.This means that the database server must analyze the statement to determine the most efficient way to extract the requested data. Tale procedura, denominata ottimizzazione dell'istruzione SELECT ,This is referred to as optimizing the SELECT statement. viene eseguita dal componente Query Optimizer.The component that does this is called the Query Optimizer. I dati di input per Query Optimizer sono costituiti dalla query, dallo schema del database (definizioni di tabella e indice) e dalle statistiche del database.The input to the Query Optimizer consists of the query, the database schema (table and index definitions), and the database statistics. L'output di Query Optimizer è un piano di esecuzione della query, talvolta definito piano di query o semplicemente piano.The output of the Query Optimizer is a query execution plan, sometimes referred to as a query plan or just a plan. La descrizione dettagliata del contenuto di un piano di query è riportata più avanti in questo argomento.The contents of a query plan are described in more detail later in this topic.

I dati di input e di output di Query Optimizer durante l'ottimizzazione di una singola istruzione SELECT sono illustrati nel diagramma seguente: query_processor_ioThe inputs and outputs of the Query Optimizer during optimization of a single SELECT statement are illustrated in the following diagram: query_processor_io

Un'istruzione SELECT definisce soltanto gli elementi seguenti:A SELECT statement defines only the following:

  • Il formato del set di risultati.The format of the result set. Questo elemento viene nella maggior parte dei casi specificato nell'elenco di selezione.This is specified mostly in the select list. Altre clausole, ad esempio ORDER BY e GROUP BY , possono tuttavia influire sul formato finale del set di risultati.However, other clauses such as ORDER BY and GROUP BY also affect the final form of the result set.
  • Le tabelle che contengono i dati di origine.The tables that contain the source data. La modalità è specificata nella clausola FROM .This is specified in the FROM clause.
  • La relazione logica tra le tabelle ai fini dell'istruzione SELECT .How the tables are logically related for the purposes of the SELECT statement. Questo elemento viene definito nelle specifiche di join, incluse nella clausola WHERE oppure in una clausola ON che segue una clausola FROM.This is defined in the join specifications, which may appear in the WHERE clause or in an ON clause following FROM.
  • Condizioni che le righe delle tabelle di origine devono soddisfare per essere incluse nel risultato dell'istruzione SELECT .The conditions that the rows in the source tables must satisfy to qualify for the SELECT statement. Queste condizioni vengono specificate nelle clausole WHERE ed HAVING .These are specified in the WHERE and HAVING clauses.

Il piano di esecuzione di una query è costituito dalla definizione degli elementi seguenti:A query execution plan is a definition of the following:

  • La sequenza di accesso alle tabelle di origine.The sequence in which the source tables are accessed.
    In genere il server di database può utilizzare molte sequenze diverse per accedere alle tabelle di base e quindi compilare il set di risultati.Typically, there are many sequences in which the database server can access the base tables to build the result set. Ad esempio, se l'istruzione SELECT fa riferimento a tre tabelle, il server di database può accedere innanzitutto a TableA, usare i dati di TableA per estrarre le righe corrispondenti da TableB, quindi usare i dati di TableB per estrarre i dati da TableC.For example, if the SELECT statement references three tables, the database server could first access TableA, use the data from TableA to extract matching rows from TableB, and then use the data from TableB to extract data from TableC. Di seguito vengono indicate le altre sequenze di accesso alle tabelle utilizzabili dal server di database:The other sequences in which the database server could access the tables are:
    TableC, TableB, TableAoTableC, TableB, TableA, or
    TableB, TableA, TableCoTableB, TableA, TableC, or
    TableB, TableC, TableAoTableB, TableC, TableA, or
    TableC, TableA, TableBTableC, TableA, TableB

  • I metodi utilizzati per estrarre i dati da ogni tabella.The methods used to extract data from each table.
    Per accedere ai dati di ogni tabella sono in genere disponibili metodi diversi.Generally, there are different methods for accessing the data in each table. Se sono necessarie solo alcune righe con valori di chiave specifici, il server di database può utilizzare un indice.If only a few rows with specific key values are required, the database server can use an index. Se sono necessarie tutte le righe della tabella, il server di database può ignorare gli indici ed eseguire un'analisi di tabella.If all the rows in the table are required, the database server can ignore the indexes and perform a table scan. Se sono necessarie tutte le righe di una tabella, ma l'indice contiene colonne chiave incluse in una clausola ORDER BY, è consigliabile eseguire l'analisi dell'indice anziché della tabella per evitare che il set di risultati venga ordinato separatamente.If all the rows in a table are required but there is an index whose key columns are in an ORDER BY, performing an index scan instead of a table scan may save a separate sort of the result set. Se una tabella è di dimensioni molto ridotte, l'analisi della tabella può rappresentare il metodo più efficiente per quasi tutti gli accessi alla tabella.If a table is very small, table scans may be the most efficient method for almost all access to the table.

Il processo di scelta di un piano di esecuzione è denominato ottimizzazione.The process of selecting one execution plan from potentially many possible plans is referred to as optimization. Query Optimizer è uno dei principali componenti di un sistema di database SQL.The Query Optimizer is one of the most important components of a SQL database system. L'overhead generato dall'utilizzo di Query Optimizer per l'analisi della query e la scelta di un piano è ampiamente compensato dall'efficienza del piano di esecuzione scelto.While some overhead is used by the Query Optimizer to analyze the query and select a plan, this overhead is typically saved several-fold when the Query Optimizer picks an efficient execution plan. Si supponga, ad esempio, che il progetto di costruzione di una casa venga assegnato a due imprese edili diverse.For example, two construction companies can be given identical blueprints for a house. Se un'impresa dedica alcuni giorni alla pianificazione della costruzione della casa e l'altra impresa inizia immediatamente la costruzione senza alcuna pianificazione, è molto probabile che l'impresa che ha pianificato il progetto termini la costruzione per prima.If one company spends a few days at the beginning to plan how they will build the house, and the other company begins building without planning, the company that takes the time to plan their project will probably finish first.

Query Optimizer di SQL ServerSQL Server è un'utilità di ottimizzazione basata sui costi.The SQL ServerSQL Server Query Optimizer is a cost-based Query Optimizer. A ogni piano di esecuzione possibile corrisponde un costo in termini di quantità di risorse del computer utilizzate.Each possible execution plan has an associated cost in terms of the amount of computing resources used. Query Optimizer analizza i piani possibili e sceglie il piano con il costo stimato minore.The Query Optimizer must analyze the possible plans and choose the one with the lowest estimated cost. Per alcune istruzioni SELECT complesse i piani di esecuzione possibili sono migliaia.Some complex SELECT statements have thousands of possible execution plans. In questi casi, Query Optimizer non analizza tutte le combinazioni possibili,In these cases, the Query Optimizer does not analyze all possible combinations. ma utilizza algoritmi complessi per individuare rapidamente un piano di esecuzione il cui costo si avvicini il più possibile al costo minimo teorico.Instead, it uses complex algorithms to find an execution plan that has a cost reasonably close to the minimum possible cost.

Query Optimizer di SQL ServerSQL Server non sceglie esclusivamente il piano di esecuzione con il costo minore in termini di risorse, ma individua il piano che restituisce più rapidamente i risultati all'utente con un costo ragionevole in termini di risorse.The SQL ServerSQL Server Query Optimizer does not choose only the execution plan with the lowest resource cost; it chooses the plan that returns results to the user with a reasonable cost in resources and that returns the results the fastest. Ad esempio, l'esecuzione parallela di una query in genere utilizza una quantità di risorse maggiore rispetto all'esecuzione seriale, ma consente di completare la query più rapidamente.For example, processing a query in parallel typically uses more resources than processing it serially, but completes the query faster. Query Optimizer di SQL ServerSQL Server userà un piano di esecuzione parallela per restituire i risultati, a condizione che tale piano non aumenti il carico sul server.The SQL ServerSQL Server Query Optimizer will use a parallel execution plan to return results if the load on the server will not be adversely affected.

Per la stima dei costi in termini di risorse relativi ai diversi metodi di estrazione delle informazioni da una tabella o da un indice, Query Optimizer di SQL ServerSQL Server si basa sulle le statistiche di distribuzione.The SQL ServerSQL Server Query Optimizer relies on distribution statistics when it estimates the resource costs of different methods for extracting information from a table or index. Le statistiche di distribuzione vengono registrate per le colonne e gli indiciDistribution statistics are kept for columns and indexes. e indicano la selettività dei valori di un indice o di una colonna.They indicate the selectivity of the values in a particular index or column. Ad esempio, in una tabella che rappresenta automobili, molte automobili vengono prodotte dallo stesso costruttore, ma a ciascuna è assegnato un numero di identificazione univoco.For example, in a table representing cars, many cars have the same manufacturer, but each car has a unique vehicle identification number (VIN). L'indice dei numeri di identificazione dei veicoli è quindi più selettivo rispetto all'indice dei produttori.An index on the VIN is more selective than an index on the manufacturer. Se le statistiche dell'indice non sono aggiornate, è possibile che Query Optimizer non scelga la soluzione migliore per lo stato corrente della tabella.If the index statistics are not current, the Query Optimizer may not make the best choice for the current state of the table. Per altre informazioni sull'aggiornamento delle statistiche dell'indice, vedere la sezione relativa all'uso delle statistiche per migliorare le prestazioni delle query.For more information about keeping index statistics current, see Using Statistics to Improve Query Performance.

Query Optimizer di SQL ServerSQL Server è un componente importante perché consente al server di database di adattarsi in modo dinamico alle condizioni variabili del database senza fare ricorso all'intervento di un programmatore o di un amministratore di database.The SQL ServerSQL Server Query Optimizer is important because it enables the database server to adjust dynamically to changing conditions in the database without requiring input from a programmer or database administrator. In questo modo i programmatori possono concentrarsi sulla descrizione del risultato finale della query.This enables programmers to focus on describing the final result of the query. A ogni esecuzione dell'istruzione, Query Optimizer di SQL ServerSQL Server compila un piano di esecuzione efficace per lo stato corrente del database.They can trust that the SQL ServerSQL Server Query Optimizer will build an efficient execution plan for the state of the database every time the statement is run.

Elaborazione di un'istruzione SELECTProcessing a SELECT Statement

Di seguito viene illustrata la procedura di base necessaria per elaborare una singola istruzione SELECT in SQL ServerSQL Server:The basic steps that SQL ServerSQL Server uses to process a single SELECT statement include the following:

  1. Il parser esegue l'analisi dell'istruzione SELECT e la suddivide in unità logiche, quali parole chiave, espressioni, operatori e identificatori.The parser scans the SELECT statement and breaks it into logical units such as keywords, expressions, operators, and identifiers.
  2. Viene compilato un albero della query, talvolta denominata sequenza logica, che descrive i passaggi logici necessari per convertire i dati di origine nel formato necessario per il set di risultati.A query tree, sometimes referred to as a sequence tree, is built describing the logical steps needed to transform the source data into the format required by the result set.
  3. Query Optimizer analizza le diverse modalità di accesso alle tabelle di origineThe Query Optimizer analyzes different ways the source tables can be accessed. e seleziona le serie di passaggi che restituiscono i risultati con maggior rapidità e con minor impiego di risorse.It then selects the series of steps that returns the results fastest while using fewer resources. L'albero della query viene aggiornato in modo da registrare la serie esatta di passaggi.The query tree is updated to record this exact series of steps. La versione finale ottimizzata dell'albero della query è denominato piano di esecuzione.The final, optimized version of the query tree is called the execution plan.
  4. Il motore relazionale avvia l'esecuzione del piano di esecuzione.The relational engine starts executing the execution plan. Man mano che vengono elaborati i passaggi che richiedono i dati delle tabelle di base, il motore relazionale richiede al motore di archiviazione di passare i dati dei set di righe richiesti dal motore relazionale stesso.As the steps that require data from the base tables are processed, the relational engine requests that the storage engine pass up data from the rowsets requested from the relational engine.
  5. Il motore relazionale elabora i dati restituiti dal motore di archiviazione nel formato definito per il set di risultati e restituisce il set di risultati al client.The relational engine processes the data returned from the storage engine into the format defined for the result set and returns the result set to the client.

Elaborazione di altre istruzioniProcessing Other Statements

La procedura di base descritta per l'elaborazione di un'istruzione SELECT è valida anche per altre istruzioni SQL, ad esempio INSERT, UPDATEe DELETE.The basic steps described for processing a SELECT statement apply to other SQL statements such as INSERT, UPDATE, and DELETE. Entrambe le istruzioniUPDATE e DELETE devono definire il set di righe da modificare o eliminare.UPDATE and DELETE statements both have to target the set of rows to be modified or deleted. usando un processo di identificazione delle righe corrispondente a quello che consente di identificare le righe di origine che formano il set di risultati di un'istruzione SELECT .The process of identifying these rows is the same process used to identify the source rows that contribute to the result set of a SELECT statement. Le istruzioni UPDATE e INSERT possono includere istruzioni SELECT incorporate che forniscono i valori dei dati da aggiornare o da inserire.The UPDATE and INSERT statements may both contain embedded `SELECT statements that provide the data values to be updated or inserted.

Anche le istruzioni DDL (Data Definition Language), quali CREATE PROCEDURE o ALTER TABLE, vengono risolte in una serie di operazioni relazionali eseguite sulle tabelle del catalogo di sistema e in alcuni casi, ad esempio con ALTER TABLE ADD COLUMN, sulle tabelle di dati.Even Data Definition Language (DDL) statements, such as CREATE PROCEDURE or ALTER TABLE, are ultimately resolved to a series of relational operations on the system catalog tables and sometimes (such as ALTER TABLE ADD COLUMN) against the data tables.

Tabelle di lavoroWorktables

È possibile che il motore relazionale debba compilare una tabella di lavoro per eseguire un'operazione logica specificata in un'istruzione SQL.The relational engine may need to build a worktable to perform a logical operation specified in an SQL statement. Le tabelle di lavoro sono tabelle interne utilizzate per inserirvi i risultati intermedi.Worktables are internal tables that are used to hold intermediate results. Le tabelle di lavoro vengono generate per alcune query GROUP BY, ORDER BYo UNION .Worktables are generated for certain GROUP BY, ORDER BY, or UNION queries. Se, ad esempio, una clausola ORDER BY fa riferimento a colonne non coperte da indici, può essere necessario generare una tabella di lavoro per disporre il set di risultati nell'ordine richiesto.For example, if an ORDER BY clause references columns that are not covered by any indexes, the relational engine may need to generate a worktable to sort the result set into the order requested. Le tabelle di lavoro vengono a volte utilizzate anche come spool per conservare temporaneamente il risultato dell'esecuzione di un piano della query.Worktables are also sometimes used as spools that temporarily hold the result of executing a part of a query plan. Le tabelle di lavoro vengono compilate in tempdb e vengono eliminate automaticamente quando non sono più necessarie.Worktables are built in tempdb and are dropped automatically when they are no longer needed.

Risoluzione delle visteView Resolution

In Query Processor di SQL ServerSQL Server le viste indicizzate e non indicizzate vengono gestite in modi diversi:The SQL ServerSQL Server query processor treats indexed and nonindexed views differently:

  • Le righe di una vista indicizzata vengono archiviate nel database con lo stesso formato di una tabella.The rows of an indexed view are stored in the database in the same format as a table. Se Query Optimizer decide di utilizzare una vista indicizzata in un piano di query, la vista indicizzata verrà gestita come una tabella di base.If the Query Optimizer decides to use an indexed view in a query plan, the indexed view is treated the same way as a base table.
  • Viene archiviata solo la definizione di una vista indicizzata e non le righe della vista.Only the definition of a nonindexed view is stored, not the rows of the view. Query Optimizer incorpora la logica della definizione della vista nel piano di esecuzione compilato per l'istruzione SQL che fa riferimento alla vista non indicizzata.The Query Optimizer incorporates the logic from the view definition into the execution plan it builds for the SQL statement that references the nonindexed view.

La logica usata da Query Optimizer di SQL ServerSQL Server per decidere quando usare una vista indicizzata è analoga alla logica usare per decidere quando usare un indice per una tabella.The logic used by the SQL ServerSQL Server Query Optimizer to decide when to use an indexed view is similar to the logic used to decide when to use an index on a table. Se i dati della vista indicizzata coprono tutta o parte dell'istruzione SQL e Query Optimizer identifica un indice della vista come percorso di accesso più economico, tale indice verrà scelto indipendentemente dal fatto che nella query venga fatto o meno riferimento alla vista in questione.If the data in the indexed view covers all or part of the SQL statement, and the Query Optimizer determines that an index on the view is the low-cost access path, the Query Optimizer will choose the index regardless of whether the view is referenced by name in the query.

Se un'istruzione SQL fa riferimento a una vista non indicizzata, il parser e Query Optimizer analizzano le origini dell'istruzione SQL e della vista e le risolvono in un singolo piano di esecuzione.When an SQL statement references a nonindexed view, the parser and Query Optimizer analyze the source of both the SQL statement and the view and then resolve them into a single execution plan. Il piano di esecuzione è lo stesso per l'istruzione SQL e per la vista.There is not one plan for the SQL statement and a separate plan for the view.

Si consideri, ad esempio, la vista seguente:For example, consider the following view:

USE AdventureWorks2014;
GO
CREATE VIEW EmployeeName AS
SELECT h.BusinessEntityID, p.LastName, p.FirstName
FROM HumanResources.Employee AS h 
JOIN Person.Person AS p
ON h.BusinessEntityID = p.BusinessEntityID;
GO

In base a questa vista le due istruzioni SQL seguenti eseguono le stesse operazioni sulle tabelle di base e producono gli stessi risultati:Based on this view, both of these SQL statements perform the same operations on the base tables and produce the same results:

/* SELECT referencing the EmployeeName view. */
SELECT LastName AS EmployeeLastName, SalesOrderID, OrderDate
FROM AdventureWorks2014.Sales.SalesOrderHeader AS soh
JOIN AdventureWorks2014.dbo.EmployeeName AS EmpN
ON (soh.SalesPersonID = EmpN.BusinessEntityID)
WHERE OrderDate > '20020531';

/* SELECT referencing the Person and Employee tables directly. */
SELECT LastName AS EmployeeLastName, SalesOrderID, OrderDate
FROM AdventureWorks2014.HumanResources.Employee AS e 
JOIN AdventureWorks2014.Sales.SalesOrderHeader AS soh
ON soh.SalesPersonID = e.BusinessEntityID
JOIN AdventureWorks2014.Person.Person AS p
ON e.BusinessEntityID =p.BusinessEntityID
WHERE OrderDate > '20020531';

La funzionalità Showplan di SQL ServerSQL Server Management Studio indica che il motore relazionale compila lo stesso piano di esecuzione per entrambe le istruzioni SELECT.The SQL ServerSQL Server Management Studio Showplan feature shows that the relational engine builds the same execution plan for both of these SELECT statements.

Utilizzo di hint con le visteUsing Hints with Views

Gli hint inseriti nelle viste di una query possono entrare in conflitto con altri hint individuati quando la vista viene espansa in modo da accedere alle relative tabelle di base.Hints that are placed on views in a query may conflict with other hints that are discovered when the view is expanded to access its base tables. In questo caso, la query restituisce un errore.When this occurs, the query returns an error. Si consideri, ad esempio, la vista seguente nella cui definizione è contenuto un hint di tabella:For example, consider the following view that contains a table hint in its definition:

USE AdventureWorks2014;
GO
CREATE VIEW Person.AddrState WITH SCHEMABINDING AS
SELECT a.AddressID, a.AddressLine1, 
    s.StateProvinceCode, s.CountryRegionCode
FROM Person.Address a WITH (NOLOCK), Person.StateProvince s
WHERE a.StateProvinceID = s.StateProvinceID;

Si supponga a questo punto di immettere la query seguente:Now suppose you enter this query:

SELECT AddressID, AddressLine1, StateProvinceCode, CountryRegionCode
FROM Person.AddrState WITH (SERIALIZABLE)
WHERE StateProvinceCode = 'WA';

La query ha esito negativo perché l'hint SERIALIZABLE applicato nella vista Person.AddrState della query viene propagato a entrambe le tabelle Person.Address e Person.StateProvince quando la vista viene espansa.The query fails, because the hint SERIALIZABLE that is applied on view Person.AddrState in the query is propagated to both tables Person.Address and Person.StateProvince in the view when it is expanded. L'espansione della vista consente anche di rilevare l'hint NOLOCK nella tabella Person.Address.However, expanding the view also reveals the NOLOCK hint on Person.Address. Gli hint SERIALIZABLE e NOLOCK sono in conflitto tra loro, pertanto la query risultante non è corretta.Because the SERIALIZABLE and NOLOCK hints conflict, the resulting query is incorrect.

Gli hint delle tabelle PAGLOCK, NOLOCK, ROWLOCK, TABLOCKo TABLOCKX sono in conflitto tra loro, così come gli hint delle tabelle HOLDLOCK, NOLOCK, READCOMMITTED, REPEATABLEREADe SERIALIZABLE .The PAGLOCK, NOLOCK, ROWLOCK, TABLOCK, or TABLOCKX table hints conflict with each other, as do the HOLDLOCK, NOLOCK, READCOMMITTED, REPEATABLEREAD, SERIALIZABLE table hints.

Gli hint possono propagarsi in più livelli di viste nidificate.Hints can propagate through levels of nested views. Si supponga, ad esempio, una query che applica l'hint HOLDLOCK in una vista v1.For example, suppose a query applies the HOLDLOCK hint on a view v1. Espandendo v1 , si noterà che la definizione di tale vista include la vista v2 ,When v1 is expanded, we find that view v2 is part of its definition. v2la cui definizione include a NOLOCK sua volta un hint in una delle tabelle di base.v2's definition includes a NOLOCK hint on one of its base tables. Questa tabella eredita anche l'hint HOLDLOCK dalla query sulla vista v1.But this table also inherits the HOLDLOCK hint from the query on view v1. Gli hint NOLOCK e HOLDLOCK sono in conflitto tra loro, pertanto la query ha esito negativo.Because the NOLOCK and HOLDLOCK hints conflict, the query fails.

Quando si usa l'hint FORCE ORDER in una query che contiene una vista, l'ordine di join delle tabelle all'interno della vista dipende dalla posizione della vista nel costrutto ordinato.When the FORCE ORDER hint is used in a query that contains a view, the join order of the tables within the view is determined by the position of the view in the ordered construct. La query seguente, ad esempio, consente di selezionare da tre tabelle e una vista:For example, the following query selects from three tables and a view:

SELECT * FROM Table1, Table2, View1, Table3
WHERE Table1.Col1 = Table2.Col1 
    AND Table2.Col1 = View1.Col1
    AND View1.Col2 = Table3.Col2;
OPTION (FORCE ORDER);

La vista View1 viene definita come illustrato di seguito:And View1 is defined as shown in the following:

CREATE VIEW View1 AS
SELECT Colx, Coly FROM TableA, TableB
WHERE TableA.ColZ = TableB.Colz;

L'ordine di join nel piano di query sarà quindi Table1, Table2, TableA, TableB, Table3.The join order in the query plan is Table1, Table2, TableA, TableB, Table3.

Risoluzione di indici nelle visteResolving Indexes on Views

Come per qualsiasi indice, SQL ServerSQL Server sceglie di usare una vista indicizzata nel piano della query solo se Query Optimizer determina che tale operazione è vantaggiosa.As with any index, SQL ServerSQL Server chooses to use an indexed view in its query plan only if the Query Optimizer determines it is beneficial to do so.

Le viste indicizzate possono essere create con qualsiasi versione di SQL ServerSQL Server.Indexed views can be created in any edition of SQL ServerSQL Server. In alcune edizioni di alcune versioni di SQL ServerSQL Server, Query Optimizer considera automaticamente la vista indicizzata.In some editions of some versions of SQL ServerSQL Server, the Query Optimizer automatically considers the indexed view. In alcune edizioni di alcune versioni di SQL ServerSQL Server, per usare una vista indicizzata è necessario usare l'hint della tabella NOEXPAND.In some editions of some versions of SQL ServerSQL Server, to use an indexed view, the NOEXPAND table hint must be used. Per i dettagli, vedere la documentazione relativa alla versione specifica.For clarification, see the documentation for each version.

In Query Optimizer di SQL ServerSQL Server viene usata una vista indicizzata quando vengono soddisfatte le condizioni seguenti:The SQL ServerSQL Server Query Optimizer uses an indexed view when the following conditions are met:

  • Le opzioni relative alla sessione indicate di seguito sono impostate su ON:These session options are set to ON:
    • ANSI_NULLS
    • ANSI_PADDING
    • ANSI_WARNINGS
    • ARITHABORT
    • CONCAT_NULL_YIELDS_NULL
    • QUOTED_IDENTIFIER
    • L'opzione di sessione NUMERIC_ROUNDABORT è impostata su OFF.The NUMERIC_ROUNDABORT session option is set to OFF.
  • In Query Optimizer viene trovata una corrispondenza tra le colonne dell'indice della vista e gli elementi della query, tra cui:The Query Optimizer finds a match between the view index columns and elements in the query, such as the following:
    • Predicati relativi a condizioni di ricerca nella clausola WHERESearch condition predicates in the WHERE clause
    • Operazioni di joinJoin operations
    • Funzioni di aggregazioneAggregate functions
    • ClausoleGROUP BY GROUP BY clauses
    • Riferimenti alla tabellaTable references
  • Il costo stimato necessario per l'utilizzo dell'indice è inferiore a quello di qualsiasi altro meccanismo di accesso considerato in Query Optimizer.The estimated cost for using the index has the lowest cost of any access mechanisms considered by the Query Optimizer.
  • Ogni tabella a cui si fa riferimento nella query, direttamente oppure espandendo una vista per accedere alle tabelle sottostanti, corrispondente a un riferimento alla tabella nella vista indicizzata, deve disporre dello stesso set di hint ad essa applicato nella query.Every table referenced in the query (either directly, or by expanding a view to access its underlying tables) that corresponds to a table reference in the indexed view must have the same set of hints applied on it in the query.
Nota

Gli hint READCOMMITTED e READCOMMITTEDLOCK sono sempre considerati diversi in questo contesto, indipendentemente dal livello corrente di isolamento delle transazioni.The READCOMMITTED and READCOMMITTEDLOCK hints are always considered different hints in this context, regardless of the current transaction isolation level.

Ad eccezione dei requisiti relativi alle opzioni SET e agli hint di tabella, si tratta delle stesse regole usate da Query Optimizer per determinare se l'indice di una tabella copre una query.Other than the requirements for the SET options and table hints, these are the same rules that the Query Optimizer uses to determine whether a table index covers a query. Per utilizzare una vista indicizzata, non è necessario specificare nient'altro nella query.Nothing else has to be specified in the query for an indexed view to be used.

Non è necessario che una query faccia riferimento in modo esplicito a una vista indicizzata nella clausola FROM affinché Query Optimizer usi la vista indicizzata.A query does not have to explicitly reference an indexed view in the FROM clause for the Query Optimizer to use the indexed view. Se la query include riferimenti alle colonne delle tabelle di base incluse anche nella vista indicizzata e tramite Query Optimizer viene determinato che l'utilizzo della vista indicizzata è il meccanismo di accesso più economico, in Query Optimizer viene scelta la vista indicizzata con modalità analoghe a quelle utilizzate per scegliere gli indici delle tabelle di base a cui non viene fatto riferimento diretto in una query.If the query contains references to columns in the base tables that are also present in the indexed view, and the Query Optimizer estimates that using the indexed view provides the lowest cost access mechanism, the Query Optimizer chooses the indexed view, similar to the way it chooses base table indexes when they are not directly referenced in a query. Query Optimizer potrebbe scegliere la vista anche se include colonne a cui non fa riferimento la query, a condizione che la vista stessa rappresenti la soluzione più economica ai fini della copertura di una o più colonne specificate nella query.The Query Optimizer may choose the view when it contains columns that are not referenced by the query, as long as the view offers the lowest cost option for covering one or more of the columns specified in the query.

Query Optimizer elabora le viste indicizzate a cui fa riferimento la clausola FROM come viste standard.The Query Optimizer treats an indexed view referenced in the FROM clause as a standard view. Tramite Query Optimizer la definizione della vista viene espansa nella query all'inizio del processo di ottimizzazione.The Query Optimizer expands the definition of the view into the query at the start of the optimization process. Viene quindi eseguita la ricerca della corrispondenza nella vista indicizzata.Then, indexed view matching is performed. La vista indicizzata potrebbe essere usata nel piano di esecuzione finale selezionato da Query Optimizer oppure il piano può ottenere dalla vista i dati necessari accedendo alle tabelle di base a cui fa riferimento la vista.The indexed view may be used in the final execution plan selected by the Query Optimizer, or instead, the plan may materialize necessary data from the view by accessing the base tables referenced by the view. Tramite Query Optimizer viene scelta l'alternativa con il costo inferiore.The Query Optimizer chooses the lowest-cost alternative.

Utilizzo di hint con viste indicizzateUsing Hints with Indexed Views

È possibile impedire l'uso delle viste indicizzate da parte di una query usando l'hint per la query EXPAND VIEWS oppure è possibile usare l'hint di tabella NOEXPAND per fare in modo che venga impiegato un indice per una vista indicizzata specificata nella clausola FROM di una query.You can prevent view indexes from being used for a query by using the EXPAND VIEWS query hint, or you can use the NOEXPAND table hint to force the use of an index for an indexed view specified in the FROM clause of a query. È tuttavia consigliabile lasciar determinare in modo dinamico a Query Optimizer i metodi di accesso migliori da utilizzare per ogni query.However, you should let the Query Optimizer dynamically determine the best access methods to use for each query. Limitare l'uso degli hint EXPAND e NOEXPAND a casi specifici per i quali si è verificato che in tal modo è possibile ottenere un miglioramento significativo delle prestazioni.Limit your use of EXPAND and NOEXPAND to specific cases where testing has shown that they improve performance significantly.

L'opzione EXPAND VIEWS specifica che in Query Optimizer non verranno usati indici delle viste per l'intera query.The EXPAND VIEWS option specifies that the Query Optimizer not use any view indexes for the whole query.

Se per una vista viene specificata l'opzione NOEXPAND , tramite Query Optimizer viene valuta l'opportunità di usare gli indici definiti per la vista.When NOEXPAND is specified for a view, the Query Optimizer considers using any indexes defined on the view. Se l'opzioneNOEXPAND viene specificata con la clausola INDEX() facoltativa, Query Optimizer userà gli indici specificati.NOEXPAND specified with the optional INDEX() clause forces the Query Optimizer to use the specified indexes. L'opzioneNOEXPAND può essere specificata solo per le viste indicizzate e non è supportata per quelle non indicizzate.NOEXPAND can be specified only for an indexed view and cannot be specified for a view not indexed.

Quando non viene specificata né l'opzione NOEXPANDEXPAND VIEWS in una query contenente una vista, la vista viene espansa per accedere alle tabelle sottostanti.When neither NOEXPAND nor EXPAND VIEWS is specified in a query that contains a view, the view is expanded to access underlying tables. Se la query che compone la vista contiene hint di tabella, tali hint vengono propagati alle tabelle sottostanti.If the query that makes up the view contains any table hints, these hints are propagated to the underlying tables. Per altre informazioni su questo processo, vedere Risoluzione delle viste. Se i set di hint presenti nelle tabelle sottostanti della vista sono identici tra loro, la query può essere utilizzata per la ricerca della corrispondenza con una vista indicizzata.(This process is explained in more detail in View Resolution.) As long as the set of hints that exists on the underlying tables of the view are identical to each other, the query is eligible to be matched with an indexed view. La maggior parte delle volte questi hint corrispondono tra loro in quanto vengono ereditati direttamente dalla vista.Most of the time, these hints will match each other, because they are being inherited directly from the view. Se. tuttavia, la query fa riferimento a tabelle anziché a viste e gli hint applicati direttamente a tali tabelle non sono identici, la query non può essere utilizzata per la ricerca della corrispondenza con una vista indicizzata.However, if the query references tables instead of views, and the hints applied directly on these tables are not identical, then such a query is not eligible for matching with an indexed view. Se gli hint INDEX, PAGLOCK, ROWLOCK, TABLOCKX, UPDLOCKo XLOCK vengono applicati a tabelle a cui fa riferimento la query dopo l'espansione della vista, la query non può essere usata per la ricerca della corrispondenza con una vista indicizzata.If the INDEX, PAGLOCK, ROWLOCK, TABLOCKX, UPDLOCK, or XLOCK hints apply to the tables referenced in the query after view expansion, the query is not eligible for indexed view matching.

Se un hint di tabella nel formato INDEX (index_val[ ,...n] ) fa riferimento a una vista in una query e non viene specificato anche l'hint NOEXPAND , l'hint per l'indice viene ignorato.If a table hint in the form of INDEX (index_val[ ,...n] ) references a view in a query and you do not also specify the NOEXPAND hint, the index hint is ignored. Per specificare l'uso di un determinato indice, usare NOEXPAND.To specify use of a particular index, use NOEXPAND.

In genere, quando Query Optimizer trova una corrispondenza tra una vista indicizzata e una query, eventuali hint specificati nelle tabelle o nelle viste nella query vengono applicati direttamente alla vista indicizzata.Generally, when the Query Optimizer matches an indexed view to a query, any hints specified on the tables or views in the query are applied directly to the indexed view. Se tramite Query Optimizer viene scelto di non utilizzare una vista indicizzata, eventuali hint vengono propagati direttamente alle tabelle a cui viene fatto riferimento nella vista.If the Query Optimizer chooses not to use an indexed view, any hints are propagated directly to the tables referenced in the view. Per altre informazioni, vedere Risoluzione delle viste.For more information, see View Resolution. Questa propagazione non riguarda gli hint di join,This propagation does not apply to join hints. che vengono applicati solo nella relativa posizione originale nella query.They are applied only in their original position in the query. Gli hint di join non vengono presi in considerazione da Query Optimizer durante la ricerca della corrispondenza tra query e viste indicizzate.Join hints are not considered by the Query Optimizer when matching queries to indexed views. Se in un piano della query viene utilizzata una vista indicizzata che corrisponde a una parte di una query contenente un hint di join, tale hint non viene utilizzato nel piano.If a query plan uses an indexed view that matches part of a query that contains a join hint, the join hint is not used in the plan.

Nelle definizioni delle viste indicizzate non sono consentiti hint.Hints are not allowed in the definitions of indexed views. Nella modalità di compatibilità 80 e superiore, in SQL ServerSQL Server vengono ignorati gli hint contenuti nelle definizioni delle viste indicizzate quando ne viene eseguita la manutenzione oppure quando vengono eseguite query in cui sono utilizzate viste indicizzate.In compatibility mode 80 and higher, SQL ServerSQL Server ignores hints inside indexed view definitions when maintaining them, or when executing queries that use indexed views. Sebbene l'utilizzo di hint nelle definizioni delle viste indicizzate non comporti la generazione di un errore di sintassi nella modalità di compatibilità 80, gli hint vengono ignorati.Although using hints in indexed view definitions will not produce a syntax error in 80 compatibility mode, they are ignored.

Risoluzione di viste partizionate distribuiteResolving Distributed Partitioned Views

Query Processor di SQL ServerSQL Server ottimizza le prestazioni delle viste partizionate distribuite.The SQL ServerSQL Server query processor optimizes the performance of distributed partitioned views. Per le prestazioni delle viste partizionate distribuite, l'aspetto più importante è rappresentato dalla necessità di ridurre al minimo la quantità di dati trasferiti tra server membri.The most important aspect of distributed partitioned view performance is minimizing the amount of data transferred between member servers.

SQL ServerSQL Server compila piani dinamici e intelligenti che consentono di usare le query distribuite in modo efficiente ai fini dell'accesso ai dati da tabelle membro remote: builds intelligent, dynamic plans that make efficient use of distributed queries to access data from remote member tables:

  • Query Processor usa prima di tutto OLE DB per recuperare le definizioni del vincolo CHECK da ogni tabella membro.The Query Processor first uses OLE DB to retrieve the check constraint definitions from each member table. In questo modo, può eseguire il mapping della distribuzione dei valori di chiave in tutte le tabelle membro.This allows the query processor to map the distribution of key values across the member tables.
  • The Query Processor compares the key ranges specified in an SQL statement WHERE di un'istruzione SQL con la mappa in cui viene indicata la distribuzione delle righe nelle tabelle membro.The Query Processor compares the key ranges specified in an SQL statement WHERE clause to the map that shows how the rows are distributed in the member tables. Compila quindi un piano di esecuzione delle query che utilizza le query distribuite per recuperare solo le righe remote necessarie per completare l'istruzione SQL.The query processor then builds a query execution plan that uses distributed queries to retrieve only those remote rows that are required to complete the SQL statement. Il piano di esecuzione viene compilato in modo che tutti gli accessi alle tabelle membro remote vengano rimandati al momento in cui vengono richieste le informazioni, indipendentemente dal fatto che si tratti di dati o metadati.The execution plan is also built in such a way that any access to remote member tables, for either data or metadata, are delayed until the information is required.

Si consideri, ad esempio, un sistema in cui la tabella Customers è partizionata tra Server1 (CustomerID da 1 a 3299999), Server2 (CustomerID da 3300000 a 6599999) e Server3 (CustomerID da 6600000 a 9999999).For example, consider a system where a customers table is partitioned across Server1 (CustomerID from 1 through 3299999), Server2 (CustomerID from 3300000 through 6599999), and Server3 (CustomerID from 6600000 through 9999999).

Si consideri quindi il piano di esecuzione compilato per la query eseguita in Server1:Consider the execution plan built for this query executed on Server1:

SELECT *
FROM CompanyData.dbo.Customers
WHERE CustomerID BETWEEN 3200000 AND 3400000;

Il piano di esecuzione di questa query estrae dalla tabella membro locale le righe con valori di chiave CustomerID compresi tra 3200000 e 3299999 e quindi esegue una query distribuita per recuperare da Server2 le righe con valori di chiave compresi tra 3300000 e 3400000.The execution plan for this query extracts the rows with CustomerID key values from 3200000 through 3299999 from the local member table, and issues a distributed query to retrieve the rows with key values from 3300000 through 3400000 from Server2.

Query Processor di SQL ServerSQL Server può inoltre compilare una logica dinamica nei piani di esecuzione delle query per istruzioni SQL in cui i valori di chiave non sono noti al momento della compilazione del piano.The SQL ServerSQL Server Query Processor can also build dynamic logic into query execution plans for SQL statements in which the key values are not known when the plan must be built. Si consideri, ad esempio, la stored procedure seguente:For example, consider this stored procedure:

CREATE PROCEDURE GetCustomer @CustomerIDParameter INT
AS
SELECT *
FROM CompanyData.dbo.Customers
WHERE CustomerID = @CustomerIDParameter;

SQL ServerSQL Server non è in grado di determinare in anticipo quale valore della chiave verrà restituito dal parametro @CustomerIDParameter a ogni esecuzione della procedura. cannot predict what key value will be supplied by the @CustomerIDParameter parameter every time the procedure is executed. Pertanto, nemmeno Query Processor può prevedere a quale tabella membro si accederà.Because the key value cannot be predicted, the query processor also cannot predict which member table will have to be accessed. Per gestire questa situazione, SQL ServerSQL Server compila un piano di esecuzione che include una logica condizionale (ovvero filtri dinamici) in grado di determinare in base al valore del parametro di input a quale tabella membro si accederà.To handle this case, SQL ServerSQL Server builds an execution plan that has conditional logic, referred to as dynamic filters, to control which member table is accessed, based on the input parameter value. Se la stored procedure GetCustomer viene eseguita in Server1, la logica del piano di esecuzione può essere rappresentata nel modo seguente:Assuming the GetCustomer stored procedure was executed on Server1, the execution plan logic can be represented as shown in the following:

IF @CustomerIDParameter BETWEEN 1 and 3299999
   Retrieve row from local table CustomerData.dbo.Customer_33
ELSE IF @CustomerIDParameter BETWEEN 3300000 and 6599999
   Retrieve row from linked table Server2.CustomerData.dbo.Customer_66
ELSE IF @CustomerIDParameter BETWEEN 6600000 and 9999999
   Retrieve row from linked table Server3.CustomerData.dbo.Customer_99

SQL ServerSQL Server compila a volte questi tipi di piani di esecuzione dinamici anche per query senza parametri. sometimes builds these types of dynamic execution plans even for queries that are not parameterized. Query Optimizer può parametrizzare una query in modo che il piano di esecuzione possa essere riutilizzato.The Query Optimizer may parameterize a query so that the execution plan can be reused. Se Query Optimizer esegue la parametrizzazione di una query che fa riferimento a una vista partizionata, non potrà più basarsi sul presupposto che le righe necessarie verranno recuperate da una tabella di base specificataIf the Query Optimizer parameterizes a query referencing a partitioned view, the Query Optimizer can no longer assume the required rows will come from a specified base table. e dovrà utilizzare filtri dinamici nel piano di esecuzione.It will then have to use dynamic filters in the execution plan.

Esecuzione di stored procedure e triggerStored Procedure and Trigger Execution

In SQL ServerSQL Server viene archiviata solo l'origine di stored procedure e trigger. SQL ServerSQL Server stores only the source for stored procedures and triggers. Se una stored procedure o un trigger viene eseguito per la prima volta, l'origine viene compilata in un piano di esecuzione.When a stored procedure or trigger is first executed, the source is compiled into an execution plan. Se la stored procedure o il trigger viene eseguito nuovamente prima che il piano di esecuzione venga rimosso dalla memoria, il motore relazionale rileva e riutilizza il piano esistente.If the stored procedure or trigger is again executed before the execution plan is aged from memory, the relational engine detects the existing plan and reuses it. Se il piano è stato rimosso dalla memoria, viene creato un nuovo piano.If the plan has aged out of memory, a new plan is built. Questo processo è simile al processo seguito da SQL ServerSQL Server per tutte le istruzioni SQL.This process is similar to the process SQL ServerSQL Server follows for all SQL statements. Il vantaggio principale delle prestazioni di stored procedure e trigger in SQL ServerSQL Server rispetto ai batch di istruzioni SQL dinamiche è che le istruzioni SQL sono sempre uguali.The main performance advantage that stored procedures and triggers have in SQL ServerSQL Server compared with batches of dynamic SQL is that their SQL statements are always the same. pertanto, il motore relazionale mette agevolmente in corrispondenza con tutti i piani di esecuzione esistenti.Therefore, the relational engine easily matches them with any existing execution plans. I piani di stored procedure e trigger sono quindi facilmente riutilizzabili.Stored procedure and trigger plans are easily reused.

Il piano di esecuzione delle stored procedure e dei trigger viene eseguito indipendentemente dal piano di esecuzione del batch che chiama la stored procedure o che attiva il trigger.The execution plan for stored procedures and triggers is executed separately from the execution plan for the batch calling the stored procedure or firing the trigger. In tal modo viene garantito un maggiore riutilizzo dei piani di esecuzione delle stored procedure e dei trigger.This allows for greater reuse of the stored procedure and trigger execution plans.

Memorizzazione nella cache e riutilizzo del piano di esecuzioneExecution Plan Caching and Reuse

In SQL ServerSQL Server è presente un pool di memoria utilizzato per archiviare sia i piani di esecuzione che i buffer dei dati. SQL ServerSQL Server has a pool of memory that is used to store both execution plans and data buffers. La percentuale del pool allocata ai piani di esecuzione o ai buffer dei dati varia dinamicamente in base allo stato del sistema.The percentage of the pool allocated to either execution plans or data buffers fluctuates dynamically, depending on the state of the system. La parte del pool di memoria usata per archiviare i piani di esecuzione è denominata cache dei piani.The part of the memory pool that is used to store execution plans is referred to as the plan cache.

I piani di esecuzione di SQL ServerSQL Server includono i componenti principali seguenti: SQL ServerSQL Server execution plans have the following main components:

  • Piano di esecuzione della query: la parte centrale del piano di esecuzione è una struttura di dati rientrante di sola lettura che può essere usata da un numero qualsiasi di utenti.Query Execution Plan The bulk of the execution plan is a re-entrant, read-only data structure used by any number of users. Questo elemento è detto piano della query.This is referred to as the query plan. Nel piano della query non viene archiviato alcun contesto utente.No user context is stored in the query plan. In memoria non vi sono mai più di una o due copie del piano della query: una copia per tutte le esecuzioni seriali e una per tutte le esecuzioni parallele.There are never more than one or two copies of the query plan in memory: one copy for all serial executions and another for all parallel executions. La copia parallela copre tutte le esecuzioni parallele, indipendentemente dal loro grado di parallelismo.The parallel copy covers all parallel executions, regardless of their degree of parallelism.
  • Contesto di esecuzione: ogni utente che esegue la query dispone di una struttura di dati contenente i dati specifici per l'esecuzione, ad esempio i valori dei parametri.Execution Context Each user that is currently executing the query has a data structure that holds the data specific to their execution, such as parameter values. Questa struttura di dati è denominata contesto di esecuzione.This data structure is referred to as the execution context. Le strutture di dati del contesto di esecuzione vengono riutilizzate.The execution context data structures are reused. Se un utente esegue una query e una delle strutture non è in uso, questa viene reinizializzata con il contesto del nuovo utente.If a user executes a query and one of the structures is not being used, it is reinitialized with the context for the new user.

execution_context

Quando in SQL ServerSQL Server viene eseguita un'istruzione SQL, il motore relazionale esegue prima di tutto una ricerca nella cache dei piani per verificare se è presente un piano di esecuzione esistente per la stessa istruzione SQL.When any SQL statement is executed in SQL ServerSQL Server, the relational engine first looks through the plan cache to verify that an existing execution plan for the same SQL statement exists. L'eventuale piano esistente trovato viene riutilizzato in SQL ServerSQL Server, evitando così l'overhead associato alla ricompilazione dell'istruzione SQL. SQL ServerSQL Server reuses any existing plan it finds, saving the overhead of recompiling the SQL statement. Se non esiste già un piano di esecuzione, in SQL ServerSQL Server viene generato un nuovo piano per la query.If no existing execution plan exists, SQL ServerSQL Server generates a new execution plan for the query.

In SQL ServerSQL Server è disponibile un algoritmo efficiente per l'individuazione di piani di esecuzione esistenti per una specifica istruzione SQL. SQL ServerSQL Server has an efficient algorithm to find any existing execution plans for any specific SQL statement. Nella maggior parte dei sistemi, le risorse minime utilizzate da questa analisi sono inferiori a quelle risparmiate riutilizzando i piani esistenti anziché compilando ogni istruzione SQL.In most systems, the minimal resources that are used by this scan are less than the resources that are saved by being able to reuse existing plans instead of compiling every SQL statement.

Per gli algoritmi che consentono di trovare la corrispondenza tra le nuove istruzioni SQL e i piani di esecuzione esistenti inutilizzati nella cache è necessario che i riferimenti agli oggetti siano completi.The algorithms to match new SQL statements to existing, unused execution plans in the cache require that all object references be fully qualified. Per la prima delle istruzioni SELECT seguenti, ad esempio, non viene trovata la corrispondenza con un piano esistente, come avviene invece per la seconda:For example, the first of these SELECT statements is not matched with an existing plan, and the second is matched:

SELECT * FROM Person;

SELECT * FROM Person.Person;

Rimozione di piani di esecuzione dalla cache dei pianiRemoving Execution Plans from the Plan Cache

I piani di esecuzione rimangono nella cache dei piani fino a quando è disponibile memoria sufficiente per archiviarli.Execution plans remain in the plan cache as long as there is enough memory to store them. In caso di un numero eccessivo di richieste di memoria, il Motore di database di SQL ServerSQL Server Database Engine usa un approccio basato sui costi per determinare i piani di esecuzione da rimuovere dalla cache dei piani.When memory pressure exists, the Motore di database di SQL ServerSQL Server Database Engine uses a cost-based approach to determine which execution plans to remove from the plan cache. Per prendere una decisione basata sui costi, il Motore di database di SQL ServerSQL Server Database Engine incrementa e decrementa una variabile relativa al costo corrente per ogni piano di esecuzione in base ai fattori descritti di seguito.To make a cost-based decision, the Motore di database di SQL ServerSQL Server Database Engine increases and decreases a current cost variable for each execution plan according to the following factors.

Quando un processo utente inserisce un piano di esecuzione nella cache, il costo corrente viene impostato sul costo di compilazione della query originale. Per i piani di esecuzione ad hoc, il processo utente imposta il costo corrente su zero.When a user process inserts an execution plan into the cache, the user process sets the current cost equal to the original query compile cost; for ad-hoc execution plans, the user process sets the current cost to zero. Quindi, ogni volta che un processo utente fa riferimento a un piano di esecuzione, il costo corrente viene reimpostato sul costo di compilazione originale. Per i piani di esecuzione ad hoc, il processo utente aumenta il costo corrente.Thereafter, each time a user process references an execution plan, it resets the current cost to the original compile cost; for ad-hoc execution plans the user process increases the current cost. Per tutti i piani, il valore massimo per il costo corrente corrisponde al costo di compilazione originale.For all plans, the maximum value for the current cost is the original compile cost.

In caso di un numero eccessivo di richieste di memoria, il Motore di database di SQL ServerSQL Server Database Engine risponde rimuovendo i piani di esecuzione dalla cache dei piani.When memory pressure exists, the Motore di database di SQL ServerSQL Server Database Engine responds by removing execution plans from the plan cache. Per determinare i piani da rimuovere, il Motore di database di SQL ServerSQL Server Database Engine esamina ripetutamente lo stato di ogni piano di esecuzione e rimuove i piani quando il relativo costo corrente è pari a zero.To determine which plans to remove, the Motore di database di SQL ServerSQL Server Database Engine repeatedly examines the state of each execution plan and removes plans when their current cost is zero. Un piano di esecuzione con un costo corrente pari a zero non viene rimosso automaticamente in caso di un numero eccessivo di richieste di memoria, ma viene rimosso solo nel momento in cui il Motore di database di SQL ServerSQL Server Database Engine esamina il piano e il costo corrente è pari a zero.An execution plan with zero current cost is not removed automatically when memory pressure exists; it is removed only when the Motore di database di SQL ServerSQL Server Database Engine examines the plan and the current cost is zero. Durante l'analisi di un piano di esecuzione attualmente non utilizzato da una query, il Motore di database di SQL ServerSQL Server Database Engine decrementa il costo corrente fino a raggiungere il valore zero.When examining an execution plan, the Motore di database di SQL ServerSQL Server Database Engine pushes the current cost towards zero by decreasing the current cost if a query is not currently using the plan.

Il Motore di database di SQL ServerSQL Server Database Engine esamina ripetutamente i piani di esecuzione fino a quando non ne viene rimosso un numero sufficiente per soddisfare i requisiti di memoria.The Motore di database di SQL ServerSQL Server Database Engine repeatedly examines the execution plans until enough have been removed to satisfy memory requirements. In caso di un numero eccessivo di richieste di memoria, è possibile che il costo di un piano di esecuzione venga incrementato e decrementato più di una volta.While memory pressure exists, an execution plan may have its cost increased and decreased more than once. Quando il numero di richieste diminuisce, il Motore di database di SQL ServerSQL Server Database Engine arresta la riduzione del costo corrente dei piani di esecuzione non usati e tutti i piani di esecuzione rimangono nella cache dei piani anche se il costo relativo è pari zero.When memory pressure no longer exists, the Motore di database di SQL ServerSQL Server Database Engine stops decreasing the current cost of unused execution plans and all execution plans remain in the plan cache, even if their cost is zero.

Il Motore di database di SQL ServerSQL Server Database Engine usa il monitoraggio risorse e i thread di lavoro dell'utente per liberare memoria dalla cache dei piani in risposta a un numero eccessivo di richieste di memoria.The Motore di database di SQL ServerSQL Server Database Engine uses the resource monitor and user worker threads to free memory from the plan cache in response to memory pressure. Il monitoraggio risorse e i thread di lavoro dell'utente possono esaminare i piani in esecuzione simultanea per ridurre il costo corrente per ogni piano di esecuzione non usato.The resource monitor and user worker threads can examine plans run concurrently to decrease the current cost for each unused execution plan. Il monitoraggio risorse rimuove i piani di esecuzione dalla cache dei piani quando è presente un numero eccessivo di richieste di memoria globaleThe resource monitor removes execution plans from the plan cache when global memory pressure exists. e libera memoria per applicare i criteri per la memoria di sistema, la memoria processi, la memoria del pool di risorse e la dimensione massima per tutte le cache.It frees memory to enforce policies for system memory, process memory, resource pool memory, and maximum size for all caches.

Le dimensioni massime di tutte le cache sono rappresentate da una funzione relativa alle dimensioni del pool di buffer e non possono superare la memoria massima del server.The maximum size for all caches is a function of the buffer pool size and cannot exceed the maximum server memory. Per altre informazioni sulla configurazione della memoria massima del server, vedere l'impostazione max server memory in sp_configure.For more information on configuring the maximum server memory, see the max server memory setting in sp_configure.

In caso di un numero eccessivo di richieste di memoria cache, i thread di lavoro dell'utente rimuovono i piani di esecuzione dalla cache dei pianiThe user worker threads remove execution plans from the plan cache when single cache memory pressure exists. e applicano i criteri per le dimensioni massime e per il numero massimo di voci della singola cache.They enforce policies for maximum single cache size and maximum single cache entries.

L'esempio seguente indica quali piani di esecuzione vengono rimossi dalla cache dei piani:The following examples illustrate which execution plans get removed from the plan cache:

  • A un piano di esecuzione viene fatto riferimento di frequente in modo che il costo non sia mai pari a zero.An execution plan is frequently referenced so that its cost never goes to zero. Il piano viene mantenuto nella cache dei piani e non viene rimosso a meno che non vi sia un numero eccessivo di richieste di memoria e il costo corrente non sia pari a zero.The plan remains in the plan cache and is not removed unless there is memory pressure and the current cost is zero.
  • Viene inserito un piano di esecuzione ad hoc, cui non viene fatto nuovamente riferimento prima che sia presente un numero massimo di richieste di memoria.An ad-hoc execution plan is inserted and is not referenced again before memory pressure exists. Dato che i piani ad hoc vengono inizializzati con un costo corrente pari a zero, quando il Motore di database di SQL ServerSQL Server Database Engine esamina il piano di esecuzione, individuerà il costo corrente pari a zero e rimuoverà il piano dalla cache dei piani.Since ad-hoc plans are initialized with a current cost of zero, when the Motore di database di SQL ServerSQL Server Database Engine examines the execution plan, it will see the zero current cost and remove the plan from the plan cache. Il piano di esecuzione ad hoc viene mantenuto nella cache dei piani con un costo corrente pari a zero in assenza di un numero eccessivo di richieste di memoria.The ad-hoc execution plan remains in the plan cache with a zero current cost when memory pressure does not exist.

Per rimuovere manualmente un singolo piano o tutti i piani dalla cache, usare DBCC FREEPROCCACHE.To manually remove a single plan or all plans from the cache, use DBCC FREEPROCCACHE.

Ricompilazione dei piani di esecuzioneRecompiling Execution Plans

Alcune modifiche in un database possono provocare un piano di esecuzione inefficiente o non valido, in base al nuovo stato del database.Certain changes in a database can cause an execution plan to be either inefficient or invalid, based on the new state of the database. SQL ServerSQL Server rileva le modifiche che rendono un piano di esecuzione non valido e contrassegna il piano come tale. detects the changes that invalidate an execution plan and marks the plan as not valid. Per la successiva connessione che esegue la query, pertanto, è necessaria la ricompilazione di un nuovo piano.A new plan must then be recompiled for the next connection that executes the query. Le condizioni che invalidano un piano includono le seguenti:The conditions that invalidate a plan include the following:

  • Modifiche apportate a una tabella o a una vista a cui fa riferimento la query (ALTER TABLE e ALTER VIEW).Changes made to a table or view referenced by the query (ALTER TABLE and ALTER VIEW).
  • Modifiche apportate a una singola procedura, che elimina dalla cache tutti i piani relativi a questa procedura (ALTER PROCEDURE).Changes made to a single procedure, which would drop all plans for that procedure from the cache (ALTER PROCEDURE).
  • Modifiche agli indici utilizzati dal piano di esecuzione.Changes to any indexes used by the execution plan.
  • Aggiornamenti ai dati statistici usati dal piano di esecuzione, generati in modo esplicito da un'istruzione, ad esempio UPDATE STATISTICS, o automaticamente.Updates on statistics used by the execution plan, generated either explicitly from a statement, such as UPDATE STATISTICS, or generated automatically.
  • Eliminazione di un indice utilizzato dal piano di esecuzione.Dropping an index used by the execution plan.
  • Chiamata esplicita a sp_recompile.An explicit call to sp_recompile.
  • Grande quantità di modifiche alle chiavi, generate dalle istruzioni INSERT o DELETE eseguite da altri utenti che modificano una tabella a cui fa riferimento la query.Large numbers of changes to keys (generated by INSERT or DELETE statements from other users that modify a table referenced by the query).
  • Nel caso di tabelle con trigger, aumento significativo del numero di righe della tabella inserite o eliminate.For tables with triggers, if the number of rows in the inserted or deleted tables grows significantly.
  • Esecuzione di una stored procedure usando l'opzione WITH RECOMPILE .Executing a stored procedure using the WITH RECOMPILE option.

La maggior parte delle ricompilazioni è necessaria per garantire la correttezza dell'istruzione o per ottenere piani di esecuzione della query potenzialmente più veloci.Most recompilations are required either for statement correctness or to obtain potentially faster query execution plans.

In SQL ServerSQL Server 2000 ogni volta che un'istruzione di un batch provoca la ricompilazione, viene ricompilato l'intero batch, indipendentemente dal fatto che sia stato inviato tramite una stored procedure, un trigger, un batch ad hoc o un'istruzione preparata.In SQL ServerSQL Server 2000, whenever a statement within a batch causes recompilation, the whole batch, whether submitted through a stored procedure, trigger, ad-hoc batch, or prepared statement, is recompiled. A partire da SQL Server 2005SQL Server 2005, viene ricompilata solo l'istruzione all'interno del batch che ha provocato la ricompilazione.Starting with SQL Server 2005SQL Server 2005, only the statement inside the batch that causes recompilation is recompiled. A causa di questa differenza, i conteggi delle ricompilazioni in SQL ServerSQL Server 2000 e versioni successive non possono essere confrontati.Because of this difference, recompilation counts in SQL ServerSQL Server 2000 and later releases are not comparable. In SQL Server 2005SQL Server 2005 e versioni successive sono inoltre presenti più tipi di ricompilazioni, in quanto il set di caratteristiche è più ampio.Also, there are more types of recompilations in SQL Server 2005SQL Server 2005 and later because of its expanded feature set.

La ricompilazione a livello di istruzione consente di migliorare le prestazioni in quanto, nella maggior parte dei casi, le ricompilazioni e gli svantaggi associati, in termini di blocchi e tempo della CPU, sono dovuti a un piccolo numero di istruzioni.Statement-level recompilation benefits performance because, in most cases, a small number of statements causes recompilations and their associated penalties, in terms of CPU time and locks. Questi svantaggi possono pertanto venire evitati per le altre istruzioni nel batch che non devono essere ricompilate.These penalties are therefore avoided for the other statements in the batch that do not have to be recompiled.

L'evento esteso (XEvent) sql_statement_recompile indica le ricompilazioni a livello di istruzione.The sql_statement_recompile extended event (xEvent) reports statement-level recompilations. Questo XEvent viene generato quando qualsiasi tipo di batch richiede una ricompilazione a livello di istruzione,This xEvent occurs when a statement-level recompilation is required by any kind of batch. inclusi trigger, stored procedure, batch ad hoc e query.This includes stored procedures, triggers, ad hoc batches and queries. I batch possono essere inviati tramite varie interfacce, inclusi sp_executesql, SQL dinamico, metodi Prepare e metodi Execute.Batches may be submitted through several interfaces, including sp_executesql, dynamic SQL, Prepare methods or Execute methods. La colonna recompile_cause dell'XEvent sql_statement_recompile contiene un codice integer che indica il motivo della ricompilazione.The recompile_cause column of sql_statement_recompile xEvent contains an integer code that indicates the reason for the recompilation. La tabella seguente include i motivi possibili:The following table contains the possible reasons:

Schema modificatoSchema changed Statistiche modificateStatistics changed
Compilazione posticipataDeferred compile Opzione SET modificataSET option changed
Tabella temporanea modificataTemporary table changed Set di righe remoto modificatoRemote rowset changed
AutorizzazioneFOR BROWSE modificataFOR BROWSE permission changed Ambiente di notifica query modificatoQuery notification environment changed
Vista partizionata modificataPartitioned view changed Opzioni cursore modificateCursor options changed
OPTION (RECOMPILE) richiesta.OPTION (RECOMPILE) requested Piano con parametri scaricatoParameterized plan flushed
Piano che influisce sulla versione del database modificatoPlan affecting database version changed Criteri di uso forzato del piano dell'archivio query modificatiQuery Store plan forcing policy changed
Uso forzato del piano dell'archivio query non riuscitoQuery Store plan forcing failed Piano mancante nell'archivio queryQuery Store missing the plan
Nota

Nelle versioni di SQL ServerSQL Server in cui non sono disponibili xEvent, è possibile usare l'evento di traccia SP:Recompile di SQL ServerSQL Server Profiler per lo stesso scopo, ovvero segnalare le ricompilazioni a livello di istruzione.In SQL ServerSQL Server versions where xEvents are not available, then the SQL ServerSQL Server Profiler SP:Recompile trace event can be used for the same purpose of reporting statement-level recompilations. Anche l'evento di traccia SQL:StmtRecompile segnala le ricompilazioni a livello di istruzione e questo evento di traccia può essere usato anche per tenere traccia delle ricompilazioni ed eseguirne il debug.The trace event SQL:StmtRecompile also reports statement-level recompilations, and this trace event can also be used to track and debug recompilations. Mentre SP:Recompile viene generato solo per stored procedure e trigger, SQL:StmtRecompile viene generato per stored procedure, trigger, batch ad hoc, batch eseguiti tramite sp_executesqlsp_executesql, query preparate e SQL dinamico.Whereas SP:Recompile generates only for stored procedures and triggers, SQL:StmtRecompile generates for stored procedures, triggers, ad-hoc batches, batches that are executed by using sp_executesql, prepared queries, and dynamic SQL. La colonna EventSubClass di SP:Recompile e SQL:StmtRecompile contiene un codice integer che indica il motivo della ricompilazione.The EventSubClass column of SP:Recompile and SQL:StmtRecompile contains an integer code that indicates the reason for the recompilation. I codici sono descritti qui.The codes are described here.

Nota

Quando l'opzione di database AUTO_UPDATE_STATISTICS è impostata su ON, le query vengono ricompilate quando sono indirizzate a tabelle o viste indicizzate le cui statistiche sono state aggiornate o le cui cardinalità sono state modificate in modo significativo dall'ultima esecuzione.When the AUTO_UPDATE_STATISTICS database option is set to ON, queries are recompiled when they target tables or indexed views whose statistics have been updated or whose cardinalities have changed significantly since the last execution. Questo comportamento si applica alle tabelle standard definite dall'utente, alle tabelle temporanee e alle tabelle inserite ed eliminate, create dai trigger DML.This behavior applies to standard user-defined tables, temporary tables, and the inserted and deleted tables created by DML triggers. Se le prestazioni delle query sono influenzate da un numero eccessivo di ricompilazioni, è possibile modificare l'impostazione su OFF.If query performance is affected by excessive recompilations, consider changing this setting to OFF. Quando l'opzione AUTO_UPDATE_STATISTICS del database è impostata su OFF, non vengono eseguite ricompilazioni in base alle statistiche o alle modifiche delle cardinalità, ad eccezione delle tabelle inserite ed eliminate create dai trigger DML INSTEAD OF.When the AUTO_UPDATE_STATISTICS database option is set to OFF, no recompilations occur based on statistics or cardinality changes, with the exception of the inserted and deleted tables that are created by DML INSTEAD OF triggers. Poiché tali tabelle vengono create in tempdb, la ricompilazione delle query che vi accedono dipende dall'impostazione di AUTO_UPDATE_STATISTICS in tempdb.Because these tables are created in tempdb, the recompilation of queries that access them depends on the setting of AUTO_UPDATE_STATISTICS in tempdb. Si noti che in SQL ServerSQL Server 2000, la ricompilazione delle query continua in base alle modifiche delle cardinalità delle tabelle inerite ed eliminate del trigger DML, anche quando l'impostazione è OFF.Note that in SQL ServerSQL Server 2000, queries continue to recompile based on cardinality changes to the DML trigger inserted and deleted tables, even when this setting is OFF.

Parametri e riutilizzo del piano di esecuzione Parameters and Execution Plan Reuse

L'utilizzo dei parametri, inclusi i marcatori di parametro nelle applicazioni ADO, OLE DB e ODBC, può comportare un maggiore riutilizzo dei piani di esecuzione.The use of parameters, including parameter markers in ADO, OLE DB, and ODBC applications, can increase the reuse of execution plans.

Avviso

L'uso di parametri o marcatori di parametro per includere i valori digitati dagli utenti offre una protezione maggiore rispetto alla concatenazione dei valori in una stringa eseguita usando un metodo API di accesso ai dati, l'istruzione EXECUTE o la stored procedure sp_executesql .Using parameters or parameter markers to hold values that are typed by end users is more secure than concatenating the values into a string that is then executed by using either a data access API method, the EXECUTE statement, or the sp_executesql stored procedure.

L'unica differenza tra le due istruzioni SELECT seguenti è rappresentata dai valori confrontati nella clausola WHERE :The only difference between the following two SELECT statements is the values that are compared in the WHERE clause:

SELECT * 
FROM AdventureWorks2014.Production.Product 
WHERE ProductSubcategoryID = 1;
SELECT * 
FROM AdventureWorks2014.Production.Product 
WHERE ProductSubcategoryID = 4;

L'unica differenza tra i piani di esecuzione delle due query è rappresentata dal valore archiviato per il confronto con la colonna ProductSubcategoryID .The only difference between the execution plans for these queries is the value stored for the comparison against the ProductSubcategoryID column. L'obiettivo principale di SQL ServerSQL Server consiste nel riconoscere sempre che le istruzioni generano essenzialmente lo stesso piano e nel riutilizzare i piani, anche se SQL ServerSQL Server a volte non riesce a eseguire questo rilevamento per istruzioni SQL complesse.While the goal is for SQL ServerSQL Server to always recognize that the statements generate essentially the same plan and reuse the plans, SQL ServerSQL Server sometimes does not detect this in complex SQL statements.

La separazione delle costanti dall'istruzione SQL tramite i parametri consente al motore relazionale di riconoscere i piani duplicati.Separating constants from the SQL statement by using parameters helps the relational engine recognize duplicate plans. È possibile utilizzare i parametri come indicato di seguito:You can use parameters in the following ways:

  • In Transact-SQL, usare sp_executesql:In Transact-SQL, use sp_executesql:

    DECLARE @MyIntParm INT
    SET @MyIntParm = 1
    EXEC sp_executesql
      N'SELECT * 
      FROM AdventureWorks2014.Production.Product 
      WHERE ProductSubcategoryID = @Parm',
      N'@Parm INT',
      @MyIntParm
    

    Questo metodo è particolarmente adatto per gli script Transact-SQL, le stored procedure o i trigger che generano istruzioni SQL in modo dinamico.This method is recommended for Transact-SQL scripts, stored procedures, or triggers that generate SQL statements dynamically.

  • ADO, OLE DB e ODBC utilizzano i marcatori di parametro.ADO, OLE DB, and ODBC use parameter markers. I marcatori di parametro sono punti interrogativi (?) che sostituiscono una costante in un'istruzione SQL e sono associati a una variabile di programma.Parameter markers are question marks (?) that replace a constant in an SQL statement and are bound to a program variable. In un'applicazione ODBC, ad esempio, verrebbero eseguite le operazioni seguenti:For example, you would do the following in an ODBC application:

    • Usare SQLBindParameter per associare una variabile integer al primo marcatore di parametro di un'istruzione SQL.Use SQLBindParameter to bind an integer variable to the first parameter marker in an SQL statement.
    • Inserire il valore intero nella variabile.Put the integer value in the variable.
    • Eseguire l'istruzione specificando il marcatore di parametro (?):Execute the statement, specifying the parameter marker (?):

      SQLExecDirect(hstmt, 
      "SELECT * 
      FROM AdventureWorks2014.Production.Product 
      WHERE ProductSubcategoryID = ?",
      SQL_NTS);
      

      Il provider OLE DB di SQL ServerSQL Server Native Client e il driver ODBC di SQL ServerSQL Server Native Client inclusi in SQL ServerSQL Server usano sp_executesql per inviare istruzioni a SQL ServerSQL Server quando nelle applicazioni vengono usati marcatori di parametro.The SQL ServerSQL Server Native Client OLE DB Provider and the SQL ServerSQL Server Native Client ODBC driver included with SQL ServerSQL Server use sp_executesql to send statements to SQL ServerSQL Server when parameter markers are used in applications.

  • Progettare stored procedure, che utilizzano parametri per schema.To design stored procedures, which use parameters by design.

Se non si compilano parametri in modo esplicito nella progettazione dell'applicazione, è anche possibile basarsi su SQL ServerSQL Server Query Optimizer per parametrizzare automaticamente query specifiche usando il funzionamento predefinito di parametrizzazione semplice.If you do not explicitly build parameters into the design of your applications, you can also rely on the SQL ServerSQL Server Query Optimizer to automatically parameterize certain queries by using the default behavior of simple parameterization. In alternativa, è possibile forzare Query Optimizer affinché esegua la parametrizzazione di tutte le query nel database impostando l'opzione PARAMETERIZATION dell'istruzione ALTER DATABASE su FORCED.Alternatively, you can force the Query Optimizer to consider parameterizing all queries in the database by setting the PARAMETERIZATION option of the ALTER DATABASE statement to FORCED.

Quando viene attivata la parametrizzazione forzata, la parametrizzazione semplice può essere comunque eseguita.When forced parameterization is enabled, simple parameterization can still occur. La query seguente, ad esempio, non può essere parametrizzata in base alle regole di parametrizzazione forzata:For example, the following query cannot be parameterized according to the rules of forced parameterization:

SELECT * FROM Person.Address
WHERE AddressID = 1 + 2;

La query può tuttavia essere parametrizzata in base alle regole di parametrizzazione semplice.However, it can be parameterized according to simple parameterization rules. Quando un tentativo di parametrizzazione forzata ha esito negativo, viene successivamente tentata la parametrizzazione semplice.When forced parameterization is tried but fails, simple parameterization is still subsequently tried.

Parametrizzazione semplice Simple Parameterization

In SQL ServerSQL Server l'uso di parametri o di marcatori di parametro nelle istruzioni di Transact-SQL consente di aumentare la capacità del motore relazionale di trovare una corrispondenza tra le nuove istruzioni SQL e i piani di esecuzione esistenti compilati in precedenza.In SQL ServerSQL Server, using parameters or parameter markers in Transact-SQL statements increases the ability of the relational engine to match new SQL statements with existing, previously-compiled execution plans.

Avviso

L'uso di parametri o marcatori di parametro per includere i valori digitati dagli utenti offre una protezione maggiore rispetto alla concatenazione dei valori in una stringa eseguita usando un metodo API di accesso ai dati, l'istruzione EXECUTE o la stored procedure sp_executesql .Using parameters or parameter markers to hold values typed by end users is more secure than concatenating the values into a string that is then executed using either a data access API method, the EXECUTE statement, or the sp_executesql stored procedure.

Se un'istruzione SQL viene eseguita senza parametri, in SQL ServerSQL Server l'istruzione viene parametrizzata a livello interno per aumentare la possibilità di trovare una corrispondenza con un piano di esecuzione esistente.If a SQL statement is executed without parameters, SQL ServerSQL Server parameterizes the statement internally to increase the possibility of matching it against an existing execution plan. Questo processo viene definito parametrizzazione semplice.This process is called simple parameterization. In SQL ServerSQL Server 2000 il processo è definito parametrizzazione automatica.In SQL ServerSQL Server 2000, the process was referred to as auto-parameterization.

Si consideri l'istruzione seguente:Consider this statement:

SELECT * FROM AdventureWorks2014.Production.Product 
WHERE ProductSubcategoryID = 1;

Il valore 1 alla fine dell'istruzione può essere specificato come parametro.The value 1 at the end of the statement can be specified as a parameter. Tramite il motore relazionale compilato il piano di esecuzione per questo batch come se fosse stato specificato un parametro al posto del valore 1.The relational engine builds the execution plan for this batch as if a parameter had been specified in place of the value 1. Grazie a questa semplice parametrizzazione, SQL ServerSQL Server riconosce che le due istruzioni seguenti generano essenzialmente lo stesso piano di esecuzione e il primo piano viene riutilizzato per la seconda istruzione:Because of this simple parameterization, SQL ServerSQL Server recognizes that the following two statements generate essentially the same execution plan and reuses the first plan for the second statement:

SELECT * FROM AdventureWorks2014.Production.Product 
WHERE ProductSubcategoryID = 1;
SELECT * FROM AdventureWorks2014.Production.Product 
WHERE ProductSubcategoryID = 4;

Durante l'elaborazione di istruzioni SQL complesse, è possibile che il motore relazionale incontri difficoltà nel determinare le espressioni che è possibile parametrizzare.When processing complex SQL statements, the relational engine may have difficulty determining which expressions can be parameterized. Per aumentare la capacità del motore relazionale di associare le istruzioni SQL complesse ai piani di esecuzione esistenti e inutilizzati, è necessario specificare in modo esplicito i parametri tramite sp_executesql o i marcatori di parametro.To increase the ability of the relational engine to match complex SQL statements to existing, unused execution plans, explicitly specify the parameters using either sp_executesql or parameter markers.

Nota

Quando vengono usati gli operatori aritmetici +, -, *, / o % per eseguire una conversione implicita o esplicita di valori costanti int, smallint, tinyint o bigint in tipi di dati float, real, decimal o numeric, SQL ServerSQL Server applica regole specifiche per calcolare il tipo e la precisione dei risultati dell'espressione.When the +, -, *, /, or % arithmetic operators are used to perform implicit or explicit conversion of int, smallint, tinyint, or bigint constant values to the float, real, decimal or numeric data types, SQL ServerSQL Server applies specific rules to calculate the type and precision of the expression results. Queste regole, tuttavia, variano in base al fatto che la query includa o meno parametri.However, these rules differ, depending on whether the query is parameterized or not. Espressioni simili nelle query possono pertanto in alcuni casi generare risultati diversi.Therefore, similar expressions in queries can, in some cases, produce differing results.

In base al comportamento predefinito della parametrizzazione semplice, in SQL ServerSQL Server viene parametrizzato un numero relativamente piccolo di query.Under the default behavior of simple parameterization, SQL ServerSQL Server parameterizes a relatively small class of queries. È tuttavia possibile fare in modo che tutte le query in un database vengano parametrizzate, rispettando determinate limitazioni, impostando l'opzione PARAMETERIZATION del comando ALTER DATABASE su FORCED.However, you can specify that all queries in a database be parameterized, subject to certain limitations, by setting the PARAMETERIZATION option of the ALTER DATABASE command to FORCED. In questo modo, è possibile migliorare le prestazioni dei database in cui viene eseguito un numero elevato di query simultanee riducendo la frequenza di compilazione delle query.Doing so may improve the performance of databases that experience high volumes of concurrent queries by reducing the frequency of query compilations.

In alternativa, è possibile specificare la parametrizzazione di una singola query e di tutte le altre con sintassi equivalente ma che differiscono solo per i valori dei parametri.Alternatively, you can specify that a single query, and any others that are syntactically equivalent but differ only in their parameter values, be parameterized.

Parametrizzazione forzata Forced Parameterization

È possibile ignorare il comportamento predefinito di parametrizzazione semplice di SQL ServerSQL Server specificando la parametrizzazione di tutte le istruzioni SELECT, INSERT, UPDATE e DELETE di un database in base a limiti specifici.You can override the default simple parameterization behavior of SQL ServerSQL Server by specifying that all SELECT, INSERT, UPDATE, and DELETE statements in a database be parameterized, subject to certain limitations. La parametrizzazione forzata viene attivata impostando l'opzione PARAMETERIZATION su FORCED nell'istruzione ALTER DATABASE .Forced parameterization is enabled by setting the PARAMETERIZATION option to FORCED in the ALTER DATABASE statement. La parametrizzazione forzata può offrire un miglioramento delle prestazioni di alcuni database riducendo la frequenza delle operazioni di compilazione e ricompilazione delle query.Forced parameterization may improve the performance of certain databases by reducing the frequency of query compilations and recompilations. I database che possono essere soggetti a un miglioramento delle prestazione grazie alla parametrizzazione forzata sono in genere quelli che ricevono volumi elevati di query simultanee da origini quali le applicazioni POS.Databases that may benefit from forced parameterization are generally those that experience high volumes of concurrent queries from sources such as point-of-sale applications.

Quando l'opzione PARAMETERIZATION è impostata su FORCED, qualsiasi valore letterale visualizzato in un'istruzione SELECT, INSERT, UPDATEo DELETE , inviato in qualsiasi forma, viene convertito in un parametro durante la compilazione delle query.When the PARAMETERIZATION option is set to FORCED, any literal value that appears in a SELECT, INSERT, UPDATE, or DELETE statement, submitted in any form, is converted to a parameter during query compilation. Le eccezioni consistono in valori letterali presenti nei costrutti di query seguenti:The exceptions are literals that appear in the following query constructs:

  • IstruzioniINSERT...EXECUTE .INSERT...EXECUTE statements.
  • Istruzioni all'interno del corpo di stored procedure, trigger o funzioni definite dall'utente.Statements inside the bodies of stored procedures, triggers, or user-defined functions. SQL ServerSQL Server riutilizza già piani di query per tali routine. already reuses query plans for these routines.
  • Istruzioni preparate già parametrizzate nell'applicazione sul lato client.Prepared statements that have already been parameterized on the client-side application.
  • Istruzioni contenenti chiamate al metodo XQuery, in cui il metodo appare in un contesto in cui i relativi argomenti verrebbero in genere parametrizzati, ad esempio una clausola WHERE .Statements that contain XQuery method calls, where the method appears in a context where its arguments would typically be parameterized, such as a WHERE clause. Se il metodo appare in un contesto in cui i relativi argomenti non verrebbero parametrizzati, il resto dell'istruzione viene parametrizzato.If the method appears in a context where its arguments would not be parameterized, the rest of the statement is parameterized.
  • Istruzioni all'interno di un cursore Transact-SQL.Statements inside a Transact-SQL cursor. Le istruzioniSELECT all'interno dei cursori API vengono parametrizzate.(SELECT statements inside API cursors are parameterized.)
  • Costrutti di query deprecati.Deprecated query constructs.
  • Qualsiasi istruzione che viene eseguita nel contesto di ANSI_PADDING o ANSI_NULLS impostata su OFF.Any statement that is run in the context of ANSI_PADDING or ANSI_NULLS set to OFF.
  • Istruzioni contenenti oltre 2.097 valori letterali idonei per la parametrizzazione.Statements that contain more than 2,097 literals that are eligible for parameterization.
  • Istruzioni che fanno riferimento a variabili, ad esempio WHERE T.col2 >= @bb.Statements that reference variables, such as WHERE T.col2 >= @bb.
  • Istruzioni contenenti l'hint per la query RECOMPILE .Statements that contain the RECOMPILE query hint.
  • Istruzioni contenenti una clausola COMPUTE .Statements that contain a COMPUTE clause.
  • Istruzioni contenenti una clausola WHERE CURRENT OF .Statements that contain a WHERE CURRENT OF clause.

Le clausole di query seguenti sono inoltre senza parametri.Additionally, the following query clauses are not parameterized. Si noti che in questi casi soltanto le clausole sono senza parametri.Note that in these cases, only the clauses are not parameterized. Altre clausole all'interno della stessa query potrebbero essere idonee per la parametrizzazione forzata.Other clauses within the same query may be eligible for forced parameterization.

  • <select_list> di qualsiasi istruzione SELECT.The <select_list> of any SELECT statement. Ciò include elenchi SELECT delle sottoquery ed elenchi SELECT all'interno delle istruzioni INSERT.This includes SELECT lists of subqueries and SELECT lists inside INSERT statements.
  • Istruzioni SELECT delle sottoquery incluse in un'istruzione IF .Subquery SELECT statements that appear inside an IF statement.
  • Clausole TOP, TABLESAMPLE, HAVING, GROUP BY, ORDER BY, OUTPUT...INTOo FOR XML di una query.The TOP, TABLESAMPLE, HAVING, GROUP BY, ORDER BY, OUTPUT...INTO, or FOR XML clauses of a query.
  • Argomenti, diretti o sottoespressioni, a OPENROWSET, OPENQUERY, OPENDATASOURCE, OPENXMLo qualsiasi operatore FULLTEXT .Arguments, either direct or as subexpressions, to OPENROWSET, OPENQUERY, OPENDATASOURCE, OPENXML, or any FULLTEXT operator.
  • Argomenti pattern ed escape_character di una clausola LIKE .The pattern and escape_character arguments of a LIKE clause.
  • Argomento style di una clausola CONVERT .The style argument of a CONVERT clause.
  • Costante integer all'interno di una clausola IDENTITY .Integer constants inside an IDENTITY clause.
  • Costanti specificate utilizzando la sintassi delle estensioni ODBC.Constants specified by using ODBC extension syntax.
  • Espressioni per le quali è possibile eseguire l'elaborazione delle costanti in fase di compilazione che rappresentano argomenti degli operatori +, -, *, / e %.Constant-foldable expressions that are arguments of the +, -, *, /, and % operators. Quando viene valutata l'idoneità per la parametrizzazione forzata, in SQL ServerSQL Server un'espressione viene considerata come idonea per l'elaborazione delle costanti in fase di compilazione quando si verificano le condizioni seguenti:When considering eligibility for forced parameterization, SQL ServerSQL Server considers an expression to be constant-foldable when either of the following conditions is true:
    • Nell'espressione non è inclusa alcuna colonna, variabile o subquery.No columns, variables, or subqueries appear in the expression.
    • L'espressione contiene una clausola CASE .The expression contains a CASE clause.
  • Argomenti delle clausole degli hint per le query.Arguments to query hint clauses. Sono inclusi l'argomento number_of_rows dell'hint per la query FAST , l'argomento number_of_processors dell'hint per la query MAXDOP e l'argomento del numero dell'hint della query MAXRECURSION .These include the number_of_rows argument of the FAST query hint, the number_of_processors argument of the MAXDOP query hint, and the number argument of the MAXRECURSION query hint.

La parametrizzazione viene eseguita a livello di singole istruzioni Transact-SQL.Parameterization occurs at the level of individual Transact-SQL statements. In altri termini, vengono parametrizzate le singole istruzioni presenti in un batch.In other words, individual statements in a batch are parameterized. In seguito alla compilazione, una query con parametri viene eseguita nel contesto del batch in cui è stata inviata originariamente.After compiling, a parameterized query is executed in the context of the batch in which it was originally submitted. Se un piano di esecuzione per una query viene memorizzato nella cache, è possibile determinare se è stata eseguita la parametrizzazione della query facendo riferimento alla colonna sql della vista a gestione dinamica sys.syscacheobjects.If an execution plan for a query is cached, you can determine whether the query was parameterized by referencing the sql column of the sys.syscacheobjects dynamic management view. Se è stata eseguita la parametrizzazione di una query, i nomi e i tipi di dati dei parametri precedono il testo del batch inviato nella colonna, ad esempio (@1 tinyint).If a query is parameterized, the names and data types of parameters come before the text of the submitted batch in this column, such as (@1 tinyint).

Nota

I nomi dei parametri sono arbitrari.Parameter names are arbitrary. Gli utenti o le applicazioni non devono basarsi su un ordine di denominazione specifico.Users or applications should not rely on a particular naming order. È inoltre possibile che nomi dei parametri, scelta dei valori letterali con parametri e spaziatura nel testo con parametri cambino tra le versioni di SQL ServerSQL Server e gli aggiornamenti dei Service Pack.Also, the following can change between versions of SQL ServerSQL Server and Service Pack upgrades: Parameter names, the choice of literals that are parameterized, and the spacing in the parameterized text.

Tipi di dati dei parametriData Types of Parameters

Quando in SQL ServerSQL Server vengono parametrizzati valori letterali, i parametri vengono convertiti nei tipi di dati seguenti:When SQL ServerSQL Server parameterizes literals, the parameters are converted to the following data types:

  • I valori letterali interi le cui dimensioni altrimenti si adatterebbero al tipo di dati int vengono parametrizzati in int. I valori letterali interi di dimensioni maggiori inclusi in predicati che comportano qualsiasi operatore di confronto, come <, <=, =, !=, >, >=, , !<, !>, <>, ALL, ANY, SOME, BETWEEN e IN, vengono parametrizzati in numeric(38,0).Integer literals whose size would otherwise fit within the int data type parameterize to int. Larger integer literals that are parts of predicates that involve any comparison operator (includes <, <=, =, !=, >, >=, , !<, !>, <>, ALL, ANY, SOME, BETWEEN, and IN) parameterize to numeric(38,0). I valori letterali di dimensioni maggiori non inclusi in predicati che comportano operatori di confronto vengono parametrizzati in numeric, la cui precisione è tale da supportarne le dimensioni e il cui valore di scala è 0.Larger literals that are not parts of predicates that involve comparison operators parameterize to numeric whose precision is just large enough to support its size and whose scale is 0.
  • I valori letterali numerici a virgola fissa inclusi in predicati che comportano operatori di confronto vengono parametrizzati in numeric con precisione 38 e valore di scala tale da supportarne le dimensioni.Fixed-point numeric literals that are parts of predicates that involve comparison operators parameterize to numeric whose precision is 38 and whose scale is just large enough to support its size. I valori letterali numerici a virgola fissa non inclusi in predicati che comportano operatori di confronto vengono parametrizzati in numeric con precisione e valore di scala tali da supportarne le dimensioni.Fixed-point numeric literals that are not parts of predicates that involve comparison operators parameterize to numeric whose precision and scale are just large enough to support its size.
  • I valori letterali numerici a virgola mobile vengono parametrizzati in float(53).Floating point numeric literals parameterize to float(53).
  • I valori letterali stringa vengono parametrizzati in varchar(8000) se il valore letterale non supera gli 8000 caratteri e in varchar(max) se è maggiore di 8000 caratteri.Non-Unicode string literals parameterize to varchar(8000) if the literal fits within 8,000 characters, and to varchar(max) if it is larger than 8,000 characters.
  • I valori letterali stringa vengono parametrizzati in nvarchar(4000) se il valore letterale non supera i 4000 caratteri e in nvarchar(max) se è maggiore di 4000 caratteri.Unicode string literals parameterize to nvarchar(4000) if the literal fits within 4,000 Unicode characters, and to nvarchar(max) if the literal is larger than 4,000 characters.
  • I valori letterali binari vengono parametrizzati in varbinary(8000) se il valore letterale non supera gli 8000 byte.Binary literals parameterize to varbinary(8000) if the literal fits within 8,000 bytes. Se il valore letterale è maggiore di 8000 byte, viene convertito in varbinary(max).If it is larger than 8,000 bytes, it is converted to varbinary(max).
  • I valori letterali di tipo money vengono parametrizzati in money.Money type literals parameterize to money.

Linee guida per l'utilizzo della parametrizzazione forzata Guidelines for Using Forced Parameterization

Quando si desidera impostare l'opzione PARAMETERIZATION su FORCED, considerare gli aspetti seguenti:Consider the following when you set the PARAMETERIZATION option to FORCED:

  • Tramite la parametrizzazione forzata, in pratica, le costanti letterali incluse in una query vengono modificate in parametri durante la compilazione di una query.Forced parameterization, in effect, changes the literal constants in a query to parameters when compiling a query. È pertanto possibile che in Query Optimizer vengano scelti piani non ottimali per le query.Therefore, the Query Optimizer might choose suboptimal plans for queries. In particolare, è meno probabile che Query Optimizer associ la query a una vista indicizzata o a un indice in una colonna calcolata.In particular, the Query Optimizer is less likely to match the query to an indexed view or an index on a computed column. Potrebbero inoltre essere scelti piani non ottimali per le query formulate nelle tabelle partizionate e nelle viste partizionate distribuite.It may also choose suboptimal plans for queries posed on partitioned tables and distributed partitioned views. Non utilizzare la parametrizzazione forzata negli ambienti basati in modo significativo su viste indicizzate e indici in colonne calcolate.Forced parameterization should not be used for environments that rely heavily on indexed views and indexes on computed columns. In generale l'opzione PARAMETERIZATION FORCED deve essere usata solo da amministratori di database esperti dopo avere determinato che le prestazioni non subiranno alcun impatto negativo.Generally, the PARAMETERIZATION FORCED option should only be used by experienced database administrators after determining that doing this does not adversely affect performance.
  • Le query distribuite che fanno riferimento a più di un database sono idonee per la parametrizzazione forzata a condizione che l'opzione PARAMETERIZATION sia impostata su FORCED nel database nel cui contesto viene eseguita la query.Distributed queries that reference more than one database are eligible for forced parameterization as long as the PARAMETERIZATION option is set to FORCED in the database whose context the query is running.
  • L'impostazione dell'opzione PARAMETERIZATION su FORCED consente di scaricare tutti i piani di query dalla cache dei piani del database, ad eccezione di quelli di cui è in corso la compilazione, la ricompilazione o l'esecuzione.Setting the PARAMETERIZATION option to FORCED flushes all query plans from the plan cache of a database, except those that currently are compiling, recompiling, or running. I piani per le query di cui è in corso la compilazione o l'esecuzione durante la modifica dell'impostazione verranno parametrizzati alla successiva esecuzione della query.Plans for queries that are compiling or running during the setting change are parameterized the next time the query is executed.
  • L'impostazione dell'opzione PARAMETERIZATION è un'operazione online che richiede che non vi sia alcun blocco esclusivo a livello del database.Setting the PARAMETERIZATION option is an online operation that it requires no database-level exclusive locks.
  • L'impostazione corrente dell'opzione PARAMETERIZATION viene mantenuta quando un database viene ricollegato o ripristinato.The current setting of the PARAMETERIZATION option is preserved when reattaching or restoring a database.

È possibile ignorare il comportamento della parametrizzazione forzata specificando che su una singola query, e su qualsiasi altra query sintatticamente equivalente ma che differisca solo nei valori dei parametri, venga eseguita la parametrizzazione semplice.You can override the behavior of forced parameterization by specifying that simple parameterization be attempted on a single query, and any others that are syntactically equivalent but differ only in their parameter values. Viceversa è possibile specificare che la parametrizzazione forzata venga tentata solo su un set di query sintatticamente equivalenti, anche se disabilitata nel database.Conversely, you can specify that forced parameterization be attempted on only a set of syntactically equivalent queries, even if forced parameterization is disabled in the database. A tale scopo, vengono usate leguide di piano .Plan guides are used for this purpose.

Nota

Quando l'opzione PARAMETERIZATION è impostata su FORCED, il report dei messaggi di errore potrebbe presentare differenze rispetto a quando l'opzione PARAMETERIZATION è impostata su SIMPLE: questa impostazione potrebbe comportare la segnalazione di più messaggi di errore nei casi in cui nella parametrizzazione semplice sarebbe stato segnalato un numero di messaggi di errore inferiore e i numeri di riga nei quali si sono verificati gli errori potrebbero non essere segnalati correttamente.When the PARAMETERIZATION option is set to FORCED, the reporting of error messages may differ from when the PARAMETERIZATION option is set to SIMPLE: multiple error messages may be reported under forced parameterization, where fewer messages would be reported under simple parameterization, and the line numbers in which errors occur may be reported incorrectly.

Preparazione delle istruzioni SQLPreparing SQL Statements

Il motore relazionale di SQL ServerSQL Server offre supporto completo per la preparazione di istruzioni SQL prima della relativa esecuzione.The SQL ServerSQL Server relational engine introduces full support for preparing SQL statements before they are executed. Se in un'applicazione è necessario eseguire più volte un'istruzione SQL, è possibile utilizzare l'API di database per:If an application has to execute an SQL statement several times, it can use the database API to do the following:

  • Preparare l'istruzione una sola volta.Prepare the statement once. L'istruzione SQL viene compilata in un piano di esecuzione.This compiles the SQL statement into an execution plan.
  • Eseguire il piano di esecuzione precompilato ogni volta che è necessario eseguire l'istruzione.Execute the precompiled execution plan every time it has to execute the statement. In questo modo, si evita di ricompilare l'istruzione SQL a ogni esecuzione dopo la prima volta.This prevents having to recompile the SQL statement on each execution after the first time.
    La preparazione e l'esecuzione delle istruzioni è controllata dalle funzioni e dai metodi dell'API.Preparing and executing statements is controlled by API functions and methods. Non è inclusa nel linguaggio Transact-SQL.It is not part of the Transact-SQL language. Il modello di preparazione/esecuzione per l'esecuzione di istruzioni SQL è supportato dal Provider OLE DB Native Client di e SQL ServerSQL Server e dal driver ODBC Native Client di SQL ServerSQL Server.The prepare/execute model of executing SQL statements is supported by the SQL ServerSQL Server Native Client OLE DB Provider and the SQL ServerSQL Server Native Client ODBC driver. A una richiesta di preparazione, il provider o il driver invia l'istruzione a SQL ServerSQL Server con una richiesta di preparazione dell'istruzione.On a prepare request, either the provider or the driver sends the statement to SQL ServerSQL Server with a request to prepare the statement. SQL ServerSQL Server compila un piano di esecuzione e restituisce un handle del piano al provider o al driver. compiles an execution plan and returns a handle for that plan to the provider or driver. Alla richiesta di esecuzione, il provider o il driver invia al server una richiesta di esecuzione del piano associato all'handle.On an execute request, either the provider or the driver sends the server a request to execute the plan that is associated with the handle.

Le istruzioni preparate non possono essere utilizzate per creare oggetti temporanei in SQL ServerSQL Server.Prepared statements cannot be used to create temporary objects on SQL ServerSQL Server. Nelle istruzioni preparate, infatti, non è consentito fare riferimento a stored procedure di sistema che creano oggetti temporanei, ad esempio tabelle temporanee.Prepared statements cannot reference system stored procedures that create temporary objects, such as temporary tables. Tali procedure devono essere eseguite in modo diretto.These procedures must be executed directly.

L'utilizzo eccessivo del modello di preparazione/esecuzione può determinare un peggioramento delle prestazioni.Excess use of the prepare/execute model can degrade performance. Se un'istruzione viene eseguita una sola volta, è sufficiente l'esecuzione diretta, che richiede un solo ciclo di andata e ritorno in rete per il server.If a statement is executed only once, a direct execution requires only one network round-trip to the server. La preparazione e l'esecuzione di un'istruzione SQL che viene eseguita una sola volta richiedono un ciclo di andata e ritorno in rete aggiuntivo (uno per preparare l'istruzione e uno per eseguirla).Preparing and executing an SQL statement executed only one time requires an extra network round-trip; one trip to prepare the statement and one trip to execute it.

È possibile preparare un'istruzione in modo più efficiente utilizzando i marcatori di parametro.Preparing a statement is more effective if parameter markers are used. Si supponga, ad esempio, che a un'applicazione venga richiesto occasionalmente di recuperare informazioni sui prodotti dal database di esempio AdventureWorks .For example, assume that an application is occasionally asked to retrieve product information from the AdventureWorks sample database. L'applicazione può eseguire questa operazione in due modi diversi.There are two ways the application can do this.

L'applicazione può innanzitutto eseguire una query distinta per ogni prodotto richiesto:Using the first way, the application can execute a separate query for each product requested:

SELECT * FROM AdventureWorks2014.Production.Product
WHERE ProductID = 63;

Altrimenti, l'applicazione può eseguire le operazioni seguenti:Using the second way, the application does the following:

  1. Preparare un'istruzione contenente un marcatore di parametro (?):Prepares a statement that contains a parameter marker (?):
    tsql SELECT * FROM AdventureWorks2014.Production.Product WHERE ProductID = ?;
  2. Associare una variabile di programma al marcatore di parametro.Binds a program variable to the parameter marker.
  3. A ogni richiesta di informazioni sul prodotto, inserire nella variabile associata il valore di chiave ed eseguire l'istruzione.Each time product information is needed, fills the bound variable with the key value and executes the statement.

Il secondo metodo è più efficiente se l'istruzione viene eseguita più di tre volte.The second way is more efficient when the statement is executed more than three times.

In SQL ServerSQL Server, il modello di preparazione/esecuzione non presenta alcun vantaggio significativo per le prestazioni rispetto all'esecuzione diretta, a causa della modalità in cui SQL ServerSQL Server riutilizza i piani di esecuzione.In SQL ServerSQL Server, the prepare/execute model has no significant performance advantage over direct execution, because of the way SQL ServerSQL Server reuses execution plans. SQL ServerSQL Server ha algoritmi efficienti per la corrispondenza di istruzioni SQL correnti con i piani di esecuzione generati per esecuzioni precedenti della stessa istruzione SQL. has efficient algorithms for matching current SQL statements with execution plans that are generated for prior executions of the same SQL statement. Se un'applicazione esegue più volte un'istruzione SQL con marcatori di parametro, SQL ServerSQL Server riutilizzerà il piano di esecuzione della prima esecuzione per la seconda e le successive esecuzioni, a meno che il piano non venga rimosso dalla cache dei piani.If an application executes a SQL statement with parameter markers multiple times, SQL ServerSQL Server will reuse the execution plan from the first execution for the second and subsequent executions (unless the plan ages from the plan cache). Il modello di preparazione/esecuzione presenta comunque i vantaggi seguenti:The prepare/execute model still has these benefits:

  • È più conveniente cercare un piano di esecuzione tramite un handle di identificazione che non utilizzare gli algoritmi per trovare una corrispondenza tra un'istruzione SQL e i piani di esecuzione esistenti.Finding an execution plan by an identifying handle is more efficient than the algorithms used to match an SQL statement to existing execution plans.
  • L'applicazione può controllare il momento della creazione del piano di esecuzione e del suo riutilizzo.The application can control when the execution plan is created and when it is reused.
  • Il modello di preparazione/esecuzione è utilizzabile con altri database, incluse le versioni precedenti di SQL ServerSQL Server.The prepare/execute model is portable to other databases, including earlier versions of SQL ServerSQL Server.

Analisi dei parametri Parameter Sniffing

Il termine "analisi dei parametri" si riferisce a un processo in base al quale SQL ServerSQL Server individua i valori dei parametri correnti durante la compilazione o ricompilazione e li passa a Query Optimizer in modo che possano essere usati per generare piani di esecuzione di query potenzialmente più efficienti."Parameter sniffing" refers to a process whereby SQL ServerSQL Server "sniffs" the current parameter values during compilation or recompilation, and passes it along to the Query Optimizer so that they can be used to generate potentially more efficient query execution plans.

I valori dei parametri vengono individuati durante la compilazione o ricompilazione per i seguenti tipi di batch:Parameter values are sniffed during compilation or recompilation for the following types of batches:

  • Stored procedureStored procedures
  • Query inviate tramite sp_executesqlQueries submitted via sp_executesql
  • Query preparatePrepared queries
Nota

Per le query che usano l'hint RECOMPILE, vengono individuati sia i valori dei parametri che i valori correnti delle variabili locali.For queries using the RECOMPILE hint, both parameter values and current values of local variables are sniffed. I valori individuati (dei parametri e delle variabili locali) sono quelli esistenti nella posizione all'interno del batch prima dell'istruzione con l'hint RECOMPILE.The values sniffed (of parameters and local variables) are those that exist at the place in the batch just before the statement with the RECOMPILE hint. In particolare, per i parametri, non vengono individuati i valori passati con la chiamata del batch.In particular, for parameters, the values that came along with the batch invocation call are not sniffed.

Elaborazione parallela di queryParallel Query Processing

In SQL ServerSQL Server è possibile eseguire query parallele, che consentono di ottimizzare l'esecuzione delle query e le operazioni sugli indici nei computer che dispongono di più microprocessori (CPU). SQL ServerSQL Server provides parallel queries to optimize query execution and index operations for computers that have more than one microprocessor (CPU). La possibilità di eseguire una query o un'operazione sugli indici in parallelo in SQL ServerSQL Server usando diversi thread di lavoro del sistema operativo assicura maggiore velocità ed efficienza.Because SQL ServerSQL Server can perform a query or index operation in parallel by using several operating system worker threads, the operation can be completed quickly and efficiently.

Durante l'ottimizzazione delle query, SQL ServerSQL Server ricerca le query o le operazioni sugli indici che potrebbero trarre vantaggio dall'esecuzione parallela.During query optimization, SQL ServerSQL Server looks for queries or index operations that might benefit from parallel execution. Nel piano di esecuzione di tali query SQL ServerSQL Server inserisce operatori di scambio per preparare la query all'esecuzione parallela.For these queries, SQL ServerSQL Server inserts exchange operators into the query execution plan to prepare the query for parallel execution. Un operatore di scambio è un operatore del piano di esecuzione della query responsabile della gestione dei processi, della ridistribuzione dei dati e del controllo di flusso.An exchange operator is an operator in a query execution plan that provides process management, data redistribution, and flow control. L'operatore di scambio include gli operatori logici Distribute Streams, Repartition Streamse Gather Streams come sottotipi, ognuno dei quali può essere incluso nell'output Showplan del piano di esecuzione parallela di una query.The exchange operator includes the Distribute Streams, Repartition Streams, and Gather Streams logical operators as subtypes, one or more of which can appear in the Showplan output of a query plan for a parallel query.

Dopo l'inserimento degli operatori di scambio, si ottiene un piano di esecuzione parallela della query.After exchange operators are inserted, the result is a parallel-query execution plan. Questo tipo di piano può usare più di un thread di lavoro.A parallel-query execution plan can use more than one worker thread. In un piano di esecuzione seriale, usato da una query non parallela, l'esecuzione è invece affidata a un solo thread di lavoro.A serial execution plan, used by a nonparallel query, uses only one worker thread for its execution. Il numero effettivo di thread di lavoro usati da una query parallela viene determinato al momento dell'inizializzazione del piano di esecuzione della query e dipende dalla complessità del piano e dal grado di parallelismo.The actual number of worker threads used by a parallel query is determined at query plan execution initialization and is determined by the complexity of the plan and the degree of parallelism. Il grado di parallelismo determina il numero massimo di CPU usate, ma non il numero di thread di lavoro usati.Degree of parallelism determines the maximum number of CPUs that are being used; it does not mean the number of worker threads that are being used. Il valore del grado di parallelismo viene impostato a livello del server e può essere modificato usando la stored procedure di sistema sp_configure.The degree of parallelism value is set at the server level and can be modified by using the sp_configure system stored procedure. Questo valore può essere sostituito per singole istruzioni di query o di indice specificando l'hint per la query MAXDOP o l'opzione di indice MAXDOP .You can override this value for individual query or index statements by specifying the MAXDOP query hint or MAXDOP index option.

Quando una delle condizioni seguenti è vera, Query Optimizer di SQL ServerSQL Server non usa un piano di esecuzione parallela per una query:The SQL ServerSQL Server Query Optimizer does not use a parallel execution plan for a query if any one of the following conditions is true:

  • Il costo dell'esecuzione seriale della query non è sufficientemente elevato da suggerire l'adozione di un piano alternativo di esecuzione parallela.The serial execution cost of the query is not high enough to consider an alternative, parallel execution plan.
  • Un piano di esecuzione seriale è considerato più veloce di ogni possibile piano di esecuzione parallela per la query in esame.A serial execution plan is considered faster than any possible parallel execution plan for the particular query.
  • La query contiene operatori scalari o relazionali che non possono essere eseguiti in parallelo.The query contains scalar or relational operators that cannot be run in parallel. Alcuni operatori possono richiedere l'esecuzione seriale di una sezione della query o dell'intero piano.Certain operators can cause a section of the query plan to run in serial mode, or the whole plan to run in serial mode.

Grado di parallelismo Degree of Parallelism

SQL ServerSQL Server rileva automaticamente il grado di parallelismo ottimale per ogni istanza di esecuzione parallela di una query o di operazione DDL sull'indice, automatically detects the best degree of parallelism for each instance of a parallel query execution or index data definition language (DDL) operation. utilizzando i criteri seguenti:It does this based on the following criteria:

  1. Esecuzione di SQL ServerSQL Server in un computer con più microprocessori o CPU, ad esempio un computer SMP (Symmetric Multiprocessing).Whether SQL ServerSQL Server is running on a computer that has more than one microprocessor or CPU, such as a symmetric multiprocessing computer (SMP).
    Solo i computer con più CPU possono utilizzare le query parallele.Only computers that have more than one CPU can use parallel queries.

  2. Disponibilità di un numero sufficiente di thread di lavoro.Whether sufficient worker threads are available.
    Per l'esecuzione di una query o di un'operazione su un indice è necessario un numero specifico di thread di lavoro.Each query or index operation requires a certain number of worker threads to execute. L'esecuzione di un piano parallelo richiede un numero di thread di lavoro maggiore rispetto all'esecuzione di un piano seriale e il numero di thread di lavoro necessari aumenta con il grado di parallelismo.Executing a parallel plan requires more worker threads than a serial plan, and the number of required worker threads increases with the degree of parallelism. Se non è possibile rispettare i requisiti di thread di lavoro del piano parallelo per un grado di parallelismo specifico, Motore di database di SQL ServerSQL Server Database Engine riduce automaticamente il grado di parallelismo o ignora completamente il piano parallelo nel contesto del carico di lavoro specificatoWhen the worker thread requirement of the parallel plan for a specific degree of parallelism cannot be satisfied, the Motore di database di SQL ServerSQL Server Database Engine decreases the degree of parallelism automatically or completely abandons the parallel plan in the specified workload context. ed esegue il piano seriale (un solo thread di lavoro).It then executes the serial plan (one worker thread).

  3. Tipo di query o di operazione sull'indice eseguita.The type of query or index operation executed.
    Le operazioni di creazione o ricompilazione di un indice o di eliminazione di un indice cluster e le query che utilizzano molte risorse CPU sono candidate ideali per un piano parallelo.Index operations that create or rebuild an index, or drop a clustered index and queries that use CPU cycles heavily are the best candidates for a parallel plan. Esempi di operazioni di questo tipo sono i join di tabelle di grandi dimensioni, le aggregazioni di ampia portata e gli ordinamenti di set di risultati estesi.For example, joins of large tables, large aggregations, and sorting of large result sets are good candidates. Nel caso di query semplici, spesso presenti nelle applicazioni di elaborazione delle transazioni, il coordinamento aggiuntivo necessario per eseguire una query in parallelo viene compensato dal potenziale miglioramento delle prestazioni.Simple queries, frequently found in transaction processing applications, find the additional coordination required to execute a query in parallel outweigh the potential performance boost. Per distinguere le query che possono trarre vantaggio dal parallelismo, Motore di database di SQL ServerSQL Server Database Engine confronta il costo stimato per l'esecuzione della query o dell'operazione sull'indice con il valore cost threshold for parallelism.To distinguish between queries that benefit from parallelism and those that do not benefit, The Motore di database di SQL ServerSQL Server Database Engine compares the estimated cost of executing the query or index operation with the cost threshold for parallelism value. È possibile, ma non consigliabile, modificare il valore predefinito (pari a 5) usando sp_configure.Although not recommended, users can change the default value of 5 using sp_configure.

  4. Presenza di un numero sufficiente di righe da elaborare.Whether there are a sufficient number of rows to process.
    Se Query Optimizer determina che il numero di righe di un flusso è troppo basso, non introduce gli operatori di scambio per la distribuzione delle righe.If the Query Optimizer determines that the number of rows is too low, it does not introduce exchange operators to distribute the rows. Gli operatori vengono pertanto eseguiti in modo seriale,Consequently, the operators are executed serially. evitando così le situazioni in cui il costo di avvio, distribuzione e coordinamento supera i vantaggi ottenuti tramite l'esecuzione parallela dell'operatore.Executing the operators in a serial plan avoids scenarios when the startup, distribution, and coordination costs exceed the gains achieved by parallel operator execution.

  5. Disponibilità di statistiche di distribuzione correnti.Whether current distribution statistics are available.
    Se il grado di parallelismo massimo non è disponibile, prima di annullare il piano parallelo vengono considerati i gradi inferiori.If the highest degree of parallelism is not possible, lower degrees are considered before the parallel plan is abandoned.
    Ad esempio, quando si crea un indice cluster in una vista, non è possibile valutare le statistiche di distribuzione perché l'indice cluster non esiste ancora.For example, when you create a clustered index on a view, distribution statistics cannot be evaluated, because the clustered index does not yet exist. In questo caso, Motore di database di SQL ServerSQL Server Database Engine non può fornire il grado di parallelismo massimo per l'operazione sull'indice.In this case, the Motore di database di SQL ServerSQL Server Database Engine cannot provide the highest degree of parallelism for the index operation. Alcuni operatori, ad esempio quelli relativi all'ordinamento e all'analisi, possono tuttavia trarre vantaggi dall'esecuzione parallela.However, some operators, such as sorting and scanning, can still benefit from parallel execution.

Nota

Le operazioni parallele sugli indici sono disponibili solo nelle edizioni Enterprise, Developer ed Evaluation di SQL ServerSQL Server.Parallel index operations are only available in SQL ServerSQL Server Enterprise, Developer, and Evaluation editions.

Al momento dell'esecuzione, Motore di database di SQL ServerSQL Server Database Engine determina se il carico di lavoro di sistema corrente e i dati di configurazione illustrati in precedenza consentono l'esecuzione parallela.At execution time, the Motore di database di SQL ServerSQL Server Database Engine determines whether the current system workload and configuration information previously described allow for parallel execution. Se l'esecuzione parallela è possibile, Motore di database di SQL ServerSQL Server Database Engine determina il numero ottimale di thread di lavoro e suddivide l'esecuzione del piano parallelo tra tali thread di lavoro.If parallel execution is warranted, the Motore di database di SQL ServerSQL Server Database Engine determines the optimal number of worker threads and spreads the execution of the parallel plan across those worker threads. Dal momento in cui viene avviata l'esecuzione parallela su più thread di lavoro di una query o di un'operazione sull'indice, viene usato lo stesso numero di thread di lavoro fino al completamento dell'operazione.When a query or index operation starts executing on multiple worker threads for parallel execution, the same number of worker threads is used until the operation is completed. Motore di database di SQL ServerSQL Server Database Engine riesamina il numero ottimale di thread di lavoro ogni volta che viene recuperato un piano di esecuzione dalla cache dei piani.The Motore di database di SQL ServerSQL Server Database Engine re-examines the optimal number of worker thread decisions every time an execution plan is retrieved from the plan cache. Ad esempio, l'esecuzione di una query può comportare l'uso di un piano seriale, un'esecuzione successiva della stessa query può richiedere un piano parallelo con tre thread di lavoro e una terza esecuzione può richiedere un piano parallelo con quattro thread di lavoro.For example, one execution of a query can result in the use of a serial plan, a later execution of the same query can result in a parallel plan using three worker threads, and a third execution can result in a parallel plan using four worker threads.

In un piano di esecuzione parallela della query, gli operatori insert, update e delete vengono eseguiti in modo seriale.In a parallel query execution plan, the insert, update, and delete operators are executed serially. La clausola WHERE di un'istruzione UPDATE o DELETE oppure la parte SELECT di un'istruzione INSERT possono tuttavia essere eseguite in parallelo.However, the WHERE clause of an UPDATE or a DELETE statement, or the SELECT part of an INSERT statement may be executed in parallel. Le modifiche apportate ai dati verranno quindi applicate in modo seriale al database.The actual data changes are then serially applied to the database.

I cursori statici e gestiti da keyset possono essere popolati tramite piani di esecuzione parallela.Static and keyset-driven cursors can be populated by parallel execution plans. La funzionalità dei cursori dinamici può invece essere implementata solo tramite l'esecuzione seriale.However, the behavior of dynamic cursors can be provided only by serial execution. Query Optimizer genera sempre un piano di esecuzione seriale per le query che fanno parte di un cursore dinamico.The Query Optimizer always generates a serial execution plan for a query that is part of a dynamic cursor.

Sostituzione dei gradi di parallelismoOverriding Degrees of Parallelism

È possibile usare l'opzione di configurazione del server max degree of parallelism (MAXDOP) (ALTER DATABASE SCOPED CONFIGURATION in Database SQLSQL Database) per limitare il numero di processori da usare per l'esecuzione del piano parallelo.You can use the max degree of parallelism (MAXDOP) server configuration option (ALTER DATABASE SCOPED CONFIGURATION on Database SQLSQL Database ) to limit the number of processors to use in parallel plan execution. L'opzione Massimo grado di parallelismo può essere ignorata per le singole istruzioni delle query e delle operazioni sugli indici specificando l'hint per le query MAXDOP o l'opzione per gli indici MAXDOP.The max degree of parallelism option can be overridden for individual query and index operation statements by specifying the MAXDOP query hint or MAXDOP index option. MAXDOP offre un maggiore controllo sulle singole query e operazioni sugli indici.MAXDOP provides more control over individual queries and index operations. Ad esempio, è possibile usare questa opzione per aumentare o diminuire il numero di processori dedicati a un'operazione sull'indice online.For example, you can use the MAXDOP option to control, by increasing or reducing, the number of processors dedicated to an online index operation. Ciò consente di bilanciare le risorse utilizzate per un'operazione sull'indice con quelle degli utenti simultanei.In this way, you can balance the resources used by an index operation with those of the concurrent users.

L'impostazione dell'opzione max degree of parallelism su 0 (impostazione predefinita) consente a SQL ServerSQL Server di usare tutti i processori disponibili fino a un massimo di 64 nell'esecuzione di piani paralleli.Setting the max degree of parallelism option to 0 (default) enables SQL ServerSQL Server to use all available processors up to a maximum of 64 processors in a parallel plan execution. Anche se SQL ServerSQL Server imposta una destinazione di runtime di 64 processori logici quando l'opzione MAXDOP è impostata su 0, è possibile impostare manualmente un valore diverso se necessario.Although SQL ServerSQL Server sets a runtime target of 64 logical processors when MAXDOP option is set to 0, a different value can be manually set if needed. L'impostazione di MAXDOP su 0 per query e indici consente a SQL ServerSQL Server di usare tutti i processori disponibili fino a un massimo di 64 per le query o gli indici specificati nell'esecuzione di piani paralleli.Setting MAXDOP to 0 for queries and indexes allows SQL ServerSQL Server to use all available processors up to a maximum of 64 processors for the given queries or indexes in a parallel plan execution. MAXDOP non è un valore imposto per tutte le query parallele, ma piuttosto un valore target provvisorio per tutte le query idonee per il parallelismo.MAXDOP is not an enforced value for all parallel queries, but rather a tentative target for all queries eligible for parallelism. Ciò significa che se ci sono thread di lavoro sufficienti disponibili in fase di esecuzione, una query può essere eseguita con un grado di parallelismo minore rispetto all'opzione di configurazione del server MAXDOP.This means that if not enough worker threads are available at runtime, a query may execute with a lower degree of parallelism than the MAXDOP server configuration option.

Fare riferimento a questo articolo del supporto tecnico Microsoft per informazioni sulle procedure consigliate per la configurazione di MAXDOP.Refer to this Microsoft Support Article for best practices on configuring MAXDOP.

Esempio di query parallelaParallel Query Example

Nella query seguente viene eseguito il conteggio del numero di ordini effettuati nel trimestre con inizio 1 aprile 2000. Per questi ordini, almeno uno degli articoli è stato ricevuto dal cliente successivamente alla data prevista.The following query counts the number of orders placed in a specific quarter, starting on April 1, 2000, and in which at least one line item of the order was received by the customer later than the committed date. La query indica il numero totale di tali ordini raggruppati per priorità di ordine e disposti in ordine di priorità crescente.This query lists the count of such orders grouped by each order priority and sorted in ascending priority order.

Nell'esempio seguente vengono utilizzati nomi di tabelle e colonne fittizi.This example uses theoretical table and column names.

SELECT o_orderpriority, COUNT(*) AS Order_Count
FROM orders
WHERE o_orderdate >= '2000/04/01'
   AND o_orderdate < DATEADD (mm, 3, '2000/04/01')
   AND EXISTS
         (
          SELECT *
            FROM    lineitem
            WHERE l_orderkey = o_orderkey
               AND l_commitdate < l_receiptdate
         )
   GROUP BY o_orderpriority
   ORDER BY o_orderpriority

Si supponga che per le tabelle lineitem e orders vengano definiti gli indici seguenti:Assume the following indexes are defined on the lineitem and orders tables:

CREATE INDEX l_order_dates_idx 
   ON lineitem
      (l_orderkey, l_receiptdate, l_commitdate, l_shipdate)

CREATE UNIQUE INDEX o_datkeyopr_idx
   ON ORDERS
      (o_orderdate, o_orderkey, o_custkey, o_orderpriority)

Di seguito viene riportato un possibile piano parallelo generato per la query indicata in precedenza:Here is one possible parallel plan generated for the query previously shown:

|--Stream Aggregate(GROUP BY:([ORDERS].[o_orderpriority])
                  DEFINE:([Expr1005]=COUNT(*)))
    |--Parallelism(Gather Streams, ORDER BY:
                  ([ORDERS].[o_orderpriority] ASC))
         |--Stream Aggregate(GROUP BY:
                  ([ORDERS].[o_orderpriority])
                  DEFINE:([Expr1005]=Count(*)))
              |--Sort(ORDER BY:([ORDERS].[o_orderpriority] ASC))
                   |--Merge Join(Left Semi Join, MERGE:
                  ([ORDERS].[o_orderkey])=
                        ([LINEITEM].[l_orderkey]),
                  RESIDUAL:([ORDERS].[o_orderkey]=
                        [LINEITEM].[l_orderkey]))
                        |--Sort(ORDER BY:([ORDERS].[o_orderkey] ASC))
                        |    |--Parallelism(Repartition Streams,
                           PARTITION COLUMNS:
                           ([ORDERS].[o_orderkey]))
                        |         |--Index Seek(OBJECT:
                     ([tpcd1G].[dbo].[ORDERS].[O_DATKEYOPR_IDX]),
                     SEEK:([ORDERS].[o_orderdate] >=
                           Apr  1 2000 12:00AM AND
                           [ORDERS].[o_orderdate] <
                           Jul  1 2000 12:00AM) ORDERED)
                        |--Parallelism(Repartition Streams,
                     PARTITION COLUMNS:
                     ([LINEITEM].[l_orderkey]),
                     ORDER BY:([LINEITEM].[l_orderkey] ASC))
                             |--Filter(WHERE:
                           ([LINEITEM].[l_commitdate]<
                           [LINEITEM].[l_receiptdate]))
                                  |--Index Scan(OBJECT:
         ([tpcd1G].[dbo].[LINEITEM].[L_ORDER_DATES_IDX]), ORDERED)

parallel_plan Piano di query con DOP 4, include un join di due tabelleparallel_plan Query plan with DOP 4, involves a two-table join

Nella figura è illustrato un piano di Query Optimizer eseguito con grado di parallelismo 4 e con un join a due tabelle.The illustration shows a Query Optimizer plan executed with a degree of parallelism equal to 4 and involving a two-table join.

Il piano parallelo contiene tre operatori di parallelismo.The parallel plan contains three parallelism operators. Sia l'operatore Index Seek dell'indice o_datkey_ptr che l'operatore Index Scan dell'indice l_order_dates_idx vengono eseguiti in parallelo.Both the Index Seek operator of the o_datkey_ptr index and the Index Scan operator of the l_order_dates_idx index are performed in parallel. In questo modo vengono creati diversi flussi esclusivi.This produces several exclusive streams. Ciò può essere determinato dagli operatori di parallelismo più vicini sopra gli operatori Index Scan e Index Seek, rispettivamente.This can be determined from the nearest Parallelism operators above the Index Scan and Index Seek operators, respectively. Entrambi gli operatori eseguono la ripartizione del tipo di scambio,Both are repartitioning the type of exchange. ovvero ridistribuiscono i dati tra i flussi creando nell'output lo stesso numero di flussi presenti nell'input.That is, they are just reshuffling data among the streams and producing the same number of streams on their output as they have on their input. Questo numero di flussi equivale al grado di parallelismo.This number of streams is equal to the degree of parallelism.

L'operatore di parallelismo sopra l'operatore Index Scan l_order_dates_idx esegue la ripartizione dei flussi di input usando il valore di L_ORDERKEY come chiave.The parallelism operator above the l_order_dates_idx Index Scan operator is repartitioning its input streams using the value of L_ORDERKEY as a key. In questo modo, lo stesso valore di L_ORDERKEY viene incluso nello stesso flusso di output.In this way, the same values of L_ORDERKEY end up in the same output stream. Allo stesso tempo, i flussi di output mantengono l'ordine della colonna L_ORDERKEY per soddisfare il requisito di input dell'operatore Merge Join.At the same time, output streams maintain the order on the L_ORDERKEY column to meet the input requirement of the Merge Join operator.

L'operatore di parallelismo sopra l'operatore Index Seek esegue la ripartizione dei flussi di input utilizzando il valore di O_ORDERKEY.The parallelism operator above the Index Seek operator is repartitioning its input streams using the value of O_ORDERKEY. Poiché l'input non viene ordinato nei valori della colonna O_ORDERKEY , che rappresenta la colonna di join dell'operatore Merge Join , l'operatore Sort tra gli operatori di parallelismo e Merge Join assicura che l'input venga ordinato per l'operatore Merge Join nelle colonne di join.Because its input is not sorted on the O_ORDERKEY column values and this is the join column in the Merge Join operator, the Sort operator between the parallelism and Merge Join operators make sure that the input is sorted for the Merge Join operator on the join columns. Analogamente all'operatore Merge Join, l'operatore Sort viene eseguito in parallelo.The Sort operator, like the Merge Join operator, is performed in parallel.

L'operatore di parallelismo superiore riunisce i risultati di numerosi flussi in un singolo flusso.The topmost parallelism operator gathers results from several streams into a single stream. Le aggregazioni parziali eseguite dall'operatore Stream Aggregate sottostante all'operatore di parallelismo vengono quindi riunite in un singolo valore SUM per ogni valore diverso di O_ORDERPRIORITY nell'operatore Stream Aggregate sopra l'operatore di parallelismo.Partial aggregations performed by the Stream Aggregate operator below the parallelism operator are then accumulated into a single SUM value for each different value of the O_ORDERPRIORITY in the Stream Aggregate operator above the parallelism operator. Poiché include due segmenti di scambio con grado di parallelismo 4, questo piano usa otto thread di lavoro.Because this plan has two exchange segments, with degree of parallelism equal to 4, it uses eight worker threads.

Operazioni parallele sugli indiciParallel Index Operations

I piani di query compilati ai fini della creazione o della ricompilazione di un indice, oppure dell'eliminazione di un indice cluster, consentono in computer con più microprocessori di eseguire operazioni parallele con più thread di lavoro.The query plans built for the index operations that create or rebuild an index, or drop a clustered index, allow for parallel, multi-worker threaded operations on computers that have multiple microprocessors.

Nota

Le operazioni parallele sugli indici sono disponibili solo nell'edizione Enterprise, a partire da SQL Server 2008SQL Server 2008.Parallel index operations are only available in Enterprise Edition, starting with SQL Server 2008SQL Server 2008.

SQL ServerSQL Server usa gli stessi algoritmi utilizzati per altre query per determinare il grado di parallelismo, ovvero il numero totale di singoli thread lavoro da eseguire, per le operazioni sugli indici. uses the same algorithms to determine the degree of parallelism (the total number of separate worker threads to run) for index operations as it does for other queries. Il grado massimo di parallelismo per un'operazione sugli indici dipende dal valore impostato per l'opzione di configurazione del server Massimo grado di parallelismo .The maximum degree of parallelism for an index operation is subject to the max degree of parallelism server configuration option. È possibile ignorare il valore dell'opzione Massimo grado di parallelismo per singole operazioni sull'indice impostando l'opzione per gli indici MAXDOP nelle istruzioni CREATE INDEX, ALTER INDEX, DROP INDEX e ALTER TABLE.You can override the max degree of parallelism value for individual index operations by setting the MAXDOP index option in the CREATE INDEX, ALTER INDEX, DROP INDEX, and ALTER TABLE statements.

Quando Motore di database di SQL ServerSQL Server Database Engine compila un piano di esecuzione dell'indice, il numero di operazioni parallele viene impostato sul valore più basso tra i seguenti:When the Motore di database di SQL ServerSQL Server Database Engine builds an index execution plan, the number of parallel operations is set to the lowest value from among the following:

  • Il numero di microprocessori, o CPU, del computer.The number of microprocessors, or CPUs in the computer.
  • Il numero specificato per l'opzione di configurazione del server Massimo grado di parallelismo.The number specified in the max degree of parallelism server configuration option.
  • Il numero di CPU che non hanno già superato una determinata soglia di carico di elaborazione per l'esecuzione dei thread di lavoro di SQL ServerSQL Server.The number of CPUs not already over a threshold of work performed for SQL ServerSQL Server worker threads.

Ad esempio, in un computer con 8 CPU in cui, tuttavia, l'opzione max degree of parallelism è impostata su 6, per un'operazione sugli indici verranno generati al massimo 6 thread di lavoro paralleli.For example, on a computer that has eight CPUs, but where max degree of parallelism is set to 6, no more than six parallel worker threads are generated for an index operation. Se 5 CPU del computer superano la soglia di carico di elaborazione per SQL ServerSQL Server al momento della compilazione del piano di esecuzione per l'indice, il piano userà solo 3 thread di lavoro paralleli.If five of the CPUs in the computer exceed the threshold of SQL ServerSQL Server work when an index execution plan is built, the execution plan specifies only three parallel worker threads.

Le fasi principali di un'operazione parallela sugli indici includono quanto segue:The main phases of a parallel index operation include the following:

  • Un thread di lavoro di coordinamento esegue rapidamente l'analisi casuale della tabella per produrre una stima della distribuzione delle chiavi dell'indice.A coordinating worker thread quickly and randomly scans the table to estimate the distribution of the index keys. Il thread di lavoro di coordinamento stabilisce i limiti delle chiavi in base ai quali verrà creato un numero di intervalli di chiavi equivalente al grado di operazioni parallele. Ogni intervallo di chiavi copre un numero di righe simile.The coordinating worker thread establishes the key boundaries that will create a number of key ranges equal to the degree of parallel operations, where each key range is estimated to cover similar numbers of rows. Se ad esempio la tabella include 4 milioni di righe e il grado di parallelismo è 4, il thread di lavoro di coordinamento determinerà i valori di chiave che delimitano 4 set di righe che includono 1 milione di righe ciascuno.For example, if there are four million rows in the table and the degree of parallelism is 4, the coordinating worker thread will determine the key values that delimit four sets of rows with 1 million rows in each set. Se non è possibile stabilire un numero sufficiente di intervalli di chiavi per utilizzare tutte le CPU, il grado di parallelismo viene ridotto di conseguenza.If enough key ranges cannot be established to use all CPUs, the degree of parallelism is reduced accordingly.
  • Il thread di lavoro di coordinamento recapita un numero di thread di lavoro pari al grado di operazioni parallele e attende che tali thread di lavoro completino le rispettive operazioni.The coordinating worker thread dispatches a number of worker threads equal to the degree of parallel operations and waits for these worker threads to complete their work. Ogni thread di lavoro esegue l'analisi della tabella di base usando un filtro che recupera solo le righe con valori di chiave inclusi nell'intervallo assegnato al thread di lavoro.Each worker thread scans the base table using a filter that retrieves only rows with key values within the range assigned to the worker thread. Ogni thread di lavoro compila una struttura di indice per le righe nel rispettivo intervallo di chiavi.Each worker thread builds an index structure for the rows in its key range. Nel caso di indici partizionati ogni thread di lavoro compila un numero specifico di partizioni.In the case of a partitioned index, each worker thread builds a specified number of partitions. Le partizioni non sono condivise tra i thread di lavoro.Partitions are not shared among worker threads.
  • Quando tutti i thread di lavoro paralleli sono stati completati, il thread di lavoro di coordinamento connette le sottounità dell'indice in un unico indice.After all the parallel worker threads have completed, the coordinating worker thread connects the index subunits into a single index. Questa fase viene eseguita solo nelle operazioni sugli indici offline.This phase applies only to offline index operations.

Singole istruzioni CREATE TABLE o ALTER TABLE possono avere più vincoli che richiedono la creazione di un indice.Individual CREATE TABLE or ALTER TABLE statements can have multiple constraints that require that an index be created. Le operazioni di creazione dell'indice vengono eseguite in serie, anche se in un computer con più CPU ogni singola operazione può essere eseguita in parallelo.These multiple index creation operations are performed in series, although each individual index creation operation may be a parallel operation on a computer that has multiple CPUs.

Architettura delle query distribuiteDistributed Query Architecture

Microsoft SQL ServerSQL Server supporta due metodi per fare riferimento a origini dati OLE DB eterogenee nelle istruzioni Transact-SQL:Microsoft SQL ServerSQL Server supports two methods for referencing heterogeneous OLE DB data sources in Transact-SQL statements:

  • Nomi di server collegatiLinked server names
    Per assegnare il nome di un server a un'origine dei dati OLE DB vengono usate le stored procedure di sistema sp_addlinkedserver e sp_addlinkedsrvlogin .The system stored procedures sp_addlinkedserver and sp_addlinkedsrvlogin are used to give a server name to an OLE DB data source. Per fare riferimento agli oggetti di server collegati nelle istruzioni Transact-SQL, è possibile usare nomi in quattro parti.Objects in these linked servers can be referenced in Transact-SQL statements using four-part names. Ad esempio, se si definisce il nome del server collegato DeptSQLSrvr per un'altra istanza di SQL ServerSQL Server, l'istruzione seguente fa riferimento a una tabella in tale server:For example, if a linked server name of DeptSQLSrvr is defined against another instance of SQL ServerSQL Server, the following statement references a table on that server:

    SELECT JobTitle, HireDate 
    FROM DeptSQLSrvr.AdventureWorks2014.HumanResources.Employee;
    

    È anche possibile specificare il nome del server collegato in un'istruzione OPENQUERY per aprire un set di righe dall'origine dei dati OLE DB.The linked server name can also be specified in an OPENQUERY statement to open a rowset from the OLE DB data source. Successivamente, è possibile inserire i riferimenti a tale set di righe nelle istruzioni Transact-SQL in base alle stesse modalità usate per i riferimenti a una tabella.This rowset can then be referenced like a table in Transact-SQL statements.

  • Nomi di connettore ad hocAd hoc connector names
    Nel caso di un numero limitato di riferimenti a un'origine dei dati, nella funzione OPENROWSET o OPENDATASOURCE vengono specificate le informazioni necessarie per la connessione al server collegato.For infrequent references to a data source, the OPENROWSET or OPENDATASOURCE functions are specified with the information needed to connect to the linked server. In seguito, sarà possibile fare riferimento a tale set di righe nelle istruzioni Transact-SQL in base alle stesse modalità usate per i riferimenti a una tabella:The rowset can then be referenced the same way a table is referenced in Transact-SQL statements:

    SELECT *
    FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
          'c:\MSOffice\Access\Samples\Northwind.mdb';'Admin';'';
          Employees);
    

SQL ServerSQL Server utilizza OLE DB per la comunicazione tra il motore relazionale e il motore di archiviazione. uses OLE DB to communicate between the relational engine and the storage engine. Il motore relazionale suddivide ogni istruzione Transact-SQL in una serie di operazioni su set di righe OLE DB semplici, aperti dal motore di archiviazione nelle tabelle di base.The relational engine breaks down each Transact-SQL statement into a series of operations on simple OLE DB rowsets opened by the storage engine from the base tables. Pertanto, il motore relazionale può aprire inoltre set di righe OLE DB semplici in qualsiasi origine dei dati OLE DB.This means the relational engine can also open simple OLE DB rowsets on any OLE DB data source.
oledb_storageoledb_storage
Il motore relazionale utilizza l'API OLE DB per aprire i set di righe nei server collegati, recuperare le righe e gestire le transazioni.The relational engine uses the OLE DB application programming interface (API) to open the rowsets on linked servers, fetch the rows, and manage transactions.

Per ogni origine dei dati OLE DB accessibile come server collegato, è necessario un provider OLE DB nel server che esegue SQL ServerSQL Server.For each OLE DB data source accessed as a linked server, an OLE DB provider must be present on the server running SQL ServerSQL Server. La serie di operazioni Transact-SQL che è possibile usare per un'origine dei dati OLE DB specifica dipende dalle funzionalità del provider OLE DB.The set of Transact-SQL operations that can be used against a specific OLE DB data source depends on the capabilities of the OLE DB provider.

Per ogni istanza di SQL ServerSQL Server, i membri del ruolo predefinito del server sysadmin possono abilitare o disabilitare l'uso di nomi di connettore ad hoc per un provider OLE DB tramite la proprietà DisallowAdhocAccess di SQL ServerSQL Server.For each instance of SQL ServerSQL Server, members of the sysadmin fixed server role can enable or disable the use of ad-hoc connector names for an OLE DB provider using the SQL ServerSQL Server DisallowAdhocAccess property. Quando l'accesso ad hoc è disabilitato, qualsiasi utente collegato a tale istanza può eseguire istruzioni SQL contenenti nomi di connettore ad hoc che fanno riferimento a qualsiasi origine dei dati in rete accessibile tramite tale provider OLE DB.When ad-hoc access is enabled, any user logged on to that instance can execute SQL statements containing ad-hoc connector names, referencing any data source on the network that can be accessed using that OLE DB provider. Per controllare l'accesso alle origini dei dati, i membri del ruolo sysadmin possono disabilitare l'accesso ad hoc per i provider OLE DB corrispondenti, limitando in tal modo l'accesso da parte degli utenti alle sole origini dei dati a cui viene fatto riferimento dai nomi dei server collegati definiti dagli amministratori.To control access to data sources, members of the sysadmin role can disable ad-hoc access for that OLE DB provider, thereby limiting users to only those data sources referenced by linked server names defined by the administrators. Per impostazione predefinita, l'accesso ad hoc è abilitato per il provider OLE DB di SQL ServerSQL Server e disabilitato per tutti gli altri provider OLE DB.By default, ad-hoc access is enabled for the SQL ServerSQL Server OLE DB provider, and disabled for all other OLE DB providers.

Le query distribuite consentono agli utenti di accedere a un'altra origine dati (ad esempio file, origini dati non relazionali come Active Directory e così via) tramite il contesto di sicurezza dell'account di Microsoft Windows usato per l'esecuzione del servizio SQL ServerSQL Server.Distributed queries can allow users to access another data source (for example, files, non-relational data sources such as Active Directory, and so on) using the security context of the Microsoft Windows account under which the SQL ServerSQL Server service is running. SQL ServerSQL Server rappresenta l'account di accesso appropriato nel caso degli account di accesso di Windows, ma non per gli account di accesso di SQL ServerSQL Server. impersonates the login appropriately for Windows logins; however, that is not possible for SQL ServerSQL Server logins. In tal modo modo, è possibile che l'utente di una query distribuita acceda a un'altra origine dei dati per cui non dispone delle autorizzazioni necessarie, ma l'account utilizzato per l'esecuzione del servizio SQL ServerSQL Server dispone di tali autorizzazioni.This can potentially allow a distributed query user to access another data source for which they do not have permissions, but the account under which the SQL ServerSQL Server service is running does have permissions. Per definire gli account di accesso specifici autorizzati per l'accesso al server collegato corrispondente, usare la stored procedure sp_addlinkedsrvlogin .Use sp_addlinkedsrvlogin to define the specific logins that are authorized to access the corresponding linked server. Poiché tale controllo non è disponibile per i nomi ad hoc, prestare attenzione quando si attiva l'accesso ad hoc in un provider OLE DB.This control is not available for ad-hoc names, so use caution in enabling an OLE DB provider for ad-hoc access.

Quando possibile, SQL ServerSQL Server esegue il push delle operazioni relazionali quali join, restrizioni, proiezioni, ordinamenti e operazioni su gruppi all'origine dati OLE DB.When possible, SQL ServerSQL Server pushes relational operations such as joins, restrictions, projections, sorts, and group by operations to the OLE DB data source. SQL ServerSQL Server non analizza per impostazione predefinita la tabella di base in SQL ServerSQL Server e non esegue operazioni relazionali in autonomia. does not default to scanning the base table into SQL ServerSQL Server and performing the relational operations itself. SQL ServerSQL Server esegue query sul provider OLE DB per determinare il livello di grammatica SQL supportata e, in base a tali informazioni, esegue il push al provider del maggior numero possibile di operazioni relazionali. queries the OLE DB provider to determine the level of SQL grammar it supports, and, based on that information, pushes as many relational operations as possible to the provider.

In SQL ServerSQL Server è disponibile un meccanismo in base al quale il provider OLE DB restituisce statistiche che indicano la modalità di distribuzione dei valori di chiave all'interno dell'origine dati OLE DB. SQL ServerSQL Server specifies a mechanism for an OLE DB provider to return statistics indicating how key values are distributed within the OLE DB data source. In questo modo, Query Optimizer di SQL ServerSQL Server può analizzare in modo più approfondito lo schema dri dati nell'origine dati in base ai requisiti di ogni istruzione SQL, generando con maggiore efficienza piani di esecuzione ottimali.This lets the SQL ServerSQL Server Query Optimizer better analyze the pattern of data in the data source against the requirements of each SQL statement, increasing the ability of the Query Optimizer to generate optimal execution plans.

Miglioramenti apportati all'elaborazione di query su tabelle e indici partizionatiQuery Processing Enhancements on Partitioned Tables and Indexes

In SQL Server 2008SQL Server 2008 sono state migliorate le prestazioni di elaborazione delle query su tabelle partizionate per molti piani paralleli, modificate le modalità di rappresentazione dei piani seriali e paralleli, nonché ottimizzate le informazioni relative al partizionamento fornite nei piani di esecuzione sia nella fase di compilazione che di esecuzione. SQL Server 2008SQL Server 2008 improved query processing performance on partitioned tables for many parallel plans, changes the way parallel and serial plans are represented, and enhanced the partitioning information provided in both compile-time and run-time execution plans. In questo argomento vengono descritti i miglioramenti apportati, viene spiegato come interpretare i piani di esecuzione delle query relativi a tabelle e indici partizionati e vengono fornite le procedure consigliate per migliorare le prestazioni delle query su oggetti partizionati.This topic describes these improvements, provides guidance on how to interpret the query execution plans of partitioned tables and indexes, and provides best practices for improving query performance on partitioned objects.

Nota

Il partizionamento di indici e tabelle è supportato solo nelle edizioni Enterprise, Developer ed Evaluation di SQL ServerSQL Server.Partitioned tables and indexes are supported only in the SQL ServerSQL Server Enterprise, Developer, and Evaluation editions.

Nuova operazione di ricerca con riconoscimento delle partizioniNew Partition-Aware Seek Operation

In SQL ServerSQL Server, la rappresentazione interna di una tabella partizionata viene modificata in modo che la tabella sia visibile all'elaboratore di query come indice multicolonna con PartitionID come colonna iniziale.In SQL ServerSQL Server, the internal representation of a partitioned table is changed so that the table appears to the query processor to be a multicolumn index with PartitionID as the leading column. PartitionID è una colonna calcolata nascosta usata internamente per rappresentare il valore ID della partizione che contiene una riga specifica.PartitionID is a hidden computed column used internally to represent the ID of the partition containing a specific row. Ad esempio, si supponga che la tabella T, definita come T(a, b, c), venga partizionata in base alla colonna A e includa un indice cluster nella colonna B.For example, assume the table T, defined as T(a, b, c), is partitioned on column a, and has a clustered index on column b. In SQL ServerSQL Server questa tabella partizionata viene considerata internamente come una tabella non partizionata caratterizzata dallo schema T(PartitionID, a, b, c) e con un indice cluster sulla chiave composta (PartitionID, b).In SQL ServerSQL Server, this partitioned table is treated internally as a nonpartitioned table with the schema T(PartitionID, a, b, c) and a clustered index on the composite key (PartitionID, b). In questo modo Query Optimizer è in grado di eseguire operazioni di ricerca basate su PartitionID su qualsiasi tabella o indice partizionato.This allows the Query Optimizer to perform seek operations based on PartitionID on any partitioned table or index.

L'eliminazione della partizione viene ora eseguita durante tale operazione di ricerca.Partition elimination is now done in this seek operation.

In addition, the Query Optimizer is extended so that a seek or scan operation with one condition can be done on PartitionID (come colonna iniziale logica) e su altre colonne chiave indice e quindi un'operazione di ricerca di secondo livello, con una condizione diversa, su una o più colonne aggiuntive, per ogni valore distinto che soddisfa la qualificazione per l'operazione di ricerca di primo livello.In addition, the Query Optimizer is extended so that a seek or scan operation with one condition can be done on PartitionID (as the logical leading column) and possibly other index key columns, and then a second-level seek, with a different condition, can be done on one or more additional columns, for each distinct value that meets the qualification for the first-level seek operation. Questa operazione, denominata skip scan consente a Query Optimizer di eseguire un'operazione di ricerca o di analisi basata su un unica condizione per determinare le partizioni a cui eseguire l'accesso e un'operazione Index Scan di secondo livello all'interno di tale operatore per restituire le righe delle partizioni che soddisfano una condizione diversa.That is, this operation, called a skip scan, allows the Query Optimizer to perform a seek or scan operation based on one condition to determine the partitions to be accessed and a second-level index seek operation within that operator to return rows from these partitions that meet a different condition. Si consideri ad esempio la query seguente.For example, consider the following query.

SELECT * FROM T WHERE a < 10 and b = 2;

Per questo esempio si supponga che la tabella T, definita come T(a, b, c), venga partizionata in base alla colonna A e includa un indice cluster nella colonna B.For this example, assume that table T, defined as T(a, b, c), is partitioned on column a, and has a clustered index on column b. I limiti delle partizione per la tabella T sono definiti dalla funzione di partizione seguente:The partition boundaries for table T are defined by the following partition function:

CREATE PARTITION FUNCTION myRangePF1 (int) AS RANGE LEFT FOR VALUES (3, 7, 10);

Per risolvere la query, Query Processor esegue un'operazione di ricerca di primo livello per individuare tutte le partizioni contenenti righe che soddisfano la condizione T.a < 10.To solve the query, the query processor performs a first-level seek operation to find every partition that contains rows that meet the condition T.a < 10. In tal modo vengono identificate le partizioni a cui effettuare l'accesso.This identifies the partitions to be accessed. All'interno di ciascuna partizione identificata, viene quindi eseguita una ricerca di secondo livello nell'indice cluster della colonna B per individuare le righe che soddisfano le condizioni T.b = 2 e T.a < 10.Within each partition identified, the processor then performs a second-level seek into the clustered index on column b to find the rows that meet the condition T.b = 2 and T.a < 10.

Nell'illustrazione seguente è riportata una rappresentazione logica dell'operazione di "skip scan".The following illustration is a logical representation of the skip scan operation. Include la tabella T con i dati nelle colonne a e b.It shows table T with data in columns a and b. Le partizioni sono numerate da 1 a 4 e i limiti delle partizioni sono contraddistinti da righe verticali tratteggiate.The partitions are numbered 1 through 4 with the partition boundaries shown by dashed vertical lines. In seguito a un'operazione di ricerca di primo livello eseguita sulle partizioni (non riportata nell'illustrazione) è stato determinato che le partizioni 1, 2 e 3 soddisfano la condizione di ricerca prevista dal partizionamento definito per la tabella e il predicato sulla colonna a,A first-level seek operation to the partitions (not shown in the illustration) has determined that partitions 1, 2, and 3 meet the seek condition implied by the partitioning defined for the table and the predicate on column a. ovvero T.a < 10.That is, T.a < 10. Il percorso attraversato dalla parte di ricerca di secondo livello dell'operazione di skip scan è illustrata dalla linea curva.The path traversed by the second-level seek portion of the skip scan operation is illustrated by the curved line. In pratica, l'operazione di skip scan cerca in ciascuna di queste partizioni le righe che soddisfano la condizione b = 2.Essentially, the skip scan operation seeks into each of these partitions for rows that meet the condition b = 2. Il costo totale dell'operazione di skip scan equivale a quello di tre operazioni Index Seek distinte.The total cost of the skip scan operation is the same as that of three separate index seeks.

skip_scan

Visualizzazione di informazioni sul partizionamento nei piani di esecuzione delle queryDisplaying Partitioning Information in Query Execution Plans

È possibile esaminare i piani di esecuzione delle query su tabelle e indici partizionati usando le istruzioni SET di Transact-SQL SET SHOWPLAN_XML o SET STATISTICS XML oppure l'output del piano di esecuzione grafico restituito in SQL ServerSQL Server Management Studio.The execution plans of queries on partitioned tables and indexes can be examined by using the Transact-SQL SET statements SET SHOWPLAN_XML or SET STATISTICS XML, or by using the graphical execution plan output in SQL ServerSQL Server Management Studio. È ad esempio possibile visualizzare il piano di esecuzione della fase di compilazione facendo clic su Visualizza piano di esecuzione stimato sulla barra degli strumenti dell'editor di query e il piano della fase di esecuzione facendo clic su Includi piano di esecuzione effettivo.For example, you can display the compile-time execution plan by clicking Display Estimated Execution Plan on the Query Editor toolbar and the run-time plan by clicking Include Actual Execution Plan.

Questi strumenti consentono di verificare le informazioni seguenti:Using these tools, you can ascertain the following information:

  • Operazioni come scans, seeks, inserts, updates, mergese deletes che accedono alle tabelle partizionate o agli indici.The operations such as scans, seeks, inserts, updates, merges, and deletes that access partitioned tables or indexes.
  • Partizioni a cui viene effettuato l'accesso tramite la query.The partitions accessed by the query. Ad esempio, il totale delle partizioni e gli intervalli relativi alle partizioni contigue a cui viene effettuato l'accesso sono disponibili nei piani di esecuzione della fase di esecuzione.For example, the total count of partitions accessed and the ranges of contiguous partitions that are accessed are available in run-time execution plans.
  • Utilizzo dell'operazione di skip scan in un'operazione di ricerca o analisi per recuperare dati da una o più partizioni.When the skip scan operation is used in a seek or scan operation to retrieve data from one or more partitions.

Miglioramenti apportati alle informazioni sulle partizioniPartition Information Enhancements

SQL ServerSQL Server fornisce informazioni migliorate sul partizionamento per i piani di esecuzione sia della fase di compilazione che della fase di esecuzione. provides enhanced partitioning information for both compile-time and run-time execution plans. I piani di esecuzione includono ora le informazioni seguenti:Execution plans now provide the following information:

  • Un attributo Partitioned facoltativo per indicare che su una tabella partizionata viene eseguito un operatore, ad esempio seek, scan, insert, update, mergeo delete.An optional Partitioned attribute that indicates that an operator, such as a seek, scan, insert, update, merge, or delete, is performed on a partitioned table.
  • Un elemento SeekPredicateNew nuovo con un sottoelemento SeekKeys che include PartitionID come colonna chiave di indice iniziale e condizioni di filtro che specificano ricerche di intervallo su PartitionID.A new SeekPredicateNew element with a SeekKeys subelement that includes PartitionID as the leading index key column and filter conditions that specify range seeks on PartitionID. La presenza di due sottoelementi SeekKeys indica che su PartitionID viene usata un'operazione di skip scan.The presence of two SeekKeys subelements indicates that a skip scan operation on PartitionID is used.
  • Informazioni di riepilogo che includono il totale delle partizioni a cui viene effettuato l'accesso.Summary information that provides a total count of the partitions accessed. Queste informazioni sono disponibili solo nei piani della fase di esecuzione.This information is available only in run-time plans.

Per illustrare la modalità di visualizzazione di queste informazioni nell'output del piano di esecuzione grafico e nell'output di Showplan XML, considerare la query seguente sulla tabella partizionata fact_sales.To demonstrate how this information is displayed in both the graphical execution plan output and the XML Showplan output, consider the following query on the partitioned table fact_sales. Questa query implica l'aggiornamento dei dati in due partizioni.This query updates data in two partitions.

UPDATE fact_sales
SET quantity = quantity * 2
WHERE date_id BETWEEN 20080802 AND 20080902;

Nella figura seguente sono illustrate le proprietà dell'operatore Clustered Index Seek nel piano di esecuzione della fase di compilazione per questa query.The following illustration shows the properties of the Clustered Index Seek operator in the compile-time execution plan for this query. Per visualizzare la definizione della tabella fact_sales e la definizione della partizione, vedere la sezione "Esempio" in questo argomento.To view the definition of the fact_sales table and the partition definition, see "Example" in this topic.

clustered_index_seek

Attributo PartitionedPartitioned Attribute

Quando su una tabella o un indice partizionato si esegue un operatore quale Index Seek , l'attributo Partitioned viene incluso sia nel piano della fase di compilazione che in quello della fase di esecuzione ed è impostato su True (1).When an operator such as an Index Seek is executed on a partitioned table or index, the Partitioned attribute appears in the compile-time and run-time plan and is set to True (1). L'attributo non viene visualizzato quando è impostato su False (0).The attribute does not display when it is set to False (0).

L'attributo Partitioned può essere visualizzato negli operatori fisici e logici seguenti:The Partitioned attribute can appear in the following physical and logical operators:

  • Table Scan
  • Index Scan
  • Index Seek
  • Insert
  • Update
  • Delete
  • Merge

Come illustrato nella figura precedente, questo attributo viene visualizzato nelle proprietà dell'operatore in cui è definito.As shown in the previous illustration, this attribute is displayed in the properties of the operator in which it is defined. Nell'output di Showplan XML, questo attributo è indicato come Partitioned="1" nel nodo RelOp dell'operatore nel quale è definito.In the XML Showplan output, this attribute appears as Partitioned="1" in the RelOp node of the operator in which it is defined.

Nuovo predicato SeekNew Seek Predicate

Nell'output di Showplan XML l'elemento SeekPredicateNew è visualizzato nell'operatore nel quale è definito.In XML Showplan output, the SeekPredicateNew element appears in the operator in which it is defined. Può contenere fino a due occorrenze del sottoelemento SeekKeys .It can contain up to two occurrences of the SeekKeys sub-element. Il primo elemento SeekKeys specifica l'operazione di ricerca di primo livello a livello di ID della partizione dell'indice logico.The first SeekKeys item specifies the first-level seek operation at the partition ID level of the logical index. Tale ricerca consente di determinare le partizioni a cui è necessario accedere per soddisfare le condizioni della query.That is, this seek determines the partitions that must be accessed to satisfy the conditions of the query. Il secondo elemento SeekKeys specifica la parte della ricerca di secondo livello dell'operazione di skip scan che viene eseguita all'interno di ciascuna partizione identificata nella ricerca di primo livello.The second SeekKeys item specifies the second-level seek portion of the skip scan operation that occurs within each partition identified in the first-level seek.

Informazioni di riepilogo sulle partizioniPartition Summary Information

Nei piani di esecuzione della fase di esecuzione le informazioni di riepilogo sulle partizioni includono il totale delle partizioni e l'identità delle partizioni effettive a cui viene effettuato l'accesso.In run-time execution plans, partition summary information provides a count of the partitions accessed and the identity of the actual partitions accessed. È possibile utilizzare queste informazioni per verificare che le partizioni a cui viene effettuato l'accesso tramite la query sono corrette e che tutte le altre partizioni non vengono considerate.You can use this information to verify that the correct partitions are accessed in the query and that all other partitions are eliminated from consideration.

Vengono fornite le informazioni seguenti: Actual Partition Counte Partitions Accessed.The following information is provided: Actual Partition Count, and Partitions Accessed.

Actual Partition Count corrisponde al numero totale di partizioni a cui si accede tramite la query.Actual Partition Count is the total number of partitions accessed by the query.

Nell'output di Showplan XMLPartitions Accessedcorrisponde alle informazioni di riepilogo sulle partizioni che vengono visualizzate nel nuovo elemento RuntimePartitionSummary del nodo RelOp dell'operatore nel quale è definito.Partitions Accessed, in XML Showplan output, is the partition summary information that appears in the new RuntimePartitionSummary element in RelOp node of the operator in which it is defined. Nell'esempio seguente è illustrato il contenuto dell'elemento RuntimePartitionSummary , in cui è indicato che viene eseguito l'accesso a due partizioni totali, ovvero la 2 e la 3.The following example shows the contents of the RuntimePartitionSummary element, indicating that two total partitions are accessed (partitions 2 and 3).

<RunTimePartitionSummary>

    <PartitionsAccessed PartitionCount="2" >

        <PartitionRange Start="2" End="3" />

    </PartitionsAccessed>

</RunTimePartitionSummary>

Visualizzazione delle informazioni sulle partizioni utilizzando altri metodi di ShowplanDisplaying Partition Information by Using Other Showplan Methods

I metodi SHOWPLAN_ALL, SHOWPLAN_TEXTe STATISTICS PROFILE di Showplan non restituiscono le informazioni sulle partizioni descritte in questo argomento, con un'unica eccezione illustrata di seguito.The Showplan methods SHOWPLAN_ALL, SHOWPLAN_TEXT, and STATISTICS PROFILE do not report the partition information described in this topic, with the following exception. In quanto incluse nel predicato SEEK , le partizioni a cui eseguire l'accesso sono identificate da un predicato di intervallo nella colonna calcolata che rappresenta l'ID di partizione.As part of the SEEK predicate, the partitions to be accessed are identified by a range predicate on the computed column representing the partition ID. L'esempio seguente mostra il predicato SEEK per un operatore Clustered Index Seek .The following example shows the SEEK predicate for a Clustered Index Seek operator. Viene effettuato l'accesso alle partizioni 2 e 3 e l'operatore di ricerca applica il filtro sulle righe che soddisfano la condizione date_id BETWEEN 20080802 AND 20080902.Partitions 2 and 3 are accessed, and the seek operator filters on the rows that meet the condition date_id BETWEEN 20080802 AND 20080902.

|--Clustered Index Seek(OBJECT:([db_sales_test].[dbo].[fact_sales].[ci]), 

        SEEK:([PtnId1000] >= (2) AND [PtnId1000] \<= (3) 

                AND [db_sales_test].[dbo].[fact_sales].[date_id] >= (20080802) 

                AND [db_sales_test].[dbo].[fact_sales].[date_id] <= (20080902)) 

                ORDERED FORWARD)

Interpretazione dei piani di esecuzione per heap partizionatiInterpreting Execution Plans for Partitioned Heaps

Un heap partizionato viene considerato come un indice logico sull'ID di partizione.A partitioned heap is treated as a logical index on the partition ID. In un piano di esecuzione l'eliminazione di partizioni in un heap partizionato viene rappresentata come un operatore Table Scan con un predicato SEEK sull'ID di partizione.Partition elimination on a partitioned heap is represented in an execution plan as a Table Scan operator with a SEEK predicate on partition ID. Nell'esempio seguente sono illustrate le informazioni di Showplan fornite:The following example shows the Showplan information provided:

|-- Table Scan (OBJECT: ([db].[dbo].[T]), SEEK: ([PtnId1001]=[Expr1011]) ORDERED FORWARD)

Interpretazione dei piani di esecuzione per join collocatiInterpreting Execution Plans for Collocated Joins

La collocazione dei join può verificarsi quando due tabelle vengono partizionate utilizzando una funzione di partizionamento identica o equivalente e le colonne di partizionamento di entrambi lati del join sono specificate nella condizione di join della query.Join collocation can occur when two tables are partitioned using the same or equivalent partitioning function and the partitioning columns from both sides of the join are specified in the join condition of the query. Query Optimizer può generare un piano in cui le partizioni di ogni tabella con ID di partizione uguali sono unite in join separatamente.The Query Optimizer can generate a plan where the partitions of each table that have equal partition IDs are joined separately. I join collocati possono risultare più rapidi di quelli non collocati perché possono richiedere una minor quantità di memoria e tempi di elaborazione inferiori.Collocated joins can be faster than non-collocated joins because they can require less memory and processing time. Query Optimizer sceglie un piano non collocato o un piano collocato sulla base delle stime dei costi.The Query Optimizer chooses a non-collocated plan or a collocated plan based on cost estimates.

In un piano collocato il join Nested Loops legge una o più partizioni di tabelle o indici unite in join dal lato interno.In a collocated plan, the Nested Loops join reads one or more joined table or index partitions from the inner side. I numeri all'interno degli operatori Constant Scan rappresentano i numeri della partizione.The numbers within the Constant Scan operators represent the partition numbers.

Quando per le tabelle o gli indici partizionati si generano piani paralleli per join collocati, viene visualizzato un operatore Parallelism tra gli operatori di join Constant Scan e Nested Loops .When parallel plans for collocated joins are generated for partitioned tables or indexes, a Parallelism operator appears between the Constant Scan and the Nested Loops join operators. In questo caso, ognuno dei thread di lavoro sul lato esterno del join legge ed elabora una partizione diversa.In this case, multiple worker threads on the outer side of the join each read and work on a different partition.

Nella figura seguente viene illustrato un piano di query parallele per un join collocato.The following illustration demonstrates a parallel query plan for a collocated join.
colocated_join

Strategia di esecuzione delle query parallele per oggetti partizionatiParallel Query Execution Strategy for Partitioned Objects

Query Processor utilizza una strategia di esecuzione parallela per query che eseguono la selezione da oggetti partizionati.The query processor uses a parallel execution strategy for queries that select from partitioned objects. Come parte della strategia di esecuzione, Query Processor determina le partizioni della tabella necessarie per eseguire la query e la proporzione di thread di lavoro da allocare a ogni partizione.As part of the execution strategy, the query processor determines the table partitions required for the query and the proportion of worker threads to allocate to each partition. Nella maggior parte dei casi, Query Processor alloca un numero di thread di lavoro uguale o quasi uguale a ogni partizione, quindi esegue la query in parallelo tra le partizioni.In most cases, the query processor allocates an equal or almost equal number of worker threads to each partition, and then executes the query in parallel across the partitions. Nei paragrafi seguenti viene descritta più dettagliatamente l'allocazione dei thread di lavoro.The following paragraphs explain worker thread allocation in greater detail.

thread di lavoro1

Se il numero di thread di lavoro è minore di quello delle partizioni, Query Processor assegna ogni thread di lavoro a una partizione diversa, lasciando inizialmente una o più partizioni senza un thread di lavoro assegnato.If the number of worker threads is less than the number of partitions, the query processor assigns each worker thread to a different partition, initially leaving one or more partitions without an assigned worker thread. Quando termina l'esecuzione di un thread di lavoro in una partizione, Query Processor assegna tale thread alla partizione successiva finché non viene assegnato un singolo thread di lavoro a ogni partizione.When a worker thread finishes executing on a partition, the query processor assigns it to the next partition until each partition has been assigned a single worker thread. Questo è l'unico caso in cui Query Processor rialloca i thread di lavoro ad altre partizioni.This is the only case in which the query processor reallocates worker threads to other partitions.
Mostra il thread di lavoro riassegnato al termine dell'esecuzione.Shows worker thread reassigned after it finishes. Se il numero di thread di lavoro è uguale a quello delle partizioni, Query Processor assegna un thread di lavoro a ogni partizione.If the number of worker threads is equal to the number of partitions, the query processor assigns one worker thread to each partition. Quando un thread di lavoro termina, non viene riallocato ad altre partizioni.When a worker thread finishes, it is not reallocated to another partition.

thread di lavoro2

Se il numero di thread di lavoro è maggiore di quello delle partizioni, Query Processor alloca un numero uguale di thread di lavoro a ogni partizione.If the number of worker threads is greater than the number of partitions, the query processor allocates an equal number of worker threads to each partition. Se il numero di thread di lavoro non è un multiplo esatto del numero di partizioni, Query Processor alloca un thread di lavoro aggiuntivo ad alcune partizioni per usare tutti i thread di lavoro disponibili.If the number of worker threads is not an exact multiple of the number of partitions, the query processor allocates one additional worker thread to some partitions in order to use all of the available worker threads. Si noti che se la partizione è unica, tutti i thread di lavoro verranno assegnati a tale partizione.Note that if there is only one partition, all worker threads will be assigned to that partition. Nel diagramma seguente sono presenti quattro partizioni e 14 thread di lavoro.In the diagram below, there are four partitions and 14 worker threads. Ogni partizione dispone di 3 thread di lavoro assegnati e due partizioni dispongono di un thread di lavoro aggiuntivo, per un totale di 14 thread di lavoro assegnati.Each partition has 3 worker threads assigned, and two partitions have an additional worker thread, for a total of 14 worker thread assignments. Quando un thread di lavoro termina, non viene riassegnato ad altre partizioni.When a worker thread finishes, it is not reassigned to another partition.

thread di lavoro3

Sebbene negli esempi precedenti venga suggerito un modo semplice per allocare i thread di lavoro, la strategia effettiva è più complessa e tiene conto di altre variabili che possono presentarsi durante l'esecuzione di query.Although the above examples suggest a straightforward way to allocate worker threads, the actual strategy is more complex and accounts for other variables that occur during query execution. Ad esempio, se la tabella è partizionata e dispone di un indice cluster nella colonna A e se in una query è presente la clausola del predicato WHERE A IN (13, 17, 25), Query Processor allocherà uno o più thread di lavoro a ciascuno dei tre valori di ricerca (A=13, A=17 e A=25) anziché eseguire l'allocazione a ogni partizione della tabella.For example, if the table is partitioned and has a clustered index on column A and a query has the predicate clause WHERE A IN (13, 17, 25), the query processor will allocate one or more worker threads to each of these three seek values (A=13, A=17, and A=25) instead of each table partition. È necessario solo eseguire la query nelle partizioni che contengono questi valori e, se tutti i predicati SEEK si trovano nella stessa partizione della tabella, tutti i thread di lavoro verranno assegnati alla partizione specifica.It is only necessary to execute the query in the partitions that contain these values, and if all of these seek predicates happen to be in the same table partition, all of the worker threads will be assigned to the same table partition.

Per illustrare un altro esempio, si supponga che la tabella dispone di quattro partizioni nella colonna A con punti limite (10, 20, 30), un indice nella colonna B e che la query include una clausola WHERE B IN (50, 100, 150)del predicato.To take another example, suppose that the table has four partitions on column A with boundary points (10, 20, 30), an index on column B, and the query has a predicate clause WHERE B IN (50, 100, 150). Dal momento che partizioni della tabella sono basate sui valori di A, i valori di B possono trovarsi in qualsiasi partizione della tabella.Because the table partitions are based on the values of A, the values of B can occur in any of the table partitions. Di conseguenza Query Processor ricercherà ciascuno dei tre valori di B (50, 100, 150) in ognuna delle quattro partizioni della tabellaThus, the query processor will seek for each of the three values of B (50, 100, 150) in each of the four table partitions. e assegnerà proporzionatamente i thread di lavoro in modo da eseguire ciascuna delle 12 analisi della query in parallelo.The query processor will assign worker threads proportionately so that it can execute each of these 12 query scans in parallel.

Partizioni della tabella basate sulla colonna ATable partitions based on column A Ricerca della colonna B in ogni partizione della tabellaSeeks for column B in each table partition
Partizione della tabella 1: A < 10Table Partition 1: A < 10 B=50, B=100, B=150B=50, B=100, B=150
Partizione della tabella 2: A >= 10 AND A < 20Table Partition 2: A >= 10 AND A < 20 B=50, B=100, B=150B=50, B=100, B=150
Partizione della tabella 3: A >= 20 AND A < 30Table Partition 3: A >= 20 AND A < 30 B=50, B=100, B=150B=50, B=100, B=150
Partizione della tabella 4: A >= 30Table Partition 4: A >= 30 B=50, B=100, B=150B=50, B=100, B=150

Procedure consigliateBest Practices

Per migliorare le prestazioni di query che accedono a una grande quantità di dati da tabelle e indici partizionati estesi, è opportuno adottare le procedure consigliate seguenti:To improve the performance of queries that access a large amount of data from large partitioned tables and indexes, we recommend the following best practices:

  • Eseguire lo striping di ogni partizione tra molti dischi.Stripe each partition across many disks. Questo è particolarmente importante durante l'uso della rotazione dei dischi.This is especially relevant when using spinning disks.
  • Quando possibile, utilizzare un server con memoria principale sufficiente per contenere partizioni a cui viene effettuato l'accesso frequentemente o a tutte le partizioni in memoria per ridurre il costo delle operazioni di I/O.When possible, use a server with enough main memory to fit frequently accessed partitions or all partitions in memory to reduce I/O cost.
  • Se i dati oggetto della query non sono tutti contenuti in memoria, comprimere le tabelle e gli indiciIf the data you query will not fit in memory, compress the tables and indexes. al fine di ridurre il costo delle operazioni di I/O.This will reduce I/O cost.
  • Utilizzare un server con processori veloci e il maggior numero possibile di core del processore per sfruttare a pieno la funzionalità di elaborazione di query parallele.Use a server with fast processors and as many processor cores as you can afford, to take advantage of parallel query processing capability.
  • Assicurarsi che per il server sia disponibile larghezza di banda sufficiente del controller I/O.Ensure the server has sufficient I/O controller bandwidth.
  • Creare un indice cluster in ogni tabella partizionata grande per sfruttare le ottimizzazioni dell'analisi dell'albero B.Create a clustered index on every large partitioned table to take advantage of B-tree scanning optimizations.
  • Quando si esegue il caricamento bulk di dati in tabelle partizionate, attenersi ai requisiti della procedura consigliata nel white paper The Data Loading Performance Guide (Guida alle prestazioni del caricamento dati).Follow the best practice recommendations in the white paper, The Data Loading Performance Guide, when bulk loading data into partitioned tables.

EsempioExample

Nell'esempio seguente viene creato un database di test che contiene un'unica tabella con sette partizioni.The following example creates a test database containing a single table with seven partitions. Per l'esecuzione delle query in questo esempio utilizzare gli strumenti descritti in precedenza per visualizzare le informazioni sul partizionamento relative ai piani della fase di compilazione e della fase di esecuzione.Use the tools described previously when executing the queries in this example to view partitioning information for both compile-time and run-time plans.

Nota

In questo esempio nella tabella viene inserito più di un milione di righe.This example inserts more than 1 million rows into the table. A seconda dell'hardware disponibile l'esecuzione di questo esempio può richiedere diversi minuti.Running this example may take several minutes depending on your hardware. Prima di eseguire questo esempio, verificare di disporre di almeno 1,5 GB di spazio libero su disco.Before executing this example, verify that you have more than 1.5 GB of disk space available.

USE master;
GO
IF DB_ID (N'db_sales_test') IS NOT NULL
    DROP DATABASE db_sales_test;
GO
CREATE DATABASE db_sales_test;
GO
USE db_sales_test;
GO
CREATE PARTITION FUNCTION [pf_range_fact](int) AS RANGE RIGHT FOR VALUES 
(20080801, 20080901, 20081001, 20081101, 20081201, 20090101);
GO
CREATE PARTITION SCHEME [ps_fact_sales] AS PARTITION [pf_range_fact] 
ALL TO ([PRIMARY]);
GO
CREATE TABLE fact_sales(date_id int, product_id int, store_id int, 
    quantity int, unit_price numeric(7,2), other_data char(1000))
ON ps_fact_sales(date_id);
GO
CREATE CLUSTERED INDEX ci ON fact_sales(date_id);
GO
PRINT 'Loading...';
SET NOCOUNT ON;
DECLARE @i int;
SET @i = 1;
WHILE (@i<1000000)
BEGIN
    INSERT INTO fact_sales VALUES(20080800 + (@i%30) + 1, @i%10000, @i%200, RAND() * 25, (@i%3) + 1, '');
    SET @i += 1;
END;
GO
DECLARE @i int;
SET @i = 1;
WHILE (@i<10000)
BEGIN
    INSERT INTO fact_sales VALUES(20080900 + (@i%30) + 1, @i%10000, @i%200, RAND() * 25, (@i%3) + 1, '');
    SET @i += 1;
END;
PRINT 'Done.';
GO
-- Two-partition query.
SET STATISTICS XML ON;
GO
SELECT date_id, SUM(quantity*unit_price) AS total_price
FROM fact_sales
WHERE date_id BETWEEN 20080802 AND 20080902
GROUP BY date_id ;
GO
SET STATISTICS XML OFF;
GO
-- Single-partition query.
SET STATISTICS XML ON;
GO
SELECT date_id, SUM(quantity*unit_price) AS total_price
FROM fact_sales
WHERE date_id BETWEEN 20080801 AND 20080831
GROUP BY date_id;
GO
SET STATISTICS XML OFF;
GO

Ulteriori informazioni Additional Reading

Guida di riferimento a operatori Showplan logici e fisiciShowplan Logical and Physical Operators Reference
Eventi estesiExtended Events
Procedure consigliate per l'archivio queryBest Practice with the Query Store
Stima della cardinalitàCardinality Estimation