SQL Server Performance Tuning ตอนที่ 1 (Disk)

การวิเคระห์ปัญหาจาก Disk

เนื่องจาก Disk เป็น Hardware ที่ทำงานช้าที่สุด และ SQL Server มีการใช้งาน Disk สูงมาก ดังนั้นการแก้ไขปัญหาขอควดที่ Disk จึงเป็นส่วนสำคัญที่ทำให้ Performance ดีขึ้นอย่างเห็นได้ชัด

ในการวิเคราะห์เรื่อง Disk นี้ ค่า Counter ที่จะนำมาใช้ จะใช้ของ Physical Disk ซึ่งสำหรับ Server ที่ใช้ Disk เป็น RAID นั้น แต่ละ Physical Disk จะหมายถึง แต่ละ Array ของ RAID

Performance Counter ที่เกี่ยวข้อง

ชื่อ Counter คำอธิบาย ค่าของ Counter
========= ====== ============
Object --> Physical Disk (เก็บข้อมูลทั้ง Data disk และ Log disk)
- % Disk Time %ของเวลาที่ disk busy Avg < 5 %
- Current Disk Queue Length จำนวน disk request ที่รอคิวอยู่ Avg < 2 per disk
- Average Disk Queue Length ค่าเฉลี่ยของ จำนวน disk request ที่รอคิวอยู่ Avg < 2 per disk
- Disk Transfers/sec อัตราการ read/write สูงสุด < 100 per disk
- Disk Bytes/sec จำนวนข้อมูลที่ถ่ายโอนกับ disk สูงสุด < 10 MB/s per disk

ซึ่งมีรายละเอียดดังนี้

%Disk Time
แสดงถึง % ของเวลาที่ Disk busy คือ มีการ read/write อยู่ ซึ่งไม่ควรมีค่าสูงเป็นเวลานาน ๆ และ หากค่านี้ >85% อย่างต่อเนื่องนาน ๆ แล้ว เราควรที่จะลดค่า %Disk Time ลง อาจจะด้วยการ Upgrade Disk และพยายามทำให้มีการใช้งาน Disk ลดลง

อีกอย่าง แม้ค่า %Disk Time จะอยู่ที่ 5% แต่ถ้าค้างอยู่ที่ 5% เป็นเวลานาน ๆ ก็สามารถทำให้ Performance แย่ลงเช่นกัน

Current Disk Queue Length
เป็นค่าที่แสดงถึง Request ที่ยังคอยการ Disk ว่างเพื่อใช้งานอยู่ ณ เวลานั้น ๆ ซึ่งหากค่านี้มีค่า > 2 ต่อ Disk แล้ว ก็แสดงให้เห็นว่ามีคอขวดเกิดขึ้นในส่วนของ Disk ( มี Request เข้าแถวคอย Disk ว่างเพื่อใช้งานอยู่ )

เช่น Array ของ RAID ที่มี 10 Disks ค่า Current Disk Queue Length ที่มากกว่า 2 per disk หรือ 20 จะหมายถึงว่ามีคอขวดเกิดขึ้นในส่วนของ Disk แล้ว
(หรืออาจะใช้ค่าเฉลี่ย Avg. Disk Queue Length ก็ได้)

Disk Transfer/Sec
เป็นค่าที่แสดงถึงอัตราการ read/write ของ Disk ซึ่ง Disk ในปัจจุบันสามารถทำงานได้ที่ประมาณ 180 disk transfer/sec สำหรับ Sequential I/O และ 100 disk transfer/sec สำหรับ Random I/O (เพราะว่ากรณี Random I/O จะมีการเคลื่อนไหวของ disk arm และ head มากกว่า) ดังนั้น Disk Transfer/Sec สูงกว่า 100 แล้ว แม้ว่า Disk Bytes/Sec จะไม่ถึง 10 MB ก็ตาม ก็แสดงว่าเกิดคอขวดที่ Disk แล้ว

Disk Bytes/Sec
เป็นค่าที่แสดงถึงจำนวนข้อมูลที่ส่งเข้า/ออก จาก Disk ในขณะที่ read/write ซึ่งโดยปกติแล้ว Disk จะมีค่า Disk Bytes/sec จะสูงสุดได้ที่ประมาณ 10MB

สำหรับงาน OLTP ทั่วไป ซึ่งการทำงานจะเป็นรายการเล็ก ๆ ข้อมูลไม่เยอะ แต่มีจำนวนรายการมาก ดังนั้น หากเป็นระบบ OLTP จะต้องคำนึงถึง Disk Transfer/Sec มากกว่า Disk Bytes/Sec

การแก้ปัญหาเรื่อง Disk
================

1. Optimize Query

2. ใช้ Disk ที่มีความเร็วสูงขึ้น เช่น ใช้ Disk 15000 rpm แทน 10000 rpm เป็นต้น

3. ใช้ RAID Array ซึ่งแต่ละแบบจะมีข้อดี ข้อเสีย และความเร็วในการ read/write ที่แตกต่างกัน

4. เพิ่ม Memory ดังที่กล่าวไปแล้วว่าปัญหา Memory ไม่พออาจจะทำให้ การใช้งาน Disk สูงได้ (ดูหัวข้อเรื่อง Memory)

5. สร้าง Files และ Filegroups เป็นหลายส่วน เนื่องจากว่าในกรณีที่มี CPU หลายตัว และมี Files และ Filegroups หลาย ๆ ตัว นั้น SQL Server สามารถที่จะทำ Multiple Parallel Scan ได้

6. สร้าง Table และ Index ไว้บน Disk คนละลูก เพื่อให้ SQL Server สามารถใช้ข้อมูลจาก Table และ Noncluster Index ได้พร้อม ๆ กัน

7. แยก Log file ไปไว้ใน Physical Disk ต่างหากอีกลูก เพราะ Log file จะมีการเขียนตลอดเวลา และ เป็นแบบ Sequential ดังนั้น การแยก Disk I/O ที่เกิดจากการเขียน Log file ออกไปจากพวก Nonsequential I/O ทำให้การทำงานของ Log file เร็วขึ้น เพราะการเคลื่อนของ arm และ head ของ Disk จะลดลง (ทั้งหมดในข้อนี้ ยกเว้น กรณีที่ Database เป็นแบบ read-only เพราะมันจะไม่มีการบันทึกแก้ไขข้อมูล จึงไม่มีการบันทึก Log file)

8. สร้าง Tempdb บน Disk ที่เป็น RAID 0 เพราะว่าทำงานได้เร็ว และ Tempdb ไม่ค่อยน่ากลัวเวลาเสียหายสักเท่าไหร่ เนื่องจากยังไง SQL Server ก็สร้าง Tempdb ใหม่ทุกครั้งที่ Restart อยู่แล้ว

Credit: insanity
Link: http://www.thaiadmin.org/board/index.php?topic=49494.0