Blog

SQL Server execution plan là gì? Chúng ta đã biết thứ tự thực hiện câu truy vấn ở mức luận lý, vậy còn ở mức vật lý thì sao? Thứ tự các bước xử lý data của một câu truy vấn ở mức vật lý chính là từng bước SQL Server thực thi như thế nào để hoàn thành yêu cầu và trả dữ liệu về cho người dùng. Hiểu các bước thực thi câu truy vấn ở mức vật lý sẽ giúp bạn nhận ra những điểm chưa tối ưu cũng như hiểu rõ tác hại của nó đối với môi trường SQL Server nơi có nhiều câu truy vấn khác đang được thực thi. Những điều này đều được thể hiện trong execution plan, hôm nay chúng ta cùng tìm hiểu các kiến thức cơ bản về execution plan, cách thu thập, đọc hiểu để có thể quản trị SQL Server tốt hơn.

Execution plan là gì?

Execution plan giống như một bản hướng dẫn chi tiết những việc phải làm để hoàn thành một câu lệnh truy vấn. Khi bạn gửi một câu lệnh SELECT đến SQL Server, sẽ có nhiều cách thực hiện những công việc cần thiết để cho ra kết quả mong muốn và Query Optimizer chịu trách nhiệm cho việc chọn cách truy vấn thế nào cho phù hợp với những tham số đầu vào. Query Optimizer sẽ sử dụng các thông tin cần thiết như những indexes nào trên bảng đó có thể dùng, statistics của các cột liên quan như thế nào, có constraint gì không,…để xây dựng những plan khả thi và chọn một trong số đó mà nó cảm thấy đủ tốt trong một khoảng thời gian hữu hạn. Kết quả của quá trình này tạo ra compiled plan, tiếp theo SQL Server sẽ làm theo những bước mô tả trong plan này để đạt được kết quả mong muốn, execution plan là run time objects của compiled plan (mặc dù hai đối tượng compiled plan và execution plan là khác nhau nhưng khi đề cập đến một trong hai ta đều hiểu đang ám chỉ đến execution plan). Việc tạo ra các compiled plan này khá tốn chi phí nên nó sẽ được lưu lại để sử dụng cho những lần sau.

Hình dạng execution plan

Nói nãy giờ vậy execution plan trông như như thế nào? Bạn có thể xem execution plan dưới ba dạng là text, XML hoặc graphic, chú ý rằng user của bạn cần permission SHOWPLAN mới có thể xem được. Hai loại đầu có thể lấy được như câu lệnh dưới đây.

USE StackOverflow2010
GO

-- execution plan dạng text
SET SHOWPLAN_TEXT ON
GO
--- điều kiện tìm kiếm ở mệnh đề HAVING
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)
GO
SET SHOWPLAN_TEXT OFF

GO
--- execution plan dạng XML
SET SHOWPLAN_XML ON
GO
--- điều kiện tìm kiếm ở mệnh đề HAVING
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)
GO
SET SHOWPLAN_XML OFF

Các bạn cần execute toàn bộ script trên để thấy kết quả. Câu lệnh SHOWPLAN_TEXT sẽ lấy ra execution plan dạng text và SHOWPLAN_XML để lấy dạng XML. Chú ý rằng SSMS 18.5 sẽ tự chuyển qua dạng graphical execution plan nếu bạn click vào kết quả XML, sau đó bạn có thể chuột phải và chọn “Show Execution Plan XML” để xem dưới dạng XML.

Mặc dù XML hơi khó đọc nhưng nó cung cấp đầy đủ thông tin nhất trong ba dạng. Ở bài này chúng ta sẽ tập trung làm quen với graphical execution plan, bạn có thể nhấn phím tổ hợp Ctrl + M trên SSMS hoặc click vào biểu tượng “Include Actual Execution Plan” như hình bên dưới để lấy graphical execution plan (chú ý, các bạn mở cửa sổ mới với câu truy vấn trong hình bên dưới)

Sau khi bạn nhấn F5 để chạy câu truy vấn, kết quả trả về bên cạnh tab Messages giờ đây có thêm tab Execution Plan, click mở tab ta sẽ có kết quả như hình sau.

Có rất nhiều thông tin từ đây, bạn có thể rê chuột vào các biểu tượng sẽ thấy tool tip hiện lên với hàng loạt giá trị cùng với ý nghĩa của chúng. Hãy tạm gác lại việc tìm hiểu những con số này sau, cho tới bước này chúng ta đã thấy ba dạng khác nhau của execution plan và cách thu thập chúng.

Estimated và actual plan

Bạn có chú ý ở script đầu tiên khi nhấn F5 kết quả trả về hai execution plan dạng text và XML ngay tức thì, còn ở script tiếp theo bạn phải đợi vài giây mới có kết quả truy vấn cùng với execution plan hay không? Sự khác biệt về thời gian này cũng là hai loại execution plan mà ta muốn lấy, một là estimated plan và hai là actual plan. Loại đầu tiên giống như tên gọi, SQL Server chỉ cần xây dựng execution plan đó rồi trả về không cần phải thực thi câu truy vấn. Còn loại thứ hai (khi nhấn Ctrl + M) gọi là actual plan vì SQL Server phải thực thi xong câu truy vấn mới trả ra chúng. Hai loại này giống nhau về hình dáng, chỉ khác chỗ actual plan có thêm các thông tin liên quan của lần thực thi đó như số rows thực tế (actual number of rows) là bao nhiêu, memory đã cấp phát bao nhiêu. Dạng graphical cũng có estimated plan, đó là biểu tượng thứ 3 bên phải của “Include Actual Execution Plan” , hoặc các bạn có thể bấm phím tổ hợp Ctrl + L để thấy SSMS show ra estimated plan ngay lập tức. Hình dưới đây chỉ ra một vài thông tin cơ bản chỉ có trên actual plan, các bạn hãy kiểm tra xem estimated plan có những thông số này không nhé.

Giá trị đầu tiên chúng ta thấy là thời gian thực thi cùng số lượng rows của từng operator, lượng memory cấp phát cho execution plan này, hoặc một số thông tin khác khi các bạn rê chuột vào từng operator sẽ xuất hiện cùng tool tip, và những cảnh báo của các operators có đính hình tam giác chấm than màu vàng. Đây là những thông tin các bạn sẽ không tìm thấy trên estimated plan.

Plan cache là gì?

Như mình có để cập ở đầu bài viết, vì chi phí mỗi khi tạo ra compiled plan khá là tốn kém nên SQL Server sẽ lưu đối tượng này vào plan cache để dùng lại cho những lần chạy sau của cùng câu lệnh truy vấn đó. Vì compiled plan này được SQL Server lưu vào plan cache trước khi thật sự thực thi nên nó chính là estimated plan. Đó là lý do khi bạn lấy một plan từ plan cache sẽ không có các thông số như actual number of rows, memory grant. Câu truy vấn sau dùng để lấy execution plan từ cache.

SELECT bucketid,usecounts,size_in_bytes,cacheobjtype,plan_handle,t.text, p.query_plan
FROM sys.dm_exec_cached_plans c
	cross apply sys.dm_exec_sql_text(c.plan_handle) t
	cross apply sys.dm_exec_query_plan(c.plan_handle) p
WHERE t.text like '%Posts%'

Mỗi compiled plan sẽ có một giá trị plan_handle và duy nhất trong plan cache, có thể xem như là ID của compiled plan. Dùng giá trị plan_handle này để lấy ra XML plan từ DMF sys.dm_exec_query_plan. Tuy nhiên, với sự hỗ trợ của SSMS 18.5 bạn chỉ cần click vào nó sẽ convert sang graphical execution plan để bạn dễ nhìn hơn, hoặc bạn có thể tự lưu nội dung XML này thành file có đuôi .sqlplan rồi mở bằng SSMS cũng sẽ nhận được graphical execution plan. Các bạn có thể kiểm tra xem có tồn tại các thông số liên quan đến actual hay không để thấy nó là một estimated plan.

Đọc hiểu execution plan như thế nào?

Hãy nhìn lại graphical execution plan ở trên, nó gồm nhiều thao tác thực hiện các công việc khác nhau và liên kết với nhau thông qua các mũi tên tạo thành một chuỗi gọi là query plan. Trong chuỗi này, mỗi thao tác được gọi là một operator (hoặc iterator) đọc data từ operator kế nó và thực hiện công việc cần thiết sau đó trả về cho operator kế tiếp. Tên của mỗi loại operator thể hiện công việc mà nó phải làm, như quét (scanning) data của bảng, cập nhật (updating) data trong bảng, lọc hoặc tổng hợp (filtering or aggregating) data. Mũi tên có hướng từ phải qua trái chính là chiều di chuyển của data trong query plan và cũng chính là chiều chúng ta đọc hiểu execution plan: từ phải qua trái, trên xuống dưới.

Với plan của chúng ta ở trên, đầu tiên là scan clustered index của bảng Posts, data được trả về cho operator Hash Match (aggregate), thằng này sẽ tổng hợp data theo cột OwnerUserId để có tổng số post theo cột này. Tiếp đến data sẽ được truyền cho Hash Match (Inner Join), operator sẽ xây dựng bảng băm theo key OwnerUserId. Đây là một join operator, thường được gọi là hash join, pha đầu xây dựng bảng băm, pha hai sẽ nhận data từ operator scan clustered index bảng Users (cột Id và Location) và dò tìm những dòng có Id bằng với OwnerUserId. Kết quả của hash join này sẽ trả về cho operator kế tiếp, một Hash Match (aggregrate) khác. Operator này nhận data là các dòng dữ liệu có hai cột Location và tổng số posts (có từ hash match (aggregate) trước đó) rồi thực hiện việc tổng hợp data theo Location và cộng tổng số posts lại với nhau. Kế tiếp nữa là operator compute scalar nhận tập dữ liệu Location với tổng posts rồi convert số tổng này sang kiểu integer. Tập dữ liệu này tiếp đến được lọc theo điều kiện Location like ‘%vietnam%’ và tổng posts > 10 ở operator filter. Operator kế tiếp làm nhiệm vụ sắp xếp các dòng dữ liệu theo tiêu chí tăng dần tổng số posts và cuối cùng trả về người dùng bởi operator select.

Đây là một query plan tương đối phức tạp nhưng bạn có thể đã hoặc sẽ gặp nhiều plan phức tạp hơn nữa, việc đọc hiểu query plan sẽ hiệu quả hơn nếu các bạn chú ý thông tin từng operator bằng cách rê chuột lên operator đó hoặc click chuột phải view properties của nó. Một vài chú ý cơ bản để dễ tiếp cận việc đọc hiểu plan như sau
– Hiểu vai trò của operator đó làm gì, và danh sách các cột output
– Xem properties để thấy cột mới được tạo ra theo biểu thức nào – mục defined values (bạn có thể xem lại properties của compute scalar hoặc hash match aggregate operator trong query plan)
– Mũi tên càng dày chứng tỏ lượng data phải đọc của operator đó càng nhiều, nếu có thể làm mũi tên trở nên mỏng càng sớm càng tốt
– Chú ý những cảnh báo, là những operator có icon tam giác màu vàng chứa dấu chấm than.

Nếu các bạn rê chuột lên operator select sẽ thấy giá trị Estimated Subtree Cost, đây là giá trị SQL Server ước lượng chi phí cho một query plan theo đơn vị tính của riêng nó. Estimated subtree cost là tổng chi phí của những operators phía trước nó (theo hướng data flow từ phải qua trái), con số này được dùng để so sánh giữa các query plan với nhau hoặc để SQL Server quyết định có nên dùng parallel query plan hay không (plan chúng ta đang xét là serial).

Ngoài những operators ở trên SQL Server còn có rất rất nhiều các operators khác để phục vụ cho những nhu cầu khác nhau trong việc thao tác, xử lý dữ liệu. Việc tìm hiểu ý nghĩa của những operators này sẽ giúp cho kĩ năng đọc hiểu execution plan của bạn ngày càng hiệu quả và thuần thục. Mình sẽ không trình bày ở bài viết này thay vào đó sẽ gửi link để các bạn tham khảo thêm.

Vậy là chúng ta đã tìm hiểu những gì cơ bản nhất về execution plan trong SQL Server, nó được tạo ra ở bước nào, lưu trữ ở đâu, có những loại nào, làm thế nào để xem execution plan của một câu truy vấn và cách đọc hiểu chúng như thế nào. Đây là một tiền đề để các bạn bước chân vào con đường tối ưu truy vấn và tiếp đến là tối ưu SQL Server.

Nguồn tham khảo:
1. SQL Server Execution Plan, Third Edition, by Grant Fritchey.
2. Inside the SQL Server Query Optimizer, free PDF download, by Benjamin Nevarez (hoặc bài viết tóm gọn ở link này).
3. Showplan operators – Fabiano Amorim (loạt bài viết khá hay về các operators trong execution plan).
4. sqlserverfast.com (rất chi tiết về cách làm việc của các operators).