kế hoạch thực thi máy chủ sql là gì? Chúng ta đã biết thứ tự thực hiện truy vấn ở mức logic và ở mức vật lý? Thứ tự của các bước xử lý dữ liệu của một truy vấn ở mức vật lý là cách mỗi bước được thực thi bởi máy chủ sql để hoàn thành yêu cầu và trả lại dữ liệu cho người dùng. Hiểu các bước thực hiện truy vấn ở cấp độ vật lý sẽ giúp bạn nhận ra các điểm dưới mức tối ưu cũng như hiểu tác động của chúng đối với môi trường máy chủ sql nơi nhiều truy vấn khác được thực thi. Tất cả những điều này được thể hiện trong kế hoạch thực thi, hôm nay chúng ta sẽ tìm hiểu kiến thức cơ bản về kế hoạch thực thi, cách thu thập, đọc và hiểu để có thể quản lý máy chủ sql tốt hơn.
kế hoạch thực thi là gì?
kế hoạch thực thi
giống như một hướng dẫn chi tiết về những việc cần làm để hoàn thành một truy vấn. khi bạn gửi câu lệnh select tới máy chủ sql, có một số cách để thực hiện công việc cần thiết để có được kết quả mong muốn và trình tối ưu hóa truy vấn chịu trách nhiệm chọn cách truy vấn khớp với các tham số đầu vào. trình tối ưu hóa truy vấn sẽ sử dụng thông tin cần thiết, chẳng hạn như chỉ mục nào trên bảng đó có thể được sử dụng, thống kê cột có liên quan như thế nào, nếu có bất kỳ hạn chế nào, v.v. để tạo ra các kế hoạch khả thi và chọn một trong số chúng cảm thấy đủ tốt. trong một thời gian hữu hạn. kết quả của quá trình này là tạo một kế hoạch đã biên dịch, sau đó máy chủ sql sẽ làm theo các bước được mô tả trong kế hoạch này để đạt được kết quả mong muốn, kế hoạch thực thi là các đối tượng thời gian chạy của kế hoạch đã biên dịch (mặc dù cả hai đối tượng đều là kế hoạch biên dịch) và thực thi kế hoạch là khác nhau, nhưng khi chúng tôi đề cập đến cái này hay cái kia, cả hai chúng tôi đều hiểu rằng chúng tôi có nghĩa là kế hoạch thực hiện). Các kế hoạch tổng hợp này khá tốn kém để tạo, vì vậy chúng sẽ được lưu lại để sử dụng trong tương lai.
Hình dạng execution plan
vậy, kế hoạch thực hiện như thế nào? Bạn có thể xem kế hoạch thực hiện ở ba định dạng: văn bản, xml hoặc đồ thị. lưu ý rằng người dùng của bạn cần quyền trình chiếu để xem nó. hai loại đầu tiên có thể nhận được như sau.
bạn cần chạy tất cả tập lệnh trên để xem kết quả. Lệnh showplan_text sẽ nhận kế hoạch thực thi dưới dạng văn bản và showplan_xml để nhận định dạng xml. lưu ý rằng ssm 18.5 sẽ tự động chuyển sang kế hoạch thực thi đồ họa nếu bạn nhấp vào xml đầu ra, sau đó bạn có thể nhấp chuột phải và chọn “hiển thị kế hoạch thực thi xml” để xem nó là 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.
kế hoạch ước tính và thực tế
Bạn đã nhận thấy trong tập lệnh đầu tiên rằng khi bạn nhấn f5, kết quả trả về hai kế hoạch thực thi ở dạng văn bản và xml ngay lập tức và trong tập lệnh tiếp theo, bạn phải đợi vài giây để nhận được kết quả của truy vấn cùng với việc thực thi. kế hoạch hay không? thời gian chênh lệch này cũng là hai loại kế hoạch thực hiện mà chúng ta muốn có được, một là kế hoạch ước tính và hai là kế hoạch thực tế. loại đầu tiên giống như tên, máy chủ sql chỉ cần tạo kế hoạch thực thi và trả về mà không cần thực hiện truy vấn. và loại thứ hai (nhấn ctrl + m ) được gọi là kế hoạch thực tế vì máy chủ sql phải hoàn thành việc thực thi truy vấn để trả lại chúng. hai loại này có hình thức tương tự nhau, ngoại trừ kế hoạch thực tế có thông tin bổ sung liên quan đến việc thực thi đó, chẳng hạn như số hàng thực sự có ( số hàng thực tế ), dung lượng bộ nhớ được cấp phát. biểu mẫu biểu đồ cũng có một kế hoạch ước tính, là biểu tượng thứ ba ở bên phải của “bao gồm kế hoạch thực hiện thực tế” hoặc bạn có thể nhấn tổ hợp phím ctrl + l để thấy rằng ssms hiển thị kế hoạch ước tính ngay lập tức. Ngay lập tức. Hình ảnh sau đây cho thấy những thông tin cơ bản chỉ có trong bản vẽ thực, hãy kiểm tra xem bản vẽ dự kiến có những thông số này hay không.
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.
bộ nhớ cache của gói là gì?
Như tôi đã đề cập ở đầu bài viết, vì chi phí tạo một kế hoạch đã biên dịch khá cao, máy chủ sql sẽ lưu đối tượng này trong bộ đệm ẩn của kế hoạch để sử dụng lại nó cho các lần thực thi tiếp theo của cùng một truy vấn. bởi vì máy chủ sql lưu trữ kế hoạch đã biên dịch này trước khi thực thi, nó là kế hoạch ước tính. đó là lý do tại sao khi bạn lấy một kế hoạch từ bộ nhớ cache kế hoạch, sẽ không có các tham số như số hàng thực tế, cấp bộ nhớ. truy vấn sau được sử dụng để lấy kế hoạch thực thi của bộ nhớ cache.
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ách đọc kế hoạch thực thi?
Nhìn vào kế hoạch thực thi đồ họa ở trên, nó bao gồm nhiều hoạt động thực hiện các công việc khác nhau và được liên kết với nhau bằng các mũi tên để tạo thành một chuỗi được gọi là kế hoạch truy vấn. trong trình tự này, mỗi thao tác được gọi là toán tử (hoặc trình lặp) đọc dữ liệu từ toán tử kế nhiệm của nó và thực hiện công việc cần thiết rồi trả lại cho toán tử tiếp theo. tên của mỗi loại toán tử đại diện cho công việc mà nó phải làm, chẳng hạn như quét (quét) dữ liệu trong bảng, cập nhật (cập nhật) dữ liệu trong bảng, lọc hoặc thêm (lọc hoặc thêm) dữ liệu. mũi tên chỉ từ phải sang trái là hướng di chuyển dữ liệu trong kế hoạch truy vấn và cũng là hướng chúng ta đọc kế hoạch thực hiện: từ phải sang trái, trên xuống dưới.
với kế hoạch của chúng tôi ở trên, trước tiên hãy quét chỉ mục nhóm của bảng bài đăng, dữ liệu được trả về toán tử đối sánh băm (tổng hợp), anh chàng này sẽ tổng hợp dữ liệu theo chủ sở hữu cột để lấy tổng số bài đăng cho cột này . . sau đó dữ liệu sẽ được chuyển đến khớp băm (phép nối bên trong), toán tử sẽ xây dựng bảng băm theo id người dùng của chủ sở hữu khóa. đây là toán tử tham gia, thường được gọi là tham gia băm, giai đoạn đầu tiên tạo bảng băm, giai đoạn thứ hai lấy dữ liệu từ toán tử bảng người dùng quét chỉ mục nhóm (cột id và vị trí) và tìm các hàng có id bằng id người dùng của chủ sở hữu. kết quả của phép nối băm này sẽ trả về toán tử tiếp theo, một khớp băm khác (tổng hợp). toán tử này nhận dữ liệu dưới dạng các dòng dữ liệu với hai cột vị trí và tổng số bài đăng (từ khớp băm trước đó (tổng hợp)) và sau đó thực hiện tổng hợp dữ liệu theo vị trí và tính tổng số bài đăng. sau đây là toán tử vô hướng tính toán lấy tập dữ liệu vị trí với tổng số bài đăng và chuyển tổng số này thành kiểu số nguyên. thì tập dữ liệu này được lọc theo vị trí là ‘% vietnam%’ và tổng số bài đăng & gt; 10 trong bộ lọc toán tử. người vận hành sau phụ trách việc sắp xếp thứ tự các dòng dữ liệu theo tiêu chí tăng tổng số ấn phẩm và cuối cùng trả về người dùng lựa chọn bởi người vận hành.
đây là một kế hoạch truy vấn tương đối phức tạp, nhưng bạn có thể có hoặc sẽ tìm thấy các kế hoạch phức tạp hơn, việc đọc và hiểu kế hoạch truy vấn sẽ hiệu quả hơn nếu bạn chú ý đến thông tin của từng toán tử khi di chuột qua toán tử hoặc bên phải. -nhấp vào thuộc tính chế độ xem của bạn. Một số lưu ý cơ bản để dễ đọc và hiểu bản kế hoạch như sau: hiểu vai trò người điều hành làm gì và danh sách các cột đầu ra; xem thuộc tính để xem cột mới được tạo với biểu thức nào; giá trị được xác định (bạn có thể xem lại (thuộc tính tính toán vô hướng hoặc toán tử tổng hợp đối sánh băm trong kế hoạch truy vấn): mũi tên dày hơn cho biết nhiều dữ liệu hơn để toán tử đó đọc, nếu bạn có thể làm cho mũi tên mỏng hơn càng sớm càng tốt: hãy chú ý cảnh báo , là các toán tử có biểu tượng hình tam giác màu vàng chứa dấu chấm than.
nếu bạn di chuột qua lựa chọn toán tử, bạn sẽ thấy giá trị chi phí ước tính của cây con , đây là giá trị của máy chủ sql ước tính chi phí của kế hoạch truy vấn theo đơn vị của chính nó. chi phí ước tính của cây con là tổng chi phí của các nhà khai thác phía trước nó (theo hướng luồng dữ liệu từ phải sang trái), con số này được sử dụng để so sánh các kế hoạch truy vấn với nhau hoặc để máy chủ sql quyết định có sử dụng một kế hoạch truy vấn song song hay không (kế hoạch chúng tôi đang xem xét là nối tiếp).
Ngoài các toán tử trên, sql server còn có nhiều toán tử khác để đáp ứng các nhu cầu khác nhau trong thao tác và xử lý dữ liệu. Hiểu được ý nghĩa của các toán tử này sẽ giúp kỹ năng đọc kế hoạch thực thi của bạn trở nên hiệu quả và thành thạo hơn. Mình sẽ không giới thiệu trong bài viết này mà sẽ gửi link cho các bạn tham khảo.
vậy là chúng ta đã tìm hiểu những điều cơ bản nhất về kế hoạch thực thi trong máy chủ sql, nó được tạo ở bước nào, nó được lưu trữ ở đâu, các kiểu thực thi là gì, cách xem kế hoạch thực thi các truy vấn của một câu. và cách đọc chúng, đây là tiền đề để bạn có thể đi sâu hơn vào con đường tối ưu hóa truy vấn và sau đó là tối ưu hóa máy chủ sql.
nguồn tham khảo: 1. Kế hoạch Thực thi SQL Server, Ấn bản thứ ba, của Grant Fritchey.2. bên trong trình tối ưu hóa truy vấn máy chủ sql, tải xuống pdf miễn phí, bởi benjamin nevarez (hoặc bài viết tóm tắt tại liên kết này) .3. toán tử showplan – fabiano amorim (loạt bài hay về toán tử kế hoạch thực thi) .4. sqlserverfast.com (rất chi tiết về cách thức hoạt động của các nhà khai thác).
