Tìm hiểu về Query Store trong phân tích hiệu suất truy vấn trên SQL Server

Trong bài viết này Stanford sẽ giới thiệu đến các bạn học quản trị sql tìm hiểu về Query Store cũng như các ứng dụng của nó trong việc phân tích hiệu suất truy vấn trong SQL Server.

Query Store trong SQL Server là một tính năng mạnh mẽ giúp theo dõi, lưu trữ và phân tích hiệu suất truy vấn theo thời gian. Nó đặc biệt hữu ích trong việc khắc phục sự cố hiệu suất và tối ưu hóa truy vấn. Dưới đây là phần giới thiệu tổng quan và phân tích chi tiết từng phần của Query Store, kèm ví dụ minh họa cụ thể.


Giới thiệu về Query Store

Query Store được giới thiệu từ SQL Server 2016 trở đi. Nó hoạt động như một "hộp đen" ghi lại lịch sử thực thi truy vấn, kế hoạch thực thi (execution plans), và thống kê hiệu suất (runtime stats).

Lợi ích:

  • Theo dõi sự thay đổi của kế hoạch thực thi theo thời gian.
  • Phân tích hiệu suất truy vấn.
  • Buộc sử dụng một kế hoạch thực thi tốt (force plan).
  • Hữu ích trong việc nâng cấp SQL Server hoặc thay đổi chỉ mục.

Các thành phần chính của Query Store

1. Query Store Configuration
Cấu hình bật/tắt và thiết lập lưu trữ.

ALTER DATABASE [TenCSDL]
SET QUERY_STORE = ON;
GO
 
ALTER DATABASE [TenCSDL]
SET QUERY_STORE (
    OPERATION_MODE = READ_WRITE,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
    DATA_FLUSH_INTERVAL_SECONDS = 900,
    INTERVAL_LENGTH_MINUTES = 60,
    MAX_STORAGE_SIZE_MB = 100,
    QUERY_CAPTURE_MODE = AUTO
);
Giải thích:
- STALE_QUERY_THRESHOLD_DAYS: số ngày giữ lại dữ liệu cũ.
- INTERVAL_LENGTH_MINUTES: khoảng thời gian chia nhỏ để thống kê.
- QUERY_CAPTURE_MODE: ALL, AUTO, hoặc NONE.

2. Query Store Catalog Views
Các bảng hệ thống để truy vấn dữ liệu từ Query Store:

  • sys.query_store_query: Thông tin truy vấn
  • sys.query_store_plan: Kế hoạch thực thi
  • sys.query_store_runtime_stats: Thống kê thời gian chạy
  • sys.query_store_runtime_stats_interval: Khoảng thời gian thống kê

Ví dụ: Truy vấn các truy vấn tốn thời gian nhất:

SELECT TOP 10
    qsqt.query_sql_text,
    qsp.plan_id,
    SUM(qsrs.avg_duration) AS TotalAvgDuration
FROM sys.query_store_query_text qsqt
JOIN sys.query_store_query qsq ON qsqt.query_text_id = qsq.query_text_id
JOIN sys.query_store_plan qsp ON qsq.query_id = qsp.query_id
JOIN sys.query_store_runtime_stats qsrs ON qsp.plan_id = qsrs.plan_id
GROUP BY qsqt.query_sql_text, qsp.plan_id
ORDER BY TotalAvgDuration DESC;
3. Force Plan (Buộc kế hoạch thực thi)
Khi một truy vấn có nhiều kế hoạch thực thi, bạn có thể buộc SQL Server sử dụng kế hoạch tốt nhất.

EXEC sp_query_store_force_plan @query_id = 123, @plan_id = 456;
Hủy bỏ:

EXEC sp_query_store_unforce_plan @query_id = 123, @plan_id = 456;
4. Xóa dữ liệu Query Store

ALTER DATABASE [TenCSDL] SET QUERY_STORE CLEAR;
Ví dụ: Các bước thực hiện sử dụng Query Store trong SQL Server

- Bước 1: Bật Query Store cho database

ALTER DATABASE AdventureWorks2019 SET QUERY_STORE = ON;
- Bước 2: Thực thi một truy vấn nhiều lần

SELECT * FROM Sales.SalesOrderHeader WHERE CustomerID = 11000;
- Bước 3: Truy vấn dữ liệu từ Query Store

SELECT
    txt.query_sql_text,
    rs.avg_duration,
    rs.execution_type_desc,
    rs.count_executions
FROM sys.query_store_query_text txt
JOIN sys.query_store_query q ON txt.query_text_id = q.query_text_id
JOIN sys.query_store_plan p ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
ORDER BY rs.avg_duration DESC;
4. Giao diện đồ họa (SSMS)
Trong SQL Server Management Studio (SSMS):
Mở database → Query Store → Chọn các báo cáo như:

  • Top Resource Consuming Queries
  • Regressed Queries
  • Tracked Queries

Như vậy qua bài viết này Stanford đã giúp các bạn học sql tìm hiểu về query store cũng như ứng dụng của nó trong phân tích hiệu suất truy vấn trên SQL Server.

Bên cạnh đó nếu bạn đang muốn được đào tạo bài bản từ cơ bản đến nâng cao có thể tham gia ngay khóa học sql cùng chuyên gia giàu kinh nghiệm Stanford tại đây: http://bit.ly/2SLPYFF và nhận ưu đãi hấp dẫn của Stanford trong thời gian này. Bạn có thể gọi theo hotline: 0963 723 236 - 0866 586 366 để được gọi lại tư vấn trực tiếp nhé.

=============================
☎ STANFORD – ĐÀO TẠO VÀ PHÁT TRIỂN CÔNG NGHỆ
Hotline: 0963 723 236 - 0866 586 366
Website: https://stanford.com.vn
Facebook: http://bit.ly/2FN0TYb
Youtube: http://bit.ly/2TkKT7I

Tags: query store trong sql server, sử dụng query store