Latihan - Mengoptimalkan kinerja aplikasi

10 menit

Dalam latihan ini, Anda akan mengamati skenario performa baru dan menyelesaikannya dengan mengoptimalkan aplikasi dan kueri.

Optimalkan kinerja aplikasi dengan Azure SQL

Dalam beberapa kasus, memigrasikan aplikasi yang ada dan beban kerja kueri SQL ke Azure dapat mengungkap peluang untuk mengoptimalkan dan menyetel kueri.

Untuk mendukung ekstensi baru ke situs web untuk pesanan AdventureWorks untuk menyediakan sistem peringkat dari pelanggan, Anda perlu menambahkan tabel baru untuk serangkaian aktivitas INSERT bersamaan yang berat. Anda telah menguji beban kerja kueri SQL di komputer pengembangan dengan SQL Server 2022 yang memiliki drive SSD lokal untuk database dan log transaksi.

Saat Anda memindahkan pengujian ke Azure SQL Database dengan menggunakan tingkat tujuan umum (8 vCores), beban kerja INSERT lebih lambat. Haruskah Anda mengubah tujuan atau tingkat layanan untuk mendukung beban kerja baru, atau haruskah Anda melihat aplikasi?

Anda dapat menemukan semua skrip untuk latihan ini di folder 04-Performance\tuning_applications di repositori GitHub yang Anda kloning atau file zip yang Anda unduh.

Membuat tabel baru untuk aplikasi

Pada Object Explorer, pilih database AdventureWorks. Gunakan File Buka>File> untuk membuka skrip order_rating_ddl.sql untuk membuat tabel dalam AdventureWorks database. Jendela editor kueri Anda akan terlihat seperti teks berikut:

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

Pilih Jalankan untuk menjalankan skrip.

Memuat kueri untuk memantau eksekusi kueri

Sekarang mari kita muat beberapa kueri T-SQL untuk tampilan manajemen dinamis (DMV) untuk mengamati kinerja kueri untuk kueri aktif, tunggu, dan I/O. Muat semua kueri ini dalam konteks AdventureWorks database.

Pada Object Explorer, pilih database AdventureWorks. Gunakan File Buka>File> untuk membuka skrip sqlrequests.sql untuk melihat kueri SQL aktif. Jendela editor kueri Anda akan terlihat seperti teks berikut:

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;

Pada Object Explorer, pilih database AdventureWorks. Gunakan File Buka>File> untuk membuka skrip top_waits.sql untuk melihat jenis tunggu teratas menurut hitungan. Jendela editor kueri Anda akan terlihat seperti teks berikut:
```
SELECT * FROM sys.dm_os_wait_stats
ORDER BY waiting_tasks_count DESC;
```
Pada Object Explorer, pilih database AdventureWorks. Gunakan File Buka>File> untuk membuka skrip tlog_io.sql untuk mengamati latensi untuk penulisan log transaksi. Jendela editor kueri Anda akan terlihat seperti teks berikut:
```
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);
```

Siapkan skrip beban kerja untuk eksekusi

Buka dan edit skrip beban kerja order_rating_insert_single.cmd .

Gantikan unique_id Anda yang diberikan dalam latihan pertama untuk nama server untuk -S parameter.
Ganti kata sandi yang Anda berikan dalam penyebaran database dari latihan pertama untuk -P parameter.
Simpan perubahan pada file.

Jalankan beban kerja

Dari prompt perintah PowerShell, ubah ke direktori untuk latihan ini:
```
cd c:<base directory>\04-Performance\tuning_applications
```
Jalankan beban kerja dengan perintah berikut:
```
.\order_rating_insert_single.cmd
```
Skrip ini menggunakan program ostress.exe untuk menjalankan 25 pengguna bersamaan dengan menjalankan pernyataan T-SQL berikut (dalam 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
```
Anda dapat melihat dari skrip ini bahwa itu bukan penggambaran nyata data yang berasal dari situs web. Tetapi itu mensimulasikan banyak peringkat pesanan yang terproses ke dalam database.

Mengamati DMV dan kinerja beban kerja

Sekarang jalankan kueri di SQL Server Management Studio (SSMS) yang sebelumnya Anda muat untuk mengamati kinerja. Jalankan kueri untuk sqlrequests.sql, top_waits.sql, dan tlog_io.sql.

Dengan kueri ini, Anda dapat mengamati fakta-fakta berikut:

Kebanyakan permintaan selalu memiliki wait_type dari WRITELOG, dengan nilai > 0.
Jenis WRITELOG tunggu adalah salah satu jumlah tertinggi untuk jenis tunggu.
Waktu rata-rata untuk menulis ke log transaksi ( avg_tlog_io_write_ms kolom dalam kumpulan hasil tlog_io.sql ) berada di suatu tempat sekitar 2 mdtk.

Durasi beban kerja ini pada instans SQL Server 2022 dengan drive SSD adalah sekitar 10-12 detik. Total durasi pada Azure SQL Database dengan core Gen5 v8 adalah sekitar 25 detik.

WRITELOG jenis tunggu dengan waktu tunggu yang lebih tinggi menunjukkan pembilasan latensi ke log transaksi. Waktu tunggu 2 ms per penulis tidak tampak seperti banyak, tetapi pada drive SSD lokal menunggu ini bisa kurang dari 1 ms.

Memutuskan resolusi

Masalahnya bukan persentase aktivitas penulisan log yang tinggi. portal Azure dan sys.dm_db_resource_stats tidak menampilkan angka apa pun yang lebih tinggi dari 20-25 persen (Anda tidak perlu mengkuerinya). Masalahnya juga bukan batas IOPS. Masalahnya adalah bahwa beban kerja aplikasi ini sensitif terhadap latensi rendah untuk penulisan log transaksi, dan tingkat tujuan umum tidak dirancang untuk jenis persyaratan latensi ini. Latensi I/O yang diharapkan untuk Azure SQL Database adalah 5-7 mdtk.

Catatan

Tujuan umum Azure SQL Database mendokumentasikan perkiraan rata-rata latensi I/O sebagai 5-7 (tulis) dan 5-10 (baca). Anda mungkin mengalami keterlambatan lebih seperti angka-angka ini. Latencies untuk tujuan umum Azure SQL Managed Instance serupa. Jika aplikasi Anda sangat sensitif terhadap keterlambatan I/O, pertimbangkan tingkatan Business Critical.

Periksa skrip T-SQL beban kerja order_rating_insert_single.sql. Masing-masing INSERT adalah penerapan transaksi tunggal, yang memerlukan flush log transaksi.

Satu komit untuk setiap sisipan tidak efisien, tetapi aplikasi tidak terpengaruh pada SSD lokal karena setiap komit sangat cepat. Tingkat harga Business Critical (tujuan layanan atau SKU) menyediakan drive SSD lokal dengan latensi yang lebih rendah. Ada kemungkinan bahwa ada pengoptimalan aplikasi, sehingga beban kerja tidak sensitif terhadap latensi I/O untuk log transaksi.

Anda dapat mengubah batch T-SQL untuk beban kerja guna membungkus BEGIN TRAN/COMMIT TRAN iterasi INSERT .

Menjalankan beban kerja yang dimodifikasi dan lebih efisien

Lakukan pengeditan pada skrip dan jalankan untuk melihat kinerja I/O yang lebih efisien. Anda dapat menemukan beban kerja yang dimodifikasi di skrip order_rating_insert.sql .

Siapkan skrip beban kerja dengan mengedit order_rating_insert.cmd untuk menggunakan nama server dan kata sandi yang benar.
Jalankan beban kerja yang dimodifikasi dengan menggunakan skrip order_rating_insert.cmd , mirip dengan cara Anda menjalankan skrip beban kerja sebelumnya.

Amati hasil baru

Lihatlah hasil skrip T-SQL untuk sqlrequests.sql di SQL Server Management Studio. Perhatikan jauh lebih sedikit WRITELOG menunggu, dan secara keseluruhan lebih sedikit waktu tunggu untuk menunggu ini.

Sekarang beban kerja berjalan jauh lebih cepat dibandingkan dengan eksekusi sebelumnya. Ini adalah contoh penyetelan aplikasi untuk kueri SQL yang akan berjalan di dalam atau di luar Azure.

Catatan

Beban kerja ini dapat berjalan lebih cepat terhadap instans Azure SQL Database dengan jenis koneksi Pengalihan . Penyebaran yang telah Anda lakukan dalam latihan ini menggunakan jenis koneksi default, yang merupakan jenis proksi karena Anda tersambung di luar Azure. Menggunakan Redirect dapat secara signifikan mempercepat beban kerja seperti ini, mengingat perjalanan pulang pergi yang diperlukan dari klien ke server.
Amati durasi beban kerja baru. Beban kerja berjalan begitu cepat mungkin sulit untuk mengamati data diagnostik dari kueri yang digunakan sebelumnya dalam aktivitas ini.

Konsep "batching" dapat membantu sebagian besar aplikasi, termasuk yang terhubung ke Azure SQL.

Tip

Tata kelola sumber daya di Azure dapat memengaruhi transaksi yang sangat besar, dan gejalanya adalah LOG_RATE_GOVERNOR. Dalam contoh ini, kolom bukan null char(500) menyita ruang dan menyebabkan catatan log transaksi besar. Anda dapat mengoptimalkan kinerja lebih banyak lagi dengan menjadikan kolom tersebut sebagai kolom panjang variabel.

Di unit berikutnya, Anda akan mempelajari tentang performa cerdas di Azure SQL.