Esercizio - Ottimizzare le prestazioni dell'applicazione

10 minuti

In questo esercizio si osserverà un nuovo scenario di prestazioni e lo si risolve ottimizzando l'applicazione e le query.

Ottimizzare le prestazioni dell'applicazione con Azure SQL

In alcuni casi, la migrazione di un'applicazione esistente e di un carico di lavoro di query SQL in Azure può offrire opportunità di ottimizzazione delle query.

Per supportare una nuova estensione di un sito Web per fornire un sistema di valutazione da parte dei clienti per gli ordini di AdventureWorks, è necessario aggiungere una nuova tabella per un set elevato di attività INSERT simultanee. È stato testato il carico di lavoro query SQL in un computer di sviluppo con SQL Server 2022 con un'unità SSD locale per il database e il log delle transazioni.

Quando si sposta il test nel database SQL di Azure usando il livello per utilizzo generico (8 vCore), il carico di lavoro INSERT è più lento. È necessario modificare l'obiettivo o il livello di servizio per supportare il nuovo carico di lavoro oppure è preferibile esaminare l'applicazione?

È possibile trovare tutti gli script per questo esercizio nella cartella 04-Performance\tuning_applications nel repository GitHub clonato o nel file ZIP scaricato.

Creare una nuova tabella per l'applicazione

In Esplora oggetti selezionare il database AdventureWorks. Usare File>aperto>file per aprire lo script order_rating_ddl.sql per creare una tabella nel AdventureWorks database. La finestra dell'editor di query avrà un aspetto simile al testo seguente:

DROP TABLE IF EXISTS SalesLT.OrderRating;
GO
CREATE TABLE SalesLT.OrderRating
(OrderRatingID int identity not null,
SalesOrderID int not null,
OrderRatingDT datetime not null,
OrderRating int not null,
OrderRatingComments char(500) not null);
GO

Selezionare Esegui per eseguire lo script.

Caricare query per monitorare l'esecuzione di query

Caricare ora alcune query T-SQL per le DMV per osservare le prestazioni di query per le query attive, le attese e l'I/O. Caricare tutte le query nel contesto del database AdventureWorks.

In Esplora oggetti selezionare il database AdventureWorks. Usare File>aperto>file per aprire lo script sqlrequests.sql per esaminare le query SQL attive. La finestra dell'editor di query avrà un aspetto simile al testo seguente:

SELECT er.session_id, er.status, er.command, er.wait_type, er.last_wait_type, er.wait_resource, er.wait_time
FROM sys.dm_exec_requests er
INNER JOIN sys.dm_exec_sessions es
ON er.session_id = es.session_id
AND es.is_user_process = 1;

In Esplora oggetti selezionare il database AdventureWorks. Usare File>aperto>file per aprire lo script top_waits.sql per esaminare i primi tipi di attesa per conteggio. La finestra dell'editor di query avrà un aspetto simile al testo seguente:
```
SELECT * FROM sys.dm_os_wait_stats
ORDER BY waiting_tasks_count DESC;
```
In Esplora oggetti selezionare il database AdventureWorks. Usare File>aperto> file per aprire lo script tlog_io.sql per osservare la latenza per le scritture del log delle transazioni. La finestra dell'editor di query avrà un aspetto simile al testo seguente:
```
SELECT io_stall_write_ms/num_of_writes as avg_tlog_io_write_ms, * 
FROM sys.dm_io_virtual_file_stats
(db_id('AdventureWorks'), 2);
```

Preparare lo script del carico di lavoro per l'esecuzione

Aprire e modificare lo script del carico di lavoro order_rating_insert_single.cmd .

Sostituire l'utente unique_id specificato nel primo esercizio per il nome del server per .-S parameter
Sostituire la password specificata nella distribuzione del database dal primo esercizio per .-P parameter
Salvare le modifiche apportate al file.

Eseguire il carico di lavoro

Da un prompt dei comandi di PowerShell passare alla directory per l'attività di questo modulo:
```
cd c:<base directory>\04-Performance\tuning_applications
```
Eseguire il carico di lavoro con il comando seguente:
```
.\order_rating_insert_single.cmd
```
Questo script usa il programma ostress.exe per eseguire 25 utenti simultanei tramite l'istruzione T-SQL seguente (nello script order_rating_insert_single.sql):
```
DECLARE @x int;
SET @x = 0;
WHILE (@x < 500)
BEGIN
SET @x = @x + 1;
INSERT INTO SalesLT.OrderRating
(SalesOrderID, OrderRatingDT, OrderRating, OrderRatingComments)
VALUES (@x, getdate(), 5, 'This was a great order');
END
```
Da questo script è possibile notare che non si tratta esattamente di una rappresentazione reale dei dati provenienti dal sito Web. Vengono invece simulate numerose valutazioni degli ordini che vengono inserite nel database.

Osservare le prestazioni delle DMV e del carico di lavoro

Eseguire ora in SQL Server Management Studio (SSMS) le query caricate in precedenza per osservare le prestazioni. Eseguire le query per sqlrequests.sql, top_waits.sql e tlog_io.sql.

Usando queste query, è possibile osservare i fatti seguenti:

Molte richieste hanno costantemente wait_type corrispondente a WRITELOG, con un valore > 0.
Il WRITELOG tipo di attesa è uno dei conteggi più alti per i tipi di attesa.
Il tempo medio di scrittura nel log delle transazioni (la avg_tlog_io_write_ms colonna nel set di risultati tlog_io.sql ) è in un punto intorno a 2 ms.

La durata di questo carico di lavoro in un'istanza di SQL Server 2022 con un'unità SSD è di circa 10-12 secondi. La durata totale nel database SQL di Azure con un core v8 di quinta generazione è di circa 25 secondi.

WRITELOG i tipi di attesa con tempi di attesa più elevati indicano lo scaricamento della latenza nel log delle transazioni. Un tempo di attesa di 2 ms per la scrittura non sembra molto, ma in un'unità SSD locale queste attese possono essere inferiori a 1 ms.

Scegliere una soluzione

Il problema non è una percentuale elevata di attività di scrittura del log. Il portale di Azure e sys.dm_db_resource_stats non mostra numeri superiori al 20-25% (non è necessario eseguirne una query). Il problema non è nemmeno il limite di operazioni di I/O al secondo. Il problema consiste nel fatto che questo carico di lavoro dell'applicazione richiede una bassa latenza per le scritture nel log delle transazioni e il livello per utilizzo generico non è progettato per questo tipo di requisiti di latenza. La latenza di I/O prevista per database SQL di Azure è di 5-7 ms.

Nota

Il database SQL di Azure per utilizzo generico documenta medie di latenza di I/O approssimative di 5-7 (scritture) e 5-10 (letture). Possono verificarsi latenze simili a questi valori. Le latenze per il livello per utilizzo generico di Istanza gestita di SQL di Azure sono simili. Se l'applicazione è molto sensibile alle latenze di I/O, prendere in considerazione i livelli business critical.

Esaminare lo script T-SQL del carico di lavoro order_rating_insert_single.sql . Ogni INSERT è un singolo commit della transazione, che richiede uno scaricamento del log delle transazioni.

Un commit per ogni inserimento non è efficiente, ma l'applicazione non è stata interessata in un'unità SSD locale perché ogni commit è avvenuto molto velocemente. Il piano tariffario business critical (obiettivo di servizio o SKU) fornisce unità SSD locali con una latenza più bassa. È possibile che sia presente un'ottimizzazione dell'applicazione, quindi il carico di lavoro non è sensibile alla latenza di I/O per il log delle transazioni.

È possibile modificare il batch T-SQL per il carico di lavoro per eseguire il wrapping di un'iterazione BEGIN TRAN/COMMIT TRAN INSERT intorno alle iterazioni.

Eseguire un carico di lavoro modificato più efficiente

Apportare modifiche agli script ed eseguirli per riscontrare prestazioni di I/O più efficienti. È possibile trovare il carico di lavoro modificato nello script order_rating_insert.sql .

Preparare lo script del carico di lavoro modificando order_rating_insert.cmd per usare il nome e la password del server corretti.
Eseguire il carico di lavoro modificato usando lo script order_rating_insert.cmd , in modo analogo a come è stato eseguito lo script del carico di lavoro precedente.

Osservare i nuovi risultati

Esaminare i risultati dello script T-SQL per sqlrequests.sql in SSMS. È possibile osservare un minor numero di attese di WRITELOG e un tempo complessivo inferiore per queste attese.

A questo punto, il carico di lavoro viene eseguito molto più velocemente rispetto all'esecuzione precedente. Questo è un esempio di ottimizzazione di un'applicazione per le query SQL che verranno eseguite all'interno o all'esterno di Azure.

Nota

Questo carico di lavoro può essere eseguito ancora più velocemente su un'istanza di database SQL di Azure con un tipo di connessione redirect. La distribuzione eseguita in questo esercizio usa un tipo di connessione predefinito, ovvero un tipo di proxy perché si è connessi all'esterno di Azure. L'uso del reindirizzamento può accelerare significativamente un carico di lavoro come questo, considerando i round trip richiesti dal client al server.
Osservare la durata del carico di lavoro. Il carico di lavoro viene eseguito così velocemente che può essere difficile osservare i dati di diagnostica delle query usate in precedenza in questa attività.

Il concetto di "invio in batch" può essere utile per la maggior parte delle applicazioni, incluse quelle connesse ad Azure SQL.

Suggerimento

La governance delle risorse in Azure può influire sulle transazioni di grandi dimensioni e i sintomi saranno LOG_RATE_GOVERNOR. In questo esempio la colonna non Null char(500) riempie gli spazi e genera record di log delle transazioni di grandi dimensioni. È possibile ottimizzare ulteriormente le prestazioni impostando tale colonna come a lunghezza variabile.

Nell'unità successiva verranno fornite informazioni sulle prestazioni intelligenti in Azure SQL.

Continua