การใช้งาน SQL Profiler

SQL Trace เป็นเครื่องมือในการดักรับข้อมูลของเหตุการณ์การต่าง ๆ ที่เกิดบนฝั่ง Server ซึ่งมีประเภทของเหตุการณ์มากถึง 150 ประเภท

โดยแต่ละเหตุการณ์จะส่งกลับข้อมูลออกมาในรูปแบบคอลัมน์ ซึ่งจะมีจำนวนกี่คอลัมน์ก็แล้วแต่เหตุการณ์นั้น ๆ

และ ยังสามารถกรองเอาเฉพาะข้อมูลที่ต้องการ แล้วบันทึกในรูปของไฟล์ หรือลงในตาราง

เราสามารถสร้าง SQL Trace ได้ 2 วิธีคือ

ผ่านเครื่องมือแบบ GUI ชื่อว่า SQL Profiler (มีใช้มาตั้งแต่ Microsoft SQL Server 7)

ผ่าน System Stored Procedure กลุ่ม sp_trace (มีใช้มาตั้งแต่ Microsoft SQL Server 2000) ประกอบด้วย

sp_trace_create
sp_trace_setevent
sp_trace_setfilter
sp_trace_setstatus

ความแตกต่างของ SQL Profiler และ sp_trace คือ

SQL Profiler	SP_trace
มี GUI	เรียกใช้ Stored Procedures
รันบนเครื่องมือแยกต่างหาก	รันภายใน Database Engine สามารถเรียกใช้ใน Stored Procedures อื่น ๆ หรือ สร้างเป็นงานอัตโนมัติได้
บันทึกลงไฟล์ (นามสกุล .trc) หรือลงตารางได้	บันทึกลงไฟล์ (นามสกุล .trc) หรือ ส่งไปยัง SMO (SQL Server Management Object) ได้
มักใช้ดักรับในช่วงสั้น ๆ เพราะต้องมีผู้ใช้คอยสั่งงาน	ติดตามระยะยาวได้ เพราะ การเปิด ปิด บันทึก สามารถทำเป็นงานอัตโนมัติที่ตอบสนองการเกิดวิกฤตด้านทรัพยากร
Traces มักมีขนาดเล็ก (ขึ้นอยู่กับระยะเวลาการดักรับ)	Traces มักมีขนาดใหญ่ (ขึ้นอยู่กับระยะเวลาการดักรับ)

เริ่มต้นใช้งาน SQL Profiler

เราสามารถเรียกใช้ SQL Profiler ได้หลายทางดังนี้

เรียกจาก Microsoft Windows Server (ผ่าน Metro UI)
เรียกผ่านเมนู Tools บน SSMS
เรียกจาก Processes Pane ใน Activity Monitor

ข้อดี จากการเรียกโดยวิธีนี้ คือ เราสามารถวิเคราะห์ ว่า session ไหน มีการใช้ทรัพยากรมาก
มีการ Lock อยู่ในสถานะ Waiting นาน โดยส่ง session เหล่านี้ไปยัง Profiler แล้ว Profiler จะมีการ filter เพื่อดักรับเฉพาะ session id ที่ส่งไป

โดยการคลิกขวาที่ Query Editor ในขณะที่ทำการ query อยู่

เราสามารถใช้วิเคราะห์เช่นเดียวกับการส่งจาก Activity Monitor คือจะมีการ filter เฉพาะ session id ที่ส่งไปเพื่อวิเคราะห์

เมื่อทำการเปิด profiler ขึ้นมาหากเป็นการเปิดปกติ เราจะต้องเชื่อมต่อไปยัง Instance ที่ต้องการดักรับ

จากรูป เป็นการเชื่อมต่อไปยัง Instance ที่อยู่ภายในเครื่องเดียวกันกับ Profiler และเป็น Default Instance เราสามารถใช้ได้ทั้ง Windows Authentication และ SQL Server Authentication

เมื่อเชื่อมต่อแล้วก็จะเข้าสู่หน้า Trace Properties ให้เราทันที แต่หากไม่ปรากฏหน้า Trace Properties ก็สามารถเรียกได้จากเมนู Files > New Trace

รูปภาพ แสดง Trace Properties

จากรูป เราสามารถกำหนดชื่อของ trace ลงไป ประเภทการบันทึกว่าจะลงไฟล์ หรือ Table ในตัวอย่างนี้ จะเขียนลงไฟล์ชื่อ FirstTrace.trc

นอกเหนือจากนั้น เราสามารถตั้งค่าขนาดของไฟล์ จากรูป จะกำหนดขนาดไว้ 15 MB และ หากเราเลือก Enable file rollover ร่วมด้วย

เมื่อบันทึกเต็มความจุที่กำหนดไว้ก็จะกลับมาเขียนทับลงไฟล์เดิม เราสามารถกำหนดให้การบันทึก trace หยุดลงตามวันและเวลาได้อีกด้วย

จากรูปเป็นการสั่งให้หยุดการบันทึก Trace ในวันที่ 30 มิถุนายน 2559 เวลา 4 ทุ่ม

รูปภาพ แสดง Trace Templates ที่มีให้เลือก

นอกเหนือจากนั้น เราสามารถสร้าง trace จาก template ที่มีให้เลือกใช้ โดย template จะเลือก Event และ Column เอาไว้ให้แล้ว

ผู้เขียนแนะนำว่าเวลาเราสร้าง trace ให้เริ่มต้นจากการเลือก template แล้วจึงเข้าไปปรับแก้

โดยพิจารณาเลือก Event และ Column ที่ต้องการเพิ่มเติม (หากเลือกเป็น blank หมายถึงต้องเลือก Event และ Column เองทั้งหมด)

Trace Properties ชื่อ Standard (Default)

รูปภาพ แสดง Template ชื่อ Standard (Default)

จาก Template ชื่อ Standard (Default) จะเห็นว่ามีการเลือก Event เอาไว้ให้ (ในกรอบสีแดง) และ มีการเลือก Column ที่สอดคล้องกับ Event เอาไว้ให้ด้วย (ในกรอบสีเขียว)

หากไม่ต้องการแสดง Column ใด เราสามารถเลือก Deselect ที่ Check Box ของ Column ในแต่ละ Event ได้ ดังนี้

รูปภาพ แสดงการ Deselect ไม่แสดง Columns ในบาง Events

แต่ถ้าหากต้องการไม่ให้แสดง Column นั้น ในทุก ๆ Event ก็สามารถทำได้อย่างรวดเร็ว โดยการคลิกขวาเข้าไปที่ชื่อ Column จากนั้นเลือก Deselect Column ดังตัวอย่าง

รูปภาพ แสดงการ Deselect ไม่แสดง Column “ApplicationName” ในทุก Events

รูปภาพ แสดงผลลัพธ์หลังจากการ Deselect Column “ApplicationName”

ดูแล้วน่าจะเป็นวิธีที่สะดวกสบายกว่า นอกเหนือจากการเลือก Events และ Columns แล้ว เราสามารถ Filter เอาเฉพาะข้อมูลที่ตรงกับ Column ที่เรากรองเอาไว้ โดยการกดที่ปุ่ม Column Filters

รูปภาพ แสดงเนื้อหาภายใน Trace

จะเห็นว่าเนื้อหาภายใน Trace ให้ข้อมูลออกมาตามที่เราเลือก Events และ Column เอาไว้ก่อนหน้า ผู้เขียนจะแสดงความสามารถอีกอย่างที่น่าสนใจ คือ

การนำเอา Performance Data จาก Performance Monitor ของ Microsoft Windows มาใช้ร่วมกับข้อมูล Trace ได้

รูปภาพแสดง Performance Monitor

ส่วนที่ต้องการใช้งานบน Performance Monitor คือ Data Collector Sets ตามที่ผู้เขียนวงไว้ในรูป

โดยผู้อ่านสามารถศึกษาการใช้งานได้จากเอกสาร Creating Data Collector Sets ผู้เขียนได้เลือก Counters เอาเฉพาะที่เกี่ยวข้องกับ CPU, Memory, I/O และ SQL Buffer Manager ดังแสดง

รูปภาพ แสดงการเลือก Counters

ตั้งค่า Data Collector Sets เสร็จให้ทำการ Start ใช้งานพร้อมไปกับ การบันทึกข้อมูล Trace

เพราะต้องดักรับในช่วงเวลาเดียวกัน (สิ่งที่นำเอาข้อมูลจาก 2 แหล่งมา Correlation กันได้คือ เวลา)

เมื่อได้ข้อมูลทั้ง Data Collector Sets และข้อมูล Trace เรียบร้อยแล้ว

เราสามารถนำมาเปิดดูพร้อมกันผ่าน SQL Profiler

โดยเลือกไปที่เมนู File > Import Performance Data จะได้ผลลัพธ์ดังแสดง

รูปภาพแสดงข้อมูล Performance Counters ร่วมกับข้อมูล Trace

จะเห็นว่าเราสามารถแสดง Counters ต่าง ๆ ในรูปแบบของกราฟเส้นอยู่บน Timeline

และใช้หาจุดที่มีการบริโภคทรัพยากรมาก

เมื่อคลิกลงไปบน Timeline ก็จะแสดงบรรทัดของ Event ที่เวลาตรงกับใน Timeline มาให้วิเคราะห์โดยง่าย

นอกจากการสร้าง Trace ผ่าน SQL Profiler แล้ว เรายังสามารถสร้างผ่านกลุ่ม System Stored Procedures

ดังที่ผู้เขียนได้กล่าวไว้ในตอนต้นบทความ โดยลำดับขั้นตอนของการเรียกใช้ Procedures เป็นดังนี้

รูปภาพ แสดงลำดับการสร้าง Traces ด้วย sp_trace

เป็นเรื่องน่ายินดีอย่างยิ่งเพราะลำดับที่ 1-4 ไม่ต้องเขียนเอง

โดยเราจะทำการสร้าง Trace ผ่าน SQL Profiler ไปตามปกติ

ผู้เขียนจะเลือก Events , Columns และการ Filters จำนวนไม่มากดังแสดง

รูปภาพ แสดงการเลือก Events, Columns และ Filters

เมื่อเลือก Events , Columns และ การ Filters เสร็จสิ้น ให้กด Run เพื่อ Start Trace

หลังจากนั้นทำการ Stop Trace ลงทันทีเลย

แล้วเลือกไปที่เมนู File > Export > Script Trace Definition > For SQL Server 2005-2016 ดังแสดง

รูปภาพแสดง การ Export ไปเป็น Script Trace

จะได้ Script ตังต่อไปนี้

มาลองดูในเฉพาะส่วนของ Events , Columns และการ Filters ที่เลือกไว้

จะพบว่าในส่วนของ sp_trace_setevent นั้นรับค่า Event เป็น Event Number และ รับค่า Column เป็น Column Number เช่นกัน

หากเราต้องเขียนเอง คงต้องพึ่ง Help เปิดกลับไปกลับมาหลายรอบกว่าจะเสร็จ

ในส่วนของ Filter ด้านล่างก็เป็นแบบเดียวกัน คือ รับค่า เป็น Column Number

อย่างที่ผู้เขียนบอกไว้ว่าลำดับที่ 1-4 ของการสร้าง Trace ผ่าน Stored Procedures เราไม่ต้องทำอะไรเพราะสามารถสร้าง Script จาก SQL Profiler ได้

แต่ Script นี้จะยังทำงานไม่ได้เพราะเราต้องระบุที่ตั้งของไฟล์ Trace ลงไปก่อนดังแสดง

ในส่วนบนของ Script จะมีให้แทนที่ InsertFileNameHere ด้วยตำแหน่งที่ตั้งไฟล์ และ ชื่อไฟล์ โดยไม่ต้องระบุนามสกุล เมื่อทำในส่วนนี้เสร็จแล้ว Script ก็พร้อมใช้งาน

ผู้เขียนทำการ Execute และได้ผลลัพธ์ดังนี้

เมื่อ Script ถูก Execute ท้ายสุดของ Script มีการแสดงค่าของตัวแปร @TraceID

ซึ่งรับค่ามาตอนที่เรียกใช้ sp_trace_create พูดง่ายๆ ก็ คือ เป็นการแสดงหมายเลข Trace ที่ยังรันอยู่ในตอนนี้

ถ้าไม่มีใครไปสั่งหยุด หรือ Database Engine ไม่หยุดทำงาน Trace นี้ก็จะรันไปเรื่อย ๆ

วิธีการหยุด ก็คือ เราจะทำขั้นตอนที่ยังเหลืออยู่ คือ ลำดับที่ 5-6 ซึ่งเป็นการเรียกใช้ sp_trace_setstatus ด้วยกันทั้งสองขั้นตอน

โดย Syntax ของ sp_trace_setstatus มีดังนี้

trace_id คือ หมายเลข Trace ที่ต้องการดำเนินการ
status คือสถานะที่ต้องการดำเนินการ
- 0 คือ หยุดการบันทึก Trace
- 1 คือ เริ่มต้นการบันทึก Trace
- 2 คือ สิ้นสุดการสั่งงาน ปิดไฟล์ Trace

ในที่นี้เรารู้ว่า trace_id มีค่าเป็น 2 จากการรัน Script ก่อนหน้า

โดยในลำดับที่ 5 จะเป็นการส่ง status มีค่าเป็น 0 เข้าไป

ส่วนในลำดับที่ 6 ก็คือการส่ง status มีค่าเป็น 2 เข้าไปนั่นเอง

ผู้เขียนจะส่งคำสั่งทั้งสองลำดับในคราวเดียว ดังนี้

จากนั้นไปตรวจสอบดูไฟล์ Trace ในตำแหน่งที่ระบุไว้ใน Script แล้วทดลองเปิดไฟล์ด้วย SQL Profiler

หากไม่มีอะไรผิดพลาดก็น่าจะเปิดไฟล์ขึ้นมาอ่านได้ตามปกติ จะเห็นว่าวิธีนี้เราจะได้ Script ออกมา และ สามารถนำ Script นี้ไปดัดแปลงใช้ได้ในหลายกรณี เช่น สร้างเป็นงานอัตโนมัติ

และ ถูกสั่งงานเมื่อประสิทธิภาพของระบบมีปัญหา ก็จะได้ไฟล์ Trace ในขณะเกิดปัญหาเก็บเอาไว้ให้วิเคราะห์ภายหลัง เปรียบเหมือนกล่องดำเครื่องบินเลยทีเดียว

บทความโดย

อาจารย์ภัคพงศ์ กฤตวัฒน์

วิทยากรดูแลและออกแบบหลักสูตร

กลุ่มวิชา SQL Server/Window Server