มา Monitor MSSQL Server กันเถอะ!!

ในทุกวันนี้เทคโนโลยีของเรามีความก้าวหน้าไปมาก แต่ไม่ว่าจะไปไกลแค่ไหนสิ่งหนึ่งที่สำคัญที่สุดก็คงจะหนีไม่พ้นเรื่อง Database ที่เป็นตัวเก็บข้อมูลของเราและมีความสำคัญมาก เคยสงสัยมั้ยว่า Database ของเรามีการใช้งานเป็นอย่างไร วันนี้จะมานำเสนอเรื่อง Database Performance Monitoring ของตัว SQL Server กันครับ ปกติแล้วการ Monitor MSSQL Server Database ของเราอย่างง่ายที่สุดเราจะใช้ Tool ที่มีชื่อว่า Performance Monitor ซึ่งอยู่ใน Windows Operating System โดย Parameter ของตัว Performance Monitor นั้นมีต่างๆ มากมาย วันนี้จะมานำเสนอ Parameter ที่ใช้หลักๆ ที่แบ่งเป็น Processor, Memory, Disk Activity และ Monitor SQL Server กันนะครับ

Processor

1. % Processor Time เป็น Parameter ที่เอาไว้ใช้สำหรับดูการใช้งานของ CPU ทั้งหมดที่มีอยู่ไปกับ Process ทั้งหมดในเครื่องว่าใช้ไปทั้งหมดเท่าไหร่ เมื่อค่าถึง 80–100% สามารถบอกได้ถึงการใช้งานของ CPU ที่สูงควรตรวจสอบ Process ที่ใช้งาน CPU สูงหรือเพิ่ม CPU เพื่อให้เพียงพอต่อการใช้งาน
2. % Privileged Time เป็น Parameter ที่บอกการใช้งานของ CPU ใน Kernel Mode กล่าวคือ เป็นการ Parameter ที่เอาไว้ดูสำหรับการใช้งาน CPU ของระบบ (Kernel Operation)
3. % User time เป็น Parameter ที่บอกการใช้งานของ User Mode กล่าวคือ เป็น Parameter ที่เอาไว้ดูสำหรับการใช้งาน CPU ของ Application ถ้าเรื่องเครื่อง Server มีเพียง MSSQL Server ใช้งานอยู่เพียงตัวเดียวสามารถบอกได้ว่าค่าที่แสดงผลออกมาเป็นของ MSSQL Server เพียงอย่างเดียว

Memory

1. Available MBs เป็น Parameter ที่เอาไว้ตรวจสอบว่า Memory เหลือเพียงพอต่อการใช้งานอยู่เท่าไหร่
2. Pages/sec เป็น Parameter ที่เอาไว้ตรวจสอบจำนวนครั้งการใช้งานของ virtual Memory ถ้าค่านี้มีค่าที่สูงหมายถึง Memory ที่มีอยู่ไม่เพียงพอต่อการใช้งาน

Disk Activity

1. Avg. Disk Queue Length หรือ Current Disk Queue Length เป็น Parameter ที่แสดงถึงค่าเฉลี่ยจำนวน Queue ของการ Read และ Write รวมกันกล่าวคือ เมื่อไหร่ก็ตามที่ค่านี้เท่ากับ 0 นั่นหมายถึงการไม่มี Queue แสดงให้เห็นว่า Disk ลูกนี้สามารถรองรับการใช้งาน (Read และ Write) ได้เลยไม่มี Wait ที่เกิดจากการ Queue

***ค่านี้ควรดูคู่กับค่า Avg. Disk Byte/Read และ Avg. Disk Byte/Write เพื่อตรวจสอบการค่าสูงสุดของการ Read และ Write กล่าวคือ เมื่อไหร่ก็ตามที่ค่า Avg. Disk Queue Length มีค่าสูงที่สุด(เทียบจากการใช้งานปกติ) เราสามารถดูค่า Avg. Disk Byte/Read และ Avg. Disk Byte/Write ไปด้วยเพื่อดูค่าสูงที่สุดที่ Disk ของเราสามารถใช้งานได้

***ค่า Avg. Disk Byte/Write ขณะที่ Avg. Disk Queue Length สูงสุดสามารถใช้เป็นการคำนวณ Auto Extend Size สูงสุดของ Data File ใน MSSQL Server ได้

1. Avg. Disk Byte/Read เป็น Parameter ที่แสดงถึงอัตราค่าเฉลี่ยของการอ่าน Disk มีหน่วยเป็น Byte ต่อวินาที
2. Avg. Disk Byte/Write เป็น Parameter ที่แสดงถึงค่าเฉลี่ยของการเขียน Disk มีหน่วยเป็น Byte ต่อวินาที

Monitor SQL Server

1. [SQLServer:Buffer Manager] “Buffer Cache hit ratio” เป็น Parameter ที่เอาไว้สำหรับตรวจสอบ Memory ของ MSSQL Server ว่าเพียงพอต่อการใช้งาน คือ ถ้าค่า 100 สามารถบอกได้ว่า Memory เพียงพอสำหรับการใช้งาน Database แต่ถ้าค่าต่ำกว่า 100 สามารถบอกได้ว่า Memory ไม่เพียงพอต่อการใช้งาน ควรปรับ Instance Memory Parameter ให้มากขึ้นกว่าเดิม
2. [SQLServer:Buffer Manager] “Checkpoint Pages / Sec” เป็น Parameter ที่แสดงถึงจำนวน Dirty pages ที่ย้ายจาก Memory ลงไปยัง Disk โดยปกติแล้วเราควรเก็บ Base Line ค่านี้เอาไว้ เพื่อนำมาเปรียบเทียบทีหลังถึงความผิดปกติของ Database ถ้าค่านี้สูงกว่าปกติเราควรใช้ Indirect Checkpoints เพื่อลดจำนวน Checkpoint Pages ต่อวินาที
3. [SQLServer:Access Methods] “Full scans/sec” เป็น Parameter ที่ตรวจสอบการทำงานแบบ Full Scan กล่าวคือเป็นจำนวนการใช้งาน Full Table Scan หรือ Index Scan ต่อวินาที ถ้าค่านี้มีค่าที่สูงสามารถบอกได้ว่าไม่มีการใช้งาน Index เลยหรือ Index ที่มีอยู่ไม่มีประสิทธิภาพมากพอ ควรตรวบสอบ Query ที่ใช้งานหรือ Fragment ของ Index
4. [SQLServer:Memory Manager] “Total Server Memory (KB)” และ “Target Server Memory (KB)” 2 ตัวนี้เป็น Parameter ที่ใช้ใกล้เคียงกันเป็นตัวที่บอกว่าเครื่อง MSSQL Server ต้องการใช้ Memory ทั้งหมดเท่าไหร่ คือ ถ้าค่า Total Server Memory (KB) เป็นค่าที้สำหรับบอกว่าตัว MSSQL Server สามารถใช้ Memory ทั้งหมดเท่าไหร่ และค่า Target Server Memory (KB) เป็นค่าที่บอกว่าตัว MSSQL Server ต้องการใช้ Memory ทั้งหมดเท่าไหร่ ถ้า 2 ค่านี้มีค่าที่เท่ากันนั่นหมายถึงตัว SQL Server ต้องการใช้ Memory ทั้งหมดที่มีอยู่
5. [SQLServer:Locks] “Average Wait Time (ms)” เป็น parameter ที่แสดงถึงเวลาที่รอของการ Lock แต่ละครั้งมีหน่วยเป็น Millisecond ค่านี้ควรมีค่าเข้าใกล้ 0 เพื่อแสดงถึงระยะเวลาในการ Lock ที่น้อย
6. [SQLServer:Locks] “Lock Waits / Sec” เป็น parameter ที่แสดงถึงจำนวนของการ lock ในแต่ละวินาที ค่านี้ควรมีค่าเข้าใกล้ 0 เพื่อแสดงถึงตัว MSSQL Server ที่ไม่มีการ Lock เลย ถ้าค่านี้มีค่ามากควรตรวจสอบ query ที่ใช้งานใน MSSQL Server เพื่อแก้ไข Query ให้ไม่เกิดการ lock

ต่อมาจะเป็นตัวที่จะช่วย Monitor การทำงานของ MSSQL Server นะครับ

SQL Server Tool

1. SQL Server Profiler เป็น Tool ที่เอาไว้ตรวจสอบ Activities ใน MSSQL Server ว่ามีการใช้งานเป็นอย่างไร tool ตัวนี้ควรใช้คู่กับ Performance Monitor Tool ของตัว Windows เพื่อตรวจสอบปัญหาหรือการใช้งานที่ช้ากว่าปกติของตัว MSSQL Server เช่น เมื่อไหร่ก็ตามที่ CPU ขึ้นสูงหรือ Memory ใช้งานสูงหรือตัว MSSQL Server มีการ Lock เกิดขึ้น ในขณะเดียวกันเราก็ใช้ตัว SQL Server Tool เพื่อตรวจสอบปัญหาหรือดูการใช้งาน MSSQL Server ในช่วงเวลาเดียวกัน

• ก่อนที่เราจะทำการตั้งค่าควรมาที่เมนู Events Selection และเลือก Show all events, Show all columns แสดงดังรูป

เรามาดู parameter ที่ควรใช้ใน SQL Server Tool กันนะครับ

• Locks → Lock:Deadlock เป็น Parameter ที่เอาไว้แสดง Session ที่ติด Dead Lock
• Performance → Show plan XML เป็น Parameter ที่เอาไว้แสดง Plan การทำงานของ Query

ตัวอย่างการใช้งานนะครับ

จากรูปเป็นการแสดงถึง Plan การทำงานของ Query ว่ามีการทำงานเป็นอย่างไรโดยในบรรทัดต่อมาเป็นการแสดง Query ที่เป็น Text

1. SSMS Activity Monitor เป็น Tool ที่แสดง Activities การทำงานของ MSSQL Server ว่ามีการใช้งานเป็นอย่างไร ใช้งาน Resource ไปเท่าไหร่ มีการ lock ของ Query หรือไม่ โดยปกติแล้วจะมาตรวจสอบที่เมนู Active Expensive Queries เพื่อตรวจสอบว่า Query ไหนที่ใช้งาน Performance สูงที่สุด
2. SQL Server Logs เป็นตัวที่แสดง Log การทำงานของ MSSQL Server ว่ามีการใช้งานเป็นอย่างไร เมื่อไหร่ก็ตามที่ตัว MSSQL Server มีการทำงานที่ผิดปกติเราควรมาตรวจสอบที่ SQL Server logs เป็นที่แรกเพื่อตรวจสอบปัญหาและแก้ปัญหาต่อไป

Windows Event Log

เป็น log ของตัว windows ที่เอาไว้ใช้สำหรับตรวจสอบ Activities การทำงานของตัว MSSQL Server สามารถเข้าไปที่เมนู Application โดยเราสามารถใช้ Windows Event Log ไว้เพื่อตรวจสอบเมื่อ MSSQL Server มีปัญหาหรือมีการใช้งานที่ช้า

จบกันไปแล้วนะครับสำหรับวิธีการตรวจสอบ Performance ของ MSSQL Server คราวหน้าจะเป็นเรื่องอะไรฝากติดตามกันด้วยนะครับ