การใช้งาน SQL Profiler

การใช้งาน 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

เราสามารถเรียกใช้ 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 Templates ที่มีให้เลือก

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

ผู้เขียนแนะนำว่าเวลาเราสร้าง trace ให้เริ่มต้นจากการเลือก template แล้วจึงเข้าไปปรับแก้ โดยพิจารณาเลือก Event และ Column ที่ต้องการเพิ่มเติม (หากเลือกเป็น blank หมายถึงต้องเลือก Event และ Column เองทั้งหมด)

รูปภาพ แสดง 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

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

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

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

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

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

ส่วนที่ต้องการใช้งานบน Performance Monitor คือ 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 จะได้ผลลัพธ์ดังแสดง

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

การสร้าง Trace ด้วย sp_trace Stored Procedures

นอกจากการสร้าง Trace ผ่าน SQL Profiler แล้ว เรายังสามารถสร้างผ่านกลุ่ม System Stored Procedures ดังที่ผู้เขียนได้กล่าวไว้ในตอนต้นบทความ โดยลำดับขั้นตอนของการเรียกใช้ Procedures เป็นดังนี้

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

เป็นเรื่องน่ายินดีอย่างยิ่งเพราะลำดับที่ 1-4 ไม่ต้องเขียนเอง โดยเราจะทำการสร้าง Trace ผ่าน SQL Profiler ไปตามปกติ แล้วเลือกไปที่เมนู File > Export > Script Trace Definition > For SQL Server 2005-2016

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

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

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

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

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

การหยุด Trace ด้วย sp_trace_setstatus

วิธีการหยุด ก็คือ เราจะทำขั้นตอนที่ยังเหลืออยู่ คือ ลำดับที่ 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 เข้าไปนั่นเอง