รู้ได้อย่างไรว่า Microsoft SQL Server มีประสิทธิภาพแย่ลง

หลายครั้งที่ผู้เขียนได้รับคำถามจากผู้ใช้ Microsoft SQL Server เกี่ยวกับปัญหาด้านประสิทธิภาพ โดยเอาค่าจาก Performance Counter บ้าง จาก Waiting Statistics บ้าง มาบอกว่าค่าเป็นเท่านั้นเท่านี้ แล้วถามว่า Microsoft SQL Server ที่เขาใช้งานอยู่มันมีประสิทธิภาพดีอยู่ไหม
รู้ได้อย่างไรว่า Microsoft SQL Server มีประสิทธิภาพแย่ลง
ทักษะ (ระบุได้หลายทักษะ)
Data

รู้ได้อย่างไรว่า Microsoft SQL Server มีประสิทธิภาพแย่ลง

หลายครั้งที่ผู้เขียนได้รับคำถามจากผู้ใช้ Microsoft SQL Server เกี่ยวกับปัญหาด้านประสิทธิภาพ โดยเอาค่าจาก Performance Counter บ้าง จาก Waiting Statistics บ้าง มาบอกว่าค่าเป็นเท่านั้นเท่านี้ แล้วถามว่า Microsoft SQL Server ที่เขาใช้งานอยู่มันมีประสิทธิภาพดีอยู่ไหม ?
 
ครั้นจะตอบว่าไม่ทราบเพราะข้อมูลไม่เพียงพอ ก็จะกล่าวโทษว่าผู้เขียนไม่มีความรู้เพียงพอจะตอบนะสิจึงไม่ตอบ
ผู้เขียนเลยจำเป็นต้องตอบโดยใช้เกณฑ์ที่เรียกว่า “Rule of Thumb” หรือค่าคร่าว ๆ ที่บอกต่อกันมา เป็นคำตอบให้
แล้วเกณฑ์เหล่านั้นไม่ดี ไม่ถูกต้องหรือ ขอตอบแบบนี้ครับ เกณฑ์จะมีความน่าเชื่อถือหรือไม่นั้นขึ้นอยู่กับแหล่งที่มา
ผู้เขียนมักอ้างอิงเกณฑ์จากแหล่งเหล่านี้
  • คำแนะนำจาก Microsoft
  • คำแนะนำจากผู้ผลิตเครื่องมือที่ใช้ติดตามประสิทธิภาพ Microsoft SQL Server โดยเฉพาะ เช่น
    • SentryOne
    • Redgate
  • คำแนะนำจาก Guru ที่น่าเชื่อถือ เช่น
    • SQLskills โดย Paul S. Randal อดีตทีมพัฒนา Microsoft SQL Server ตั้งแต่ยุคแรกๆ
    • SQL Server Diagnostic Information Queries โดย Glenn Berry อดีตทีมงานของ SQLskills สามารถติดตาม Blogs เก่าได้บน SQLskills และ Blogs ใหม่ได้บน glennsqlperformance
แต่แนวทางที่ผู้เขียนจะแนะนำในบทความนี้ คือ คุณต้องสร้างเกณฑ์สำหรับการติดตามประสิทธิภาพ Microsoft SQL Server ของคุณเองขึ้นมา
มักเรียกว่า Performance Baseline หรือ Performance Matric
ฟังไม่ผิดครับว่าเกณฑ์ของตนเอง แล้วมันจะน่าเชื่อถือกว่าของบรรดา Guru ได้อย่างไร ติดตามอ่านกันต่อได้เลย

Performance Baseline

การสร้างเกณฑ์วัดขึ้นมาใช้เองเป็นเรื่องที่ทำกันมาเป็นปกติอยู่แล้วในงาน Monitoring และเป็นคำแนะนำจาก Microsoft เองอีกด้วย
โดยมีเอกสารสั้นมาก เอกสารหนึ่งเป็นเครื่องยืนยัน https://docs.microsoft.com/en-us/sql/relational-databases/performance/establish-a-performance-baseline
 
แม้ว่าเอกสารดังกล่าวเป็นเอกสารสั้นๆ แต่ในหลักสูตรฝึกอบรมของ Microsoft มีการบรรยายพร้อมแบบฝึกหัดให้ทำกันเป็นเรื่องเป็นราวเลยทีเดียว
ในหลักสูตรฝึกอบรมของสถาบัน 9Expert กำลังจะมีการสอนเกี่ยวกับ Performance Tuning ก็ให้ความสำคัญเกี่ยวกับการสร้าง Performance Baseline ไว้ใช้เช่นกัน
แต่กำลังตัดสินใจอยู่ว่าจะนำเสนอผ่านเครื่องมือราคาแพงอย่าง SQLSentry จาก SentryOne หรือใช้ Pssdiag/Sqldiag Manager ที่เป็น Project Opensource ของ Microsoft ดี หรือใช้ทั้งสองอย่างดี


 

Performance Baseline บน SQL Sentry
จากรูปเป็นการสร้าง Performance Baseline บน SQL Sentry เพียงแค่คลิกขวาไปบนช่วงเวลาที่ต้องการเก็บค่ามาสร้างเป็น Baseline ใช้งานง่ายมาก ๆ
 
Performance Baseline คือบรรดาค่าเกณฑ์วัดประสิทธิภาพพื้นฐานที่เก็บมาตอน Microsoft SQL Server ทำงานในสภาวะปกติ
แต่ก็ไม่ใช่สภาวะไม่มีภาระงานอะไรเลยนะ เป็นสภาวะที่มีภาระงานปกตินี่แหละ และเป็นสภาวะที่ทุกฝ่านยอมรับได้มาใช้เป็นเกณฑ์ ประโยชน์ที่ได้คือ
  1. สามารถเห็นแนวโน้มด้านประสิทธิภาพที่เปลี่ยนแปลงไปได้
  2. สามารถนำสภาวะปัจจุบันไปเทียบกับ Baseline เพื่อค้นพบการเปลี่ยนแปลง แล้วแก้ปัญหาได้ง่ายขึ้น
  3. ช่วยประเมินการบริโภคทรัพยากร และวางแผนทรัพยากรล่วงหน้าได้
บางครั้งเราอาจต้องสร้าง Baseline มากกว่าหนึ่งตัว ตามสภาวะใช้งานหลายรูปแบบที่อาจเกิดกับ Microsoft SQL Server เช่น Peak hours, Off-peak hours, Weekly reports หรือ Monthly reports เป็นต้น
การติดตามประสิทธิภาพว่าดีหรือแย่ เราจะทำการดึงค่าประสิทธิภาพปัจจุบันไปเทียกกับค่าใน Baseline เรียกการกระทำนี้ว่า Benchmarking 
เช่น ครั้งนี้  Stored Procedure  ชื่อ X ของเราใช้เวลาประมวลผลทั้งสิ้น 5 วินาที แต่ที่บันทึกไว้ใน Baseline บอกว่าใช้เวลาประมวลผลเพียง 2 วินาที แบบนี้ก็ตอบได้ว่า Stored Procedure  ชื่อ X มีประสิทธิภาพแย่ลง
ค่าประสิทธิภาพที่เราสนใจนำมาสร้างเป็น Baseline ประกอบด้วย
  • Configuration ทั้งระดับ Instance และระดับ Database เช่นค่า Max Server Memory, Cost Threshold for Parallelism หรือ Max Degree of Parallelism เป็นต้น ค่าเหล่านี้สามารถถูกแก้ไขได้ แต่การแก้ไขโดยขาดความเข้าใจอย่างลึกซึ้ง นำมาซึ่งผลกระทบด้านประสิทธิภาพอย่างยิ่ง
  • การบริโภคทรัพยากรทั้ง CPU, Memory และ I/O ผ่าน Performance Counters เพื่อนำมาประเมินแนวโน้ม และวางแผนเพื่อจัดหา หรือจัดการกับทรัพยากรล่วงหน้าได้
  • ขนาดของฐานข้อมูล หากแหล่งจัดเก็บข้อมูลถูกใช้งานเต็มความจุเมื่อไหร่ ฐานข้อมูลก็จะหยุดทำงาน เพื่อให้แน่ใจว่ารับมือทันท่วงทีต้องติดตามข้อมูลนี้อย่างสม่ำเสมอ
  • Wait Statistics เป็นจุดแรกที่ต้องเข้าไปดูหากพบปัญหาด้านประสิทธิภาพ เพราะเราสามารถระบุถึงสาเหตุที่แท้จริงได้จากจุดนี้ การนำ Wait Statistics ปัจจุบันไปเทียบกับ Baseline จะทำให้เห็นปัญหาแล้วแก้ไขได้

เราสามารถเก็บข้อมูลจากแหล่งข้อมูลต่อไปนี้
 

  • สืบค้นจาก Dynamic Management Objects อาจอยู่ในรูปของ Dynamic Management Views หรือ Dynamic Management Table-Valued Functions
  • สืบค้นจาก System Catalog Views
  • ข้อมูลดักรับจาก Extended Events
  • ข้อมูลดักรับ SQL Trace (สร้างจาก SQL Profiler หรือ sp_trace_create ปัจจุบันแนะนำให้เปลี่ยนไปใช้ Extended Events แทน เพราะ SQL Trace หยุดพัฒนาไปตั้งแต่เวอร์ชั่น 2008)
  • Performance Counters บน Performance Monitor ของ Microsoft Windows (หากเป็น Counters ของฝั่ง Microsoft SQL Server สามารถสืบค้นได้จาก Dynamic Management View ชื่อ sys.dm_os_performance_counters)
ส่วนใหญ่เครื่องไม้เครื่องมือในการติดตามประสิทธิภาพของ SQL Server ก็อาศัยข้อมูลจากแหล่งเหล่านี้ทั้งสิ้น เช่น บรรดา  Standard Reports ใน SQL Server Management Studio (SSMS) ดังแสดง
 
Standard Reports ใน SQL Server Management Studio
ความแปลกของค่าประสิทธิภาพคือแทบทั้งหมดจัดเก็บแบบ Cumulative หรือเป็นค่าบวกสะสมไม่เว้นแม้กระทั้ง Performance Counters บน Microsoft Windows
แล้วทำไมเวลาเราดูกราฟจาก Performance Monitor บน Windows กราฟถึงไม่พุ่งขึ้นอย่างเดียวหล่ะ แต่มีทั้งขึ้นและลงด้วย ลองดูตัวอย่างดังคิวรี่ต่อไปนี้
 
SELECT pages_kb FROM sys.dm_os_memory_clerks WHERE type='CACHESTORE_SQLCP'
 
WAITFOR DELAY '00:01'
 
SELECT pages_kb FROM sys.dm_os_memory_clerks WHERE type='CACHESTORE_SQLCP'
 
WAITFOR DELAY '00:01'
 
SELECT pages_kb FROM sys.dm_os_memory_clerks WHERE type='CACHESTORE_SQLCP'
 
 
จะเห็นว่าเป็นการสืบค้นข้อมูลจาก Dynamic Management View ชื่อ sys.dm_os_memory_clerks ทั้งหมด 3 ครั้งโดยแต่ละครั้งห่างกัน 1 นาที ได้ผลลัพธ์ดังแสดง
การสืบค้นข้อมูลจาก Dynamic Management View

พบว่าครั้งที่ 1 มีค่า 9,032 Kbytes ครั้งที่ 2 มีค่า 13,920 Kbytes และครั้งสุดท้ายคือ 19,808 Kbytes จะเห็นว่าค่าเป็นบวกสะสมไปเรื่อย ๆ
หากเราคิวรี่ตรง ๆ เราต้องตั้งกลไกให้ทำการดึงข้อมูลในมีความถี่คงที่ ตัวอย่างนี้คือ ทุก 1 นาที หรือ 60 วินาที ดังนั้นหากเอาไปวาดเป็นกราฟจะได้จุดสองจุดคือ

 

จุดแรก =( ค่าครั้งที่ 2 -ค่าครั้งที่ 1)/60=( 13920 -9032)/60=81.46 KBytes ต่อวินาที
และ
จุดสอง =( ค่าครั้งที่ 3 -ค่าครั้งที่ 2)/60=( 19808-13920 )/60=98.13 KBytes ต่อวินาที
 
หากนำทั้งสองจุดไปวาดกราฟ กราฟที่ได้ก็จะพุ่งขึ้นนิดหน่อย
ผู้เขียนต้องการสื่อว่าหากต้องการคิวรี่เอง จะไม่สามารถดูผลตรง ๆ จากคิวรี่ได้ และการคิวรี่ต้องทำกำหนดความถี่ในการ Sampling ข้อมูลให้เท่า ๆ กัน
ค่าที่เห็นในกราฟเป็นค่าเฉลี่ย หากต้องการความแม่นยำสูงต้อง Sampling ให้ถี่ แต่แลกมาด้วยพื้นที่จัดเก็บที่มากขึ้น
และอาจย้อนไปส่งผลกระทบต่อประสิทธิภาพการทำงานของ Microsoft SQL Server
แต่หาก Sampling ห่าง ค่าเฉลี่ยที่ได้อาจมีความแม่นยำน้อยลง แต่ข้อดีคือไม่เปลืองพื้นที่จัดเก็บและส่งผลกระทบต่อประสิทธิภาพการทำงานน้อย
 
แนะนำเครื่องมือฟรี Pssdiag/Sqldiag Manager 
แนะนำเครื่องมือฟรี Pssdiag/Sqldiag Manager
เป็นเครื่องมือ Opensource ที่เคยอยู่ในโปรเจค CodePlex ของ Microsoft (ปัจจุบันย้ายไปอยู่บน GitHub แล้ว สามารถเข้าไปดูและดาวน์โหลดได้ตามลิงก์ https://github.com/Microsoft/DiagManager )
โดยเครื่องมือนี้จะมี GUI เพื่อมารับความต้องการจากเราว่าจะดึงค่าประสิทธิภาพอะไรออกมาแสดงบ้าง สามารถทำงานได้กับ Microsoft SQL Server ตั้งแต่เวอร์ชั่น 2008 จนถึงเวอร์ชั่น 2019 ซึ่งเป็นเวอร์ชั่นล่าสุดเลยทีเดียว
จะเห็นว่าสามารถดึงค่าประสิทธิภาพมาจาก Extended Event, SQL Trace, Performance Counters บน Windows และจากบรรดา Dynamic Management Objects ก็ถือว่าครบถ้วนอยู่
เมื่อกด Save จะเป็นการถามหา Folder เพื่อจัดเก็บ Script ที่จะใช้สำหรับดึงข้อมูลจะเป็นไฟล์บีบอัดชื่อ pssd.zip เมื่อแตกไฟล์ออกมาจะได้ไฟล์ต่าง ๆ ดังนี้
 
Dynamic Management Objects
จะพบว่ามีไฟล์สคริปต์หลายรูปแบบกันเลยตั้งแต่
  • T-SQL Script ไฟล์นามสกุล .sql
  • Command Shell Script ทั้งไฟล์นามสกุล .bat และ .cmd
  • VB Script ไฟล์นามสกุล .vbs
  • และ Power Shell Script  ไฟล์นามสกุล .ps1
เราต้องเรียกใช้ไฟล์สคริปต์ชื่อ pssdiag.cmd จากนั้นสคริปต์นี้จะไปเรียกไฟล์สคริปต์อื่น ๆ ขึ้นมาทำงาน
แต่เราไม่สามารถดับเบิลคลิกไปที่ไฟล์ตรง ๆ ได้ จำเป็นต้องเรียก Command Prompt แบบ Run as administrator ก่อน
จากนั้นจึงเรียกใช้ไฟล์สคริปต์ชื่อ pssdiag.cmd ดังแสดง
ไฟล์สคริปต์ชื่อ pssdiag.cmd

เมื่อสคริปต์รันมาถึงการแจ้งเตือนสีเขียว อย่าเพิ่งกด Ctrl+C เพราะเป็นการดักรับผ่าน SQL Trace และ Extended Event
ให้ดักรับในสภาวะที่มีภาระงานและระบบเป็นปกติ หากต้องการนำไปสร้างเป็น Baseline และดักรับในสภาวะอื่น ๆ ที่ต้องการทำ Benchmarking จนคิดว่าสมควรแก่เวลาค่อยกด Ctrl+C สคริปต์จะแจ้งว่าหยุดดักรับดังแสดง

Benchmarking

จากนั้นผลลัพธ์จะถูกบรรจุลงในโฟลเดอร์ Output ภายใต้โฟลเดอร์ที่ทำการรันสคริปต์ โดยมีบรรดาไฟล์ผลลัพธ์ดังแสดง

จากนั้นผลลัพธ์จะถูกบรรจุลงในโฟลเดอร์ Output ภายใต้โฟลเดอร์ที่ทำการรันสคริปต์
ประกอบด้วย
  • ไฟล์นามสกุล .OUT และ .TXT
    • เก็บผลลัพธ์ของ PssDiag
    • เก็บรายละเอียดการเรียกใช้ SQL Trace
    • เก็บรายละเอียดการเรียกใช้ Extended Event
    • เก็บผลลัพธ์ของ Stored  Procedure
    • เก็บข้อมูลที่ Filter มาจาก Error Log ตอนเกิด Shutdown
    • เก็บรายละเอียดการเรียกใช้ Powr Plan
  • ไฟล์นามสกุล .xel เก็บผลลัพธ์ของการดักรับด้วย Extended Event
  • ไฟล์นามสกุล .trc เก็บผลลัพธ์ของการดักรับด้วย SQL Trace
  • ไฟล์นามสกุล .sqlplan เก็บรายละเอียด Compile Plan ของ Expensive Query

หากเราต้องอ่านไฟล์เหล่านี้เองคงไม่สะดวกเท่าไหร่ ยังมีอีกโปรเจค Codeplex ชื่อ SQLNexus ปัจจุบันถูกย้ายมาอยู่ที่ GitHub เช่นกัน
สามารถเข้าไปดูและดาวน์โหลดได้ตามลิงก์ https://github.com/microsoft/SqlNexus เราจะใช้ SQL Nexus ทำหน้าที่อ่านผลลัพธ์จาก Pssdiag/Sqldiag
 
เมื่อเรียกใช้งานจะต้องทำการเชื่อมต่อไปยัง Instance ด้วย Login ที่ถือครองบทบาท sysadmin เพื่อสร้างฐานข้อมูล sqlnexus ใช้สำหรับเก็บค่าที่ได้มาจากผลลัพธ์ของ Pssdiag/Sqldiag แยกลงแต่ตาราง
 
เริ่มจาก Import ข้อมูลผลลัพธ์ของ Pssdiag/Sqldiag โดยระบุไปที่โฟล์เดอร์ Output ก่อนหน้า กดปุ่ม Import 
 
 


 

SQL Nexus

หลังจากฐานข้อมูลถูกสร้าง SQL Nexus จะเริ่มทำการ Import ข้อมูลดังแสดง

หลังจากฐานข้อมูลถูกสร้าง SQL Nexus จะเริ่มทำการ Import ข้อมูล

ก็จะเห็น Report  น่าตาแบบนี้ให้ใช้งาน

SQL SERVER Bottleneck Analysis
Blocking and Resource Wait Statistics
SQL SERVER Memory Brokers

และยังมีข้อมูลดิบจัดเก็บลงตารางต่าง ๆ ในฐานข้อมูล sqlnexus ดังแสดง

ข้อมูลในตาราง tbl_AnalysisSummary
จากรูปเป็นข้อมูลในตาราง tbl_AnalysisSummary
จะเห็นว่ามีการสรุปผลการวิเคราะห์ออกมาให้ โดยแบ่งเป็นระดับความแรงบอกไว้ด้วยคอลัมน์ TypeDesc และคำอธิบายในคอลัมน์ Description ไปจนถึงลิงก์ที่ให้รายละเอียดเพิ่มเติมใน InternalUrl เป็น Path
ภายใน HTML Report ที่ได้มา หรือคอลัมน์ ExternalUrl เป็นลิงก์ไปยังไซต์ในอินเตอร์เน็ต ซึ่งแน่นอนว่าคำแนะนำแบบนี้เข้าข่ายเป็น  “Rule of Thumb” แต่ก็ไม่เสียหลายหากเราจะตั้งต้น Baseline จาก “Rule of Thumb” ที่น่าเชื่อถือ


สรุป

การสร้าง Baseline เพื่อเป็นเกณฑ์ชี้วัดว่าสถานะปัจจุบันของเรานั้นดีหรือแย่จากเกณฑ์ที่ตั้งไว้แค่ไหน เป็นสิ่งที่ควรกระทำ
Baseline ควรถูกสร้างตอนมีภาระงานอยู่แต่ทำงานได้อย่างปกติ และอาจมีหลาย Baseline ได้ตามความแตกต่างของภาระงาน
และ Baseline ไม่ควรถูกสร้างครั้งเดียวแล้วใช้เป็นเกณฑ์ไปตลอด ควรมีการปรับให้ทันสมัยอาจทุกๆ 3 เดือนเป็นอย่างน้อย
 
มีผู้ผลิตเครื่องไม้เครื่องมือทั้งสร้าง Baseline ได้ง่าย และการทำ Benchmarking อาจอยู่ในรูปแบบ Continuous Benchmarking โดยสามารถแจ้งเตือนไปยังผู้ดูแลได้ทันทีตามเกณฑ์ที่ตั้งไว้
แต่หากไม่มีเครื่องไม้เครื่องมือราคาแพง การสร้างสคริปต์ขึ้นมาใช้เองก็สามารถทำได้ แต่ต้องรู้ว่าจะไปหยิบจับค่ามาจากไหน และค่าอะไรบ้าง
 

ผู้เขียนมีตัวอย่างสคริปต์แจกให้ในหลักสูตร SQL Server Performance Tuning ฝึกอบรม และอธิบายโดยละเอียดถึงค่าแต่ะค่าที่ใช้เป็นเกณฑ์วัดประสิทธิภาพโดยละเอียด 
และไม่เพียงวิเคราะห์จากค่าประสิทธิภาพเท่านั้น ยังแสดงตัวอย่างการวิเคราะห์ Expensive Query ที่พบมากให้ผู้เข้าอบรมเห็นอีกด้วย
ส่วนบทความทำนองนี้ผู้เขียนอาจเอาบรรดา “Rule of Thumb” ที่น่าเชื่อถือมาเล่าให้ฟังกัน โปรดติดตามบทความกันต่อไปนะครับ

 

บทความโดย
อาจารย์ภัคพงศ์ กฤตวัฒน์
  • วิทยากรผู้ดูแลและออกแบบหลักสูตร
    • กลุ่มวิชา SQL Server/Window Server
  • Microsoft SQL Server Specialist
  • Microsoft Certified Trainer (2002-Present)
  • Co-Founder at Data Meccanica Co., Ltd.
Tags