การ จะตอบว่าใช้เมื่อไหร่ก็ขึ้นกับ fragmentation ของ index น่ะครับ ถ้าเกิด fragmentation มากก็ควรจะ reindex ซะ เพื่อให้ database server ทำงานได้เร็วขึ้น ซึ่งใน SQL Server จะมีคำสั่ง dbcc showcontig เอาไว้ดูว่า index เกิดมี fragmentation รึเปล่า
แล้วเราจะให้มัน re บ่อย ๆ ดีไหมครับ ( ประมาณเดือนละครั้ง ) พอดีผมไปเห็นในตัว database maintenance plans
บ่อย ๆ ถ้าไม่กลัวเสียเวลาก็ทำได้ครับ ส่วนจะทำบ่อยแค่ไหน ก็คงต้องพิจารณา fragmentation ของ index ซึ่ง fragmentation ของ index จะมากน้อย ก็ขึ้นกับพฤติกรรมการ insert, update, delete ข้อมูลใน table นั้นๆ อีกทีหนึ่ง เพราะ ถ้าไม่มีการ insert, update, delete ข้อมูลใน table เลย หรือ มีแต่น้อยมาก ความถี่ในการ reindex ก็ไม่จำเป็นจะต้องถี่มาก ( คือ กรณีแบบนี้ reindex ไปมันก็ไม่ช่วยให้การทำงานของ database server เร็วขึ้นสักเท่าไหร่ )
ข้อเสียของการ reindex ที่สำคัญที่ต้องพิจารณา ก็คือ ช่วงที่กำลังทำงานอยู่มันจะ เกิด blocking ทำให้ application ไม่สามารถทำงานได้ ดังนั้น ถ้าทำบ่อย ๆ มันก็จะ เกิด blocking บ่อย ๆ แต่เราก็สามารถเลี่ยงไปทำเวลาที่ไม่มี user ใช้งานได้ เช่น ตอนกลางคืน
สรุป ถ้ายังคิดอะไรไม่ออก ก็ เดือนละครั้งไปก่อนก็ได้ครับ
วิธีกรใช้ dbcc showcontig
- Pages Scanned จำนวน page ของข้อมูลใน table หรือ index ถ้าเยอะแสดงว่า table หรือ index ใหญ่
- Extents Scanned จำนวน extent ของข้อมูลใน table หรือ index ถ้าเยอะแสดงว่า table หรือ index ใหญ่
- Extent Switches จำนวนครั้งที่เกิดการ switch extent เมื่อมีการอ่านข้อมูล ถ้าเยอะแสดงว่ามี fragmentation เยอะ เปรียบเทียบเหมือนไฟล์บน hdd ที่มี fragment เยอะ เวลาอ่านไฟล์ก็จะกระโดดไป กระโดดมาหลายครั้ง
- Avg. Pages per Extent ก็เอา จน. page / จน. extent
- Scan Density [Best Count:Actual Count] คือ ค่าเปรียบเทียบระหว่าง จน. extent ที่ switch ในกรณีสมมติว่าข้อมูลต่อเนื่องกันอย่างสมบูรณ์ (best count) / ค่าจริง (เอามาจาก extents switch) ซึ่งยิ่งใกล้ 100 ยิ่งดี
- Logical Scan Fragmentation และ - Extent Scan Fragmentation
สองตัวนี้คือ ค่า fragmentation ของ page และ extent ซึ่ง ยิ่ง ใกล้ 0 ยิ่งดี
- Avg. Bytes Free per Page จน.พื้นที่ที่เหลือ / page
- Avg. Page Density (full) ค่าเป็น % ถ้าค่าสูง แสดงว่า page มีข้อมูลค่อนข้างเต็ม
สอง ตัวนี้จะเกี่ยวข้องกับ fill factor ของ index และขึ้นกับพฤติกรรมของการ insert, update, delete ข้อมูลด้วย ซึ่งจะมากดี หรือจะน้อยดี ก็แล้วแต่กรณี
คร่าว ๆ แบบภาษาพูดง่าย ๆ ก็คือ ถ้า table ไม่ค่อยมีการ insert, update, delete แล้วล่ะก็ ( เพราะ การ insert, update, delete จะทำให้เกิดการเปลี่ยนแปลงของ index ) ให้ใส่ fill factor ให้สูง ๆ ใกล้ๆ 100% แล้ว avg. byte free per page จะน้อย และ avg page density ก็จะมี % สูงตามไปด้วย ทำให้เวลาอ่านข้อมูลที่ต่อเนื่องกันแล้วอ่านได้เร็วกว่า
เปรียบ เทียบกับสมุดจดงานก็แล้วกัน กรณี fill factor สูง ก็นึกถึงสมุดจดงาน ที่บังคับให้เขียนเต็มทุก ๆ หน้านะครับ เวลาอ่าน ก็จะอ่านได้ต่อเนื่องได้เร็ว เพราะ จน.ครั้งที่พลิกหน้าก็น้อย ไม่โดดไป โดดมา
ซึ่งหาก table ไม่ค่อยเปลี่ยนแปลงแล้วไปใช้ fill factor 50% มันก็เหมือนกับ สมุดที่เราเขียนแค่ครึ่งหน้า ก็ขึ้นหน้าใหม่แล้ว มันจะเปลืองหน้า แถมเวลาอ่านยังต้องพลิกหน้าหลายครั้งกว่าด้วย ทำให้อ่านได้ช้าลง
ทีนี้ สมมติว่า table มีการ insert หรือ แก้ไข อยู่เรื่อย ๆ แล้วเราใช้ fill factor 100% ก็เหมือนสมุดจดที่เต็มหน้าแล้ว เวลาแทรกข้อมูล จะทำยังไง (ซึ่งถ้าการ insert เป็นการต่อท้ายอย่างเดียว ก็จะไม่เป็นปัญหา แต่สมมติว่าเราจะไป insert ข้อมูลตรงกลาง ของ table ) ในการทำงาน เมื่อหน้ากระดาษ (page) เต็ม ก็จะต้องไปเพิ่ม page ใหม่ต่อข้างท้าย ทำให้เวลาอ่าน ต้องกระโดดไปอ่านข้างท้ายทีหนึ่ง แล้วค่อย กระโดดกลับมาอ่านต่อจากเดิมที่ค้างเอาไว้
( ดังนั้น ถ้า table มีการเปลี่ยนแปลงข้อมูลส่วนที่ก่อให้เกิดการเปลี่ยนแปลง index บ่อย แล้วเราไปใช้ fill factor ของ index เป็น 100% เจ้า index พวกนี้ก็จะเกิด fragmentation ได้อย่างรวดเร็ว คือ reindex ไปแป๊บ ๆ เดี๋ยวมันก็เกิด fragmentation อีกล่ะ )
แล้วหากใช้ fill factor 50% หละ ก็คือ แต่ละหน้าของสมุดจะใช้แค่ครึ่งเดียว ทำให้เวลา insert หรือแทรกข้อมูลมีที่ว่างเหลือให้อีก ซึ่งมันเหมือนจะดีกว่ากรณีสักครู่นี้ แต่ก็อีกแหละ ถ้าหากมีการอ่านข้อมูลต่อเนื่องกันเยอะ ๆ มันจะสู้กรณีที่เป็น fill factor 100% ไม่ได้ เพราะมันต้องพลิกหน้าอ่านเยอะกว่า
ซึ่งการ reindex ก็คือ การเอาสมุดแบบนี้มาเขียนเรียงใหม่นั่นเอง โดยเมื่อเขียนใหม่แล้วก็จะเหลือพื้นที่ว่าง ๆ ตาม fill factor ของ index ตามที่กำหนดเอาไว้ สมุดแต่ละหน้า ก็คือ page นั่นแหละ ส่วน extent ก็คือ สมุดหลาย ๆ หน้ารวมเรียกเป็น 1 extent
ก็อาจจะไม่ถูกต้อง 100% นะครับ อธิบายแบบเทียบง่าย ๆ
อยาก ให้คุณลอง จดบันทึกค่าจาก dbcc showcontig เอาไว้ ทั้งก่อน และหลัง การทำ dbcc dbreindex แล้วก็เอามาเปรียบเทียบกันน่ะครับ แล้วจากนั้น อาจจะ run เจ้า dbcc showcontig เรื่อย ๆ แล้วคอยเทียบการเปลี่ยนแปลงเอา เพื่อหา config ที่เหมาะสมสำหรับ app ของคุณเอง เช่น อยากแก้ fill factor หรือไม่ หรือ จะตั้ง schedule ให้ reindex ถี่แค่ไหนดี
ที่มา http://www.thaiadmin.org/board/index.php?topic=46795.0
