Thaiadmin

[Article] SQL Server Performance Tuning ตอนที่ 1

0 สมาชิก และ 1 บุคคลทั่วไป กำลังดูหัวข้อนี้

ออฟไลน์ insanity

  • *****
  • 1,793
  • 77
  • เพศ: ชาย
  • Life would be much easier if I had the source code
    • http://jullapol.blogspot.com
[Article] SQL Server Performance Tuning ตอนที่ 1
« เมื่อ: 25 กุมภาพันธ์ 2007, 23:49:32 »
บทความสรุปจากหนังสือที่กำลังอ่านอยู่น่ะครับ  ( SQL Server Query Performance Tuning Distilled, 2nd Edition ) แต่ไม่รู้จะทนอ่าน/เขียนได้จนจบรึเปล่า   แฮะ ๆ  เข้าเรื่องเลยละกัน
 ^-^

ในการวิเคราะห์การทำงานของ 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 เท่านั้น)


ปล. ตรงไหนที่ผิด หรือ ตรงไหนมีใคร หรือ เจ้าหน้าที่ท่านใด เห็นว่าอธิบายไม่ละเอียด  ช่วยกันเพิ่มเติมได้นะครับ  จะได้สมบูรณ์มากขึ้น 
 ^-^
« แก้ไขครั้งสุดท้าย: 11 มีนาคม 2007, 19:52:45 โดย insanity »

ออฟไลน์ insanity

  • *****
  • 1,793
  • 77
  • เพศ: ชาย
  • Life would be much easier if I had the source code
    • http://jullapol.blogspot.com
[Article] SQL Server Performance Tuning ตอนที่ 1
« ตอบกลับ #1 เมื่อ: 26 กุมภาพันธ์ 2007, 23:22:27 »
การวิเคระห์ปัญหาจาก 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 อยู่แล้ว

« แก้ไขครั้งสุดท้าย: 11 มีนาคม 2007, 19:42:11 โดย insanity »

ออฟไลน์ insanity

  • *****
  • 1,793
  • 77
  • เพศ: ชาย
  • Life would be much easier if I had the source code
    • http://jullapol.blogspot.com
Re: [Article] SQL Server Performance Tuning ตอนที่ 1
« ตอบกลับ #2 เมื่อ: 28 กุมภาพันธ์ 2007, 00:02:03 »
การวิเคราะห์ปัญหาจาก Processor
========================

ตัว Counters ที่เกี่ยวข้องก็มี
   ชื่อ Counter                                                                    คำอธิบาย                                                                                 ค่าของ Counter
     =========                                                                    ======                                                                               ============
Object --> Processor(_Total)
-  % Processor Time                                             %ของเวลาที่ Processor busy                                                    Avg < 80 %
-  % Privilege Time                                                %ของเวลาที่ Processor ทำงานใน Privileged mode             Avg < 10 %
Object --> System
-  Processor Queue Length                                 จำนวน request ที่รอคิว Processor อยู่                                      Avg < 2
-  Context Switches/Sec                                       อัตราการ Switch จาก Thread หนึ่งไปอีก Thread หนึ่ง              Avg < 1000 per processor

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

% Processor Time
ค่านี้ไม่ควรเกิน 80% เป็นเวลานาน ๆ  ถ้าหากว่าค่า %Process Time สูงอย่างต่อเนื่อง ขณะที่ค่า Counter ของ Disk และ Network ต่ำอยู่ จะแสดงให้เห็นว่าเกิดคอขวดที่ Processor  แต่ หาก %Processor Time อยู่ที่ 85% ขณะที่ % Disk Time อยู่ที่ 50% ลักษณะนี้จะดูเหมือนว่า CPU ส่วนใหญ่จะใช้เวลาไปกับการจัดการ Disk ซึ่งหากจะให้สรุปแน่นอน จะต้องดูค่า % Privileged Time ต่อไป ซึ่งในลักษณะนี้ เราควรที่จะแก้ไขปัญหาเรื่อง Disk ก่อน ซึ่งปัญหาเรื่อง Disk ก็อาจจะมาจากเรื่องของ Memory อีกทีหนึ่ง ดังที่ได้กล่าวไปแล้ว

%Privileged Time
ใน Windows นั้น Processor จะทำงานอยู่ 2 mode คือ user mode และ privileged mode (หรือ kernel mode) ซึ่งกิจกรรมต่าง ๆ ในระดับของ System เช่น disk access จะทำงานอยู่ใน  Privileged mode ดังนั้น หากพบว่า % privileged time อยู่สูงกว่า 20-25% (ในเครื่องที่มีเฉพาะ SQL Server)  นั้นจะหมายถึงว่า มีการใช้งาน I/O สูงมาก ซึ่งโดยปกติ %Privileged Time จะอยู่ที่ 5-10%

Process Queue Length
หมายถึงจำนวนของ Threads ที่อยู่ใน Processor Queue (จะมี Processor Queue แค่ Queue เดียวเท่านั้น ไม่ว่าจะมีกี่ตัวก็ตาม) ค่า Process Queue Length ที่สูงกว่า 2 อย่างต่อเนื่อง จะหมายถึงมีการใช้งาน Processor ที่สูง โดยปกติแล้วเราสามารถดูค่าการใช้งาน Processor ได้จาก %Processor Time อยู่แล้ว แต่ว่าค่า Process Queue Length จะช่วยยืนยันได้ว่ามีการใช้งานของ Processor ที่สูงจริง ( นอกจาก Processor ไม่ว่างทำงานตลอดแล้ว ยังมีงานรอ Queue ให้ทำอีก )

Context Switches /sec
ค่าควรอยู่ที่ประมาณ 300-1000 Context Switches/sec per processor ค่าที่สูงกว่านี้แสดงว่าเกิด page faults เนื่องจาก Memory ไม่พอ สำหรับค่าที่แสดงใน Counter นี้เป็นค่ารวมของทุก ๆ Processors รวมกัน

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

1. Optimize Query โดยใช้ Profiler ทำการจับ Query ที่มี Workload ของ Processor สูง ๆ แล้วทำการแก้ไข

2. เพิ่มจำนวน Processor หรือ เปลี่ยนเป็น Processor ที่ทำงานได้เร็วขึ้น  ซึ่ง
     -  หาก % Processor Time สูง แต่  Processor Queue Length ต่ำ  ควรเปลี่ยนเป็น Processor ที่ทำงานเร็วขึ้น
     -  หาก % Processor Time สูง และ Processor Queue Length สูง ควรเพิ่มจำนวน Processor ให้มากขึ้น

3. อย่าเปิด Screen Save บน Server โดยเฉพาะอย่างยิ่งพวกที่ใช้ OpenGL

4. ใช้ Lightweight Pooling หรือ NT fibers ซึ่งจะช่วยลด Context Switches และเพิ่มประสิทธิภาพได้ หากว่าระบบอยู่ภายใต้เงื่อนไขทั้ง 3 ข้อนี้
     -  Server มี Processor หลายตัว อยู่แล้ว
     -  Processors ทุกตัวใช้งานเกือบเต็ม
     -  มี Context Switches มากกว่า 1000 per processor

ออฟไลน์ insanity

  • *****
  • 1,793
  • 77
  • เพศ: ชาย
  • Life would be much easier if I had the source code
    • http://jullapol.blogspot.com
Re: [Article] SQL Server Performance Tuning ตอนที่ 1
« ตอบกลับ #3 เมื่อ: 6 มีนาคม 2007, 22:26:22 »
การวิเคราะห์ปัญหาจาก Network
======================

ใน Application ที่เป็นลักษณะของ OLTP โดยส่วนใหญ่แล้วมักจะไม่พบปัญหาในเรื่องของระบบ Network แต่จะพบในลักษณะของปัญหาจาก Hardware ( Routers, Hubs) และ Driver มากกว่า 

สำหรับค่า Counter ที่เกี่ยวข้องมีดังนี้

    ชื่อ Counter                                                                    คำอธิบาย                                                                                 ค่าของ Counter
     =========                                                                    ======                                                                               ============
Object --> Network Interface (Network Card)
-  Bytes Total/Sec                                                  จำนวนข้อมูล (bytes) ที่ Transfer บน NIC                                  Avg < 50 % ของ NIC Capacity

Object --> Network Segment
-  % Network Utilization                                         % ของ bandwidth ที่ใช้                                                               Avg < 80%

โดยมีรายละเอียด ดังนี้

Bytes Total/Sec
ค่านี้จะบอกถึงประสิทธิภาพในการทำงานของ Network Card  ตัวค่า Bytes Total/Sec ควรจะมีค่าสูง ซึ่งแสดงถึงอัตราการส่งข้อมูลสำเร็จที่สูง และเนื่องจากอาจจะต้องเผื่อไว้ในกรณีที่มีการส่งข้อมูลจำนวนมาก ๆ ในระยะสั้น ๆ ค่านี้จึงไม่ควรสูงกว่า 50% ของ Capacity ของมัน

หากค่านี้สูงจนเกือบเท่ากับ Capacity ของมัน ในขณะที่ค่า Processor, Memory ต่ำ ๆ กลาง ๆ ไม่สูง ให้สงสัยว่าอาจจะเกิดปัญหากับ Network Connection แล้ว

%Net Utilization
ค่านี้แสดงถึง % ของ Network Bandwidth ที่ใช้งานอยู่ ซึ่งไม่ควรสูงมากจนเกือบเต็ม เพราะต้องเผื่อ Bandwidth เอาไว้กรณีที่มีการส่งข้อมูลจำนวนสูงมาก ๆ ในระยะสั้น ๆ

การแก้ไขปัญหาเรื่อง Network
====================

1. Optimize Query เช่น
     - ใช้ Stored Procedure แทนการส่งคำสั่ง SQL ยาวๆ 
     - Group คำสั่ง SQL หลาย ๆ คำสั่ง เป็น Stored Procedure 
     - เรียกใช้ข้อมูลเท่าที่จำเป็น ข้อมูลส่วนใดที่ไม่ใช้ก็ไม่ควรเรียกออกมา
     - ย้าย Business logic ที่ต้องทำงานเกี่ยวข้องกับ Data จำนวนมาก ๆ ไปยัง Database Server โดยเขียนใหม่ในรูปของ Stored Procedure, Trigger เพื่อลด Network round-trips

 
2. เพิ่ม Network Adapters

3. ในการเลือกซื้อ หรือ Upgrade Network Adapter ให้เลือกที่ Driver สามารถ support "interrupt moderation" หรือ "interrupt avoidance"
« แก้ไขครั้งสุดท้าย: 11 มีนาคม 2007, 09:39:44 โดย insanity »

ออฟไลน์ insanity

  • *****
  • 1,793
  • 77
  • เพศ: ชาย
  • Life would be much easier if I had the source code
    • http://jullapol.blogspot.com
Re: [Article] SQL Server Performance Tuning ตอนที่ 1
« ตอบกลับ #4 เมื่อ: 11 มีนาคม 2007, 10:03:47 »
นอกจากการตรวจสอบ Hardware resource แล้ว ตามที่กล่าวมาแล้ว ในการวิเคราะห์ overall performance ของ SQL Server  ยังมี Counters อีกกลุ่ม ซึ่งสามารถอธิบายการทำงานโดยรวม ๆ ของ SQL Server ด้วย ดังนี้

( เพิ่มเติม ค่า Counters เหล่านี้ ตามหนังสือ ไม่มีค่าให้เปรียบเทียบว่า ต้องสูงกว่าเท่าใด หรือ ต่ำกว่าเท่าใด จึงจะเหมาะสม ดังนั้น อาจจะต้องคอยสังเกตบันทึกเอาว่า ขณะที่ Server ทำงานปกติค่าเหล่านี้เป็นอย่างไร  และตอนที่พบว่า Server ทำงานช้า ค่าเหล่านี้เป็นอย่างไร แล้วหาทางปรับปรุงแก้ไข ให้ค่าต่าง ๆ เหล่านี้ลดลง/เพิ่มขึ้น จนสู่ภาวะที่เป็นปกติ หรือ ภาวะที่ผู้ใช้งานพอใจใน Response ของ Server )

   ชื่อ Counter                                                                    
   =========                                                                  
Object --> SQLServer : Access Method
- FreeSpace Scans/Sec
- Full Scans/Sec

Object --> SQLServer : Latches
- Total Latch Wait Time (ms)

Object --> SQLServer : Locks(_Total)
- Lock Timeouts/Sec
- Lock Wait Time (ms)
- Number of Deadlocks/sec

Object --> SQLServer : SQL Statistics
- Batch Requests/sec
- SQL Re-Compilations/sec

Object --> SQLServer : General Statistics
- User Connections

ซึ่ง Counters เหล่านี้ สามารถใช้ในการแยกวิเคราะห์ในแต่ละความหมายได้ดังนี้

กรณี Missing Indexes (ขาด Indexes ที่ควรจะมี)
====================================

เนื่องจากการไม่ได้ทำ Index จะก่อให้เกิด Table Scan หรือ ก่อให้เกิดการดึงข้อมูลจำนวนมาก ๆ ดังนั้น จึงพิจารณาได้จาก

FreeSpace Scans/Sec
Counter ตัวนี้จะแสดงการ Insert ข้อมูลที่ไม่มี Physical Order ของ Rows ( ก็คือ การ Insert ลง Table ที่ไม่มี Clustered Index ซึ่ง Table ลักษณะนี้จะเรียกว่า Heap Table) การทำการ Insert ตารางพวก Heap Table นี้จะทำให้เกิดปัญหาเรื่อง Performance ที่มาจาก Space allocation algorithm ของ SQL 2000 ดังนั้นในทุก Table จึงควรจะต้องมี Physical order โดยการใช้ Clustered Index

Full Scans/Sec
ค่านี้จะแสดงจำนวน Full Scans ที่เกิดใน Table หรือ View ซึ่งสาเหตุของ Full Scan จะมาได้จาก
-  ไม่มี Index
-  เกิดการดึงข้อมูลจำนวนมาก ๆ

ดังนั้น ถ้า Full Scans ขึ้น ก็ควรที่จะวิเคราะห์หา Query ที่เป็นต้นเหตุ ซึ่งในการวิเคราะห์ Query ที่ก่อให้เกิดปัญหาเหล่านี้ จะต้องใช้ Profiler ในการตรวจสอบ ซึ่ง Query ที่ทำงานกับ Table ที่ไม่มี Index หรือ Query ที่ดีงข้อมูลจำนวนมาก ๆ จะก่อให้เกิด Logical Read จำนวนมาก และทำให้เกิดการใช้ CPU Time มากเช่นกัน

(ยกเว้น ในกรณีของการใช้ Temporary Tables ซึ่ง Tables เหล่านี้มักจะไม่มี Index อยู่แล้ว)

กรณี Database Blocking
====================

Total Latch Wait Time (ms)
Latch เป็น object ตัวหนึ่งที่คล้าย ๆ กับ Lock ซึ่ง SQL Server เอาไว้สำหรับจัดการเรื่อง Integrity ( สามารถหารายละเอียดจาก google เพิ่มเติมได้ ) ซึ่งค่า Counter นี้จะแสดงถึง Total Wait Time ที่ต้องรอในการขอ Latch Request

Lock Timeouts/sec and Lock Wait Time (ms)
ค่า Lock Timeouts/sec ควรจะ = 0 และค่า Lock Wait Time ควรจะต่ำมาก ๆ ซึ่งหากค่าทั้ง 2 สูง จะแสดงว่าเกิดการ Blocking จำวนมากขึ้นใน Database ซึ่งต้องหาสาเหตุ และแก้ไขต่อไป

Number of Deadlocks/sec
ค่านี้ ควรจะ = 0 ซึ่งหากค่านี้มากกว่า 0 แล้วจะต้องทำการตรวจสอบ Deadlock และหาทางแก้ไข

กรณี Nonreusable Execution Plan
============================

การ Nonreusable execution plan ของ Query เป็นผลให้เพิ่มการใช้งานของ CPU ให้สูงขึ้น ซึ่งการตรวจสอบว่าเกิด Nonreusable Execution Plan ทำได้โดยใช้

SQL Re-Compilations/sec
ค่า SQL Recompilations/sec นี้ควรจะใกล้ๆ  กับ 0 หากค่าสูงกว่า 0 อย่างต่อเนื่องแล้ว ก็ควรจะใช้ Profiler ในการตรวจสอบหา Stored Procedure ที่เกิดการ recompilation เพื่อหาสาเหตุและแก้ไขต่อไป

กรณีทั่วไป
=======

User Connections
จำนวน Connections ที่ใช้งาน SQL Server อยู่ในขณะนั้น

Batch Requests/Sec
จำนวน Batch Request

ค่า Counter ทั้ง 2 ตัวนี้ จะแสดงถึงภาระ (Load) ของ SQL Server ในขณะนั้น ๆ



สรุปท้าย
=====
ค่าจาก Counters ทั้งหลาย ควรจะมีการเก็บบันทึกข้อมูลเอาไว้ เพื่อเอาไว้เปรียบเทียบกับในอนาคต จะได้รู้สภาพการทำงาน/ใช้งาน ของ Server ที่เปลี่ยนแปลงไป จะได้หาทางแก้ไข หรือ ไว้เปรียบเทียบกรณีที่มีการเปลี่ยนแปลง Hardware หรือ แก้ไข/เพิ่ม/ลด Applications หรือ users ว่ามีทำให้เกิดการเปลี่ยนแปลงอย่างไร เพื่อประโยชน์ในการวิเคราะห์ปัญหาต่าง ๆ ในอนาคต

( แต่จะเก็บอย่างไรนี่ซิ เพื่อให้ดูได้ง่าย ๆ เปรียบเทียบได้ง่าย ๆ และใช้งานง่าย)


                                                                                       จบตอนที่ 1
                                                                                       =======

« แก้ไขครั้งสุดท้าย: 11 มีนาคม 2007, 11:17:31 โดย insanity »

ออฟไลน์ insanity

  • *****
  • 1,793
  • 77
  • เพศ: ชาย
  • Life would be much easier if I had the source code
    • http://jullapol.blogspot.com
Re: [Article] SQL Server Performance Tuning ตอนที่ 1
« ตอบกลับ #5 เมื่อ: 11 มีนาคม 2007, 10:58:10 »
บันทึกท้ายตอน
==========

ที่เขียนๆ  มาตั้งแต่ต้นสรุปเอาจากหนังสือน่ะครับ ส่วนในข้อความนี้ ขอไว้ Note อะไรที่ผมอยากเขียนเพิ่มละกันนะครับ     ^-^

เรื่อง Performance Monitor Tool
=========================
- กรณีที่วัดเป็นแบบ Realtime มันจะแสดงได้แค่ 100 จุด ใน 1 หน้าจอ  ซึ่งถ้าจะให้ใน 1 หน้าจอ แสดงผลลัพธ์ได้เต็มวันแล้ว จะต้อง Sampling time เป็น 864 วินาที ซึ่งก็นานพอดูเลยหละ แล้วพอพ้น 1 หน้าจอไป ข้อมูลก็หายไป
- กรณีเก็บเป็น Counter Logs จะเก็บข้อมูลของค่า Counters ลง Text files ก็จะดีกว่าแบบ Realtime ตรงที่สามารถเก็บข้อมูลได้ และสามารถใช้ Performance Monitor ในการเปิดข้อมูลจาก Logs มาแสดงบน Graph ได้ด้วย  แต่ผมว่ามันใช้งานไม่ค่อยสะดวกเท่าไหร่
- การใช้ Cacti มาแสดงจาก Performance Counters - เรื่องแบบนี้เคยเห็นจากใน forum ของ cacti.net เหมือนกัน แล้วก็สนใจว่าจะทำอยู่ แต่ยังขาดความสามารถอยู่น่ะครับ 
   ( ผมว่าน่าสนใจนะ เอา Counter Logs ที่เก็บไว้มาเข้า Cacti แล้วให้แสดงผล ซึ่งก็จะสามารถแสดงย้อนหลังเป็น Graph ได้ แล้วน่าจะใช้งานง่ายกว่าการเอา Performance Monitor มาเปิดไฟล์ log เอาด้วยมั๊ง )
- การเก็บค่าต่าง ๆ ของ Counters เอาไว้เพื่อเปรียบเทียบในอนาคต -- เรื่องนี้ก็ยังไม่รู้เหมือนกันว่าการเก็บในรูปแบบไหนจะเหมาะสมที่สุด สำหรับการนำมาใช้งาน

 ^-^

SQL Server Health and History Tool (SQLH2)
======================================
- ชื่อนี้ คือ Tool ฟรีจาก Microsoft การทำงานก็ตามชื่อน่ะครับ เอาไว้เก็บข้อมูลพวก เริ่มตั้งแต่ข้อมูลทั่วไป --> Counters แต่ละตัว ของ SQL Server ซึ่งสามารถ download ได้จาก web ของ MS เลย 
   ( ถ้าจะเก็บ Performance Counters ต้องติดตั้ง SQL Server Health and History Tool Performance Collector เพิ่มเติมก่อน แต่ก็มีให้ download เช่นเดียวกัน)
- Requirements ของมันก็ Microsoft น่ะครับ IIS, SQL Server, Reporting Serivce และ Dotnet framework 1.1 สำหรับตัว Report มีให้ Download ได้เพิ่มติม
- ถ้าไม่ลืมจะมาอธิบายคร่าว ๆ ต่อตรงนี้ เผื่อมีใครอยากอ่าน


Counters บางตัว
============
- %Disk Time  - บางครั้งจะพบว่า ค่า %Disk Time มันเกิน 100 ตลอดเวลา แล้วจะดูได้ยังไงหละ มิต้องไปนั่งจ้องไฟ HDD วัดอัตราการกระพริบของไฟ HDD เอาหรือ 
  ( เจ้า % Disk Time นี่มีผู้รู้ เคยแนะนำให้ว่าดูที่ % Idle Time แทนได้ครับ มันจะกลับกัน ซึ่งผมยังไม่ได้หารายละเอียดวิธีคำนวณของมันอ่านดู ก็เป็นว่าเล่าสู่กันฟังละกัน )
« แก้ไขครั้งสุดท้าย: 11 มีนาคม 2007, 19:44:03 โดย insanity »

ออฟไลน์ franket

  • **
  • 625
  • 4
  • เพศ: ชาย
Re: [Article] SQL Server Performance Tuning ตอนที่ 1
« ตอบกลับ #6 เมื่อ: 17 กันยายน 2007, 12:47:46 »
แวะเข้ามาอ่านด้วย ดีจัง เดี๋ยวต้องลองไปดูบ้าง

ผู้ต้องใช้ MS SQL Server อย่างจำใจ หุ หุ
กลุ่มผู้ดูแลระบบแห่งประเทศไทย ขอระงับการใช้ลายเซ็นต์รูปภาพ
อนุญาตให้ใช้ได้เพียง ลายเซ็นต์ที่เป็นข้อความ
จึงประกาศมาเพื่อขอความร่วมมือ จากสมาชิกทุกๆ ท่าน
ในนาม กลุ่มผู้ดูแลระบบแห่งประเทศไทย

ออฟไลน์ muchi

  • *****
  • 132
  • 0
  • เพศ: หญิง
Re: [Article] SQL Server Performance Tuning ตอนที่ 1
« ตอบกลับ #7 เมื่อ: 11 ตุลาคม 2007, 09:07:59 »
ขอบคุณมาก ๆ เลยคะ  กำลังจะต้องใช้เดือนหน้าพอดี จะได้มีแหล่งข้อมูลไว้ศึกษา
กลุ่มผู้ดูแลระบบแห่งประเทศไทย ขอระงับการใช้ลายเซ็นต์รูปภาพ
อนุญาตให้ใช้ได้เพียง ลายเซ็นต์ที่เป็นข้อความ
จึงประกาศมาเพื่อขอความร่วมมือ จากสมาชิกทุกๆ ท่าน
ในนาม กลุ่มผู้ดูแลระบบแห่งประเทศไทย

ออฟไลน์ DcSs

  • ***
  • 4
  • 0
  • TH@min Membership
Re: [Article] SQL Server Performance Tuning ตอนที่ 1
« ตอบกลับ #8 เมื่อ: 12 ตุลาคม 2007, 21:50:24 »
มีประโยชน์มากเลยครับ   ;D
ตอน 2 มาหรือยังจะช่วยอ่าน ครับ
<b>กลุ่มผู้ดูแลระบบแห่งประเทศไทย ขอระงับการใช้ลายเซ็นต์รูปภาพ
อนุญาตให้ใช้ได้เพียง ลายเซ็นต์ที่เป็นข้อความ
จึงประกาศมาเพื่อขอความร่วมมือ จากสมาชิกทุกๆ ท่าน
ในนาม กลุ่มผู้ดูแลระบบแห่งประเทศไทย</b>

ออฟไลน์ weahason

  • ***
  • 169
  • 0
Re: [Article] SQL Server Performance Tuning ตอนที่ 1
« ตอบกลับ #9 เมื่อ: 3 พฤษภาคม 2008, 21:21:11 »
เมื่อไรจะต่อ ตอน 2 ครับ

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

GARY

Re: [Article] SQL Server Performance Tuning ตอนที่ 1
« ตอบกลับ #10 เมื่อ: 3 มิถุนายน 2008, 23:26:36 »
เห็นคุณ insanity ทำให้ขนาดนี้นับถือจากใจจริง ทำให้ผมรู้สึกอยากจะถ่ายทอดความรู้ที่มีให้เต็มๆ เลยครับ ถ้าเรื่องอะไรที่เกี่ยวกับ SAN system or SAP system
ผมยินดีนะครับ

ออฟไลน์ Jiggy

  • *
  • 838
  • 10
  • เพศ: ชาย
  • TH@min Membership
Re: [Article] SQL Server Performance Tuning ตอนที่ 1
« ตอบกลับ #11 เมื่อ: 4 มิถุนายน 2008, 12:31:32 »
กำลังหาข้อมูลเกี่ยวกับเรื่องนี้อยู่พอดีครับ
กลุ่มผู้ดูแลระบบแห่งประเทศไทย ขอระงับการใช้ลายเซ็นต์รูปภาพ
อนุญาตให้ใช้ได้เพียง ลายเซ็นต์ที่เป็นข้อความ
จึงประกาศมาเพื่อขอความร่วมมือ จากสมาชิกทุกๆ ท่าน
ในนาม กลุ่มผู้ดูแลระบบแห่งประเทศไทย

Re: [Article] SQL Server Performance Tuning ตอนที่ 1
« ตอบกลับ #12 เมื่อ: 6 มิถุนายน 2008, 01:43:25 »
ขอความกรุณาต่อเร็วนะครับกำลังต้องการอยู่ครับ
กลุ่มผู้ดูแลระบบแห่งประเทศไทย ขอระงับการใช้ลายเซ็นต์รูปภาพ
อนุญาตให้ใช้ได้เพียง ลายเซ็นต์ที่เป็นข้อความ
จึงประกาศมาเพื่อขอความร่วมมือ จากสมาชิกทุกๆ ท่าน
ในนาม กลุ่มผู้ดูแลระบบแห่งประเทศไทย

ออฟไลน์ insanity

  • *****
  • 1,793
  • 77
  • เพศ: ชาย
  • Life would be much easier if I had the source code
    • http://jullapol.blogspot.com
Re: [Article] SQL Server Performance Tuning ตอนที่ 1
« ตอบกลับ #13 เมื่อ: 12 มิถุนายน 2008, 20:08:24 »
ควรอ่านเพิ่มเติมเพื่อเพิ่มประสบการณ์
========================

1. จะทำยังไงกับมันดีน๊อ...ช่วยกันวิเคราะห์หน่อยครับ
==================================
http://www.thaiadmin.org/board/index.php?topic=79754.0
Case นี้จบไปแบบหนึ่ง  แม้ว่าน่าจะยังมีปัญหาเรื่อง Design DB  แต่เนื่องจากทุนหนา (Hardware เหลือเฟือ) ก็จบไปด้วยดี  O0


2.รบกวนช่วยหน่อยครับ อยากรู้ว่าใช่ปัญหาไหม...
================================
http://www.thaiadmin.org/board/index.php?topic=83142.0
Case นี้ คนถามไม่เข้ามา update ความคืบหน้าแล้ว เลยไม่รู้ว่าแก้ไขสำเร็จ หรือไม่   ???
   ตามความเห็น ผมคิดว่าแก้ปัญหาไปได้ระดับหนึ่ง ซึ่ง System น่าจะทำงานได้เร็วขึ้น / ปัญหาน่าจะลดลง  แต่ก็อาจจะยังไม่พอเพียงที่ทำให้ Response ของ System อยู่ในระดับที่ user ใช้งานแล้วสบายใจ 







** ขออนุญาติ Lock นะครับ **
« แก้ไขครั้งสุดท้าย: 22 กรกฎาคม 2008, 18:11:49 โดย insanity »