SQL Server execution plan là gì ? Chúng ta đã biết thứ tự triển khai câu truy vấn ở mức luận lý, vậy còn ở mức vật lý thì sao ? Thứ tự những bước giải quyết và 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 để triển khai xong nhu yếu và trả tài liệu về cho người dùng. Hiểu những 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õ tai hại của nó so với môi trường tự nhiên 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 bộc lộ trong execution plan, ngày hôm nay tất cả chúng ta cùng tìm hiểu và khám phá những kiến thức và kỹ năng cơ bản về execution plan, cách tích lũy, đọc hiểu để hoàn toàn 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 cụ thể những việc phải làm để hoàn thành xong 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 thi những việc làm thiết yếu để cho ra hiệu quả mong ước và Query Optimizer chịu nghĩa vụ và trách nhiệm cho việc chọn cách truy vấn thế nào cho tương thích với những tham số nguồn vào. Query Optimizer sẽ sử dụng những thông tin thiết yếu như những indexes nào trên bảng đó hoàn toàn có thể dùng, statistics của những cột tương quan như thế nào, có constraint gì không, … để thiết kế 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 chừng thời hạn hữu hạn. Kết quả của quy trình này tạo ra compiled plan, tiếp theo SQL Server sẽ làm theo những bước miêu tả trong plan này để đạt được tác dụng mong ước, execution plan là run time objects của compiled plan ( mặc dầu hai đối tượng người tiêu dù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 những compiled plan này khá tốn ngân sách nên nó sẽ được lưu lại để sử dụng cho những lần sau .
Quá trình xử lý một câu truy vấn
Hình dạng execution plan
Nói nãy giờ vậy execution plan trông như như thế nào ? Bạn hoàn toàn có thể xem execution plan dưới ba dạng là text, XML hoặc graphic, quan tâm rằng user của bạn cần permission SHOWPLAN mới hoàn toàn có thể xem được. Hai loại đầu hoàn toàn 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.
Execution plan dạng text (ở giữa)
Execution plan 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)
chọn show execution plan trên giao diện SSMS
Sau khi bạn nhấn F5 để chạy câu truy vấn, tác dụng trả về bên cạnh tab Messages giờ đây có thêm tab Execution Plan, click mở tab ta sẽ có hiệu quả như hình sau .
graphical execution plan
Có rất nhiều thông tin từ đây, bạn hoàn toàn có thể rê chuột vào những hình 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 và khám phá những số lượng này sau, cho tới bước này tất cả chúng ta đã thấy ba dạng khác nhau của execution plan và cách tích lũy 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é.
Thông tin chỉ có trên actual plan
Giá trị tiên phong tất cả chúng ta thấy là thời hạn thực thi cùng số lượng rows của từng operator, lượng memory cấp phép cho execution plan này, hoặc 1 số ít thông tin khác khi những bạn rê chuột vào từng operator sẽ Open cùng tool tip, và những cảnh báo nhắc nhở của những operators có đính hình tam giác chấm than màu vàng. Đây là những thông tin những 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ì ngân sách mỗi khi tạo ra compiled plan khá là tốn kém nên SQL Server sẽ lưu đối tượng người dù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à nguyên do khi bạn lấy một plan từ plan cache sẽ không có những thông số kỹ thuật 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%'
truy vấn data trong plan cache
Mỗi compiled plan sẽ có một giá trị plan_handle và duy nhất trong plan cache, hoàn toàn 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ự tương hỗ 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 hoàn toà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 hoàn toàn có thể kiểm tra xem có sống sót những thông số kỹ thuật tương 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 triển khai những việc làm khác nhau và link với nhau trải qua những 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à triển khai việc làm thiết yếu sau đó trả về cho operator tiếp nối. Tên của mỗi loại operator bộc lộ việc làm mà nó phải làm, như quét ( scanning ) data của bảng, update ( 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 vận động và di chuyển của data trong query plan và cũng chính là chiều tất cả 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 những operators khác để ship hàng cho những nhu yếu khác nhau trong việc thao tác, giải quyết và xử lý tài liệu. Việc tìm hiểu và khám phá ý 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 suất cao và thuần thục. Mình sẽ không trình diễn ở bài viết này thay vào đó sẽ gửi link để những bạn tìm hiểu thêm thêm .
Vậy là tất cả chúng ta đã tìm hiểu và khám phá những gì cơ bản nhất về execution plan trong SQL Server, nó được tạo ra ở bước nào, tàng 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 đề để những 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).
Share this:
Like this:
Like
Loading …
