Author Topic: SQL Server Performance Tips: Rebuilding Indexes  (Read 56769 times)

Yusuf

  • Administrator
  • Sr. Member
  • ****
  • Posts: 1,335
    • Orisinil.com
SQL Server Performance Tips: Rebuilding Indexes
« on: June 21, 2010, 09:32:24 AM »
Periodically (daily, weekly, or monthly) perform a database reorganization on all the indexes on all the tables in your database. This will rebuild the indexes so that the data is no longer fragmented. Fragmented data can cause SQL Server to perform unnecessary data reads, slowing down SQL Server's performance. It will also update column statistics.

If you do a reorganization on a table with a clustered index, any non-clustered indexes on that same table will automatically be rebuilt.

Database reorganizations can be done using the Maintenance Wizard, or by running your own custom script via the SQL Server Agent (see below).

The DBCC DBREINDEX command will not automatically rebuild all of the indexes on all the tables in a database; it can only work on one table at a time. But if you run the following script, you can index all the tables in a database with ease:

--Script to automatically reindex all tables in a database

Code: [Select]
USE otomax --Enter the name of the database you want to reindex

DECLARE @TableName varchar(255)

DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,' ',95)
FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor

DEALLOCATE TableCursor

The script will automatically reindex every index in every table of any database you select, and provide a fill factor of 95%. You can substitute any number appropriate for the fill factor in the above script.

When DBCC DBREINDEX is used to rebuild indexes, keep in mind that as the indexes on a specific table are being rebuilt, that the table becomes unavailable for use by your users.

For example, when a non-clustered index is rebuilt, a shared table lock is put on the table, preventing all but SELECT operations to be performed on it. When a clustered index is rebuilt, an exclusive table lock is put on the table, preventing any table access by your users. Because of this, you should only run this command when users don't need access to the tables being reorganized. [7.0, 2000, 2005] Updated 7-24-2006

sumber: http://www.sql-server-performance.com/tips/rebuilding_indexes_p1.aspx
« Last Edit: March 04, 2012, 02:43:25 PM by Yusuf »

AE_Reload

  • User OtomaX
  • Sr. Member
  • ****
  • Posts: 1,256
  • SMS/WA:087771191777
Re: SQL Server Performance Tips: Rebuilding Indexes
« Reply #1 on: June 21, 2010, 10:23:23 AM »
ora ngerti bos  ;D

Budi Santoso

  • Dealer OtomaX
  • Sr. Member
  • ****
  • Posts: 1,996
  • Budi Santoso
Re: SQL Server Performance Tips: Rebuilding Indexes
« Reply #2 on: June 21, 2010, 02:44:44 PM »
Maksud nya gmn...

Babar Blas gak Mudeng Bahasa Inggris...  :'( :'( :'( :'( :'( :'( :'(

di translate Keluarnya gini...
Quote
Berkala (harian, mingguan, atau bulanan) melakukan reorganisasi database pada semua indeks pada semua tabel dalam database Anda. Hal ini akan membangun kembali indeks sehingga data tidak lagi terfragmentasi. Terfragmentasi data dapat menyebabkan SQL Server untuk melakukan data yang tidak perlu dibaca, memperlambat kinerja SQL server. Hal ini juga akan memperbarui kolom statistik.

Jika Anda melakukan reorganisasi di atas meja dengan clustered index, setiap non-clustered indexes pada tabel yang sama secara otomatis akan dibangun kembali.

Database reorganisasi dapat dilakukan dengan menggunakan Wizard Pemeliharaan, atau dengan menjalankan script kustom Anda sendiri melalui SQL Server Agen (lihat di bawah).

Perintah DBCC DBREINDEX tidak akan secara otomatis membangun kembali semua indeks pada semua tabel dalam database, hanya dapat bekerja pada satu meja pada suatu waktu. Tapi jika Anda menjalankan script berikut, Anda dapat mengindeks semua tabel dalam database dengan mudah:


Script secara otomatis akan Isi nama baru kotak setiap indeks dalam setiap tabel database yang Anda pilih, dan memberikan faktor isi dari 90%. Anda dapat mengganti sejumlah faktor yang tepat untuk mengisi naskah di atas.

Ketika DBCC DBREINDEX digunakan untuk membangun kembali indeks, perlu diingat bahwa sebagai indeks pada tabel tertentu sedang dibangun kembali, bahwa meja menjadi tidak tersedia untuk digunakan oleh pengguna Anda.

Sebagai contoh, ketika indeks non-clustered ini dibangun kembali, meja bersama kunci diletakkan di atas meja, mencegah semua tapi operasi SELECT untuk dilakukan di atasnya. Ketika sebuah clustered index ini dibangun kembali, kunci meja eksklusif disimpan di meja, meja mencegah akses oleh pengguna Anda. Karena itu, Anda hanya perlu menjalankan perintah ini ketika pengguna tidak memerlukan akses ke meja yang ditata kembali. [7,0, 2000, 2005] Diperbarui 2006/07/24
« Last Edit: June 21, 2010, 02:49:09 PM by Owner_Salsabila »
SAFANA Corporation
IT Solution - Online Payment - General Payment


Jam Kerja Fix 07:00 - 22:00 Setiap Hari, Kecuali Hari Minggu (jika hari Minggu urgent bisa kontak via SMS)
Telegram:
@budi_s4n2s0, @owner_safana
WhatsApp:628112886854
Site : http://one.safana.co.id/

zetha

  • User OtomaX
  • Jr. Member
  • **
  • Posts: 167
Re: SQL Server Performance Tips: Rebuilding Indexes
« Reply #3 on: June 21, 2010, 11:01:21 PM »
Periodically (daily, weekly, or monthly) perform a database reorganization on all the indexes on all the tables in your database. This will rebuild the indexes so that the data is no longer fragmented. Fragmented data can cause SQL Server to perform unnecessary data reads, slowing down SQL Server's performance. It will also update column statistics.

If you do a reorganization on a table with a clustered index, any non-clustered indexes on that same table will automatically be rebuilt.

Database reorganizations can be done using the Maintenance Wizard, or by running your own custom script via the SQL Server Agent (see below).

The DBCC DBREINDEX command will not automatically rebuild all of the indexes on all the tables in a database; it can only work on one table at a time. But if you run the following script, you can index all the tables in a database with ease:

--Script to automatically reindex all tables in a database

Code: [Select]
USE DatabaseName --Enter the name of the database you want to reindex

DECLARE @TableName varchar(255)

DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor

DEALLOCATE TableCursor

The script will automatically reindex every index in every table of any database you select, and provide a fill factor of 90%. You can substitute any number appropriate for the fill factor in the above script.

When DBCC DBREINDEX is used to rebuild indexes, keep in mind that as the indexes on a specific table are being rebuilt, that the table becomes unavailable for use by your users.

For example, when a non-clustered index is rebuilt, a shared table lock is put on the table, preventing all but SELECT operations to be performed on it. When a clustered index is rebuilt, an exclusive table lock is put on the table, preventing any table access by your users. Because of this, you should only run this command when users don't need access to the tables being reorganized. [7.0, 2000, 2005] Updated 7-24-2006

sumber: http://www.sql-server-performance.com/tips/rebuilding_indexes_p1.aspx

solusi http://forum.orisinil.com/index.php?topic=1546.0, setelah berbulan2 pake otomax br x ini ngalamin yg aneh, tapi langsung beres, trims big bozz :-* tapi lbh enaknya dijelaskan maksud dan tujuan script ini biar lbh di mengerti oleh org awam seperti saya...
« Last Edit: June 21, 2010, 11:07:57 PM by zetha »

mahfud

  • User OtomaX --
  • Jr. Member
  • **
  • Posts: 209
    • Exemplary Сasual Dating - Verified Females
Re: SQL Server Performance Tips: Rebuilding Indexes
« Reply #4 on: June 22, 2010, 03:45:42 AM »

Yusuf

  • Administrator
  • Sr. Member
  • ****
  • Posts: 1,335
    • Orisinil.com
Re: SQL Server Performance Tips: Rebuilding Indexes
« Reply #5 on: June 22, 2010, 08:19:32 AM »
Sedikit sharing aja...

Index adalah semacam tabel tambahan dari tabel sesungguhnya. Tujuannya adalah untuk mempercepat query pencarian data.

Karena data terus berubah/bertambah, Index ini bisa ter-"fragmented" sehingga menurunkan performance operasi pada tabel tersebut.

Script diatas tujuannya untuk defrag index. Coba bayangkan seperti kita defrag harddisk, kurang lebih sama persis seperti itu...

Pada proses defrag, sebaiknya matikan OtomaX atau program2 lain yang mengakses database tersebut.


deydi

  • User OtomaX --
  • Sr. Member
  • ****
  • Posts: 1,271
  • pm
    • Pasang Iklan Baris GRATIS
Re: SQL Server Performance Tips: Rebuilding Indexes
« Reply #6 on: June 22, 2010, 11:42:05 AM »
Sedikit sharing aja...

Index adalah semacam tabel tambahan dari tabel sesungguhnya. Tujuannya adalah untuk mempercepat query pencarian data.

Karena data terus berubah/bertambah, Index ini bisa ter-"fragmented" sehingga menurunkan performance operasi pada tabel tersebut.

Script diatas tujuannya untuk defrag index. Coba bayangkan seperti kita defrag harddisk, kurang lebih sama persis seperti itu...

Pada proses defrag, sebaiknya matikan OtomaX atau program2 lain yang mengakses database tersebut.



pak, script diatas ditempatkan dimana?
http://www.dmtronik.com
http://www.manadowebhost.com
YM : dmtronik_cs, cs_dmtronik, otomax_manado

ARCON

  • User OtomaX
  • Jr. Member
  • **
  • Posts: 115
Re: SQL Server Performance Tips: Rebuilding Indexes
« Reply #7 on: June 22, 2010, 05:40:50 PM »
gak ngerti blas .....  ;D
make e utk otomax gimana ???   :-[

Neo_BjBRefill

  • Bukan server Biasa..... Mkios Server BjbRefill02
  • Full Member
  • ***
  • Posts: 660
  • My Future In My Hand
Re: SQL Server Performance Tips: Rebuilding Indexes
« Reply #8 on: June 22, 2010, 06:43:02 PM »
Mantap..... big bos.... cara ini bermanfaat banget buat yang sudah bengkak databasenya.....

AE_Reload

  • User OtomaX
  • Sr. Member
  • ****
  • Posts: 1,256
  • SMS/WA:087771191777
Re: SQL Server Performance Tips: Rebuilding Indexes
« Reply #9 on: June 22, 2010, 10:40:39 PM »
Cara pake nya gmn ya ??

fadlyfayruz

  • User OtomaX
  • Full Member
  • ***
  • Posts: 704
  • Melayani H2H server kecil & server besar
    • DFlash pulsa
Re: SQL Server Performance Tips: Rebuilding Indexes
« Reply #10 on: June 24, 2010, 01:51:23 AM »
nih aku bikinin file phpnya biar lebih gampang..
jadi tinggal masuk masukin parameter aja  ;D

[attachment deleted by admin]
Webreport : 🌎 http://report.dflash.co.id 🌎
CHANNEL : https://t.me/dflashpart2
Telegram CS: @csodflash
CALL CENTER : 📞0897 33000 87 PSTN: ☎ 021-29868031
Alamat : 🏤 Jl. Sarikaya 1 No. 225, Pancoran Mas. Depok 🏤

fra

  • User OtomaX --
  • Full Member
  • ***
  • Posts: 943
Re: SQL Server Performance Tips: Rebuilding Indexes
« Reply #11 on: June 24, 2010, 05:36:57 AM »
nih aku bikinin file phpnya biar lebih gampang..
jadi tinggal masuk masukin parameter aja  ;D

sedot lagi...  ;)
Tq
« Last Edit: June 24, 2010, 05:40:55 AM by fra »

fadlyfayruz

  • User OtomaX
  • Full Member
  • ***
  • Posts: 704
  • Melayani H2H server kecil & server besar
    • DFlash pulsa
Re: SQL Server Performance Tips: Rebuilding Indexes
« Reply #12 on: June 24, 2010, 10:02:42 AM »
nih aku bikinin file phpnya biar lebih gampang..
jadi tinggal masuk masukin parameter aja  ;D

sedot lagi...  ;)
Tq
semalem abis tes
pas lagi sepi trx otomax di close, database di backup (buat jaga2)
jalanin file nya deh..
stelah selesai. Tak buka lagi oto nya..
wuihh jadi cepeet...
kerasa banget bedanya..

tested by me ;D
Webreport : 🌎 http://report.dflash.co.id 🌎
CHANNEL : https://t.me/dflashpart2
Telegram CS: @csodflash
CALL CENTER : 📞0897 33000 87 PSTN: ☎ 021-29868031
Alamat : 🏤 Jl. Sarikaya 1 No. 225, Pancoran Mas. Depok 🏤

ARCON

  • User OtomaX
  • Jr. Member
  • **
  • Posts: 115
Re: SQL Server Performance Tips: Rebuilding Indexes
« Reply #13 on: June 24, 2010, 10:23:23 AM »
make' nya gimana gan,...  ???   ???   ???

RieNDya_MulTiTrX

  • User OtomaX
  • Full Member
  • ***
  • Posts: 983
Re: SQL Server Performance Tips: Rebuilding Indexes
« Reply #14 on: June 24, 2010, 10:26:04 AM »
 ??? ??? ???
Telegram CS :
08981154682

Telegram Channel :
https://t.me/joinchat/AAAAAEV1aRlxxuizgufoeQ

AE_Reload

  • User OtomaX
  • Sr. Member
  • ****
  • Posts: 1,256
  • SMS/WA:087771191777
Re: SQL Server Performance Tips: Rebuilding Indexes
« Reply #15 on: June 24, 2010, 10:52:24 AM »
 ??? ??? ??? ???

ARCON

  • User OtomaX
  • Jr. Member
  • **
  • Posts: 115
Re: SQL Server Performance Tips: Rebuilding Indexes
« Reply #16 on: June 24, 2010, 11:54:09 AM »
kasih tutornya dong ..  ;D

Yusuf

  • Administrator
  • Sr. Member
  • ****
  • Posts: 1,335
    • Orisinil.com
Re: SQL Server Performance Tips: Rebuilding Indexes
« Reply #17 on: June 24, 2010, 07:09:07 PM »
make' nya gimana gan,...  ???   ???   ???

pake sql studio bos. ada klik tombol new query. masukin scriptnya disitu dan execute...

VIOLET CELLULAR

  • User OtomaX
  • Full Member
  • ***
  • Posts: 785
Re: SQL Server Performance Tips: Rebuilding Indexes
« Reply #18 on: June 26, 2010, 10:22:49 PM »
MANTAB berasa banget .....
thxs big boss

AE_Reload

  • User OtomaX
  • Sr. Member
  • ****
  • Posts: 1,256
  • SMS/WA:087771191777
Re: SQL Server Performance Tips: Rebuilding Indexes
« Reply #19 on: June 26, 2010, 11:01:20 PM »
di sy kok keluar gini y  ??? ??? ???
Msg 911, Level 16, State 1, Line 1
Could not locate entry in sysdatabases for database 'DatabaseName'. No entry found with that name. Make sure that the name is entered correctly.