Reindex for MS SQL Server 2000

การ maintenance ฐานข้อมูลเป็นเรื่องที่สำคัญมาก ทุกๆวัน ฐานข้อมูลจะผ่านการ Insert, Update, Delete ข้อมูลต่างๆ มากมาย ซึ่งการ Re-index ก็เป็นส่วนหนึ่งของการ maintenance ระบบให้มีประสิทธิภาพ (performance) ที่ดีอยู่ตลอดเวลา

---------------------------------------------------------
Real case
ตอนเช้าของเกือบทุกวัน ผมจะได้รับสายฯ จาก users มาต่อว่า "ทำไมค้นหาข้อมูลนานมากเลย" "ระบบของคุณขึ้น Error ว่า Timeout Expire อีกแล้ว" "ผม Insert ข้อมูลไม่ได้" คำถามต่างๆเหล่านี้ทำให้ผมต้องทำอะไรสักอย่าง เพราะปกติแล้ว ไม่เคยมีปัญหา users สามารถใช้งานระบบมาได้นานเป็นระยะเวลาหนึ่ง

แล้วผมก็สังเกตุได้ว่า ฐานข้อมูล มันมีขนาดที่ใหญ่โตมากขึ้น การค้นหาแต่ละครั้งนั้นใช้เวลานาน ดังนั้นจำเป็นต้องใช้งาน Index ซึ่งผมก็ได้สร้าง Index เพื่อแก้ไขปัญหาดังกล่าว ซึ่งก็ได้ผลดีครับ การค้นหาทำได้เร็วขึ้นกว่าแต่ก่อน แต่แล้ว 1 สัปดาห์ต่อมา ผมก็กลับได้รับโทรศัพท์เช่นเดิมอีก ... แล้วผมต้องทำเช่นไรอีกละเนี่ย ในเมื่อผมก็สร้าง Index แล้วนิ...
---------------------------------------------------------

เนื่องจาก Index ซึ่งโดยส่วนใหญ่แล้วเป็น non-clustered index จะไม่ถูกจัดเรียงใหม่ทุกครั้งที่มีการ Insert, Update, Delete ข้อมูล ต่างจาก Clustered index ที่โดยส่วนใหญ่เป็น Primary Key (ที่ผมบอกว่าโดยส่วนใหญ่นั้น เพราะ Primary Key ไม่จำเป็นต้องเป็น Clustered index เสมอไป) ที่จะถูกจัดเรียงใหม่ทุกครั้งที่มีการกระทำ Insert, Update, Delete ดังนั้น เมื่อมีการสร้าง หรือ ปรับปรุงข้อมูลในตารางที่มี non-clustered index อยู่บ่อยๆแล้ว ถ้าไม่มีการจัดเรียง index ใหม่บ้างแล้ว ก็อาจทำให้ประสิทธิภาพในการค้นหาทำได้ไม่ดีเท่าที่ควร และยิ่งตารางที่มีข้อมูลเป็นจำนวนมากด้วยแล้ว ก็ยิ่งจะเห็นผลกระทบได้ชัดเจน

ใน SQL Server 2000 มี Sql Statement ที่ใช้เพื่อทำการ re-index คือ

Syntax

DBCC DBREINDEX
( [ 'database.owner.table_name'
[, index_name
[ , fillfactor ]
]
]
) [ WITH NO_INFOMSGS ]เช่น
DBCC DBREINDEX('dbo.Orders')
Go

ผลลัพท์ที่ได้คือ

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

จาก Statement ข้างต้น เป็นการ re-index ตารางที่เราต้องทราบไว้ล่วงหน้าว่ามีตารางชื่ออะไร เราจึงสามารถกำหนดคำสั่งได้ ถ้าฐานข้อมูลของเรามีตารางสัก 10 ตารางก็คงไม่เท่าไร แต่ถ้ามีสัก 100 ตารางล่ะ คงให้มากำหนด DBCC DBREINDEX(...) แต่ละตารางคงไม่ไหว ผมมีวิธีที่จะทำการ re-index ตารางทั้งหมดในฐานข้อมูล โดยใช้ sql statement ด้านล่างนี้

declare @name varchar(128), --ประกาศตัวแปรสำหรับเก็บชื่อ ตาราง

@user varchar(128), --ประกาศตัวแปรสำหรับเก็บชื่อ เจ้าของตาราง
@statement varchar(1000)

declare tablename cursor for --ประกาศตัวแปร cursor ข้อมูล ตาราง จาก sysobjects

select o.name, u.name
from sysobjects as o, sysusers as u
where o.uid = u.uid
and xtype = 'U'

open tablename --เปิด cursor

fetch next from tablename --อ่านข้อมูลจาก cursor และให้ข้อมูลเข้าตัวแปร
into @name, @user

while @@fetch_status = 0 -- วนลูป cursor จนกว่าหมดข้อมูล
begin

set @statement = 'DBCC DBREINDEX (''[' + @user + '].[' + @name + ']'')' --สร้าง sql statement เพื่อ re-index ตาราง
print @statement + '...'
execute (@statement) -- สั่งให้ execute sql statement ที่ re-index ตาราง
fetch next from tablename
into @name, @user

end

close tablename -- ปิด cursor
deallocate tablename -- เคลียร์ memory

go

จาก statement ข้างต้่น สามารถนำไปวางไว้ใน job agent เพื่อให้ re-index ตารางในฐานข้อมูลตามเวลาที่ต้องการได้ทันที