Menganalisis beban kerja basis data di instans DB Amazon RDS for SQL Server dengan basis data Engine Tuning Advisor

Basis data Engine Tuning Advisor adalah aplikasi klien yang disediakan oleh Microsoft yang menganalisis beban kerja basis data dan merekomendasikan serangkaian indeks optimal untuk basis data Microsoft SQL Server berdasarkan jenis kueri yang Anda jalankan. Seperti SQL Server Management Studio, Anda menjalankan Tuning Advisor dari komputer klien yang terhubung ke instans DB Amazon RDS yang menjalankan SQL Server. Komputer klien dapat berupa komputer lokal yang Anda jalankan di lokasi on-premise Anda sendiri atau dapat berupa instans Windows Amazon EC2 yang berjalan di wilayah yang sama dengan instans DB Amazon RDS Anda.

Bagian ini menunjukkan cara mengambil beban kerja Tuning Advisor yang akan dianalisis. Ini adalah proses yang dipilih untuk mengambil beban kerja karena Amazon RDS membatasi akses host ke instans SQL Server. Untuk informasi selengkapnya, lihat Database Engine Tuning Advisor dalam dokumentasi Microsoft.

Untuk menggunakan Tuning Advisor, Anda harus menyediakan beban kerja ke advisor tersebut. Beban kerja adalah sekumpulan pernyataan Transact-SQL yang dijalankan di basis data atau basis data yang ingin Anda selaraskan. Basis data Engine Tuning Advisor menggunakan file pelacakan, tabel pelacakan, skrip Transact-SQL, atau file XML sebagai input beban kerja saat mengatur basis data. Ketika bekerja dengan Amazon RDS, beban kerja dapat berupa file di komputer klien atau tabel basis data di DB Amazon RDS for SQL Server yang dapat diakses oleh komputer klien. File atau tabel harus berisi kueri dari basis data yang ingin disetel dalam format yang sesuai untuk diputar ulang.

Untuk mengoptimalkan Tuning Advisor, beban kerja harus serealistis mungkin. Anda dapat membuat file atau tabel beban kerja dengan melakukan pelacakan terhadap instans DB Anda. Saat pelacakan berjalan, Anda dapat menyimulasikan beban di instans DB atau menjalankan aplikasi dengan beban normal.

Ada dua jenis pelacakan: sisi klien dan sisi server. Pelacakan sisi klien lebih mudah diatur dan Anda dapat menonton pelacakan peristiwa yang direkam secara waktu nyata dalam SQL Server Profiler. Pelacakan sisi server lebih rumit untuk disiapkan dan memerlukan beberapa skrip Transact-SQL. Selain itu, karena pelacakan ditulis ke file di instans DB Amazon RDS, ruang penyimpanan akan digunakan oleh pelacakan. Ini penting untuk melacak berapa banyak ruang penyimpanan yang digunakan oleh pelacakan sisi server yang berjalan karena instans DB dapat memasuki status penuh penyimpanan dan tidak akan lagi tersedia jika ruang penyimpanan habis.

Untuk pelacakan sisi klien, ketika jumlah data pelacakan yang cukup telah diambil di SQL Server Profiler, Anda kemudian dapat membuat file beban kerja dengan menyimpan pelacakan ke file di komputer lokal atau di tabel basis data di instans DB yang tersedia untuk komputer klien. Kekurangan utama dari penggunaan pelacakan sisi klien adalah bahwa pelacakan mungkin tidak mengambil semua kueri saat berada dalam beban berat. Hal ini dapat melemahkan efektivitas analisis yang dilakukan oleh basis data Engine Tuning Advisor. Jika Anda perlu menjalankan pelacakan dalam beban berat dan ingin memastikan bahwa pelacakan dapat menangkap setiap kueri selama sesi pelacakan, Anda harus menggunakan pelacakan sisi server.

Untuk pelacakan sisi server, Anda harus mendapatkan file pelacakan di instans DB ke dalam file beban kerja yang sesuai atau Anda dapat menyimpan pelacakan ke tabel di instans DB setelah pelacakan selesai. Anda dapat menggunakan SQL Server Profiler untuk menyimpan pelacakan ke file di komputer lokal atau membuat Tuning Advisor membaca dari tabel pelacakan di instans DB.

Menjalankan pelacakan sisi klien di instans DB SQL Server

Untuk menjalankan pelacakan sisi klien di instans DB SQL Server

1. Mulai SQL Server Profiler. SQL Server Profiler diinstal di folder Performance Tools dari folder instans SQL Server. Anda harus memuat atau menentukan templat definisi pelacakan untuk memulai pelacakan sisi klien.

2. Di menu SQL Server Profiler File, pilih Pelacakan Baru. Di kotak dialog Hubungkan ke Server, masukkan titik akhir instans DB, port, nama pengguna utama, dan kata sandi basis data yang ingin Anda lacak.

3. Di kotak dialog Properti Pelacakan, masukkan nama pelacakan dan pilih templat definisi pelacakan. Templat default, TSQL_Replay, dikirimkan bersama aplikasi. Anda dapat mengedit templat ini untuk menentukan pelacakan Anda. Edit peristiwa dan informasi peristiwa di bagian tab Pemilihan Peristiwa dari kotak dialog Properti Pelacakan.

   Untuk informasi templat definisi pelacakan dan cara menggunakan SQL Server Profiler untuk menentukan pelacakan sisi klien selengkapnya, lihat Database Engine Tuning Advisor dalam dokumentasi Microsoft.

4. Mulai pelacakan sisi klien dan perhatikan kueri SQL secara waktu nyata saat berjalan di instans DB Anda.

5. Pilih Hentikan Pelacakan dari menu File setelah Anda menyelesaikan pelacakan. Simpan hasil sebagai file atau sebagai tabel pelacakan di instans DB.

Menjalankan pelacakan sisi server di instans DB SQL Server

Menulis skrip untuk membuat pelacakan sisi server bisa menjadi rumit dan tidak dicakup dalam dokumen ini. Bagian ini berisi contoh skrip yang dapat Anda gunakan sebagai contoh. Seperti halnya pelacakan sisi klien, tujuan skrip ini adalah untuk membuat file beban kerja atau tabel pelacakan yang dapat Anda buka menggunakan basis data Engine Tuning Advisor.

Berikut ini adalah contoh skrip singkat yang memulai pelacakan sisi server dan mengambil detail ke file beban kerja. Pelacakan pada mulanya menyimpan ke file RDSTrace.trc di direktori D:\RDSDBDATA\Log dan bergulir setiap 100 MB sehingga file pelacakan berikutnya diberi nama RDSTrace_1.trc, RDSTrace_2.trc, dst.

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

Contoh berikut adalah skrip yang menghentikan pelacakan. Perhatikan bahwa pelacakan yang dibuat oleh skrip sebelumnya terus berjalan hingga Anda secara eksplisit menghentikan pelacakan atau proses tidak berjalan di ruang disk.

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 

Anda dapat menyimpan hasil pelacakan sisi server ke tabel basis data dan menggunakan tabel basis data sebagai beban kerja untuk Tuning Advisor menggunakan fungsi fn_trace_gettable. Perintah berikut memuat hasil dari semua file bernama RDSTrace.trc di direktori D:\rdsdbdata\Log, termasuk semua file rollover, seperti RDSTrace_1.trc, ke dalam tabel bernama RDSTrace dalam basis data saat ini.

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

Untuk menyimpan file rollover tertentu ke tabel, misalnya file RDSTrace_1.trc, tentukan nama file rollover dan pengganti 1, bukan default, sebagai parameter terakhir untuk fn_trace_gettable.

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

Menjalankan Tuning Advisor dengan pelacakan

Setelah Anda membuat pelacakan, baik sebagai file lokal maupun sebagai tabel basis data, Anda kemudian dapat menjalankan Tuning Advisor pada instans DB Anda. Menggunakan Tuning Advisor dengan Amazon RDS adalah proses yang sama seperti ketika bekerja dengan instans SQL Server jarak jauh mandiri. Anda dapat menggunakan Tuning Advisor UI di mesin klien atau menggunakan utilitas dta.exe dari baris perintah. Dalam kedua kasus, Anda harus terhubung ke instans DB Amazon RDS menggunakan titik akhir instans DB serta memberikan nama pengguna utama dan kata sandi pengguna utama Anda saat menggunakan Tuning Advisor.

Contoh kode berikut menunjukkan penggunaan utilitas baris perintah dta.exe di instans DB Amazon RDS dengan titik akhir dta.cnazcmklsdei.us-east-1.rds.amazonaws.com. Contohnya mencakup nama pengguna utama admin dan kata sandi pengguna utama test, basis data contoh yang akan disetel diberi nama mesin bernama C:\RDSTrace.trc. Kode baris perintah contoh juga menentukan sesi pelacakan bernama RDSTrace1 dan menentukan file output ke mesin lokal bernama RDSTrace.sql untuk skrip output SQL, RDSTrace.txt untuk file hasil, dan RDSTrace.xml untuk file XML analisis. Terdapat pula tabel kesalahan yang ditentukan di basis data RDSDTA bernama 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 

Berikut adalah contoh kode baris perintah yang sama kecuali beban kerja input merupakan tabel di instans Amazon RDS jarak jauh yang disebut RDSTrace dan berada di basis data 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

Untuk daftar lengkap parameter baris perintah utilitas dta, lihat dta Utility dalam dokumentasi Microsoft.