ในการวิเคราะห์การทำงานของ SQL Serverเพื่อปรับปรุงประสิทธิภาพในการทำงาน ของ SQL Server ซึ่งสามารถแบ่งออกเป็น 2 ลักษณะใหญ่ ๆ ได้ คือ
- System หรือ Server ก็คือ การ Tune ในเส่วนที่เกี่ยวกับ Hardware resource ซึ่งในการวิเคราะห์จะอาศัย เครื่องมือ คือ Performance Monitor หรือ System Monitor Tool ของ Windows Server เป็นหลัก
- SQL Query คือ การ Tune ตัว SQL Query ซึ่งในการวิเคราะห์จะอาศัยเครื่องมือ คือ SQL Profiler ในการค้นหา Query ที่ควรจะถูกทำการ Optimize และ ดู Execution Plan ใน Query Analyzer เพื่อวิเคราะห์ หาทางแก้ไข เป็นหลัก
ลองดูทีละส่วนนะครับ
เรื่องของ System
=============
ก่อนอื่น จะต้องรู้จัก Performance Monitor หรือ System Monitor Tool เสียก่อน ซึ่งมันก็คือ เครื่องมือที่ใช้สำหรับการตรวจรายละเอียดการใช้ Resource ต่าง ๆ ของระบบ เช่น Memory, Disk, Processor และ Network รวมถึงค่าแสดงการทำงานต่าง ๆ ของ SQL Server โดยที่เราจะเรียก System Component ที่ให้ข้อมูลเหล่านี้ว่า "Performance Object" และ แต่ละ Performance Object จะประกอบไปด้วย "Counters" ซึ่งจะแสดงลักษณะต่าง ๆ ของ Component ตัวอย่างเช่น Object ที่ชื่อ Processor ก็จะมี Counter ตัวหนึ่ง ที่ชื่อ % Processor Time เป็นต้น นอกจากนี้ ในแต่ละ System Component ยังอาจจะประกอบไปด้วยหลาย Instance เช่น เราอาจจะมี CPU 2 ตัว ซึ่งจะทำให้ Processor Object ประกอบด้วย Instance 0,1 และ _Total (เป็นตัวรวมของทุก ๆ Instance)
เราสามารถใช้ System Monitor Tool ทำงานได้ 2 ลักษณะ คือ
- Real time ในรูปแบบของ Graph หรือ
- Counter Log ซึ่งก็คือการ Capture ค่าต่าง ๆ เก็บไว้เป็น Log เอาไว้ดูทีหลัง
ในส่วนของ System นี้ จุดที่จะทำการวิเคราะห์การใช้งานก็คือ Resource ต่าง ๆ ของระบบ ได้แก่
- Memory
- Disk I/O
- Processor และ
- Network
โดยการวิเคราะห์จะทำการหา ส่วนที่เป็นคอขวด (Bottleneck) หรือส่วนที่เป็นปัญหา และทำการแก้ไขปัญหาที่พบ
วิธีในการวิเคราะห์จุดที่เป็น Bottleneck ที่ดีอย่างหนึ่ง ก็คือ การดูว่าเกิด Queue ขึ้นที่จุดใด ซึ่งการมี Queue เกิดขึ้นก็จะแสดงให้เห็นถึง Resource ที่เริ่มจะไม่เพียงพอ ตัวอย่างเช่น การเข้าแถวรับบริการของธนาคาร สมมติว่า พนักงานให้บริการลูกค้า 1 คน โดยใช้เวลา 15 วินาที แต่ว่ามีลูกค้าคอยอยู่ 10 คน แสดงว่า ลูกค้าคนใหม่ที่มาต่อแถว จะต้องรอถึง 150 วินาที ก่อนที่จะรับบริการ โดยใช้เวลา 15 วินาที รวมเป็น 165 วินาที เป็นต้น
เพราะฉะนั้น เมื่อจุดใดมี Queue สูงขึ้น ก็แสดงให้เห็นว่าจุดนั้น เริ่มจะประสบกับปัญหาแล้ว
(Performance Object ไม่ทุกตัวที่จะมี Counter ที่แสดง Queue อยู่ แต่ว่า เกือบทุกตัวจะมี Counters ที่แสดงถึงอัตราการใช้ Resource ที่มากกว่าที่มีอยู่ เช่น Memory จะไม่มี Counter ที่แสดง Queue แต่ว่าจะมี Counter ที่แสดง Hard Page Fault ซึ่งจะแสดงถึง ความต้องการใช้งาน Memory มากกว่าที่มี )
สำหรับการแก้ไขปัญหา ก็ทำได้ 2 แบบ คือ
- เพิ่ม Resource เช่น เพิ่ม Memory, Disk
- ลดอัตราการใช้งานของ Resource เช่น การเพิ่ม Index จะช่วยลด I/O Request ของ Disk ได้
การวิเคราะห์ปัญหาจาก Memory
=======================
Performance Counter ที่เกี่ยวข้อง
ชื่อ Counter คำอธิบาย ค่าของ Counter
========= ====== ============
Object --> Memory
- Available MB Physical Memory ที่เหลืออยู่ Avg > 10 MB
- Pages/Sec จำนวน hard page faults Avg < 50
- Page Faults / Sec จำนวน Page Faults ทั้งหมด เปรียบเทียบกับข้อมูลเก่า
Object --> SQL Server :Buffer Manager
- Buffer Cache Hit Ratio % ข้อมูลที่ดึงจาก Buffer Avg >= 90%
- Free Pages จน. Page ที่ว่างอยู่ อย่างน้อย ควร > 640
Object --> SQL Server : Memory Manager
- Memory Grants Pending จน. Process ที่รอใช้ Memory Avg= 0
- Target Server Memory (KB) Physical memory ที่ SQL ต้องการ ใกล้เคียงกับ จำนวน Physical memory
- Total Server Memory (KB) Physical memory ที่ SQL ใช้ ใกล้เคียงกับ Target Server memory
ลองมาดูรายละเอียดของแต่ละตัว ว่าคืออะไร
Available MB
ตัว Counter นี้แสดงถึง free physical memory ในเครื่อง ในเครื่องที่มี Performance ที่ดี ค่านี้ไม่ควรต่ำเกินไปนัก ซึ่ง SQL Server ที่มีการตั้งค่าเป็น Dynamic Memory Usage แล้ว ค่านี้จะอยู่ราว ๆ 4-10 MB โดยที่ Windows เองจะพยายามป้องกันไม่ให้ค่านี้ต่ำกว่า 4 MB ถ้าหากค่านี้ต่ำกว่า 4 MB จะทำให้เกิดการ Paging ขึ้นเป็นจำนวนมาก ซึ่งจะทำให้การทำงานช้าลงอย่างเห็นได้ชัด
Pages and Page Fault Counters
ก่อนอื่นต้องรู้จักกับ Page fault ก่อน page fault คือ Page ที่ไม่อยู่ใน พื้นที่ของ memory ของ process ที่กำลังทำงานอยู่ (หรือ Working Set) ซึ่งจะมีทั้ง soft page fault คือ page ทึ่ต้องการอยู่ใน physical memory ส่วนอื่น และ hard page fault คือ page ที่ต้องการ อยู่ใน Disk ต้องไปดึงเอาข้อมูลมาจาก Disk ซึ่งการทำงานกับ Disk จะกินเวลามาก ดังนั้น สิ่งที่ควรคำนึงถึง ก็คือ Hard page fault มากกว่า Soft Page Fault
Pages/Sec นั้น แสดงถึง จำนวน Page ที่อ่าน/เขียน กับ Disk ใน 1 วินาที ซึ่งก็คือ Hard Page Fault
Page Faults/sec ก็จะแสดงถึง จำนวน Page Faults ทั้งหมดใน 1 วินาที ซึ่งก็คือ Hard Page Fault + Soft Page Fault
ดังนั้น ค่า Hard Page Fault หรือ Pages/Sec จึงไม่ควรจะสูง โดยในระบบที่มี Disk ช้า ไม่ควรมีค่าเกินกว่า 50/sec
Buffer Cache Hit Ratio
Buffer Cache ควรมีค่ามากกว่า 90% สำหรับงาน OLTP ซึ่งโดยปกติแล้ว จะพบว่ามีค่า > 99% เป็นส่วนใหญ่ ค่า Buffer Cache Hit Ratio ที่ต่ำกว่า 90% แสดงให้เห็นว่า SQL Server ต้องการ memory มากกว่า Physical Memory ที่มีอยู่
Free Pages
ถ้าหากค่านี้ต่ำกว่า 640 Pages หรือ 5 MB (1 Page = 8k) แล้ว หมายความว่า Physical Memory ต่ำเกินไป หรือ มีส่วนของ Buffer Cache จำนวนสูงมาก
ถ้าหาก Free Pages > 5,000 แสดงว่า SQL Server ยังไม่ต้องการ Memory มากกว่าที่มีอยู่
Memory Grants Pending
หมายถึง จำนวน Process ที่รอ Memory จาก SQL Server อยู่ ถ้ามีค่าสูง แสดงว่า Memory ไม่พอ โดยปกติแล้ว ค่านี้ควรจะ = 0 ตลอดเวลา
Target Server Memory (KB) and Total Server Memory (KB)
Target Server Memory คือ จำนวน Memory เป้าหมายถึง SQL Server ต้องการใช้งาน ส่วน Total Server Memory คือ จำนวน Memory ที่ SQL Server ครอบครองใช้งานอยู่
ซึ่งหาก Target Server Memory มีค่าสูงกว่า Total Server Memory มาก แสดงว่าระบบมี Physical Memory น้อยเกินไป
สรุปก็คือ ถ้าค่าต่าง ๆ ไม่เป็นไปตามนี้แล้ว สามารถกล่าวได้ว่า น่าจะมีปัญหา Memory ไม่พอเกิดขึ้นมาแล้ว
การแก้ปัญหาเรื่องของ Memory
=====================
1. ทำการ Optimize Query เช่น สร้าง Index เป็นต้น ซึ่งจะต้องวิเคราะห์ Query ก่อนที่จะทำการแก้ไข
2. เพิ่ม Memory ให้แก่ SQL Server หรือในกรณีที่กำหนด Max server memory เอาไว้ต่ำอยู่ ก็สามารถเพิ่มค่า Max Server Memory ได้
(ดูใน Enterprise Manager ให้คลิ๊กขวาที่ Server --> Properties แล้วดูจะพบว่ามี Max Server Memory อยู่)
3. ถ้าหากมี RAM 4GB และใช้ Windows 2000 Advanced Server หรือ Windows 2000 Data Center ให้ทำการ Enable 3GB ใน boot.ini
4. ถ้าหากมี RAM > 4GB และใช้ Windows 2000 Advanced Server หรือ Windows 2000 Data Center ให้ทำการ เพิ่ม /PAE ใน boot.ini และ enable AWE ให้ SQL Server ( ซึ่งถ้ามี RAM ไม่เกิน 16 GB สามารถใช้ /3GB ร่วมกับ /PAE ได้ แต่ถ้าเกิน 16GB จะใช้ได้แค่ /PAE เท่านั้น)
Credit: insanity
- System หรือ Server ก็คือ การ Tune ในเส่วนที่เกี่ยวกับ Hardware resource ซึ่งในการวิเคราะห์จะอาศัย เครื่องมือ คือ Performance Monitor หรือ System Monitor Tool ของ Windows Server เป็นหลัก
- SQL Query คือ การ Tune ตัว SQL Query ซึ่งในการวิเคราะห์จะอาศัยเครื่องมือ คือ SQL Profiler ในการค้นหา Query ที่ควรจะถูกทำการ Optimize และ ดู Execution Plan ใน Query Analyzer เพื่อวิเคราะห์ หาทางแก้ไข เป็นหลัก
ลองดูทีละส่วนนะครับ
เรื่องของ System
=============
ก่อนอื่น จะต้องรู้จัก Performance Monitor หรือ System Monitor Tool เสียก่อน ซึ่งมันก็คือ เครื่องมือที่ใช้สำหรับการตรวจรายละเอียดการใช้ Resource ต่าง ๆ ของระบบ เช่น Memory, Disk, Processor และ Network รวมถึงค่าแสดงการทำงานต่าง ๆ ของ SQL Server โดยที่เราจะเรียก System Component ที่ให้ข้อมูลเหล่านี้ว่า "Performance Object" และ แต่ละ Performance Object จะประกอบไปด้วย "Counters" ซึ่งจะแสดงลักษณะต่าง ๆ ของ Component ตัวอย่างเช่น Object ที่ชื่อ Processor ก็จะมี Counter ตัวหนึ่ง ที่ชื่อ % Processor Time เป็นต้น นอกจากนี้ ในแต่ละ System Component ยังอาจจะประกอบไปด้วยหลาย Instance เช่น เราอาจจะมี CPU 2 ตัว ซึ่งจะทำให้ Processor Object ประกอบด้วย Instance 0,1 และ _Total (เป็นตัวรวมของทุก ๆ Instance)
เราสามารถใช้ System Monitor Tool ทำงานได้ 2 ลักษณะ คือ
- Real time ในรูปแบบของ Graph หรือ
- Counter Log ซึ่งก็คือการ Capture ค่าต่าง ๆ เก็บไว้เป็น Log เอาไว้ดูทีหลัง
ในส่วนของ System นี้ จุดที่จะทำการวิเคราะห์การใช้งานก็คือ Resource ต่าง ๆ ของระบบ ได้แก่
- Memory
- Disk I/O
- Processor และ
- Network
โดยการวิเคราะห์จะทำการหา ส่วนที่เป็นคอขวด (Bottleneck) หรือส่วนที่เป็นปัญหา และทำการแก้ไขปัญหาที่พบ
วิธีในการวิเคราะห์จุดที่เป็น Bottleneck ที่ดีอย่างหนึ่ง ก็คือ การดูว่าเกิด Queue ขึ้นที่จุดใด ซึ่งการมี Queue เกิดขึ้นก็จะแสดงให้เห็นถึง Resource ที่เริ่มจะไม่เพียงพอ ตัวอย่างเช่น การเข้าแถวรับบริการของธนาคาร สมมติว่า พนักงานให้บริการลูกค้า 1 คน โดยใช้เวลา 15 วินาที แต่ว่ามีลูกค้าคอยอยู่ 10 คน แสดงว่า ลูกค้าคนใหม่ที่มาต่อแถว จะต้องรอถึง 150 วินาที ก่อนที่จะรับบริการ โดยใช้เวลา 15 วินาที รวมเป็น 165 วินาที เป็นต้น
เพราะฉะนั้น เมื่อจุดใดมี Queue สูงขึ้น ก็แสดงให้เห็นว่าจุดนั้น เริ่มจะประสบกับปัญหาแล้ว
(Performance Object ไม่ทุกตัวที่จะมี Counter ที่แสดง Queue อยู่ แต่ว่า เกือบทุกตัวจะมี Counters ที่แสดงถึงอัตราการใช้ Resource ที่มากกว่าที่มีอยู่ เช่น Memory จะไม่มี Counter ที่แสดง Queue แต่ว่าจะมี Counter ที่แสดง Hard Page Fault ซึ่งจะแสดงถึง ความต้องการใช้งาน Memory มากกว่าที่มี )
สำหรับการแก้ไขปัญหา ก็ทำได้ 2 แบบ คือ
- เพิ่ม Resource เช่น เพิ่ม Memory, Disk
- ลดอัตราการใช้งานของ Resource เช่น การเพิ่ม Index จะช่วยลด I/O Request ของ Disk ได้
การวิเคราะห์ปัญหาจาก Memory
=======================
Performance Counter ที่เกี่ยวข้อง
ชื่อ Counter คำอธิบาย ค่าของ Counter
========= ====== ============
Object --> Memory
- Available MB Physical Memory ที่เหลืออยู่ Avg > 10 MB
- Pages/Sec จำนวน hard page faults Avg < 50
- Page Faults / Sec จำนวน Page Faults ทั้งหมด เปรียบเทียบกับข้อมูลเก่า
Object --> SQL Server :Buffer Manager
- Buffer Cache Hit Ratio % ข้อมูลที่ดึงจาก Buffer Avg >= 90%
- Free Pages จน. Page ที่ว่างอยู่ อย่างน้อย ควร > 640
Object --> SQL Server : Memory Manager
- Memory Grants Pending จน. Process ที่รอใช้ Memory Avg= 0
- Target Server Memory (KB) Physical memory ที่ SQL ต้องการ ใกล้เคียงกับ จำนวน Physical memory
- Total Server Memory (KB) Physical memory ที่ SQL ใช้ ใกล้เคียงกับ Target Server memory
ลองมาดูรายละเอียดของแต่ละตัว ว่าคืออะไร
Available MB
ตัว Counter นี้แสดงถึง free physical memory ในเครื่อง ในเครื่องที่มี Performance ที่ดี ค่านี้ไม่ควรต่ำเกินไปนัก ซึ่ง SQL Server ที่มีการตั้งค่าเป็น Dynamic Memory Usage แล้ว ค่านี้จะอยู่ราว ๆ 4-10 MB โดยที่ Windows เองจะพยายามป้องกันไม่ให้ค่านี้ต่ำกว่า 4 MB ถ้าหากค่านี้ต่ำกว่า 4 MB จะทำให้เกิดการ Paging ขึ้นเป็นจำนวนมาก ซึ่งจะทำให้การทำงานช้าลงอย่างเห็นได้ชัด
Pages and Page Fault Counters
ก่อนอื่นต้องรู้จักกับ Page fault ก่อน page fault คือ Page ที่ไม่อยู่ใน พื้นที่ของ memory ของ process ที่กำลังทำงานอยู่ (หรือ Working Set) ซึ่งจะมีทั้ง soft page fault คือ page ทึ่ต้องการอยู่ใน physical memory ส่วนอื่น และ hard page fault คือ page ที่ต้องการ อยู่ใน Disk ต้องไปดึงเอาข้อมูลมาจาก Disk ซึ่งการทำงานกับ Disk จะกินเวลามาก ดังนั้น สิ่งที่ควรคำนึงถึง ก็คือ Hard page fault มากกว่า Soft Page Fault
Pages/Sec นั้น แสดงถึง จำนวน Page ที่อ่าน/เขียน กับ Disk ใน 1 วินาที ซึ่งก็คือ Hard Page Fault
Page Faults/sec ก็จะแสดงถึง จำนวน Page Faults ทั้งหมดใน 1 วินาที ซึ่งก็คือ Hard Page Fault + Soft Page Fault
ดังนั้น ค่า Hard Page Fault หรือ Pages/Sec จึงไม่ควรจะสูง โดยในระบบที่มี Disk ช้า ไม่ควรมีค่าเกินกว่า 50/sec
Buffer Cache Hit Ratio
Buffer Cache ควรมีค่ามากกว่า 90% สำหรับงาน OLTP ซึ่งโดยปกติแล้ว จะพบว่ามีค่า > 99% เป็นส่วนใหญ่ ค่า Buffer Cache Hit Ratio ที่ต่ำกว่า 90% แสดงให้เห็นว่า SQL Server ต้องการ memory มากกว่า Physical Memory ที่มีอยู่
Free Pages
ถ้าหากค่านี้ต่ำกว่า 640 Pages หรือ 5 MB (1 Page = 8k) แล้ว หมายความว่า Physical Memory ต่ำเกินไป หรือ มีส่วนของ Buffer Cache จำนวนสูงมาก
ถ้าหาก Free Pages > 5,000 แสดงว่า SQL Server ยังไม่ต้องการ Memory มากกว่าที่มีอยู่
Memory Grants Pending
หมายถึง จำนวน Process ที่รอ Memory จาก SQL Server อยู่ ถ้ามีค่าสูง แสดงว่า Memory ไม่พอ โดยปกติแล้ว ค่านี้ควรจะ = 0 ตลอดเวลา
Target Server Memory (KB) and Total Server Memory (KB)
Target Server Memory คือ จำนวน Memory เป้าหมายถึง SQL Server ต้องการใช้งาน ส่วน Total Server Memory คือ จำนวน Memory ที่ SQL Server ครอบครองใช้งานอยู่
ซึ่งหาก Target Server Memory มีค่าสูงกว่า Total Server Memory มาก แสดงว่าระบบมี Physical Memory น้อยเกินไป
สรุปก็คือ ถ้าค่าต่าง ๆ ไม่เป็นไปตามนี้แล้ว สามารถกล่าวได้ว่า น่าจะมีปัญหา Memory ไม่พอเกิดขึ้นมาแล้ว
การแก้ปัญหาเรื่องของ Memory
=====================
1. ทำการ Optimize Query เช่น สร้าง Index เป็นต้น ซึ่งจะต้องวิเคราะห์ Query ก่อนที่จะทำการแก้ไข
2. เพิ่ม Memory ให้แก่ SQL Server หรือในกรณีที่กำหนด Max server memory เอาไว้ต่ำอยู่ ก็สามารถเพิ่มค่า Max Server Memory ได้
(ดูใน Enterprise Manager ให้คลิ๊กขวาที่ Server --> Properties แล้วดูจะพบว่ามี Max Server Memory อยู่)
3. ถ้าหากมี RAM 4GB และใช้ Windows 2000 Advanced Server หรือ Windows 2000 Data Center ให้ทำการ Enable 3GB ใน boot.ini
4. ถ้าหากมี RAM > 4GB และใช้ Windows 2000 Advanced Server หรือ Windows 2000 Data Center ให้ทำการ เพิ่ม /PAE ใน boot.ini และ enable AWE ให้ SQL Server ( ซึ่งถ้ามี RAM ไม่เกิน 16 GB สามารถใช้ /3GB ร่วมกับ /PAE ได้ แต่ถ้าเกิน 16GB จะใช้ได้แค่ /PAE เท่านั้น)
Credit: insanity
No comments:
Post a Comment