Desain database yang baik tentunya perlu memperhatikan juga index untuk table-table yang memerlukannya. Tidak semua table memerlukan index, namun untuk table-table transaksi, index mutlak diperlukan. Dengan menyediakan index yang tepat, pencarian data akan menjadi lebih cepat. Peran index sama halnya seperti index dari sebuah buku. Ketika kita mencari pembahasan tentang Left Join dalam buku SQL Server, maka akan jauh lebih mudah apabila kita mencari dulu kedalam index, mencari entry Left Join, mendapatkan nomor halamannya, lalu pergi ke halaman yang ditunjuk oleh index untuk mendapat penjelasan mengenai Left Join. Cara ini jauh lebih mudah daripada harus mencari satu per satu kata didalam buku tersebut. Kurang lebih seperti itulah peran index pada suatu table.

Index yang tersusun dengan baik tentunya akan memudahkan pencarian. Namun ada kalanya terjadi fragmentasi pada index sehingga perlu dilakukan perawatan secara berkala agar performa tetap terjaga. Melakukan maintenance index tentunya adalah hal biasa yang dilakukan oleh para DBA. Umumnya kita membuat suatu job untuk melakukan maintenance index yang akan bekerja secara otomatis pada waktu yang dapat kita tentukan. Cara lainnya adalah membuat script T-SQL.

Tindakan yang dapat dilakukan yaitu merebuild atau mereorganize index.  Untuk dapat menentukan tindakan mana yang perlu diambil, maka kita perlu menganalisa seberapa besar fragmentasi terjadi pada suatu index. Sebagai contoh, script berikut untuk menganalisa index dari table Orders di database Penjualan:

declare @db_id tinyint

declare @ob_id int

set @db_id = db_id()

set @ob_id = object_id('Orders')

select a.index_id,b.name,index_type_desc,alloc_unit_type_desc,

avg_fragmentation_in_percent,fragment_count,

avg_fragment_size_in_pages from sys.dm_db_index_physical_stats(@db_id,@ob_id,null,null,'LIMITED') a

inner join

sys.indexes b on a.object_id = b.object_id and a.index_id = b.index_id

Hasilnya sebagai berikut (beberapa kolom tidak ditampilkan):

(cont)

Sebagai acuan, kita bisa menggunakan panduan dari BOL:

Tabel ini punya dua index, index pertama PK_Orders terfragment 25% dan index kedua CustomerId terfragment 67%. Berdasarkan angka acuan, kita akan melakukan REBUILD index untuk index PK_Orders dan REORGANIZE untuk index CustomerId.

ALTER INDEX PK_Orders ON Orders REBUILD

ALTER INDEX CustomerId ON Orders REORGANIZE

Hasilnya,

(cont)

Average fragmentation menunjukkan seberapa parah index tersebut teracak dalam database. Semakin rendah, berarti semakin tersusun baik index tersebut.

Ada sebuah tool yang bisa digunakan untuk melakukan pekerjaan ini, yaitu IdxMtc yang penulis buat sendiri. Namun sayangnya baru ada untuk SQL Server 2005. Silahkan mendownloadnya disini secara gratis.

Demikian, semoga bermanfaat.