Trik Mempercepat / Tuning Query Execution di MS SQL Server - Indexing & Security Issue

#Indexing

Pertanyaan

1. Apa itu index, Bagaimana cara kerjanya dan apa kegunaanya?
2. Kapan index diperlukan?
3. Field apa saja yang perlu di-index?
4. Susunan index yang optimal seperti apa?

Studi Kasus

Nama Tabel : T_Kegiatan
Fields :

• Id Int (Bukan Primary Key)
• Kegiatan varchar(100)
• TglJam datetime

Jumlah data : 35.000.000

Query yang dijalankan :
Select * from T_Kegiatan where TglJam>='2011/01/01 00:00:01" and TglJam <='2011/01/01 00:30:01"

Hasil : 

• Durasi execution 30menit
• Hasil yang keluar 8 Record

Kenapa lambat? Solusi :

• Table Scan alias mencari data satu-satu mulai record ke-1 sampai ke-35jt. Akibatnya adalah lambat, ini sama saja dengan buku super tebal tanpa daftar isi/index .
• Bayangkan agan mau cari sesuatu pd buku 1jt halaman tanpa daftar isi/index? (Ini Jawaban pertanyaan 1) 
• Mencari data pada data yang besar - 35jt record (Ini jawaban pertanyaan 2). 
• Tidak ada Index. Diperlukan index pada field TglJam. (Ini jawaban pertanyaan 3)

Jawaban

No.2 (Kapan index diperlukan?) 

• Saat Query terasa lambat karena jumlah data semakin banyak.
• Table dengan data yang sedang sampai besar (kira-kira di atas 5.000 record).
• Jika data yang ingin di catch kira2 1-2% dari total data.

No.3 (Field apa saja yangg perlu di-index?)

• Field yang mayoritas digunakan dalam where dan on
• Field yang nilai/isi datanya hampir unik
• Jawaban no.4 baca di bawah ya

Tambahan No.3:

• Index unik pada kolom parent key
• Index unik pada kolom kunci utama (primary key)
• Index pada kolom kunci asing (foreign key)
• Index pada kolom yang digunakan sebagai kriteria pemilihan
• Index pada kolom yang digunakan untuk pengurutan (order by)
• Index pada kolom yang digunakan untuk pengelompokan (group by)

No.4 (Susunan index yang optimal seperti apa?)

• Harusnya urutan where/on pada query mirip dengan urutan di index.
• Jangan buat terlalu banyak index karena malah akan memperlambat query. Ikuti jawaban no.3 untuk memilih field-field tepat, dan jangan buat index untuk semua kondisi where/on karena bisa jadi lemot abis. 
• Letakkan Field yang paling unik di depan, lalu berikutnya yang agak unik, begitu seterusnya (disarankan max 4 field index cukup memadai).

...

Study Kasus Memahami cara kerja SQL Engine Memilih Index
(Gunakan Estimate Execution Plan)

Table : T_transaksi (yang menampung semua transaksi pembayaran)
Fields : 

• id (Cluster Index primary key)
• id_cabang (Non Cluster Index = IdxCbg)
• tgltransaksi
• pembayaran.

Jumlah baris : 100.000

Hasil setelah di-group by id_cabang diperoleh hasil sebagai berikut :

• id_cabang - count
• 001 - 80000
• 002 - 19900
• 003 - 100

Soal 1 :
Select * from t_transaksi whre id_cabang='001'

Pertanyaan

1. jika query tersebut dijalankan apa index IdxCbg digunakan? 
2. index apa yang digunakan pada query diatas index seek atau scan?
3. cluster index atau non cluster index yang digunakan?

Soal 2 :
Select * from t_transaksi whre id_cabang='003'
 

Pertanyaan

1. jika query tersebut dijalankan apa index IdxCbg digunakan? 
2. index apa yang digunakan pada query diatas index seek atau scan? 
3. cluster index atau non cluster index yang digunakan?

Pertanyaan Akhir

1. menurut agan apa perlu buat index baru dgn nama IdxCbg field id_Cabang ? 
2. apa yang membedakan soal 1 dan soal 2?

Jawaban

Non Clustered index adalah sebuah index yang diurutan berdasarkan indexnya bukan dari row nya dimana clustered index akan sangat berguna jika ingin mencari data yang sedikit di dalam row yang jumlahnya banyak

Clustered index adalah sebuah index yang akan membuat baris baris di dalam row menjadi berurutan (jadi disini row di dalam table yang diurutkan bukan dari indexnya) Hal ini sangat berguna karena pembacaan row menjadi sequential / tidak loncat-loncat.

Jadi yang soal 1 harusnya idxCbg nggak digunakan karena row yang dicari terlalu banyak (80% dari total row) sehingga penggunaan index akan memperlambat. Jika Perlu dioptimasi maka perlu dibuat clustered index idxCbg tersebut
 
Namun yang soal 2 IdxCbg seharusnya digunakan karena cocok dengan prinsip Non Cluster index.

Kalau index seek adalah optimizer akan menggunakan index sebagai bahan pencarian yang mana sangatlah cocok untuk query yang selective sebagai contoh yang id_cabang = '103'

Sedangkan index scan adalah optimizer akan membaca semua row di dalam table dan mengembalikan yang sesuai dengan criteria. Disini agan bertanya kenapa ga pake index saja bacanya. Hal ini karena pembacaan semua row tanpa menggunakan index pembacaan dengan menggunakan index. sehingga untuk query yang mengembalikan banyak row yaitu id_cabang = 101 dimana 80% dari total row maka kemungkinan besar akan menggunakan index scan.

Sedangkan untuk pembuatan cluster dan nonclustered index pada soal keempat sebenarnya tergantung seberapa sering query tersebut digunakan dan juga seberapa menggangunya. Jelas pembuatan index tersebut akan mempercepat namun perlu diingat juga bahwa pembuatan index akan memperlama proses DML dan akan membuang jumlah hard disk. Nah yang ini tergantung kebutuhan agan.

#Security Issue dan Anti Virus

Pakai antivirus, bahkan nambahin program yang jalan di DB server jelas bikin lambat.

1. Program tambahan makan resource processor, dll juga. 
2. AV bakal scan koneksi, isi paket, dll sebelum diperbolehkan di passing ke DB server.

Anti virus sebaiknya ada di layer depannya, jangan pas di install diaplikasi db nya. Sebisa mungkin server DB bebas dari yang namanya anti virus.

Peruntukan Server Database tidak dicampur dengan Server aplikasi lain (File Sharing, Web & Email Server, dll...), praktis lebih aman. Jika diperlukan Firewall diaktifkan cukup dibuka port 1433. Kalau mau lebih aman lagi segmentasi server DB dibedain dengan jaringan lokal yang ada, dan untuk koneksinya mesti di NAT lewat router.


Sumber : Kaskus