SQL Server – Cách Tìm và Phân Tích Các Truy Vấn Sử Dụng Hints Cách Chính Xác Nhất

Posted on by admin

Trong thực tế vận hành hệ thống SQL Server, đôi khi DBA cần rà soát tất cả các truy vấn đang chạy trên server để xem những truy vấn nào đang sử dụng query hints, table hints hay index hints như NOLOCK, FORCESEEK, MAXDOP… Đây thường là các truy vấn bất thường hoặc có thể gây ảnh hưởng đến hiệu năng, nên việc xác định chúng một cách nhanh và chính xác là rất quan trọng.

Vấn Đề Khi Tìm Truy Vấn Có Hints

Một cách làm phổ biến nhiều người hay nghĩ tới là:

  • Dò từng query text trong sys.sql_modules bằng wildcard như %NOLOCK% hoặc %FORCESEEK%

Nhưng cách này rất dễ ra false-positive vì từ khóa có thể xuất hiện trong:

  • Tên bảng
  • Bình luận trong code
  • Hoặc không phải truy vấn bị chạy trực tiếp bởi SQL Server (ví dụ ứng dụng bên ngoài sinh động)

Hoặc bạn có thể nghĩ tới dùng trace/profiler — nhưng:

  • Gây tải hệ thống cao
  • Rất khó kiểm soát và vẫn không đảm bảo chính xác.

✔ Giải Pháp Hiệu Quả: Phân Tích Plan Cache

SQL Server lưu giữ các kế hoạch thực thi truy vấn (execution plans) trong bộ nhớ đệm (plan cache). Và kế hoạch này chứa các dấu hiệu mà query hint để lại trong XML. Điều này tạo ra một cách tiếp cận tốt hơn để “bóc tách” các truy vấn sử dụng hint mà không cần phải dò text thủ công.

Cách Làm:

Bạn kết hợp các Dynamic Management View/Function sau:

  • sys.dm_exec_cached_plans
  • sys.dm_exec_sql_text
  • sys.dm_exec_text_query_plan

Để lấy ra cả query text và kế hoạch dạng text bình thường (không phải XML), giúp bạn search dễ hơn:

SELECT t.[text], qp.query_plan
FROM sys.dm_exec_cached_plans AS p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
CROSS APPLY sys.dm_exec_text_query_plan(p.plan_handle, 0, -1) AS qp;

✔ Khi xem qua query plan, bạn sẽ thấy các dấu hiệu của hints như:

  • <QueryPlan NonParallelPlanReason=”MaxDOPSetToOne”> → dùng OPTION (MAXDOP 1)
  • <IndexScan ForceScan=”1”> → dùng WITH (FORCESCAN)
  • <IndexScan NoExpandHint=”1”> → dùng WITH (NOEXPAND)
  • <IndexScan … ForceSeek=”1”> → dùng WITH (FORCESEEK)

Ví Dụ Cụ Thể

Đoạn SQL dưới đây sẽ giúp bạn lọc ra tất cả các query cache đang sử dụng một số hints phổ biến:

SELECT
  [Query] = t.[text], 
  [Database] = DB_NAME(t.dbid),
  qp.query_plan,
  [ForceSeek]  = CASE WHEN qp.query_plan LIKE '%ForceSeek="1"%'
                      THEN 1 ELSE 0 END,
  [ForceScan]  = CASE WHEN qp.query_plan LIKE '%ForceScan="1"%'
                      THEN 1 ELSE 0 END,
  [NoExpand]   = CASE WHEN qp.query_plan LIKE '%NoExpandHint="1"%'
                      THEN 1 ELSE 0 END,
  [NoLock]     = CASE WHEN UPPER(t.[text]) LIKE '%NOLOCK%' 
                      THEN 1 ELSE 0 END,
  [MaxDop]     = CASE WHEN qp.query_plan LIKE '%"MaxDopSet%' 
                      AND UPPER(t.[text]) LIKE '%MAXDOP%'
                      THEN 1 ELSE 0 END
FROM
  sys.dm_exec_cached_plans p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) t
CROSS APPLY sys.dm_exec_text_query_plan(p.plan_handle,0,-1) qp
WHERE
  t.[text] NOT LIKE '%dm_exec_cached_plans%'
  AND (
        qp.query_plan LIKE '%ForceSeek="1"%'
     OR qp.query_plan LIKE '%ForceScan="1"%'
     OR qp.query_plan LIKE '%NoExpandHint="1"%'
     OR UPPER(t.[text]) LIKE '%NOLOCK%'
     OR (qp.query_plan LIKE '%"MaxDopSet%' AND UPPER(t.[text]) LIKE '%MAXDOP%')
      );

Kết quả trả về sẽ liệt kê query + database + cờ đánh dấu hint tương ứng.

⚠ Những Lưu Ý Quan Trọng

Một số điểm bạn cần biết khi sử dụng cách này:

❗ 1. Không Phải Hints Nào Cũng Có Trong Plan

Một vài hints như:

  • OPTION (FORCE ORDER)
  • OPTION (FAST n)
  • Join hints như INNER LOOP JOIN
    … không thể tìm qua plan XML nên vẫn phải parse query text nếu muốn phát hiện.

❗ 2. Plan Cache Không Phải Luôn Đầy Đủ

  • Sau khi restart SQL Server hoặc chạy DBCC FREEPROCCACHE, cache sẽ bị xóa.
  • Truy vấn chạy chỉ 1 lần và không nằm trong cache sẽ bị bỏ qua.

???? Do đó nếu bạn cần theo dõi lâu dài, hãy chạy script này định kỳ và lưu lại kết quả.

❗ 3. Hints Như OPTION(RECOMPILE) Không Tồn Tại Trong Cache

  • Những hints này khiến truy vấn không cache, nên cách này sẽ không tìm được.

Ưu Nhược Điểm Của Phương Pháp Này

⭐ Ưu Điểm

✔ Tốn ít tài nguyên hơn so với trace/profiler
✔ Ít false-positive hơn search text thủ công
✔ Có thể lọc nhanh các truy vấn bất thường để audit hoặc tối ưu

⚠ Nhược Điểm

❌ Không bắt được tất cả hints
❌ Cần chạy định kỳ để cập nhật
❌ Cần kết hợp với parsing text nếu muốn “gần như 100% bao phủ”

Kết Luận Cho DBA

Nếu bạn muốn audit những truy vấn dùng hint trong SQL Server một cách nhanh và chính xác, thì parsing plan cache là cách hiệu quả hơn là dò text thủ công hay trace nặng máy.

Với cách này, bạn có thể:

  • Tìm được các query dùng FORCESEEK, NOLOCK, MAXDOP…
  • Giảm sai lệch do false-positive
  • Tạo dashboard/alert để tối ưu hệ thống

Quan trọng nhất là bạn phải hiểu cả giới hạn của plan cache và cần kết hợp với các kỹ thuật khác để có cái nhìn đầy đủ nhất về workload trên server.