Analisi del carico di lavoro del database su un'istanza database Amazon RDS for SQL Server con Tuning Advisor motore di database

Database Engine Tuning Advisor è un'applicazione client fornita da Microsoft che analizza il carico di lavoro dei database e suggerisce un insieme ottimale di indici per i database Microsoft SQL Server in base al tipo di query eseguite. Come SQL Server Management Studio, Tuning Advisor viene eseguito da un computer client che si connette all'istanza database Amazon RDS che esegue SQL Server. Il computer client può essere un computer eseguito in locale nella tua rete o può essere un'istanza Amazon EC2 Windows in esecuzione nella stessa regione dell'istanza database Amazon RDS.

Questa sezione mostra come acquisire un carico di lavoro affinché Tuning Advisor lo analizzi. Questa è la procedura consigliata per acquisire un carico di lavoro perché Amazon RDS limita l'accesso host all'istanza SQL Server. Per ulteriori informazioni, consulta Database Engine Tuning Advisor nella documentazione Microsoft.

Per utilizzare Tuning Advisor, occorre fornire ad Advisor ciò che chiamiamo "carico di lavoro". Un carico di lavoro è un insieme di istruzioni Transact-SQL che vengono eseguite su uno o più database che desideri ottimizzare. Durante l'ottimizzazione dei database, Database Engine Tuning Advisor si serve di file e tabelle di traccia, script Transact-SQL o file XML come input del carico di lavoro. Quando usi Amazon RDS, un carico di lavoro può essere un file in un computer client o una tabella di database in un database Amazon RDS for SQL Server accessibile al computer client. Il file o la tabella devono contenere query sul database che desideri ottimizzare in un formato riproducibile.

Per ottenere la massima efficacia di Tuning Advisor, i carichi di lavoro dovrebbero essere il più possibile realistici. Puoi generare un file o una tabella del carico di lavoro creando una traccia dell'istanza database. Quando una traccia è in esecuzione, puoi simulare un carico sull'istanza database oppure eseguire le applicazioni con carico normale.

Esistono due tipi di tracce: lato client e lato server. Le tracce lato client sono più facili da configurare e puoi osservare in tempo reale gli eventi di traccia che vengono acquisiti in SQL Server Profiler. Una traccia lato server è più complicata da configurare e richiede una certa quantità di scripting Transact-SQL. Inoltre, la traccia occupa spazio di storage perché viene trascritta in un file sull'istanza database in Amazon RDS. È importante monitorare quanto spazio di storage viene utilizzato da una traccia in esecuzione lato server perché l'istanza database potrebbe acquisire lo stato di storage completo e non sarebbe più disponibile se lo spazio di storage venisse esaurito.

Per le tracce lato client, dopo che una quantità sufficiente di dati di traccia è stata acquisita in SQL Server Profiler, puoi generare il file del carico di lavoro salvando la traccia in un file sul computer locale o in una tabella di database su un'istanza database accessibile dal computer client. Il principale svantaggio dell'utilizzo di una traccia lato client consiste nel fatto che la traccia potrebbe non essere in grado di acquisire tutte le query in condizioni di carico intenso. Ciò potrebbe rendere meno efficace l'analisi eseguita da Database Engine Tuning Advisor. Se devi eseguire una traccia con carichi intensi e desideri fare in modo che tale traccia acquisisca ogni query di una sessione di traccia, è preferibile utilizzare una traccia lato server.

Per le tracce lato server, devi memorizzare i file di traccia sull'istanza database in un file del carico di lavoro idoneo oppure puoi salvare la traccia in una tabella sull'istanza database dopo il suo completamento. Puoi utilizzare SQL Server Profiler per salvare la traccia in un file sul computer o fare in modo che Tuning Advisor legga la tabella di traccia sull'istanza database.

Esecuzione di una traccia lato client su un'istanza database SQL Server

Per eseguire una traccia lato client su un'istanza database SQL Server

1. Avvia SQL Server Profiler. SQL Server Profiler è installato nella sottocartella Performance Tools della cartella della tua istanza SQL Server. Per avviare una traccia lato client, devi caricare o definire un modello di definizione di traccia.

2. Nel menu SQL Server Profiler File (File di SQL Server Profiler), fai clic su New Trace (Nuova traccia). Nella casella di dialogo Connect to Server (Connessione al server), immetti l'endpoint dell'istanza database, la porta, il nome utente e la password master per il database per cui desideri eseguire la traccia.

3. Nella casella di dialogo Trace Properties (Proprietà traccia), immetti un nome per la traccia e scegli un modello di definizione della traccia. Un modello predefinito, TSQL_Replay, viene fornito con l'applicazione. Puoi modificare questo modello per definire la tua traccia. Modifica gli eventi e le relative informazioni nella scheda Events Selection (Selezione eventi) della casella di dialogo Trace Properties.

   Per ulteriori informazioni sui modelli di definizione della traccia e sull'utilizzo di SQL Server Profiler per specificare una traccia lato client, consulta Database Engine Tuning Advisor nella documentazione Microsoft..

4. Avvia la traccia lato client e osserva in tempo reale le query SQL che vengono eseguite sull'istanza database.

5. Selezionare Stop Trace (Arresta traccia) dal menu File (File) quando hai completato la traccia. Salva i risultati in un file o come tabella di traccia sull'istanza database.

Esecuzione di una traccia lato server su un'istanza database SQL Server

Scrivere script per la creazione di una traccia lato server può essere complicato e non rientra nell'ambito di questo documento. Questa sezione contiene script che puoi utilizzare come esempio. Come per le tracce lato client, l'obiettivo è creare un file del carico di lavoro o una tabella di traccia che puoi aprire con Database Engine Tuning Advisor.

Di seguito è riportato uno script sintetico di esempio per l'avvio di una traccia lato server e l'acquisizione dei dettagli in un file del carico di lavoro. All'inizio dello script la traccia viene salvata nel file RDSTrace.trc nella directory D:\RDSDBDATA\Log e viene eseguito il rollover ogni 100 MB, pertanto i file di traccia sequenziali sono denominati RDSTrace_1.trc, RDSTrace_2.trc, ecc.

DECLARE @file_name NVARCHAR(245) = 'D:\RDSDBDATA\Log\RDSTrace';
DECLARE @max_file_size BIGINT = 100;
DECLARE @on BIT = 1
DECLARE @rc INT
DECLARE @traceid INT

EXEC @rc = sp_trace_create @traceid OUTPUT, 2, @file_name, @max_file_size
IF (@rc = 0) BEGIN
   EXEC sp_trace_setevent @traceid, 10, 1, @on
   EXEC sp_trace_setevent @traceid, 10, 2, @on
   EXEC sp_trace_setevent @traceid, 10, 3, @on
 . . .
   EXEC sp_trace_setfilter @traceid, 10, 0, 7, N'SQL Profiler'
   EXEC sp_trace_setstatus @traceid, 1
   END

L'esempio seguente consiste in uno script per l'arresto di una traccia. La traccia creata dallo script precedente continua a essere eseguita finché non la arresti esplicitamente o il processo esaurisce lo spazio su disco.

DECLARE @traceid INT
SELECT @traceid = traceid FROM ::fn_trace_getinfo(default) 
WHERE property = 5 AND value = 1 AND traceid <> 1 

IF @traceid IS NOT NULL BEGIN
   EXEC sp_trace_setstatus @traceid, 0
   EXEC sp_trace_setstatus @traceid, 2
END 

Puoi salvare i risultati di una traccia lato server in una tabella di database e servirti di questa tabella come carico di lavoro per Tuning Advisor utilizzando la funzione fn_trace_gettable. Con i comandi seguenti i risultati di tutti i file denominati RDSTrace.trc presenti nella directory D:\rdsdbdata\Log, inclusi tutti i file di rollover come RDSTrace_1.trc, vengono caricati in una tabella denominata RDSTrace nel database corrente.

SELECT * INTO RDSTrace
FROM fn_trace_gettable('D:\rdsdbdata\Log\RDSTrace.trc', default); 

Per salvare uno specifico file di rollover in una tabella, ad esempio il file RDSTrace_1.trc, specifica il nome del file e sostituisci il valore predefinito con 1 come ultimo parametro di fn_trace_gettable.

SELECT * INTO RDSTrace_1
FROM fn_trace_gettable('D:\rdsdbdata\Log\RDSTrace_1.trc', 1); 

Esecuzione di Tuning Advisor con una traccia

Una volta creata una traccia come file locale o tabella di database, puoi eseguire Tuning Advisor sull'istanza database. Per utilizzare Tuning Advisor con Amazon RDS si segue la stessa procedura adottata quando si lavora con un'istanza SQL Server remota standalone. Puoi utilizzare l'interfaccia utente di Tuning Advisor sulla macchina client oppure l'utilità dta.exe dalla riga di comando. In entrambi i casi, devi connetterti all'istanza database Amazon RDS utilizzando il relativo endpoint e fornire il tuo nome utente e la tua password master quando utilizzi Tuning Advisor.

L'esempio di codice seguente mostra l'uso dell'utilità a riga di comando dta.exe su un'istanza database Amazon RDS con un endpoint dta.cnazcmklsdei.us-east-1.rds.amazonaws.com. Nell'esempio sono inclusi il nome utente master admin e la password utente master test, il database di esempio da sintonizzare è denominato dal computer C:\RDSTrace.trc. Il codice della riga di comando di esempio specifica una sessione di traccia denominata RDSTrace1 e i file di output nel computer locale denominati RDSTrace.sql per lo script di output SQL, RDSTrace.txt per un file dei risultati e RDSTrace.xml per un file XML dell'analisi. Nel database RDSDTA è anche specificata una tabella degli errori denominata RDSTraceErrors.

dta -S dta.cnazcmklsdei.us-east-1.rds.amazonaws.com -U admin -P test -D RDSDTA -if C:\RDSTrace.trc -s RDSTrace1 -of C:\ RDSTrace.sql -or C:\ RDSTrace.txt -ox C:\ RDSTrace.xml -e RDSDTA.dbo.RDSTraceErrors 

Ecco il codice della riga di comando di esempio, ma il carico di lavoro di input qui è una tabella nell'istanza Amazon RDS remota denominata RDSTrace, che si trova nel database RDSDTA.

dta -S dta.cnazcmklsdei.us-east-1.rds.amazonaws.com -U admin -P test -D RDSDTA -it RDSDTA.dbo.RDSTrace -s RDSTrace1 -of C:\ RDSTrace.sql -or C:\ RDSTrace.txt -ox C:\ RDSTrace.xml -e RDSDTA.dbo.RDSTraceErrors

Per un elenco completo dei parametri della riga di comando dell'utilità dta, consulta dta Utility nella documentazione di Microsoft.