Query Optimization - Tối Ưu Truy Vấn

1. Query Processing Pipeline

Mỗi khi SQL Server nhận một query, nó đi qua pipeline 4 bước:

SQL Text
   ↓
[1. Parser]          → Kiểm tra syntax, tạo Parse Tree
   ↓
[2. Algebrizer/Bind] → Resolve tên objects/columns, kiểm tra semantics
   ↓
[3. Query Optimizer] → Tạo Execution Plan tối ưu (cost-based)
   ↓
[4. Execution Engine]→ Thực thi plan, trả kết quả

Chi tiết từng bước

-- Bước 1: Parser - kiểm tra syntax
SELECCT * FROM Orders;   -- ❌ Syntax error tại Parser

-- Bước 2: Algebrizer - resolve objects
SELECT * FROM NonExistentTable;   -- ❌ Object not found tại Algebrizer

-- Bước 3: Optimizer - tạo plan
-- Optimizer thử nhiều alternatives, chọn plan có estimated cost thấp nhất
-- Không phải luôn chọn plan THỰC TẾ nhanh nhất (chỉ dựa vào statistics)

-- Bước 4: Execution
-- Thực thi plan, có thể khác với estimate nếu statistics không chính xác

2. Cost-Based Optimizer

SQL Server’s Query Optimizer (QO) là cost-based: nó tạo nhiều plan alternatives và chọn plan có estimated cost thấp nhất.

Cost dựa trên gì?

• Statistics: phân phối dữ liệu của cột (histogram)
• Cardinality estimates: ước tính số hàng mỗi operator trả về
• I/O cost: số page reads ước tính
• CPU cost: computational cost

-- Xem statistics của một column
DBCC SHOW_STATISTICS ('Orders', 'IX_Orders_CustomerId');
-- Kết quả: Header (stats), Density Vector, Histogram (phân phối giá trị)

-- Cập nhật statistics
UPDATE STATISTICS Orders IX_Orders_CustomerId WITH FULLSCAN;
UPDATE STATISTICS Orders;  -- Tất cả stats cho bảng

-- Xem khi statistics được update lần cuối
SELECT 
    s.name AS StatName,
    sp.last_updated,
    sp.rows,
    sp.rows_sampled,
    sp.modification_counter
FROM sys.stats s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE s.object_id = OBJECT_ID('Orders');

Estimated vs Actual Rows

-- Bật actual execution plan (Ctrl+M trong SSMS)
-- Xem: Estimated Rows vs Actual Rows trong mỗi operator
-- Nếu chênh lệch lớn → statistics không chính xác → plan không tốt

-- Ví dụ: Optimizer ước tính 10 rows nhưng thực tế 1,000,000 rows
-- → Optimizer chọn Nested Loops (tốt cho nhỏ) nhưng thực tế nên dùng Hash Join

3. SARGable Predicates

SARGable (Search ARGument ABLE) là predicate mà SQL Server có thể dùng để thực hiện index seek thay vì scan.

Nguyên tắc

Predicate không SARGable khi: cột indexed nằm bên trong một function hoặc biểu thức.

-- ❌ Non-SARGable: hàm trên cột
WHERE YEAR(OrderDate) = 2024
WHERE MONTH(OrderDate) = 6
WHERE UPPER(LastName) = 'NGUYEN'
WHERE LEN(PhoneNumber) = 10
WHERE SUBSTRING(ProductCode, 1, 3) = 'SKU'

-- ✅ Viết lại SARGable
WHERE OrderDate >= '2024-01-01' AND OrderDate < '2025-01-01'
WHERE OrderDate >= '2024-06-01' AND OrderDate < '2024-07-01'
WHERE LastName = 'Nguyen'   -- CI collation → không cần UPPER
WHERE PhoneNumber LIKE '__________'   -- Nếu phải dùng LIKE
WHERE ProductCode LIKE 'SKU%'

-- ❌ Non-SARGable: biểu thức trên cột
WHERE Price * 1.1 > 100
WHERE Age + 5 = 30
WHERE OrderId + 1 = @Param

-- ✅ Viết lại: di chuyển biểu thức sang phía giá trị
WHERE Price > 100 / 1.1        -- ≈ 90.9
WHERE Age = 30 - 5             -- = 25
WHERE OrderId = @Param - 1

-- ❌ Non-SARGable: implicit/explicit conversion
WHERE CAST(OrderId AS VARCHAR) = '1234'
WHERE CONVERT(NVARCHAR, OrderDate, 103) = '01/01/2024'

-- ✅ Giữ đúng kiểu dữ liệu
WHERE OrderId = 1234           -- INT so sánh INT
WHERE OrderDate = '2024-01-01' -- Implicit convert từ string sang DATE là OK

4. Common Anti-Patterns (Lỗi phổ biến)

4.1 Function trong WHERE trên cột indexed

-- ❌ Buộc table scan
SELECT * FROM Orders WHERE YEAR(OrderDate) = 2024;

-- ✅ Index seek
SELECT * FROM Orders 
WHERE OrderDate >= '2024-01-01' AND OrderDate < '2025-01-01';

4.2 LIKE với leading wildcard

-- ❌ Table/Index scan - không thể seek khi bắt đầu bằng %
SELECT * FROM Products WHERE ProductName LIKE '%phone%';

-- ✅ Tốt hơn: trailing wildcard có thể seek
SELECT * FROM Products WHERE ProductName LIKE 'Samsung%';

-- ✅ Nếu cần full-text search: dùng Full-Text Index + CONTAINS
SELECT * FROM Products WHERE CONTAINS(ProductName, 'phone');

4.3 Implicit Conversion

-- Bảng: CustomerCode NVARCHAR(20) (Unicode)
-- ❌ Parameter @code là VARCHAR → implicit conversion, index scan
DECLARE @code VARCHAR(20) = 'CUST001';
SELECT * FROM Customers WHERE CustomerCode = @code;
-- SQL Server phải convert tất cả CustomerCode sang VARCHAR để so sánh

-- ✅ Dùng đúng kiểu dữ liệu
DECLARE @code NVARCHAR(20) = N'CUST001';
SELECT * FROM Customers WHERE CustomerCode = @code;

-- Kiểm tra implicit conversion trong execution plan:
-- Tìm operator có "CONVERT_IMPLICIT" trong predicate

4.4 SELECT *

-- ❌ SELECT * - kéo về nhiều cột không cần thiết
SELECT * FROM Orders WHERE CustomerId = 100;

-- ✅ Chỉ lấy cột cần
SELECT OrderId, OrderDate, TotalAmount FROM Orders WHERE CustomerId = 100;
-- Lợi ích:
-- 1. Giảm network bandwidth
-- 2. Có thể dùng covering index (tránh key lookup)
-- 3. Tránh break code khi schema thay đổi

4.5 OR thay vì UNION ALL (đôi khi)

-- ❌ OR có thể không dùng được multiple indexes hiệu quả
SELECT * FROM Orders 
WHERE CustomerId = 100 OR Status = 'Urgent';

-- ✅ UNION ALL thường hiệu quả hơn
SELECT * FROM Orders WHERE CustomerId = 100
UNION ALL
SELECT * FROM Orders WHERE Status = 'Urgent' AND CustomerId <> 100;

4.6 NOT IN với NULL

-- ❌ Nguy hiểm: nếu BlacklistIds có NULL → query trả về rỗng
SELECT * FROM Orders WHERE CustomerId NOT IN (SELECT CustomerId FROM Blacklist);

-- ✅ An toàn
SELECT * FROM Orders o
WHERE NOT EXISTS (SELECT 1 FROM Blacklist b WHERE b.CustomerId = o.CustomerId);

5. JOIN Algorithms

SQL Server có 3 thuật toán join, optimizer chọn dựa trên kích thước bảng, indexes và statistics.

Nested Loops Join

For each row in Outer:
    Seek/Scan Inner table for matching rows

-- Nested Loops tốt khi:
-- 1. Outer table nhỏ (ít rows sau filter)
-- 2. Inner table có index trên join key
-- 3. Join selectivity cao (ít rows match)

-- Optimizer thường chọn Nested Loops cho:
SELECT c.Name, o.OrderDate
FROM Customers c                      -- Outer (nhỏ, vd: 1 khách)
INNER JOIN Orders o ON o.CustomerId = c.CustomerId  -- Inner (có index)
WHERE c.CustomerId = 12345;

Hash Join

Phase 1 (Build): Hash mọi hàng của Build Input vào hash table
Phase 2 (Probe): Với mỗi hàng Probe Input, lookup trong hash table

-- Hash Join tốt khi:
-- 1. Cả hai inputs lớn, không có index phù hợp
-- 2. Không yêu cầu sorted input
-- 3. Ước tính nhiều rows match

-- ⚠️ Khi hash table không vừa memory → spill sang TempDB (Hash Spill)
-- Xuất hiện trong execution plan là dấu hiệu cần index hoặc tăng memory

-- Xem Hash Spill warnings
SELECT * FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE CAST(qp.query_plan AS NVARCHAR(MAX)) LIKE '%SpillToTempDb%';

Merge Join

Cả hai inputs phải được SORT theo join key
Đọc song song, merge như merge sort

-- Merge Join tốt khi:
-- 1. Cả hai inputs đã sorted (từ index hoặc Sort operator)
-- 2. JOIN dạng equality (equi-join)
-- 3. Cả hai inputs tương đương lớn

-- Merge Join rất hiệu quả nếu đã có sorted index:
SELECT o.OrderId, od.ProductId
FROM Orders o                          -- Clustered index trên OrderId → sorted
INNER JOIN OrderDetails od ON o.OrderId = od.OrderId  -- Index trên OrderId → sorted
-- → Optimizer thường chọn Merge Join ở đây

So sánh

6. Query Hints

-- OPTION (RECOMPILE): Tạo plan mới mỗi lần, dùng actual parameter values
-- Hữu ích khi parameter sniffing gây vấn đề
SELECT * FROM Orders WHERE CustomerId = @Id
OPTION (RECOMPILE);

-- MAXDOP: Giới hạn số CPU dùng cho query
SELECT * FROM BigTable
OPTION (MAXDOP 4);  -- Tối đa 4 cores

SELECT * FROM SmallQuery
OPTION (MAXDOP 1);  -- Không dùng parallelism

-- OPTIMIZE FOR: Hint cho optimizer dùng giá trị giả định
SELECT * FROM Orders WHERE CustomerId = @Id
OPTION (OPTIMIZE FOR (@Id = 1000));   -- Optimize như thể @Id = 1000

-- OPTIMIZE FOR UNKNOWN: Không dùng cached value, dùng average statistics
SELECT * FROM Orders WHERE CustomerId = @Id
OPTION (OPTIMIZE FOR (@Id UNKNOWN));

-- USE PLAN: Ép dùng execution plan XML cụ thể (advanced)
SELECT * FROM Orders
OPTION (USE PLAN N'<ShowPlanXML.../>');

-- LOOP JOIN / HASH JOIN / MERGE JOIN: Ép thuật toán join
SELECT * FROM Orders o
INNER HASH JOIN Customers c ON o.CustomerId = c.CustomerId
OPTION (HASH JOIN);

-- FORCE ORDER: Ép optimizer join theo thứ tự viết trong query
SELECT * FROM A
INNER JOIN B ON A.Id = B.AId
INNER JOIN C ON B.Id = C.BId
OPTION (FORCE ORDER);

-- NO_PERFORMANCE_SPOOL: Tắt spool optimization
-- DISABLE_OPTIMIZER_ROWGOAL: Tắt row goal optimization
-- ENABLE_PARALLEL_PLAN_PREFERENCE: Ưu tiên plan song song

7. Plan Cache & Parameter Sniffing

Plan Cache

SQL Server cache execution plans để tránh compile lại mỗi lần.

-- Xem plans trong cache
SELECT 
    qs.execution_count,
    qs.total_logical_reads,
    qs.total_elapsed_time / 1000 AS total_ms,
    SUBSTRING(qt.text, 1, 300) AS query_text,
    qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_logical_reads DESC;

-- Xóa tất cả plan cache (chỉ dùng dev/troubleshooting!)
DBCC FREEPROCCACHE;

-- Xóa plan cụ thể
DBCC FREEPROCCACHE (@plan_handle);

Parameter Sniffing

Khi stored procedure compiles lần đầu, optimizer dùng giá trị parameter lúc đó để tạo plan. Plan này có thể tốt cho giá trị đó nhưng tệ cho giá trị khác.

-- Stored procedure
CREATE PROCEDURE GetOrdersByCustomer @CustomerId INT
AS
BEGIN
    SELECT OrderId, OrderDate, TotalAmount
    FROM Orders
    WHERE CustomerId = @CustomerId;
END;

-- Lần đầu gọi với CustomerId = 1 (có 1,000,000 orders → optimizer dùng Table Scan)
EXEC GetOrdersByCustomer 1;  -- Plan: Table Scan (tốt cho nhiều rows)

-- Lần sau gọi với CustomerId = 99999 (có 5 orders)
EXEC GetOrdersByCustomer 99999;  -- ❌ Dùng lại plan cũ (Table Scan cho 5 rows → rất chậm!)

Giải pháp Parameter Sniffing

-- Giải pháp 1: OPTION (RECOMPILE) - compile lại mỗi lần
CREATE PROCEDURE GetOrdersByCustomer @CustomerId INT
AS
BEGIN
    SELECT OrderId, OrderDate, TotalAmount
    FROM Orders
    WHERE CustomerId = @CustomerId
    OPTION (RECOMPILE);  -- Mất benefit của plan caching
END;

-- Giải pháp 2: OPTIMIZE FOR UNKNOWN
WHERE CustomerId = @CustomerId
OPTION (OPTIMIZE FOR (@CustomerId UNKNOWN));

-- Giải pháp 3: Local variable trick (không được sniff)
CREATE PROCEDURE GetOrdersByCustomer @CustomerId INT
AS
BEGIN
    DECLARE @LocalId INT = @CustomerId;  -- Optimizer không thể sniff local var
    SELECT * FROM Orders WHERE CustomerId = @LocalId;
END;

-- Giải pháp 4: Multiple procedures cho các scenarios khác nhau
IF @CustomerId IN (1, 2, 3)  -- Known "high volume" customers
    EXEC GetOrdersByCustomer_HighVolume @CustomerId
ELSE
    EXEC GetOrdersByCustomer_Normal @CustomerId

8. Recompilation

Khi nào plan bị recompile?

-- 1. Schema thay đổi (ALTER TABLE, CREATE INDEX, etc.)
-- 2. Statistics thay đổi (sau threshold = 20% of rows + 500 rows modified)
-- 3. SET options thay đổi trong session
-- 4. Database thay đổi (sp_recompile)
-- 5. OPTION (RECOMPILE) hint

-- Buộc recompile tất cả procedures liên quan đến table
EXEC sp_recompile 'Orders';

-- Xem recompilation events với Extended Events hoặc Profiler
-- Event: sql_statement_recompile (Extended Events)
-- Event Class: SP:Recompile (Profiler)

Xem Queries với nhiều recompiles

SELECT 
    qs.plan_generation_num,  -- Số lần plan được regenerate
    qs.execution_count,
    SUBSTRING(qt.text, 1, 200) AS query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
WHERE qs.plan_generation_num > 10
ORDER BY qs.plan_generation_num DESC;

9. TempDB Usage

Temp Tables vs Table Variables

-- Temp Table (#)
CREATE TABLE #TempOrders (
    OrderId INT,
    CustomerId INT,
    TotalAmount DECIMAL(18,2)
);
-- Trong TempDB, có statistics, có thể có indexes
-- Scope: session (hoặc nested scope)

INSERT INTO #TempOrders SELECT OrderId, CustomerId, TotalAmount FROM Orders WHERE ...;

-- Table Variable (@)
DECLARE @TempOrders TABLE (
    OrderId INT,
    CustomerId INT,
    TotalAmount DECIMAL(18,2)
);
-- Không có statistics (optimizer assume 1 row!)
-- Scope: batch
-- Không commit/rollback riêng

Worktables trong TempDB

SQL Server tự tạo worktables trong TempDB cho:

• Hash Join spill (khi memory không đủ)
• Sort spill
• Spool operations
• Recursive CTE

-- Monitor TempDB usage
SELECT 
    r.session_id,
    r.total_elapsed_time,
    tdb.internal_objects_alloc_page_count AS temp_pages_allocated,
    SUBSTRING(qt.text, 1, 200) AS query_text
FROM sys.dm_exec_requests r
INNER JOIN sys.dm_db_task_space_usage tdb ON r.session_id = tdb.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) qt
WHERE tdb.internal_objects_alloc_page_count > 0
ORDER BY tdb.internal_objects_alloc_page_count DESC;

10. Parallelism (Xử lý song song)

Khi nào query đi parallel?

SQL Server sẽ cân nhắc parallel plan khi:

1. Estimated cost > cost threshold for parallelism (mặc định: 5)
2. MAXDOP không = 1
3. Query đủ phức tạp để benefit từ parallelism

-- Xem cấu hình parallelism
SELECT name, value_in_use
FROM sys.configurations 
WHERE name IN ('max degree of parallelism', 'cost threshold for parallelism');

-- Đổi cost threshold (khuyến nghị: 25-50 cho OLTP)
EXEC sp_configure 'cost threshold for parallelism', 50;
RECONFIGURE;

-- Đổi MAXDOP toàn server
EXEC sp_configure 'max degree of parallelism', 8;
RECONFIGURE;

MAXDOP Recommendations

-- Query-level MAXDOP
SELECT * FROM BigTable OPTION (MAXDOP 8);

-- Database-level MAXDOP (SQL Server 2016+)
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 4;

Vấn đề với Parallelism

-- CXPACKET wait: worker threads chờ nhau trong parallel query
-- Xem top waits
SELECT TOP 10
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    max_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ('SLEEP_TASK', 'BROKER_TO_FLUSH', 'WAITFOR', 'CLR_AUTO_EVENT')
ORDER BY wait_time_ms DESC;

-- CXPACKET cao có thể chỉ ra:
-- 1. Parallelism không cần thiết (tăng cost threshold)
-- 2. Skewed data distribution (uneven work distribution)
-- 3. Worker thread chờ I/O

11. DMVs for Performance Analysis

sys.dm_exec_query_stats

-- Top 20 queries tốn nhiều logical reads nhất
SELECT TOP 20
    qs.execution_count,
    qs.total_logical_reads,
    qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
    qs.total_elapsed_time / 1000000 AS total_seconds,
    qs.total_elapsed_time / qs.execution_count / 1000 AS avg_ms,
    SUBSTRING(qt.text, (qs.statement_start_offset/2)+1, 
        ((CASE qs.statement_end_offset 
            WHEN -1 THEN DATALENGTH(qt.text) 
            ELSE qs.statement_end_offset END 
         - qs.statement_start_offset)/2)+1) AS query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY qs.total_logical_reads DESC;

sys.dm_exec_requests

-- Xem các query đang chạy hiện tại
SELECT 
    r.session_id,
    r.status,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time / 1000 AS wait_seconds,
    r.total_elapsed_time / 1000 AS elapsed_seconds,
    r.cpu_time,
    r.logical_reads,
    DB_NAME(r.database_id) AS database_name,
    SUBSTRING(qt.text, (r.statement_start_offset/2)+1, 200) AS current_statement
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) qt
WHERE r.session_id != @@SPID   -- Loại bỏ session hiện tại
ORDER BY r.total_elapsed_time DESC;

sys.dm_os_wait_stats

-- Phân tích wait statistics (reset sau mỗi lần restart hoặc DBCC SQLPERF)
SELECT TOP 20
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    max_wait_time_ms,
    signal_wait_time_ms,
    wait_time_ms - signal_wait_time_ms AS resource_wait_ms
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
    'SLEEP_TASK', 'BROKER_TO_FLUSH', 'BROKER_EVENTHANDLER',
    'CHECKPOINT_QUEUE', 'DBMIRROR_EVENTS_QUEUE', 'SQLTRACE_BUFFER_FLUSH',
    'CLR_AUTO_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT',
    'HADR_FILESTREAM_IOMGR_IOCOMPLETION', 'HADR_WORK_QUEUE', 'LAZYWRITER_SLEEP',
    'LOGMGR_QUEUE', 'ONDEMAND_TASK_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH',
    'RESOURCE_QUEUE', 'SERVER_IDLE_CHECK', 'SLEEP_DBSTARTUP', 'SLEEP_DCOMSTARTUP',
    'SLEEP_MASTERDBREADY', 'SLEEP_MASTERMDREADY', 'SLEEP_MASTERUPGRADED',
    'SLEEP_MSDBSTARTUP', 'SLEEP_SYSTEMTASK', 'SLEEP_TEMPDBSTARTUP',
    'SNI_HTTP_ACCEPT', 'SP_SERVER_DIAGNOSTICS_SLEEP', 'SQLTRACE_BUFFER_FLUSH',
    'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', 'XE_DISPATCHER_WAIT', 'XE_TIMER_EVENT'
)
ORDER BY wait_time_ms DESC;

Common Wait Types

12. Extended Events vs SQL Trace

SQL Trace (Legacy - không dùng trên production mới)

-- SQL Trace (Profiler) - deprecated
-- Không dùng trên SQL Server 2019+ cho workloads mới

Extended Events (XE) - Modern approach

-- Tạo Extended Events session để capture slow queries (> 5 giây)
CREATE EVENT SESSION [SlowQueries] ON SERVER
ADD EVENT sqlserver.sql_statement_completed (
    ACTION (
        sqlserver.sql_text,
        sqlserver.client_hostname,
        sqlserver.username,
        sqlserver.database_name
    )
    WHERE (duration > 5000000)  -- > 5 giây (đơn vị: microseconds)
),
ADD EVENT sqlserver.rpc_completed (
    ACTION (sqlserver.sql_text)
    WHERE (duration > 5000000)
)
ADD TARGET package0.ring_buffer (SET max_memory = 51200)  -- 50MB buffer
WITH (MAX_DISPATCH_LATENCY = 5 SECONDS);

-- Bắt đầu session
ALTER EVENT SESSION [SlowQueries] ON SERVER STATE = START;

-- Đọc kết quả từ ring_buffer
SELECT  
    xdr.value('@name', 'nvarchar(50)') AS event_name,
    xdr.value('(action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS sql_text,
    xdr.value('(data[@name="duration"]/value)[1]', 'bigint') / 1000000.0 AS duration_sec,
    xdr.value('(data[@name="logical_reads"]/value)[1]', 'bigint') AS logical_reads,
    xdr.value('@timestamp', 'datetime2') AS event_time
FROM (
    SELECT CAST(target_data AS XML) AS target_data
    FROM sys.dm_xe_session_targets xst
    INNER JOIN sys.dm_xe_sessions xs ON xst.event_session_address = xs.address
    WHERE xs.name = 'SlowQueries' AND xst.target_name = 'ring_buffer'
) t
CROSS APPLY t.target_data.nodes('RingBufferTarget/event') AS xTable(xdr)
ORDER BY xdr.value('@timestamp', 'datetime2') DESC;

-- Dừng và xóa session
ALTER EVENT SESSION [SlowQueries] ON SERVER STATE = STOP;
DROP EVENT SESSION [SlowQueries] ON SERVER;

Query Store (SQL Server 2016+)

Query Store lưu lịch sử plans và performance - không cần XE session để track regressions.

-- Bật Query Store
ALTER DATABASE YourDB SET QUERY_STORE = ON;
ALTER DATABASE YourDB SET QUERY_STORE (
    OPERATION_MODE = READ_WRITE,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
    DATA_FLUSH_INTERVAL_SECONDS = 60,
    MAX_SIZE_MB = 1024,
    QUERY_CAPTURE_MODE = AUTO   -- Capture queries vượt ngưỡng
);

-- Top queries theo CPU trong Query Store
SELECT TOP 20
    q.query_id,
    qt.query_sql_text,
    rs.avg_cpu_time,
    rs.avg_logical_io_reads,
    rs.avg_duration,
    rs.count_executions
FROM sys.query_store_query q
INNER JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
INNER JOIN sys.query_store_plan p ON q.query_id = p.query_id
INNER JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
ORDER BY rs.avg_cpu_time DESC;

-- Force a specific plan (plan regression fix)
EXEC sp_query_store_force_plan @query_id = 1, @plan_id = 2;

So sánh XE vs Query Store vs Profiler