Lezione 2: Uso del Consigliere di Ottimizzazione del Motore di Database

si applica a:SQL Server

Advisor di ottimizzazione del motore di database consente di ottimizzare i database, gestire le sessioni di ottimizzazione e visualizzare i consigli di ottimizzazione. Gli utenti con conoscenza avanzata delle strutture di progettazione fisica possono usare questo strumento per eseguire analisi esplorative dell'ottimizzazione del database. I principianti dell'ottimizzazione del database possono anche usare lo strumento per trovare la migliore configurazione delle strutture di progettazione fisica per i carichi di lavoro ottimizzati. Questa lezione fornisce una pratica di base per gli amministratori di database che sono nuovi all'interfaccia utente grafica dell'Advisor per l'Ottimizzazione del Motore di Database e per gli amministratori di sistema che potrebbero non avere una conoscenza approfondita delle strutture di progettazione fisica.

Prerequisites

Per completare questa esercitazione, sono necessari SQL Server Management Studio, l'accesso a un server che esegue SQL Server e un database AdventureWorks2025.

• Installare SQL Server Management Studio.
• Installare SQL Server 2017 Developer Edition.
• Scaricare un database campione AdventureWorks.

Le istruzioni per il ripristino dei database in SSMS sono disponibili qui: Ripristinare un database.

Note

Questa esercitazione è destinata a un utente che ha familiarità con l'uso di SQL Server Management Studio e le attività di amministrazione di base del database.

Ottimizzazione di un carico di lavoro

È possibile usare il Consulente di ottimizzazione del motore di database per trovare la progettazione fisica ottimale del database per le prestazioni delle query nei database e nelle tabelle selezionate per l'ottimizzazione.

1. Copiare un'istruzione SELECT di esempio e incollare l'istruzione nell'editor di query di SQL Server Management Studio. Salvare il file come MyScript.sql in una directory in cui è possibile trovarlo facilmente. Di seguito è riportato un esempio compatibile con il AdventureWorks2025 database.

Use [AdventureWorks2022]; -- may need to modify database name to match database
GO
SELECT DISTINCT pp.LastName, pp.FirstName 
FROM Person.Person pp JOIN HumanResources.Employee e
ON e.BusinessEntityID = pp.BusinessEntityID WHERE pp.BusinessEntityID IN 
(SELECT SalesPersonID 
FROM Sales.SalesOrderHeader
WHERE SalesOrderID IN 
(SELECT SalesOrderID 
FROM Sales.SalesOrderDetail
WHERE ProductID IN 
(SELECT ProductID 
FROM Production.Product p 
WHERE ProductNumber = 'BK-M68B-42')));
GO

2. Avvia Il consulente di ottimizzazione del motore di database. Selezionare Database Tuning Advisor dal menu Strumenti in SQL Server Management Studio (SSMS). Per altre informazioni, vedere Avviare Ottimizzazione guidata motore di database. Connettersi a SQL Server nella finestra di dialogo Connetti al server .

3. Nella scheda Generale del riquadro destro dell'interfaccia utente grafica del Database Engine Tuning Advisor, scrivere MySession in Nome sessione.

4. Selezionare File per il carico di lavoro e selezionare l'icona binocolo per cercare un file del carico di lavoro. Individuare il file MyScript.sql salvato nel passaggio 1.

5. Selezionare AdventureWorks2025 nell'elenco Database per l'analisi del carico di lavoro , selezionare AdventureWorks2025 nella griglia Selezionare database e tabelle da ottimizzare e selezionare Salva log di ottimizzazione. Il database per l'analisi del carico di lavoro specifica il primo database a cui si connette l'Advisor per l'ottimizzazione del motore di database durante la sintonizzazione di un carico di lavoro. Dopo che l'ottimizzazione inizia, l'Advisor di ottimizzazione del motore di database si connette ai database specificati dalle istruzioni contenute nel carico di lavoro.

6. Fare clic sulla scheda Opzioni di ottimizzazione . Non si impostano opzioni di ottimizzazione per questa procedura, ma è necessario attendere qualche minuto per esaminare le opzioni di ottimizzazione predefinite. Premere F1 per visualizzare la Guida per questa pagina a schede. Fare clic su Opzioni avanzate per visualizzare opzioni di ottimizzazione aggiuntive. Fare clic su Aiuto nella finestra di dialogo Opzioni di Ottimizzazione Avanzate per informazioni sulle opzioni di ottimizzazione visualizzate in tale finestra. Fare clic su Annulla per chiudere la finestra di dialogo Opzioni di ottimizzazione avanzata , lasciando selezionate le opzioni predefinite.

7. Fare clic sul pulsante Avvia analisi sulla barra degli strumenti. Mentre il Database Engine Tuning Advisor analizza il carico di lavoro, è possibile monitorare lo stato nella scheda Avanzamento. Al termine dell'ottimizzazione, viene visualizzata la scheda Raccomandazioni.

   Se viene visualizzato un errore relativo alla data e all'ora di arresto dell'ottimizzazione, controllare l'ora di Arresto nella scheda principale Opzioni di ottimizzazione. Assicuratevi che la data e l'ora di Arresto siano maggiori della data e dell'ora correnti e, se necessario, modificarle.

8. Al termine dell'analisi, salvare il consiglio come script di Transact-SQL facendo clic su Salva raccomandazioni dal menu Azioni . Nella finestra di dialogo Salva con nome, passare alla cartella in cui si desidera salvare lo script delle raccomandazioni e digitare il nome file MyRecommendations.

Visualizzare le raccomandazioni per l'ottimizzazione

1. Nella scheda Raccomandazioni usare la barra di scorrimento nella parte inferiore della pagina a schede per visualizzare tutte le colonne Raccomandazioni indice . Ogni riga rappresenta un oggetto di database (indici o viste indicizzate) che il Consulente per l'Ottimizzazione del Motore di Database raccomanda di eliminare o creare. Scorrere fino alla colonna più a destra e fare clic su una definizione. Il Database Engine Tuning Advisor visualizza una finestra di anteprima script SQL in cui è possibile visualizzare lo script Transact-SQL che crea o elimina l'oggetto di database in quella riga. Fare clic su Chiudi per chiudere la finestra di anteprima.

   Se si riscontrano difficoltà a individuare una definizione contenente un collegamento, fare clic per deselezionare la casella di controllo Mostra oggetti esistenti nella parte inferiore della pagina a schede, riducendo così il numero di righe visualizzate. Quando si deseleziona questa casella di controllo, il Database Engine Tuning Advisor visualizza solo gli oggetti per i quali ha generato una raccomandazione. Selezionare la casella di controllo Mostra oggetti esistenti per visualizzare tutti gli oggetti di database attualmente esistenti nel AdventureWorks2025 database. Utilizzare la barra di scorrimento sul lato destro della pagina a schede per visualizzare tutti gli oggetti.

2. Fare clic con il pulsante destro del mouse sulla griglia nel riquadro Raccomandazioni indice . Questo menu di scelta rapida consente di selezionare e deselezionare le raccomandazioni. Consente inoltre di modificare il tipo di carattere per il testo della griglia.

3. Scegliere Salva raccomandazioni dal menu Azioni per salvare tutte le raccomandazioni in un unico script Transact-SQL. Assegnare allo script il nome MySessionRecommendations.sql.

   Aprire lo script MySessionRecommendations.sql nell'editor di query di SQL Server Management Studio per visualizzarlo. È possibile applicare le raccomandazioni al AdventureWorks2025 database di esempio eseguendo lo script nell'editor di query, ma non eseguire questa operazione. Chiudere lo script nell'editor di query senza eseguirlo.

   In alternativa, è anche possibile applicare le raccomandazioni facendo clic su Applica raccomandazioni nel menu Azioni di Ottimizzazione guidata motore di database, ma non applicare queste raccomandazioni ora in questa procedura.

4. Se nella scheda Raccomandazioni sono presenti più raccomandazioni, deselezionare alcune delle righe che elencano gli oggetti di database nella griglia Raccomandazioni indice .

5. Scegliere Valuta raccomandazioni dal menu Azioni. Il Database Engine Tuning Advisor crea una nuova sessione di ottimizzazione in cui è possibile valutare un sottoinsieme dei consigli originali di MySession.

6. Digitare EvaluateMySession per il nuovo nome della sessione e fare clic sul pulsante Avvia analisi sulla barra degli strumenti. È possibile ripetere i passaggi 2 e 3 per questa nuova sessione di ottimizzazione per visualizzarne le raccomandazioni.

Summary

La valutazione di un sottoinsieme di raccomandazioni di ottimizzazione potrebbe essere necessaria se è necessario modificare le opzioni di ottimizzazione dopo l'esecuzione di una sessione. Ad esempio, se si chiede a Ottimizzazione guidata motore di database di prendere in considerazione le viste indicizzate quando si specificano le opzioni di ottimizzazione per una sessione, ma dopo la generazione della raccomandazione si decide contro l'uso delle viste indicizzate. È quindi possibile usare l'opzione Valuta raccomandazioni dal menu Azioni per fare in modo che l'Ottimizzatore del motore di database effettui nuovamente una valutazione della sessione senza considerare le viste indicizzate. Quando si usa l'opzione Valuta raccomandazioni , le raccomandazioni generate in precedenza vengono ipoteticamente applicate alla progettazione fisica corrente per arrivare alla progettazione fisica per la seconda sessione di ottimizzazione.

È possibile visualizzare altre informazioni sui risultati di ottimizzazione nella scheda Report , descritta nell'attività successiva di questa lezione.

Visualizzare i report di ottimizzazione

Sebbene sia utile visualizzare gli script che possono essere usati per implementare i risultati di ottimizzazione, il Database Engine Tuning Advisor fornisce anche molti report utili da consultare. Questi report forniscono informazioni sulle strutture di progettazione fisica esistenti nel database che si sta ottimizzando e sulle strutture consigliate. I report di ottimizzazione possono essere visualizzati facendo clic sulla scheda Report come descritto nella procedura seguente.

1. Selezionare la scheda Report nell'Assistente alla Ottimizzazione del Database.

2. Nel riquadro Riepilogo ottimizzazione è possibile visualizzare informazioni su questa sessione di ottimizzazione. Usare la barra di scorrimento per visualizzare tutto il contenuto del riquadro. Si noti il miglioramento percentuale previsto e lo spazio usato dalla raccomandazione. È possibile limitare lo spazio usato dalla raccomandazione quando si impostano le opzioni di ottimizzazione. Nella scheda Opzioni di ottimizzazione selezionare Opzioni avanzate. Controllare Definisci spazio massimo per le raccomandazioni e specificare in megabyte lo spazio massimo che può essere usato da una configurazione delle raccomandazioni. Usare il pulsante Indietro nel browser dell'aiuto per tornare a questa esercitazione.

   

3. Nel riquadro Report di ottimizzazione fare clic su Rapporto sul costo delle istruzioni nell'elenco Seleziona rapporto. Se è necessario più spazio per visualizzare il report, trascinare il bordo del riquadro Monitoraggio sessione a sinistra. A ogni istruzione Transact-SQL eseguita su una tabella nel database è associato un costo delle prestazioni. Questo costo delle prestazioni può essere ridotto creando indici efficaci nelle colonne a cui si accede di frequente in una tabella. Questo report mostra il miglioramento percentuale stimato tra il costo originale dell'esecuzione di un'istruzione nel carico di lavoro e il costo se viene implementata la raccomandazione di ottimizzazione. Si noti che la quantità di informazioni contenute nel report si basa sulla lunghezza e sulla complessità del carico di lavoro.

   

4. Fare clic con il pulsante destro del mouse sul riquadro Report costi afferenti nell'area della griglia e scegliere Esporta su File. Salvare il report come MyReport. Un'estensione .xml viene aggiunta automaticamente al nome del file. È possibile aprire MyReport.xml nell'editor XML preferito o in SQL Server Management Studio per visualizzare il contenuto del report.

5. Tornare alla scheda Report di Database Engine Tuning Advisor e fare clic destro di nuovo sul Report costo istruzione. Esaminare le altre opzioni disponibili. Si noti che è possibile modificare il tipo di carattere per il report visualizzato. Il cambiamento del tipo di carattere qui si riflette anche nelle altre pagine a schede.

6. Fare clic su altri report nell'elenco Seleziona report per acquisire familiarità con essi.

Summary

Ora hai esplorato la scheda Report dell'interfaccia utente grafica di Ottimizzazione guidata del motore di database per la sessione di ottimizzazione mySession. È possibile usare questi stessi passaggi per esplorare i report generati per la sessione di ottimizzazione EvaluateMySession. Fare doppio clic su EvaluateMySession nel riquadro Monitoraggio sessione per iniziare.

Lezione successiva

Lezione 3: Uso dell'utilità del prompt dei comandi DTA