Blog

Một khi đã hiểu cách SQL Server thực thi một câu truy vấn như thế nào cũng là lúc trong đầu bạn xuất hiện những ý tưởng như thay thế execution plan operator này bằng cái khác, đảo lộn thứ tự vài operators hoặc 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. Bạn có quyền làm những việc ấy, đặc biệt nếu bạn hiểu rõ các đặc tính dữ liệu trong cơ sở dữ liệu của bạn và tình huống cụ thể của câu truy vấn đang gặp phải thì bạn có thể can thiệp vào việc SQL Server xây dựng query plan để đạt được hình dáng mong muố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. các bạn có thể tìm thấy đầy đủ document từ Microsoft theo link này, có rất nhiều thứ trong đó và bạn sẽ cần nhiều thời gian để đọc và thử nghiệm từng tình huống. Ở bài viết này mình 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.

Chúng ta sẽ dùng hai câu lệnh đếm sao đề cập ở những bài trước để demo việc làm sao thay đổi hình dáng query plan của câu truy vấn này giống với câu truy vấn kia và xem tốc độ truy vấn có thay đổi theo hay không. Hai câu truy vấn này sử dụng database stackoverflow, nếu bạn chưa có sẵn thì hãy theo bài hướng dẫn cài đặt ở đây. 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.

SET STATISTICS IO, TIME ON

--- điều kiện tìm kiếm ở mệnh đề HAVING (câu truy vấn lọc muộn)
SELECT location, COUNT(*) AS cnt
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 cnt
OPTION (MAXDOP 1)
 
--- điều kiện tìm kiếm ở mệnh đề WHERE (câu truy vấn lọc sớm)
SELECT location, COUNT(*) AS cnt
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 cnt
OPTION (MAXDOP 1)

SET STATISTICS TIME, IO OFF

Kết quả STATISTICS IO, TIME trên máy của mình như sau:

(8 rows affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 3, logical reads 192, physical reads 9, read-ahead reads 183, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Users'. Scan count 1, logical reads 7405, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Posts'. Scan count 1, logical reads 800856, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row affected)

 SQL Server Execution Times:
   CPU time = 1719 ms,  elapsed time = 1796 ms.

(8 rows affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Posts'. Scan count 1, logical reads 800856, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Users'. Scan count 1, logical reads 7405, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row affected)

 SQL Server Execution Times:
   CPU time = 1281 ms,  elapsed time = 1277 ms.

Để thuận tiện và dễ liên tưởng hơn trong việc đề cập đến hai câu truy vấn trên ta sẽ gọi câu truy vấn có điều kiện tìm kiếm Location LIKE ‘%vietnam%’ ở mệnh đề HAVING là câu truy vấn lọc muộn còn câu truy vấn dưới có điều kiện tìm kiếm Location LIKE ‘%vietnam%’ ở mệnh đề WHERE là câu truy vấn lọc sớm như hai dòng highlight 3 & 14 trong script đầu. Kết quả cho thấy câu truy vấn lọc muộn tốn nhiều IO hơn, CPU Time và elapsed time cũng cao hơn nên nó rõ ràng chạy chậm hơn câu truy vấn lọc sớm.

Và actual plan của hai câu truy vấn

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 lọc muộn truy cập bảng Posts trước và có thêm Hash Aggregate operator, trong khi câu truy vấn lọc sớm 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 lọc muộn

Theo hình 1, execution plan phía trên là của câu truy vấn lọc muộn. 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 lọc sớm 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 lọc muộn ở 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

--- điều kiện tìm kiếm ở mệnh đề HAVING (câu truy vấn lọc muộn)
SELECT location, COUNT(*) AS cnt
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 cnt
OPTION (MAXDOP 1, FORCE ORDER)
 
--- điều kiện tìm kiếm ở mệnh đề WHERE (câu truy vấn lọc sớm)
SELECT location, COUNT(*) AS cnt
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 cnt
OPTION (MAXDOP 1)

SET STATISTICS TIME, IO OFF

Query hints được sử dụng ở mệnh đề OPTION như dòng 12 được highlight trong script trên, và kết quả thu được như hình 2 bên dưới.

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 lọc muộn 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 lọc sớm ở dưới (actual number of row 667).

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 lọc muộn 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 lọc sớm

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.

SET STATISTICS IO, TIME ON

--- điều kiện tìm kiếm ở mệnh đề HAVING (câu truy vấn lọc muộn)
SELECT location, COUNT(*) AS cnt
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 cnt
OPTION (MAXDOP 1)
 
--- điều kiện tìm kiếm ở mệnh đề WHERE (câu truy vấn lọc sớm)
SELECT location, COUNT(*) AS cnt
FROM Posts p
    INNER JOIN Users u ON u.Id = p.OwnerUserId
WHERE PostTypeId = 2 AND location LIKE '%vietnam%'
GROUP BY location
HAVING COUNT(*) > 10
ORDER BY cnt
OPTION (MAXDOP 1, FORCE ORDER)

SET STATISTICS TIME, IO OFF

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.

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.

SET STATISTICS IO, TIME ON

--- điều kiện tìm kiếm ở mệnh đề HAVING (câu truy vấn lọc muộn)
SELECT location, COUNT(*) AS cnt
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 cnt
OPTION (MAXDOP 1)
 
--- điều kiện tìm kiếm ở mệnh đề WHERE (câu truy vấn lọc sớm)
SELECT location, SUM(totalPosts) AS cnt
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 cnt
OPTION (MAXDOP 1, FORCE ORDER)

SET STATISTICS TIME, IO OFF

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.

Bên cạnh sự khác nhau về các operators mới phát sinh còn có sự khác nhau ở Hash join mà chúng ta cần quan tâm. Phép join ở câu truy vấn lọc sớm có cảnh bảo (warning) trong khi ở trên không có (chú ý, mình đang đề cập operator join). Warning này xuất hiện là vì lượng memory cấp phát cho operator join này không đủ nên SQL Server cần dùng thêm không gian lưu trữ của tempdb (trên disk) để xử lý data trong quá trình truy vấn. Nếu không có warning nghĩa là SQL Server có đủ memory để thực hiện và hiển nhiên tốc độ sẽ nhanh hơn vì không cần lưu trữ trên disk (buộc phải ghi xuống disk và đọc lên lại).

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

Để khắc phục tình trạng warning như trên chúng ta cần giúp SQL Server ước lượng chính xác lượng memory cần dùng, bằng cách cung cấp đầy đủ và đúng đắn thống kê data trong bảng Posts này cụ thể hơn là statistics trên cột OwnerUserId. Mình sẽ nói về SQL Server statistics ở bài sau, lúc này 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. 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.

USE StackOverflow2010
GO
UPDATE STATISTICS Posts WITH FULLSCAN

GO

SET STATISTICS IO, TIME ON

--- điều kiện tìm kiếm ở mệnh đề HAVING (câu truy vấn lọc muộn)
SELECT location, COUNT(*) AS cnt
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 cnt
OPTION (MAXDOP 1, RECOMPILE)
 
--- điều kiện tìm kiếm ở mệnh đề WHERE (câu truy vấn lọc sớm)
SELECT location, SUM(totalPosts) AS cnt
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 cnt
OPTION (MAXDOP 1, FORCE ORDER, RECOMPILE)

SET STATISTICS TIME, IO OFF

Để đảm bảo SQL Server không sử dụng lại query plan cũ (đã estimated lượng memory không đủ) mình đã thêm RECOMPILE vào mệnh đề OPTION để SQL Server xây dựng lại query plan mới với statistics mới. Hai câu truy vấn trên giờ có query plan như sau.

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.

SET STATISTICS IO, TIME ON

--- điều kiện tìm kiếm ở mệnh đề HAVING (câu truy vấn lọc muộn)
SELECT location, COUNT(*) AS cnt
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 cnt
OPTION (MAXDOP 1, RECOMPILE)
 
--- điều kiện tìm kiếm ở mệnh đề WHERE (câu truy vấn lọc sớm)
SELECT location, SUM(totalPosts) AS cnt
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 cnt
OPTION (MAXDOP 1, FORCE ORDER, RECOMPILE, HASH GROUP)

SET STATISTICS TIME, IO OFF

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.

So sánh kết quả giữa các lần biến đổi execution plan

Bảng dưới đây so sánh các thông số về logical reads, CPU time, elapsed time của từng bước thay đổi plan như trên. Bởi vì mỗi lần chạy có thể có elapsed time khác nhau (tùy vào tài nguyên của máy tính) nên mỗi bước mình chạy vài lần để lấy giá trị ổn định nhất để các bạn tham khảo.

Nhìn chung kết quả cuối cùng của câu truy vấn lọc sớm 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ài viết hôm nay khá là dài và cũng chứa rất nhiều thông tin để chúng ta trau dồi vào kĩ năng viết câu truy vấn của mình. 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.