Modul Lab 7
Performance
TUNING
ABD 2024
PROGRAM STUDI INFORMATIKA
FAKULTAS TEKNOLOGI INDUSTRI
UNIVERSITAS ATMA JAYA YOGYAKARTA
BY IYORI MELIS ANGEL
Perhatikan Instruksinya!
Modul ini merupakan modul hands on, seiring berjalannya modul ini praktikan
diharapkan untuk mengikuti step yang dijelaskan di SQL Server masing-
masing sebagai pengumpulan Guided.
ATURAN: Baca atau baca sekilas teori, praktekkan pada SQL Server
DOWNLOAD:
[Link]
usp=sharing
Restore Database
Buka MS SQL Server kalian
Download [Link] pada link di halaman
sebelumnya, kemudian klik kanan pada Databases dan pilih Restore
Database...
Pilih source Device dengan menekan titik 3.
Restore Database
Buka MS SQL Server kalian
Add lokasi file [Link] pada device kalian, dan
tekan OK.
Tekan OK lagi, lalu lanjut baca teori dulu
Istilah Penting
Performance
Tuning?
Proses mengoptimalkan kinerja sistem atau aplikasi untuk meningkatkan
kecepatan, efisiensi, dan daya tanggapnya. Proses ini melibatkan identifikasi dan
penanganan bottlenecks serta masalah lainnya yang membatasi kinerja sistem.
(Source)
Beberapa tools performance tuning pada MS SQL Server: Performance monitor,
activity monitor, sql profiler, execution plans, dll
Tuning in
Database?
Proses penyesuaian atau pengoptimalan untuk meningkatkan performa basis
data sehingga dapat berjalan dengan efisien. Tujuan dari pengaturan basis data
adalah untuk mengatur data agar lebih cepat diambil atau diakses. (Source)
SQL
Tuning?
Proses meningkatkan efisiensi query SQL untuk mempercepat kinerja server
basis data. Tujuan utamanya adalah memperbaiki cara query dijalankan agar
mengurangi waktu eksekusi dan penggunaan sumber daya, sehingga basis data
dapat merespons permintaan lebih cepat dan efisien. (Source)
9 Penghambat Kinerja
Basis Data
Masalah pada indeks*
Indeks tidak ada atau tidak dikonfigurasi dengan benar.
Statistik yang Tidak Akurat
Statistik yang tidak akurat dapat menghasilkan rencana query yang tidak optimal
Desain Query yang Buruk
Menyebabkan penggunaan sumber daya yang tidak efisien.
Execution plan yang buruk*
Menyebabkan cara pengambilan data yang tidak efisien.
Blocking dan Deadlock yang Berlebihan
Menyebabkan transaksi tertunda dan meningkatkan waktu tunggu.
Desain Basis Data yang Buruk
Desain yang buruk seperti kurangnya normalisasi atau hubungan yang salah
menyebabkan kinerja lambat
Recompiling Execution Plans
Mengkonsumsi sumber daya yang signifikan.
Query Ad-hoc yang Berlebihan
Mengakibatkan penggunaan sumber daya yang tidak efisien.
Fragmentasi Indeks yang Tinggi
Memperlambat waktu pengambilan data dan meningkatkan beban kerja.
Keterangan:
* = Akan dipelajari di modul ini
Execution Plan
Execution Plan adalah peta yang dibuat oleh query optimizer yang
menunjukkan bagaimana query SQL akan dieksekusi oleh basis data.
Rencana ini dihasilkan dengan bantuan statistik tabel dan memberikan
wawasan tentang langkah-langkah yang akan diambil untuk mendapatkan
hasil query. Execution plan dapat disimpan dalam bentuk grafikal, teks, dan
XML.
3 Jenis Execution plan:
Estimated Execution Plan: Menunjukkan bagaimana query diharapkan
akan dieksekusi berdasarkan statistik yang ada tanpa harus execute query.
Actual Execution Plan: Menunjukkan langkah-langkah yang benar-benar
diambil oleh basis data selama eksekusi query.
Live Query Statistics: Memberikan visualisasi real-time dari eksekusi query.
Query optimizer?
Bagian dari mesin SQL Server yang digunakan untuk menentukan rencana eksekusi SQL yang
paling efisien. Query Optimizer ini bertugas mengoptimalkan setiap pernyataan SQL
berdasarkan statistik yang ada.
Melihat Estimated
Execution Plan
Tekan New Query, lalu inputkan query berikut.
Tenang aja ini bisa copas kok
SELECT [AddressID]
,[AddressLine1]
,[AddressLine2]
,[City]
,[StateProvinceID]
,[PostalCode]
,[SpatialLocation]
,[rowguid]
,[ModifiedDate]
FROM [AdventureWorks2016].[Person].[Address]
Melihat Estimated
Execution Plan
Select seluruh query dan tekan Display Estimated Execution Plan
atau gunakan shortcut Ctrl + L
Pada hasil, arahkan cursor ke Clustered Index Scan untuk melihat
informasi lebih detail
Melihat Actual
Execution Plan
Pada query yang sama, select seluruh query dan tekan Include
Actual Execution Plan atau gunakan shortcut Ctrl + M, setelah itu
Execute.
Pada bagian hasil tekan tab Execution tab, dan arahkan cursor ke
Clustered Index untuk melihat informasi lebih detail dari actual
execution plan.
Melihat Live Query
Statistics
Pada query yang sama tekan Include Live Query Statistics, lalu
Execute.
Lihat Hasilnya... Habis ini balik baca teori
Index
Indeks adalah struktur khusus yang mempercepat pengambilan data dari
tabel dan tampilan (views). Tanpa indeks, mesin query harus memeriksa setiap
catatan dari awal hingga akhir (disebut table scan), tetapi dengan indeks,
pencarian akan berhenti segera setelah indeks yang relevan ditemukan,
sehingga mengurangi waktu pemrosesan.
Jenis-jenis index dalam MS SQL Server: clustered index*, non clustered index*,
column stored index, unique index, covering index, filtered index, hash index,
spatial index, full text index, XML index
Keterangan:
* = akan dipelajari di modul ini
Clustered Index
Clustered Index adalah jenis indeks khusus dalam basis data yang menentukan
urutan fisik penyimpanan data dalam tabel. Indeks ini tidak hanya
mereferensikan data, tetapi juga mengatur bagaimana data disimpan secara
fisik, mirip dengan bagaimana folder dalam lemari arsip diatur berdasarkan
kriteria tertentu dan dokumen di dalamnya disusun dalam urutan yang
sistematis.
Ketika kita membuat clustered index pada tabel di SQL Server, data diatur ulang
dalam struktur B-Tree berdasarkan kolom kunci yang ditentukan oleh clustered
index. Contohnya:
Clustered Index
Kelebihan:
Query yang memanfaatkan urutan yang ditentukan oleh clustered index
dapat mengalami peningkatan performa yang signifikan, cth. query DML.
Overhead penyimpanan yang lebih rendah: Tidak diperlukan struktur
terpisah untuk menyimpan data indeks, sehingga menghemat ruang
penyimpanan dibandingkan dengan non-clustered index dengan banyak
kolom yang disertakan.
Limitasi:
Hanya satu per tabel: Sebuah tabel hanya dapat memiliki satu clustered
index karena data hanya dapat diurutkan secara fisik dalam satu cara.
Update berpotensi lebih lambat: Memasukkan atau memperbarui data
dapat menjadi lebih lambat dengan clustered index karena seluruh data
perlu diatur ulang untuk mempertahankan urutan sortir.
Menambahkan
Clustered Index
Pada query sebelumnya tambahkan INTO Address_TEMP, lalu
Execute.
Setelah itu, pada bagian paling bawah query ketik Address_TEMP,
lalu select dan tekan Alt + f1.
Pada bagian ini, artinya tabel [Link] sudah berhasil diduplikasi
ke tabel sementara Address_TEMP.
Menambahkan
Clustered Index
Selanjutnya, kita akan menambahkan clustered index dengan
mengetik query dibawah, select query kemudian Execute.
Hasilnya data pada kolom AddressID sudah berubah menjadi
clustered index.
Cara memeriksa:
Apabila kita mengetikkan Address_TEMP, select, dan jalankan alt + f1,
maka akan terlihat bahwa Address_TEMP sudah memiliki clustered
index CIX_Address_TEMP
Non-Clustered Index
Bayangkan kita memiliki sebuah buku yang berisi daftar resep masakan. Kita
sering mencari resep berdasarkan bahan-bahan tertentu. Sekarang, bayangkan
kita ingin mencari semua resep yang menggunakan tomat sebagai salah satu
bahan.
Dengan non-clustered index, itu seperti kita memiliki daftar di bagian
belakang buku yang disusun berdasarkan bahan-bahan. Kita dapat melihat di
bagian "Bahan-bahan" di bagian belakang buku dan menemukan entri untuk
"Tomat". Di sampingnya, kita dapat melihat daftar nomor halaman di mana
resep-resep yang menggunakan tomat tercantum.
Non-clustered index dapat membantu kita menemukan informasi dengan
cepat tanpa harus membaca seluruh data dari awal. Struktur ini menyimpan
nilai-nilai kolom tertentu yang dipilih (index key) dan penunjuk yang mengarah
ke baris aktual dalam tabel yang berisi nilai tersebut.
Non-Clustered Index
Kelebihan:
Berbeda dengan clustered index yang mengurutkan ulang seluruh data
tabel, non-clustered index menciptakan sebuah struktur terpisah.
Pencarian Lebih Cepat
Kekurangan:
Non-clustered index membutuhkan ruang penyimpanan tambahan untuk
struktur indeks terpisah.
Terdapat langkah tambahan untuk mengikuti penunjuk ke baris data
aktual, yang bisa sedikit lebih lambat dibandingkan mengakses data dari
clustered index. Salah satunya pada query DML.
Menambahkan Non
-Clustered Index
Selanjutnya, kita akan menambahkan non-clustered index dengan
mengetik query dibawah, select query kemudian Execute.
Hasilnya sudah terbentuk struktur non-clustered index terpisah
untuk kolom City.
Cara memeriksa:
Apabila kita mengetikkan Address_TEMP, select, dan jalankan alt + f1,
maka akan terlihat bahwa Address_TEMP sudah memiliki non
clustered index NCIX_Address_TEMP.
Table Scan VS Index
Seek
Table Scan:
Operasi di mana DBMS secara berurutan membaca setiap baris dalam tabel
untuk memenuhi kriteria pencarian dalam sebuah query. Ini adalah metode
pencarian paling lambat karena memerlukan pemeriksaan menyeluruh pada
setiap baris dalam tabel. Digunakan apabila tidak ada clustered atau non-
clustered index.
Index Seek:
Operasi di mana DBMS menggunakan indeks untuk langsung mencari data
yang sesuai dengan kriteria pencarian dalam sebuah query. Digunakan dalam
pencarian data dengan clustered-index.
Key Lookup
Key Lookup:
Operasi di mana DBMS menggunakan indeks untuk menemukan baris yang
memenuhi kriteria pencarian, tetapi kemudian perlu melakukan pencarian
tambahan menggunakan nilai kunci yang ditemukan dalam indeks untuk
mengambil data yang sebenarnya dari tabel. Biasanya digunakan pada
pencarian dengan non-clustered index, dengan ciri query menggunakan
WHERE, JOIN, ataupun HAVING.
Table Scan VS Index
Seek and Key Lookup
Buat tabel baru Address_table_scan dengan menjalankan
SELECT [AddressID]
,[AddressLine1]
,[AddressLine2]
,[City]
,[StateProvinceID]
,[PostalCode]
,[SpatialLocation]
,[rowguid]
,[ModifiedDate]
INTO Address_table_scan
FROM [AdventureWorks2016].[Person].[Address]
Hidupkan Include Actual Execution Plan
Table Scan VS Index
Seek and Key Lookup
Ketik Query berikut pada bagian bawah
SELECT * FROM Address_table_scan WHERE City='Boston'
SELECT * FROM Address_TEMP WHERE City='Boston'
Select query yang pertama lalu Execute
Buka bagian Execution plan, dan ditemukan bahwa query
menggunakan table scan (menelusuri seluruh data dalam tabel)
padahal yang dicari hanya 1 data saja = sangat tidak efektif.
Table Scan VS Index
Seek and Key Lookup
Lakukan hal yang sama pada query selanjutnya, dan didapat hasil
dengan cost dan waktu yang lebih sedikit
Dan Semangat!