Làm việc với Query Hint để tối ưu Execution Plan trong SQL Server

Khi đã hiểu cách SQL Server thực thi một câu truy vấn như thế nào có thể bạn sẽ có ý tưởng thay đổi hình dáng của execution plan với mong muốn câu truy vấn đó sẽ chạy nhanh hơn.

T-SQL có hỗ trợ những cú pháp để người viết có thể gợi ý SQL Server xây dựng query plan theo ý muốn và thường được gọi là query hint. Ở bài viết này Stanford sẽ demo một vài tình huống để các bạn thấy cách tác động vào query plan như thế nào. Có một điều các bạn cần ghi nhớ rằng trong hầu hết các tình huống SQL Server đều làm tốt việc xây dựng query plan, tức là tạo ra execution plan tương đối hiệu quả. Nếu có tình huống nào đó SQL Server chưa làm tốt thì nhiệm vụ của chúng ta cần hiểu rõ lý do tại sao và giúp SQL Server có đầy đủ thông tin hơn để tự nó tạo ra query plan đủ tốt. Việc can thiệp bằng những query hints như sau dành cho những người hiểu rõ tình huống đang gặp và không có lựa chọn nào khác, vì bất kì hệ thống nào về lâu dài dữ liệu cũng sẽ thay đổi và việc dùng query hint sẽ giới hạn khả năng thay đổi theo của query plan.

Hai câu truy vấn này sử dụng database stackoverflow bạn có thể tải về tại đây nếu chưa có. Nếu bạn đã có rồi thì hãy xóa những statistics tự tạo bởi SQL Server để kết quả trên máy của bạn giống những gì mình demo ở bài này. Khi mọi thứ đã sẵn sàng chúng ta bắt đầu hành trình với đoạn T-SQL dưới đây, vì chúng ta mới xóa vài statistics trên bảng Posts và Users nên hãy chạy hai câu truy vấn dưới đây vài lần, sau đó hãy Include Actual Execution Plan (Ctrl + M) và chạy lần nữa.

---Câu 1: Lọc ở mệnh đề where
SELECT location, COUNT(*) AS total
FROM Users u
    INNER JOIN Posts p ON u.Id = p.OwnerUserId
WHERE PostTypeId = 2 AND location LIKE '%vietnam%'
GROUP BY location
HAVING COUNT(*) > 10
ORDER BY total
OPTION (MAXDOP 1);
go
--Câu 2: Lọc ở mệnh đề having
SELECT location, COUNT(*) AS total
FROM Users u
    INNER JOIN Posts p ON u.Id = p.OwnerUserId
WHERE PostTypeId = 2
GROUP BY location
HAVING location LIKE '%vietnam%'
    AND COUNT(*) > 10
ORDER BY total
OPTION (MAXDOP 1);

Kết quả STATISTICS IO, TIME trên máy của mình với bảng so sánh hiệu suất như sau:

 

 Câu lệnh Logical Reads CPU Time (ms)   Elapsed Time (ms)
 Câu lệnh 1  800856  2610  3189
 Câu lệnh 2  800856 2953 3507
Và đây là kết quả của Actual Plan của 2 câu lệnh truy vấn:



Hình 1: Execution Plan ban đầu của 2 câu lệnh

Về execution plan của hai câu truy vấn, chúng giống nhau từ phần join hai bảng trở về sau (hướng từ phải sang trái) như khung màu đỏ trong hình. Trước đó, thứ tự truy cập bảng của chúng đảo ngược nhau. Câu truy vấn 2 truy cập bảng Posts trước và có thêm Hash Aggregate operator, trong khi câu truy vấn 1 truy cập bảng Users trước. Có phải chính vì sự khác biệt về thứ tự truy cập bảng (hoặc hình dáng execution plan) tạo nên sự khác nhau về tốc độ truy vấn? Hay là do điều kiện lọc nằm ở hai mệnh đề khác nhau mới là nguyên nhân chính? Để có thể trả lời câu hỏi này chúng ta hãy đưa chúng về cùng một hình dáng execution plan rồi so sánh các giá trị logical reads, CPU Time và Elapsed time thử xem cuối cùng là do đâu.

Thay đổi execution plan của câu truy vấn 2

Theo hình 1, execution plan phía trên là của câu truy vấn 2. Nó bắt đầu bằng việc scan bảng Posts trước rồi mới join với bảng Users, ngược với câu truy vấn 1 là scan bảng Users trước. Để thay đổi thứ tự này chúng ta có thể sử dụng query hint FORCE ORDER. Cú pháp này dùng để ép SQL Server truy xuất data các bảng theo đúng thứ tự bạn chỉ định trong câu T-SQL bạn viết. Trong câu truy vấn 2 ở mệnh đề FROM bạn chỉ định bảng Users trước, rồi mới tới bảng Posts nhưng SQL Server đã thay đổi theo ý của nó như ta thấy trong execution plan ở hình 1. Bây giờ bạn thêm query hint như đoạn code bên dưới để xem kết quả như thế nào.

SET STATISTICS IO, TIME ON
go
---Câu 1: Lọc ở mệnh đề where
SELECT location, COUNT(*) AS total
FROM Users u
    INNER JOIN Posts p ON u.Id = p.OwnerUserId
WHERE PostTypeId = 2 AND location LIKE '%vietnam%'
GROUP BY location
HAVING COUNT(*) > 10
ORDER BY total
OPTION (MAXDOP 1);
go
--Câu 2: Lọc ở mệnh đề having
SELECT location, SUM(totalPosts) AS total
FROM (  SELECT OwnerUserId, COUNT(*) AS totalPosts
        FROM Posts
        WHERE PostTypeId = 2
        GROUP BY OwnerUserId
    ) p
    INNER JOIN Users u ON u.Id = p.OwnerUserId
WHERE location LIKE '%vietnam%'
GROUP BY location
HAVING SUM(totalPosts) > 10
ORDER BY total
OPTION (MAXDOP 1, FORCE ORDER);
Query hints được sử dụng ở mệnh đề OPTION như trong câu lệnh 2 trong script trên, và kết quả thu được như hình 2 bên dưới.

 

Hình 2: sử dụng query hints FORCE ORDER cho câu truy vấn 2.

Hai execution plan đã có hình dáng giống nhau hoàn toàn, điều đó có nghĩa các bước SQL Server thực hiện truy vấn cho hai câu trên tương đương nhau. Nếu có khác biệt ở hai execution plan trên thì đó là kích thước của mũi tên sau Hash join, mũi tên dày hơn ở câu truy vấn 2 cho thấy lượng data Hash join đẩy qua operator tiếp theo (actual number of row 2582181) nhiều hơn so với câu truy vấn 1 ở dưới (actual number of row 667).

Câu lệnh Logical Reads CPU Time (ms) Elapsed Time (ms)
Câu lệnh 1 800856 2765 3245
Câu lệnh 2 800856 3812 4434

Hiệu suất truy vấn với execution plan giống nhau
Bảng trên cho thấy lượng logical reads là như nhau nhưng CPU time gần như gấp đôi. Vậy dù cho hình dáng execution plan giống nhau nhưng với việc áp dụng điều kiện của câu lệnh 2 data phải di chuyển qua nhiều operators hơn mới đến được filter operators thành ra chi phí cũng tăng lên nhiều. Các bạn có thể thấy điều này ở các operators Hash join, Hash Match (aggregate), Compute Scalar. Phải sau khi qua Filter kích thước mũi tên ở hai query plan mới giống nhau.

Thay đổi execution plan của câu truy vấn 1

Sử dụng query hint: FORCE ORDER
Từ kết quả của execution plan như hình 1 ở dưới, chúng ta cũng sẽ sử dụng FORCE ORDER để biến đổi hình dáng của nó đúng thứ tự như câu truy vấn phía trên là truy cập bảng Posts trước, sau đó mới đến bảng Users. Lần này chúng ta cần thay đổi nội dung T-SQL để chỉ định lại thứ tự các bảng.

go
---Câu 1: Lọc ở mệnh đề where
SELECT location, COUNT(*) AS total
FROM Users u
    INNER JOIN Posts p ON u.Id = p.OwnerUserId
WHERE PostTypeId = 2 AND location LIKE '%vietnam%'
GROUP BY location
HAVING COUNT(*) > 10
ORDER BY total
OPTION (MAXDOP 1, FORCE ORDER);
go
--Câu 2: Lọc ở mệnh đề having
SELECT location, COUNT(*) AS total
FROM Users u
    INNER JOIN Posts p ON u.Id = p.OwnerUserId
WHERE PostTypeId = 2
GROUP BY location
HAVING location LIKE '%vietnam%'
    AND COUNT(*) > 10
ORDER BY total
OPTION (MAXDOP 1);
Kết quả tương đối giống nhau, truy suất bảng đúng thứ tự mong muốn nhưng câu truy vấn lọc sớm không có operator Hash Match (aggregate) giống như khung chữ nhật tô đỏ trên hình.


Hình 3: sử dụng query hints FORCE ORDER cho câu truy vấn 1

Viết lại câu truy vấn để tạo thêm Hash Match (aggregate)

Chúng ta cần làm thế nào đó để có operator Hash Match (aggregate) này. Vì operator này tính tổng số posts theo OwnerUserId nên ta thử trực tiếp tính toán ra giá trị này thay vì để SQL Server tự chọn (và nó đã không), kết quả của việc tính toán này là một derived table sau đó tiếp tục join với bảng Users.

--Viết lại câu truy vấn 1 để tạo thêm Hash Match (aggregate)
go
--Câu 1: Lọc ở mệnh đề where
SELECT location, SUM(totalPosts) AS total
FROM (  SELECT OwnerUserId, COUNT(*) AS totalPosts
        FROM Posts
        WHERE PostTypeId = 2
        GROUP BY OwnerUserId
    ) p
    INNER JOIN Users u ON u.Id = p.OwnerUserId
WHERE location LIKE '%vietnam%'
GROUP BY location
HAVING SUM(totalPosts) > 10
ORDER BY total
OPTION (MAXDOP 1, FORCE ORDER, RECOMPILE);
 
go
--Câu 2: Lọc ở mệnh đề having
SELECT location, COUNT(*) AS total
FROM Users u
    INNER JOIN Posts p ON u.Id = p.OwnerUserId
WHERE PostTypeId = 2
GROUP BY location
HAVING location LIKE '%vietnam%'
    AND COUNT(*) > 10
ORDER BY total
OPTION (MAXDOP 1, RECOMPILE);
Execution plan của lần sửa đổi này đã có Hash Match (aggregate), tuy nhiên nó tặng thêm cái Compute Scalar operator và có thêm sự biến đổi sau khi join. SQL Server không dùng Hash Match nữa mà sử dụng Stream Aggregate nên cần thêm Sort operator trước đó. Những operators phát sinh này được đánh dấu bằng khung highlight màu đỏ như hình dưới.


Hình 4: Viết lại câu truy vấn để có operator Hash Match (Aggregate)

Cập nhật các statistics và loại bỏ Compute Scalar operator

Bạn chỉ cần chạy câu lệnh cập nhật tất cả statistics có trên bảng Posts với option FULLSCAN (quét qua hết data của bảng khi làm thống kê, thay vì mặc định chỉ là một phần) như script ở dưới để loại bỏ warning vì không đủ bộ nhớ mục hash match. Thêm nữa, mặc dù operator Compute Scalar trong query plan trên có chi phí rất thấp, hầu như không đáng kể nhưng để làm cho hai query plan giống nhau hoàn toàn ta có thể loại bỏ operator này bằng cách thay hàm COUNT() trong derived table bằng hàm SUM(). Hãy chạy update statistics riêng với hai câu truy vấn.

--Cập nhật các statistics và loại bỏ Compute Scalar operator
GO
UPDATE STATISTICS Posts WITH FULLSCAN
  
GO
  
SET STATISTICS IO, TIME ON
---Câu lệnh 2: Lọc ở having
go
SELECT location, COUNT(*) AS total
FROM Users u
    INNER JOIN Posts p ON u.Id = p.OwnerUserId
WHERE PostTypeId = 2
GROUP BY location
HAVING location LIKE '%vietnam%'
    AND COUNT(*) > 10
ORDER BY total
OPTION (MAXDOP 1, RECOMPILE);
go
--Câu lệnh 1: Lọc ở where
SELECT location, SUM(totalPosts) AS total
FROM (  SELECT OwnerUserId, SUM(1) AS totalPosts
        FROM Posts
        WHERE PostTypeId = 2
        GROUP BY OwnerUserId
    ) p
    INNER JOIN Users u ON u.Id = p.OwnerUserId
WHERE location LIKE '%vietnam%'
GROUP BY location
HAVING SUM(totalPosts) > 10
ORDER BY total
OPTION (MAXDOP 1, FORCE ORDER, RECOMPILE);

Vậy là chúng ta đã tới gần kết quả hơn một xíu trong việc biến đổi hai query plan trở nên giống nhau. Bây giờ chỉ còn lại Sort và Stream Aggregate operators như bạn thấy trên hình 5, loại chúng đi và thay bằng hash match aggregate là chúng giống nhau như đúc rồi.

Sử dụng query hint: Hash Group

Query hints HASH GROUP được dùng để bảo SQL Server hãy sử dụng hash match aggregate operator cho tất cả các hành động gôm nhóm trong câu truy vấn.

go
SELECT location, COUNT(*) AS total
FROM Users u
    INNER JOIN Posts p ON u.Id = p.OwnerUserId
WHERE PostTypeId = 2
GROUP BY location
HAVING location LIKE '%vietnam%'
    AND COUNT(*) > 10
ORDER BY total
OPTION (MAXDOP 1, RECOMPILE);
go
--Câu lệnh 1: Lọc ở where
SELECT location, SUM(totalPosts) AS total
FROM (  SELECT OwnerUserId, SUM(1) AS totalPosts
        FROM Posts
        WHERE PostTypeId = 2
        GROUP BY OwnerUserId
    ) p
    INNER JOIN Users u ON u.Id = p.OwnerUserId
WHERE location LIKE '%vietnam%'
GROUP BY location
HAVING SUM(totalPosts) > 10
ORDER BY total
OPTION (MAXDOP 1, FORCE ORDER, RECOMPILE, HASH GROUP);
Kết quả thu được thật đúng như ta mong đợi, query plan của câu truy vấn lọc sớm đã giống hoàn toàn query plan của câu tru vấn lọc muộn. Chúng ta đã thành công khi dần biến đổi thay thế các operators theo ý muốn của mình.


Kết quả so sánh của 2 câu lệnh:

Câu lệnh Logical Reads CPU Time (ms) Elapsed Time (ms)
Câu lệnh 1 800856 2860 3619
Câu lệnh 2 800856 3078 3639

Nhìn chung kết quả cuối cùng của câu truy vấn 1 không tốt bằng ban đầu mặc dù logical reads giống nhau. Điều này phần nào chứng minh rằng lựa chọn query plan ban đầu của SQL Server là tương đối ổn. Giống như mình đã nói lúc đầu, nếu khi phân tích execution plan và nhận ra có vấn đề về hiệu suất (performance issue) thì bạn hãy tìm hiểu nguyên nhân rồi giúp SQL Server khắc phục thiếu xót đó.

Bắt đầu bằng việc nhận dạng các operators trong execution plan và hiểu vai trò của chúng trong query, tiếp đến ta sử dụng query hints để điều khiển một hoặc một nhóm các operators thành một dạng khác. Cứ như vậy cho đến khi chúng ta có được query plan mong muốn. Công việc này lúc đầu hơi có chút khó khăn nhưng khi các bạn dành nhiều thời gian để tìm hiểu, phân tích và tối ưu hiệu suất truy vấn thì dần dần bạn sẽ thao tác dễ dàng hơn trong việc điều khiển execution plan.

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 hint, làm việc với execution plan