Articolo
03/31/2012

Utilizzo delle informazioni sugli indici mancanti per scrivere istruzioni CREATE INDEX

In questo argomento vengono fornite linee guida ed esempi per l'utilizzo delle informazioni restituite dai componenti della caratteristica degli indici mancanti per scrivere istruzioni CREATE INDEX per gli indici mancanti.

Ordinamento delle colonne nelle istruzioni CREATE INDEX

I componenti della caratteristica degli indici mancanti elencano nell'output le colonne di uguaglianza, disuguaglianza e incluse.

Ad esempio, l'elemento MissingIndexes negli Showplan XML indica se una colonna chiave di un indice viene utilizzata per stabilire l'uguaglianza (=) o la disuguaglianza (<, > e così via) nel predicato dell'istruzione Transact-SQL oppure è inclusa solo per coprire una query. Queste informazioni vengono visualizzate sotto forma di uno dei valori seguenti per l'attributo Usage del sottoelemento ColumnGroup:

Gli oggetti a gestione dinamica sys.dm_db_missing_index_details e sys.dm_db_missing_index_columns restituiscono risultati che indicano se una colonna chiave di indice è una colonna di uguaglianza, disuguaglianza o inclusa. Il set di risultati di sys.dm_db_missing_index_details restituisce queste informazioni nelle colonne equality_columns, inequality_columns e included_columns. Nel set di risultati restituito da sys.dm_db_missing_index_columns queste informazioni vengono visualizzate nella colonna column_usage.

Utilizzare le linee guida seguenti per ordinare le colonne nelle istruzioni CREATE INDEX create dall'output del componente della caratteristica degli indici mancanti:

Elencare prima le colonne di uguaglianza (all'estrema sinistra nell'elenco di colonne).
Elencare le colonne di disuguaglianza dopo le colonne di uguaglianza (a destra delle colonne di uguaglianza elencate).
Elencare le colonne incluse nella clausola INCLUDE dell'istruzione CREATE INDEX.
Per determinare un ordine efficiente per le colonne di uguaglianza, disporle in base al livello di selettività elencando prima le colonne più selettive.

Esempi

Utilizzo dell'output dell'elemento MissingIndexes negli Showplan XML

La caratteristica degli indici mancanti si avvale delle informazioni generate automaticamente da Query Optimizer durante l'ottimizzazione di una query. È tuttavia necessario eseguire innanzitutto le query sull'istanza di SQL Server in modo che Query Optimizer possa generare le informazioni sugli indici mancanti.

Nell'esempio seguente viene illustrato come creare un'istruzione DDL (Data Definition Language) sulla base delle informazioni restituite dall'elemento MissingIndexes:

Attivare la caratteristica Showplan XML mediante l'opzione SET STATISTICS XML ON ed eseguire la query seguente sul database di esempio AdventureWorks2008R2:

USE AdventureWorks2008R2;
GO
SET STATISTICS XML ON;
GO
SELECT CustomerID, SalesOrderNumber, SubTotal
FROM Sales.SalesOrderHeader
WHERE ShipMethodID > 2
AND SubTotal > 500.00
AND Freight < 15.00
AND TerritoryID = 5;
GO

Visualizzare l'output restituito nell'elemento MissingIndexes dell'elemento Showplan prodotto:

<MissingIndexes>

  <MissingIndexGroup Impact="95.8296">

    <MissingIndex Database="[AdventureWorks2008R2]" Schema="[Sales]" Table="[SalesOrderHeader]">

      <ColumnGroup Usage="EQUALITY">

        <Column Name="[TerritoryID]" ColumnId="14" />

      </ColumnGroup>

      <ColumnGroup Usage="INEQUALITY">

        <Column Name="[ShipMethodID]" ColumnId="17" />

        <Column Name="[SubTotal]" ColumnId="21" />

        <Column Name="[Freight]" ColumnId="23" />

      </ColumnGroup>

      <ColumnGroup Usage="INCLUDE">

        <Column Name="[SalesOrderNumber]" ColumnId="8" />

        <Column Name="[CustomerID]" ColumnId="11" />

      </ColumnGroup>

    </MissingIndex>

  </MissingIndexGroup>

</MissingIndexes>
Creare l'indice mancante utilizzando le informazioni restituite negli elementiMissingIndex e ColumnGroup per scrivere un'istruzione DDL CREATE INDEX come segue:
```
USE AdventureWorks2008R2;
GO
IF EXISTS (SELECT name FROM sys.indexes
           WHERE name = N'IX_SalesOrderHeader_TerritoryID')
     DROP INDEX IX_SalesOrderHeader_TerritoryID ON Sales.SalesOrderHeader;
GO
CREATE NONCLUSTERED INDEX IX_SalesOrderHeader_TerritoryID
     ON Sales.SalesOrderHeader (TerritoryID, ShipMethodID, SubTotal, Freight)
     INCLUDE (SalesOrderNumber, CustomerID);
GO
```
In questa istruzione CREATE INDEX vengono utilizzati il database (USE AdventureWorks2008R2), lo schema e il nome della tabella (ON Sales.SalesOrderHeader) elencati nell'elemento MissingIndex. Vengono inoltre utilizzate le colonne elencate per ogni sottoelemento ColumnGroup delle colonne chiave (TerritoryID, ShipMethodID, SubTotal, Freight) e delle colonne non chiave (INCLUDE (SalesOrderNumber, CustomerID)).

Utilizzo dei risultati restituiti da un oggetto a gestione dinamica

Per poter recuperare le informazioni sugli indici mancanti, è necessario innanzitutto eseguire le query sull'istanza di SQL Server affinché Query Optimizer possa generare tali informazioni.

Nell'esempio seguente viene illustrato come creare un'istruzione DDL sulla base delle informazioni restituite dalla vista a gestione dinamica sys.dm_db_missing_index_details.

Eseguire la query seguente sul database di esempio AdventureWorks2008R2:

USE AdventureWorks2008R2;
GO
SELECT City, StateProvinceID, PostalCode
FROM Person.Address
WHERE StateProvinceID = 9;
GO

Eseguire la query seguente sulla vista a gestione dinamica sys.dm_db_missing_index_details:
```
SELECT *
FROM sys.dm_db_missing_index_details
```
L'esecuzione della query su questa vista a gestione dinamica restituisce i risultati seguenti:

index_handle	database_id	object_id	equality_columns	inequality_columns	included_columns	statement
1	6	53575229	[StateProvince]	NULL	[City], [PostalCode]	[AdventureWorks].[Person].[Address]

Dopo aver eseguito la query sulla vista a gestione dinamica sys.dm_db_missing_index_details, è possibile creare l'indice mancante utilizzando le informazioni restituite nelle colonne equality_columns, included_columns e statement come segue:

USE AdventureWorks2008R2;
GO
IF EXISTS (SELECT name FROM sys.indexes
           WHERE name = N'IX_PersonAddress_StateProvinceID')
     DROP INDEX IX_PersonAddress_StateProvinceID ON Person.Address;
GO
CREATE NONCLUSTERED INDEX IX_PersonAddress_StateProvinceID
     ON Person.Address (StateProvinceID)
     INCLUDE (City, PostalCode);
GO

In questo esempio non sono state restituite colonne in inequality_columns nel set di risultati sys.dm_db_missing_index_details. Qualora tali colonne fossero state restituite, sarebbe stato necessario elencarle dopo le colonne in equality_columns. Le colonne restituite in included_columns vengono sempre elencate nella clausola INCLUDE dell'istruzione CREATE INDEX.