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

นอกจากการตรวจสอบ 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 ว่ามีทำให้เกิดการเปลี่ยนแปลงอย่างไร เพื่อประโยชน์ในการวิเคราะห์ปัญหาต่าง ๆ ในอนาคต

Credit: insanity

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