Mastering SQL Server Query Store: Capture, Analyze, และ Resolve Query Performance

บทนำ

Microsoft® SQL Server® Query Store ตามชื่อคือ Store ที่ Capture Database History ของ Executed Queries, Query Runtime Execution Statistics, และ Execution Plans เนื่องจาก Data Stored บน Disk คุณ Retrieve Query Store Data ได้ทุกเมื่อสำหรับ Troubleshooting และ SQL Server Restarts ไม่ส่งผลต่อ Data ใช้ Query Store (Introduced ใน SQL Server 2016 Available ใน All Later Editions) Troubleshoot Performance Issues ที่เกิดจาก Query Plan Changes

Introduction

คุณอาจพบ Baseline Data Analysis Useful สำหรับ Performance Troubleshooting แต่จนกว่า Query Store จะ Introduced Information นั้นไม่ Available Natively ใน SQL Server ถ้า Query Store Enabled สำหรับ Database มัน Keep Information เกี่ยวกับ Executed Queries พร้อม Execution Plans และ Runtime Statistics Enabled ที่ Database Level และสามารถ Enable สำหรับ All Users' Databases และ MSDB System Database Query Store-related Information และ Metadata Stored ใน Internal Tables ภายใน Database เอง ไม่ต้อง Manage Separate Backup ของ Query Store เพราะ Standard Database Backup มี Required Information ทั้งหมด เพื่อ View Query Store Data ต้องมี Permission View Database State และ DB_Owner Rights เพื่อ Force และ Unforce Plans สามารถ View Data ผ่าน Management Studio และ T-SQL

Setting Up Query Store

Steps Enable Query Store สำหรับ Database:

1. Right-click Database -> Go to Properties
2. Under Select a Page Choose Select Query Store
3. ใน Section General Change Operation Mode (Requested) จาก Off เป็น Read-Write
4. Leave Other Fields ใน Operation Mode (Requested) Drop-down Box เป็น Pre-populated Default Values
5. Click Ok ใน Database Properties Box Enable Query Store สำหรับ Selected Database

Enable Query Store ด้วย T-SQL:

ALTER DATABASE [DB_Name] SET QUERY_STORE = ON;

Various Configuration Options of Query Store

List คำอธิบาย Query Store Options:

• Operation Mode (Requested) มี 3 Values: Off, Read Only, Read Write เมื่อ Query Store Mode = Read ไม่ Collect New Execution Plans หรือ Query Runtime Statistics Mode นี้สำหรับ Read-only Operations เกี่ยวกับ Query Store เปลี่ยนเป็น Read Write Enable Query Store Gather Executed Queries, Execution Plans, และ Runtime Statistics
• Data Flush Interval (Minutes) Set ว่าจะ Flush Collected Execution Plans และ Query Runtime Statistics จาก Memory ไป Disk ทุกกี่นาที Default = 15 Minutes
• Statistics Collection Interval Define Aggregation Interval ของ Query Runtime Statistics ภายใน Query Store Default = 60 Minutes
• Max Size (MB) Configure Max Size ของ Query Store Default = 100 MB Data ใน Query Store Stored ใน Database ที่ SQL Server Query Store Enabled ขึ้นกับ Size Configured ที่นี่ หลัง Query Store ถึง Max Size Operation Mode จะ Switch เป็น Read Only อัตโนมัติ
• Capture Mode Choose Query Type ที่ Capture ใน Query Store Default = All Store Every Executed Query ตั้งเป็น auto Query Store พยายาม Triage Query Capture ตาม Priority และพยายาม Ignore Infrequently Executed และ Ad hoc Queries
• Stale Query Threshold (Days) Define Data อยู่ใน Query Store นานแค่ไหน Default = 30 Days

Query Store Reports

Query Store มี Reports:

• Regressed Queries: Pinpoint Queries ที่ Execution Metrics Regressed หรือ Changed to Worse 近期
• Overall Resource Consumptions: Analyze Total Resource Consumption สำหรับ Database สำหรับ Execution Metrics ใดๆ
• Top Resource Consuming Queries: Queries ที่มี Biggest Impact บน Database Resource Consumption
• Queries with Forced Plans: Built-in Report แสดง All Queries with Forced Execution Plans
• Queries with High Variation: Report แสดง Queries ที่มี Frequent Parameterization Problems มากที่สุด
• Tracked Queries: Track Execution ของ Most Important Queries ใน Real Time

Forcing Plans with Query Store

เพราะ Plan Regression Query ที่ทำงานดีเมื่อวาน อาจ Take Too Long, Consume Too Many Resources หรือ Timeout วันนี้ Default SQL Server Keep Latest Execution Plan เท่านั้นสำหรับ Queries Schema, Statistics, หรือ Indexes Changes อาจ Change Query Execution Plan ที่ Query Optimizer ใช้ Plan อาจ Dropped เนื่องจาก Plan Cache Memory Pressure

Query Store Store Query Plans และ Statistical Information Aggregated Over Time ภายใน Monitored Databases ต่างจาก Plan Cache Query Store Retain Multiple Plans Per Query และ Maintain History ของ Query Plan Changes พร้อม Associated Stats ต่อ Plan ด้วยการ Choose Among Various Execution Plans คุณ Force Execution Plan ใดๆ Query Optimizer จะ Use Forced Execution Plan นี้สำหรับ Upcoming Query Executions เท่านั้น

Query Store -> Open Top Resource Consuming Queries Report Lists Queries ที่ Resource Intensive Select Query สำหรับ Examine:

Hover Mouse Over Plan ดู Related Statistics

Compare Different Plans

Plan 216 Details:

Plan 195 Details:

Average Duration ของ Plan 216 ต่ำกว่า ดังนั้นใช้ Force Plan นี้สำหรับ Further Executions Click Force Plan Confirm Screen "Do you want to force plan 216 for query 42?"

Click Yes หลัง Force Plan Highlighted ด้วย Checkmark ใน Future Plan นี้จะถูก Query Optimizer ใช้สำหรับ Execution

Best Practices with Query Store

• ใช้ Latest SQL Server Management Studio เพื่อ Latest Features และ Enhancements
• Verify และ Monitor Query Store Data Collection
• Set Optimal Query Capture Mode และ Revisit Query Store Configuration Options ตามความจำเป็นเพื่อ Adjust
• Avoid Using Non-parameterized Queries
• Check Status ของ Forced Plans Regularly

สรุป

Query Store เป็น Useful Feature Introduced ใน SQL Server 2016 เนื่องจาก Tuning Performance Enhancements เป็น Key Skill ที่ทุก DBA ต้องการ คุณควรเรียนรู้ Configure และ Use Query Store สามารถใช้ Query Store Track Performance Changes และ Troubleshoot Degradation ใน Query Performance โดย Compare Execution Plans สามารถ Force Query Plan สำหรับ Query ใดๆ Override Plan ใน Plan Cache ให้ Performance Benefits Query Store ไม่มี Major Impact บน SQL Server Performance เพราะใช้ Capture และ Save Queries Execution Statistics และ Plans เพื่อ View Later

ใช้แท็บ Feedback เพื่อแสดงความคิดเห็นหรือถามคำถาม

เพิ่มประสิทธิภาพสภาพแวดล้อมด้วยบริการผู้เชี่ยวชาญ

ผู้เชี่ยวชาญ Rackspace Application Services (RAS) ให้บริการ Professional และ Managed Services ครอบคลุม Portfolio หลากหลาย:

• Platform สำหรับ eCommerce และ Digital Experience
• Enterprise Resource Planning (ERP)
• Business Intelligence
• Salesforce Customer Relationship Management (CRM)
• Databases
• Email Hosting และ Productivity

เรามอบ:

• Unbiased Expertise: ลดความซับซ้อนและนำทางการ Modernization เน้นความสามารถที่สร้างคุณค่าทันที
• Fanatical Experience™: รวมแนวทาง Process First. Technology Second.® พร้อม Technical Support ให้ Soluation ครบวงจร
• Unrivaled Portfolio: ประยุกต์ประสบการณ์ Cloud มากมายช่วยเลือกและ Deploy เทคโนโลยีที่เหมาะสมบน Cloud ที่เหมาะสม
• Agile Delivery: ตรงจุดที่คุณอยู่ในการเดินทางและทำให้ความสำเร็จของเราสอดคล้องกับคุณ

แชทตอนนี้เพื่อเริ่มต้น