Miglioramenti delle prestazioni mediante le indicazioni di Ottimizzazione guidata motore di databasePerformance Improvements using DTA recommendations

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


Le prestazioni dei carichi di lavoro di data warehousing e analisi possono trarre vantaggio dagli indici columnstore, in particolare per le query che devono eseguire l'analisi di tabelle di grandi dimensioni.The performance of data warehousing and analytic workloads can greatly benefit from columnstore indexes, particularly for queries that need to scan large tables. Gli indici Rowstore (albero B+-) sono particolarmente utili per le query che accedono a quantità relativamente piccole di dati durante la ricerca di un determinato valore o intervallo di valori.Rowstore (B+-tree) indexes are most effective for queries that access relatively small amounts of data searching for a particular value or range of values. Poiché possono restituire righe ordinate, gli indici rowstore consentono anche di ridurre il costo dell'ordinamento nei piani di esecuzione delle query.Since rowstore indexes can deliver rows in sorted order, they can also reduce the cost of sorting in query execution plans. Pertanto, la scelta della combinazione di indici rowstore e columnstore da compilare dipende dal carico di lavoro dell'applicazione.Therefore, the choice of which combination of rowstore and columnstore indexes to build is dependent on your application’s workload.

A partire da SQL Server 2016, Ottimizzazione guidata motore di database può proporre una combinazione di indici rowstore e columnstore appropriata grazie all'analisi di uno specifico carico di lavoro del database.The Database Engine Tuning Advisor (DTA), starting in SQL Server 2016, can recommend a suitable combination of rowstore and columnstore indexes by analyzing a given database workload.

Per illustrare i vantaggi delle indicazioni di Ottimizzazione guidata motore di database relative alle prestazioni del carico di lavoro, abbiamo provato diversi carichi di lavoro reali dei clienti.To demonstrate the benefits of DTA's recommendations on workload performance, we experimented with several real customer workloads. Per ogni carico di lavoro dei clienti, Ottimizzazione guidata motore di database analizza le singole query e il carico di lavoro completo delle query.For each customer workload, we let DTA analyze individual queries as well as the full workload of queries. Consideriamo tre alternative:We consider three alternatives:

  1. Solo indici columnstore: compilare solo gli indici columnstore per tutte le tabelle senza usare Ottimizzazione guidata motore di database.Columnstore only: Build only columnstore indexes for all tables without using DTA.
  2. Ottimizzazione guidata motore di database (solo indici rowstore): eseguire Ottimizzazione guidata motore di database con l'opzione per fornire indicazioni solo per gli indici rowstore.DTA (rowstore only): Run DTA with the option to recommend rowstore indexes only.
  3. Ottimizzazione guidata motore di database (indici rowstore + columnstore): eseguire Ottimizzazione guidata motore di database con l'opzione per fornire indicazioni sia per gli indici rowstore che per gli columnstore.DTA (rowstore + columnstore): Run DTA with the option to recommend both rowstore and columnstore indexes.

In ogni caso abbiamo implementato gli indici consigliati.In each case, we then implemented the recommended indexes. Viene evidenziato il tempo di CPU (in millisecondi) come valore medio di più esecuzioni della query o del carico di lavoro.We report the CPU Time (in milliseconds) averaged over multiple runs of the query or the workload. Nella figura seguente è riportato il tempo di CPU in millisecondi per i carichi di lavoro tra due diversi database dei clienti.The figure below plots the CPU time in milliseconds for workloads across two different customer databases. Si noti che l'asse y (tempo di CPU) usa una scala logaritmica.Note that the y-axis (CPU Time) uses a logarithmic scale.

Prestazioni degli indici rowstore e columnstore in Ottimizzazione guidata motore di database

Necessario per progettazioni fisiche miste: primo set di barre corrispondente a Cliente 1 Query 1.Need for mixed physical designs: The first set of bars corresponding to Customer 1 Query 1. L'indicazione di Ottimizzazione guidata motore di database (rowstore + columnstore) che prevede un set di quattro indici columnstore e sei indici rowstore ha come risultato un tempo di CPU da 2,5 a 4 volte inferiore rispetto all'uso solo dell'indice columnstore e di Ottimizzazione guidata motore di database (solo rowstore).DTA (rowstore + columnstore) recommends a set of four columnstore and six rowstore indexes which results in 2.5X – 4X lower CPU time compared to columnstore index only and DTA (rowstore only). Questo esempio dimostra i vantaggi delle progettazioni fisiche miste costituite da indici rowstore e columnstore anche per una singola query.This demonstrates the benefits of mixed physical designs consisting of rowstore and columnstore indexes even for a single query.

Efficacia delle indicazioni relative agli indici rowstore: il secondo e terzo set di barre, corrispondenti a Cliente 1 Query 2 e a Cliente 2 Query 1, sono casi in cui le query includono predicati del filtro selettivo che traggono vantaggio dagli indici rowstore adatti.Effectiveness of rowstore index recommendations: The second and third set of bars (corresponding to Customer 1 Query 2 and Customer 2 Query 1) are cases where the queries have selective filter predicates that benefit from suitable rowstore indexes. Per entrambe queste query, Ottimizzazione guidata motore di database (solo rowstore) e Ottimizzazione guidata motore di database (rowstore + columnstore) consigliano l'uso solo degli indici rowstore.For both these queries, DTA (rowstore only) and DTA (rowstore + columnstore) recommends rowstore indexes only. Questi esempi illustrano inoltre che, anche se Ottimizzazione guidata motore di database viene chiamato con l'opzione per consigliare gli indici columnstore, il relativo approccio basato sui costi fa sì che venga proposto un indice columnstore solo se effettivamente il carico di lavoro può trarne vantaggio.These examples also show that even when DTA is invoked with the option to recommend columnstore indexes, its cost-based approach ensures that it recommends a columnstore index only if the workload can actually benefit from it.

Efficacia delle indicazioni relative agli indici columnstore: il quarto set di barre corrispondente a Cliente 2 Query 2 rappresenta un caso in cui la query esegue l'analisi di tabelle di grandi dimensioni e può trarre beneficio dagli indici columnstore.Effectiveness of columnstore index recommendations: The fourth set of bars corresponding to Customer 2 Query 2 represents a case where the query scans large tables which would benefit from columnstore indexes. Ottimizzazione guidata motore di database (solo rowstore) genera un'indicazione il cui tempo di CPU è superiore rispetto a quando sono presenti gli indici columnstore.DTA (rowstore only) generates a recommendation whose CPU Time is higher compared to when columnstore indexes are present. Ottimizzazione guidata motore di database (rowstore + columnstore) consiglia gli indici columnstore adatti, ottenendo prestazioni di esecuzione delle query corrispondenti a quelle in cui viene usata l'opzione basata solo su indici columnstore.DTA (rowstore + columnstore) recommends suitable columnstore indexes, thus matching the query execution performance of the columnstore only option.

Efficacia delle indicazioni per il carico di lavoro con più query: il set finale di barre corrispondente al carico di lavoro completo per Cliente 2 illustra la capacità di Ottimizzazione guidata motore di database di analizzare più query nel carico di lavoro per consigliare un set appropriato di indici rowstore e columnstore al fine di migliorare il costo di esecuzione globale del carico di lavoro.Effectiveness of recommendations for workload with multiple queries: The final set of bars corresponding to the full workload for Customer 2 exemplifies DTA’s ability to analyze multiple queries in the workload to recommend a suitable set of rowstore and columnstore indexes which can improve the overall workload’s execution cost. Ottimizzazione guidata motore di database (rowstore + columnstore) consiglia quattro indici columnstore e decine di indici rowstore, ottenendo come risultato un rilevante miglioramento delle prestazioni per il carico di lavoro, se confrontato con l'opzione che prevede la compilazione solo di indici columnstore, nonché un miglioramento da 4 a 5 volte superiore se confrontato con Ottimizzazione guidata motore di database (solo rowstore).DTA (rowstore + columnstore) recommends four columnstore indexes and tens of rowstore indexes that result in over an order of magnitude improvement for the workload when compared to the option that builds only columnstore indexes; and about 4X–5X improvement when compared to DTA (rowstore only).

In sintesi, gli esempi precedenti illustrano la capacità di Ottimizzazione guidata motore di database di sfruttare gli indici sia rowstore che columnstore supportati nel motore di database di SQL Server e di consigliare una combinazione appropriata di indici in grado di ridurre in modo significativo il tempo di CPU per il carico di lavoro.In summary, the above examples illustrate DTA’s ability to suitably leverage both rowstore and columnstore indexes supported in the SQL Server Database Engine, and recommend an appropriate combination of indexes that can significantly reduce CPU Time for the workload.

Vedere ancheSee Also

Ottimizzazione guidata motore di databaseDatabase Engine Tuning Advisor

Indicazioni relative agli indici columnstore in Ottimizzazione guidata motore di databaseColumnstore index recommendations in Database Engine Tuning Advisor (DTA)

Guida agli indici columnstoreColumnstore Indexes Guide

Indici columnstore per il data warehousingColumnstore Indexes for Data Warehousing

CREATE COLUMNSTORE INDEX (Transact-SQL)CREATE COLUMNSTORE INDEX (Transact-SQL)

CREATE INDEX (Transact-SQL)CREATE INDEX (Transact-SQL)