พัฒนาการของ Query Store

พัฒนาการของ Query Store

ตั้งแต่ได้เขียนบทความ “รู้จัก Query Store บน SQL Server 2016” เอาไว้ก่อนหน้านี้ แล้วได้เว้นช่วงไปพอสมควร ปัจจุบัน Microsoft SQL Server ใกล้จะออกเวอร์ชั่น 2019 ในเร็ววันนี้ มาดูกันว่า Query Store เปลี่ยนไปจากเดิมมากแค่ไหนกัน

Query Store บน Microsoft SQL Server 2016

เมื่อคุณเปิดคุณสมบัติ Query Store เพื่อให้สามารถเก็บบันทึก Execution Plan เดิมที่เคย Cached เอาไว้ แต่ถูกขับไล่ออกจากหน่วยความจำ (Execution Plan Eviction) ไปแล้ว เผื่อว่า Execution Plan ตัวใหม่ที่มาแทนที่จะมีภาวะถดถอยกว่าตัวเก่าที่ถูกขับไล่ออกไป เรายังสามารถนำ Plan เก่าที่อาจจะดีกว่ากลับมา Force ใช้งานได้อีกครั้ง

ถึงแม้จะเป็นแนวคิดที่ดี แต่จะมีผู้ดูแลระบบซักกี่คนที่จะค่อยมาวิเคราะห์การถดถอยของ Plan Cached แล้วมานั่ง Force Plan กัน

Query Store บน Microsoft SQL Server 2017

เมื่อ Microsoft SQL Server 2017 ปล่อยโฆษณาออกมา บอกว่ามี Auto Tuning ผู้เขียนถึงกับตกใจ นึกว่าเราจะไม่มีงานทำกันแล้ว อันที่จริง Auto Tuning สำหรับ Microsoft SQL Server 2017 บน On-Premise นั้นเป็นการต่อยอด Query Store โดยสามารถทำ Automatic plan choice correction ได้
(จากบทความ “เล่าถึง Microsoft SQL Server 2017 Datasheet พอสังเขป ตอนที่ 1”)

Microsoft SQL Server จะวิเคราะห์ภาวะถดถอยด้วยกลไกทางสถิติของตนเอง และเลือกที่จะ Force Plan เองหากจำเป็น ไม่ต้องพึ่งผู้ดูแลระบบเป็นคน Force Plan อีกต่อไป

Automatic plan choice correction ทำได้โดย

เปิด Query Store บนฐานข้อมูลเป้าหมาย
เปิดคุณสมบัติ Automatic plan choice correction บนฐานข้อมูลเป้าหมาย ดังนี้

ALTER DATABASE current

SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON );

Query Store บน Microsoft SQL Server 2019

ดูเหมือนว่า Microsoft ไม่ได้มีอะไรเพิ่มเติมมากนักสำหรับเวอร์ชั่น 2019 เพียงแค่ปรับปรุงค่า Default ให้เหมาะสมต่อการใช้งานมากขึ้น
(ซึ่งถือว่าดีมาก หากผู้ใช้ต้องการเปิดคุณสมบัติ Auto Tuning ขึ้นมาใช้งาน และยังสามารถนำค่า Default นี้กลับไปกำหนดให้กับเวอร์ชั่น 2017 ได้อีกด้วย)

ค่า Default ของ Query Store บน Microsoft SQL Server 2017

จากเดิมค่า Query Store Capture Mode มีค่าเป็น ALL ในเวอร์ชั่น 2016-2017 ได้เปลี่ยนเป็น AUTO ในเวอร์ชั่น 2019 (สำหรับบน Azure SQL Database เป็น AUTO มาตั้งแต่ต้น) ซึ่งเป็นค่าที่เหมาะสมสำหรับ Auto Tuning มากกว่า

ค่า Default ของ Query Store บน Microsoft SQL Server 2019

ความหมายของ Query Store Capture Mode มีค่าเป็น AUTO คือแทนที่จะเก็บทุก Plan Cached ที่ถูกขับไล่ออกจากหน่วยความจำ มาเป็นการเก็บเฉพาะที่เห็นว่าสำคัญเท่านั้น เกณฑ์ที่ใช้พิจารณาความสำคัญ Microsoft ไม่มีเอกสารเผยแพร่ออกมา แต่อย่างน้อยมี 3 ค่าจาก Syntax ของคำสั่ง ALTER DATABASE พอจะแสดงให้เห็นได้ (ขอแสดงเพียงบางส่วนของ Syntax)
ผู้อ่านสามารถดู Syntax ตัวเต็มได้จาก https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-set-options?view=sql-server-2017

ALTER DATABASE { database_name | CURRENT }

SET

{

<option_spec> [ ,...n ] [ WITH <termination> ]

}

<option_spec> ::=

{

…

| <automatic_tuning_option>

…

| <query_store_options>

…

}

;

<automatic_tuning_option> ::=

{

AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = { ON | OFF } )

}

<query_store_options> ::=

{

QUERY_STORE

{

= OFF

| = ON [ ( <query_store_option_list> [,...n] ) ]

| ( < query_store_option_list> [,...n] )

| CLEAR [ ALL ]

}

<query_store_option_list> ::=

{

OPERATION_MODE = { READ_WRITE | READ_ONLY }

| CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = number )

| DATA_FLUSH_INTERVAL_SECONDS = number

| MAX_STORAGE_SIZE_MB = number

| INTERVAL_LENGTH_MINUTES = number

| SIZE_BASED_CLEANUP_MODE = { AUTO | OFF }

| QUERY_CAPTURE_MODE = { ALL | AUTO | CUSTOM | NONE }

| MAX_PLANS_PER_QUERY = number

| WAIT_STATS_CAPTURE_MODE = { ON | OFF }

| QUERY_CAPTURE_POLICY = ( <query_capture_policy_option_list> [,...n] )

}

<query_capture_policy_option_list> :: =

{

STALE_CAPTURE_POLICY_THRESHOLD = number { DAYS | HOURS }

| EXECUTION_COUNT = number

| TOTAL_COMPILE_CPU_TIME_MS = number

| TOTAL_EXECUTION_CPU_TIME_MS = number

พบว่าเราสามารถกำหนด Query Store Capture Policy เพิ่มเติมได้ (ไม่มีใน GUI ของ SSMS) ซึ่งหากเปิด Query Store Capture Mode เป็น AUTO ค่าเหล่านี้เราไม่ต้องตั้ง แต่หากเลือก Query Store Capture Mode เป็น Custom เราสามารถกำหนดค่าเหล่านี้ได้คือ

EXECUTION_COUNT หากกำหนดเป็น 50 จะหมายถึงบันทึกเฉพาะ Query ที่ถูก Execute เกิน 50 ครั้งขึ้นไป
TOTAL_COMPILE_CPU_TIME_MS หากกำหนดเป็น 60000 หรือ 60 วินาที จะหมายถึงบันทึกเฉพาะ Query ที่ใช้เวลาในการ Compile เกิน 1 นาทีขึ้นไป
TOTAL_EXECUTION_CPU_TIME_MS หากกำหนดเป็น 60000 หรือ 60 วินาที จะหมายถึงบันทึกเฉพาะ Query ที่ใช้เวลาในการประมวลผลเกิน 1 นาทีขึ้นไป

โดยจำนวนจะถึงเกณฑ์ดังกล่าวในกรอบเวลาที่กำหนดไว้ในค่า STALE_CAPTURE_POLICY_THRESHOLD (ปรากฏใน GUI ของ SSMS) โดยในเวอร์ชั่น 2016-2017 คือภายใน 367 วัน แต่สำหรับเวอร์ชั่น 2019 คือภายใน 30 วันตามรูป

หากผู้อ่านต้องการตั้งค่า Query Store บนฐานข้อมูลใดๆ เพื่อสนับสนุน Auto Tuning ในเวอร์ชั่น 2017 แต่มีการตั้งค่าเหมือน Default ในเวอร์ชั่น 2019 ทำได้ดังนี้ (ตัวอย่างบนฐานข้อมูล TestDB)

USE [master]

ALTER DATABASE [TestDB]

SET QUERY_STORE = ON

ALTER DATABASE [TestDB]

SET QUERY_STORE

(

OPERATION_MODE = READ_WRITE

, CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 90)

, MAX_STORAGE_SIZE_MB = 1000

, QUERY_CAPTURE_MODE = AUTO

)

ALTER DATABASE [TestDB]

SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON );

สรุป

การนำ Plan เก่าที่ถูกขับไล่ออกไปแล้วกลับมาใช้สามารถทำได้โดยเปิดคุณสมบัติ Query Store บนฐานข้อมูล แต่การนำ Plan เก่ากลับมา Force เองอาจเป็นงานที่ยุ่งยากเกินไป การเปิด Auto Tuning (Automatic plan choice correction) บน Microsoft SQL Server ตั้งแต่เวอร์ชั่น 2017 ขึ้นไปน่าจะช่วยในงานดังกล่าวง่ายขึ้นมา เพียงแต่การตั้งค่าในเวอร์ชั่น 2017 นั้นอาจทำให้ได้ Plan เข้ามาใน Query Store มากเกินความจำเป็น การกำหนด Query Store Capture Policy ที่เหมาะสม หรือดีกว่าคือการเปิด Query Store Capture Mode เป็น AUTO จะทำให้ได้ Plan ที่ผ่านเกณฑ์ของ Microsoft ว่ามีความสำคัญเข้ามาเป็นตัวเลือกให้แก่กลไก Auto Tuning น่าจะเหมาะสมที่สุด

สุดท้ายขอแนะนำให้ผู้อ่านทำการติดตาม Patch เกี่ยวกับ Query Store มาปรับปรุงก่อนการใช้งาน เพราะมีรายงานผลกระทบด้านประสิทธิภาพที่เกิดจากการเปิดใช้ Query Store อยู่บ้าง แต่ได้รับการบรรเทาแล้ว และติดตามบทความจากผู้เขียนเกี่ยวกับความคืบหน้าของ Query Store ต่อไปหากมีอะไรเพิ่มเติมในเวอร์ชั่นจริงของ 2019 ที่จะถูกปล่อยออกมาในเร็ววันนี้