Giải thích kế hoạch truy vấn ước tính và thực tế
Thực tế so với kế hoạch thực hiện ước tính có thể gây nhầm lẫn. Sự khác biệt là kế hoạch thực tế bao gồm thống kê thời gian chạy không được ghi lại trong kế hoạch ước tính. Các toán tử được sử dụng và thứ tự thực hiện sẽ giống như kế hoạch ước tính trong hầu hết các trường hợp. Một điều cần cân nhắc khác là việc nắm được kế hoạch thực thi thực tế yêu cầu thực thi truy vấn, có thể tốn thời gian hoặc không thể thực hiện được. Ví dụ: một câu UPDATE lệnh chỉ có thể chạy một lần. Tuy nhiên, nếu bạn cần xem kết quả truy vấn và kế hoạch, bạn cần sử dụng một trong các tùy chọn kế hoạch thực tế.
Như minh họa, bạn có thể tạo kế hoạch ước tính trong SSMS bằng cách chọn nút được biểu thị bằng hộp kế hoạch truy vấn ước tính (hoặc sử dụng lệnh bàn phím Control+L). Bạn có thể tạo kế hoạch thực tế bằng cách chọn biểu tượng được hiển thị (hoặc sử dụng lệnh bàn phím Control+M), rồi thực thi truy vấn. Hai nút tùy chọn hoạt động khác nhau. Nút Bao gồm Kế hoạch Truy vấn Ước tính phản hồi ngay lập tức cho bất kỳ truy vấn nào được tô sáng (hoặc toàn bộ không gian làm việc, nếu không có gì được tô sáng), trong khi nút Bao gồm Kế hoạch Truy vấn Thực tế yêu cầu thực hiện truy vấn.
Có chi phí cho cả hai thực hiện một truy vấn và tạo ra một kế hoạch thực hiện ước tính, vì vậy xem kế hoạch thực hiện nên được thực hiện cẩn thận trong một môi trường sản xuất.
Thông thường, bạn có thể sử dụng kế hoạch thực thi ước tính trong khi viết truy vấn để hiểu đặc điểm hiệu suất của truy vấn, xác định chỉ mục bị thiếu hoặc phát hiện các bất thường của truy vấn. Kế hoạch thực hiện thực tế được sử dụng tốt nhất để hiểu hiệu suất thời gian chạy của truy vấn và quan trọng nhất là khoảng trống trong dữ liệu thống kê khiến trình tối ưu hóa truy vấn đưa ra các lựa chọn tối ưu phụ dựa trên dữ liệu có sẵn.
Đọc kế hoạch truy vấn
Kế hoạch thực thi cho bạn biết công cụ cơ sở dữ liệu đang thực hiện những tác vụ nào trong khi truy xuất dữ liệu cần thiết để thỏa mãn một truy vấn. Hãy đi sâu vào kế hoạch.
SELECT [stockItemName]
,[UnitPrice] * [QuantityPerOuter] AS CostPerOuterBox
,[QuantityonHand]
FROM [Warehouse].[StockItems] s
JOIN [Warehouse].[StockItemHoldings] sh ON s.StockItemID = sh.StockItemID
ORDER BY CostPerOuterBox;
Truy vấn này đang nối bảng StockItems với bảng StockItemHoldings trong đó các giá trị trong cột StockItemID bằng nhau. Trước tiên, công cụ cơ sở dữ liệu phải xác định các hàng đó trước khi có thể xử lý phần còn lại của truy vấn.
Mỗi biểu tượng trong kế hoạch đại diện cho một thao tác cụ thể, tương ứng với các hành động và quyết định khác nhau tạo nên kế hoạch thực thi. Công cụ cơ sở dữ liệu SQL Server có hơn 100 toán tử truy vấn có thể là một phần của kế hoạch thực thi. Bên dưới mỗi biểu tượng toán tử, có một tỷ lệ phần trăm chi phí so với tổng chi phí của truy vấn. Ngay cả một hoạt động hiển thị một chi phí 0% vẫn đại diện cho một số chi phí. Trên thực tế, 0% là do làm tròn, vì chi phí kế hoạch đồ họa luôn được hiển thị dưới dạng số nguyên và tỷ lệ phần trăm thực là một số ít hơn 0,5%.
Dòng thực thi trong kế hoạch thực thi là từ phải sang trái và từ trên xuống dưới, vì vậy trong kế hoạch này, thao tác Quét Chỉ mục Theo cụm trên chỉ mục liên cụm StockItemHoldings.PK_Warehouse_StockItemHoldings là thao tác đầu tiên trong truy vấn. Độ rộng của các đường kết nối các toán tử được dựa trên số hàng dữ liệu ước tính chuyển sang toán tử tiếp theo. Mũi tên dày là chỉ báo về toán tử lớn để chuyển toán tử và có thể biểu thị một cơ hội để điều chỉnh truy vấn. Bạn cũng có thể giữ chuột trên một nhà cung cấp dịch vụ và xem thông tin bổ sung trong Mẹo Công cụ.
Mẹo công cụ nêu bật chi phí và ước tính cho kế hoạch ước tính và cho một kế hoạch thực tế, nó bao gồm so sánh với các hàng và chi phí thực tế. Mỗi toán tử cũng có các thuộc tính cung cấp nhiều chi tiết hơn chú giải công cụ. Bằng cách bấm chuột phải vào một toán tử cụ thể, bạn có thể chọn tùy chọn Thuộc tính từ menu ngữ cảnh để xem danh sách thuộc tính đầy đủ. Tùy chọn này sẽ mở một ngăn Thuộc tính riêng biệt trong SQL Server Management Studio, theo mặc định nằm ở bên phải. Sau khi ngăn Thuộc tính mở ra, việc chọn bất kỳ toán tử nào sẽ điền danh sách Thuộc tính cùng với các chi tiết cho toán tử đó. Ngoài ra, bạn có thể mở ngăn Thuộc tính bằng cách chọn dạng xem trong menu SQL Server Management Studio chính, rồi chọn Thuộc tính.
Ngăn Thuộc tính bao gồm thông tin bổ sung và hiển thị danh sách đầu ra, nêu chi tiết các cột được chuyển đến toán tử tiếp theo. Các cột này có thể chỉ ra rằng cần có chỉ mục không kết nối để cải thiện hiệu suất truy vấn khi được phân tích bằng quét chỉ mục liên cụm. Kể từ khi một hoạt động quét chỉ mục liên cụm đọc toàn bộ bảng, một chỉ mục nonclustered cột StockItemID trong mỗi bảng có thể hiệu quả hơn trong trường hợp này.
Tạo hồ sơ truy vấn nhẹ
Khi bạn tạo kế hoạch thực hiện thực tế, cho dù sử dụng SSMS hoặc mở rộng sự kiện giám sát cơ sở hạ tầng, nó có thể giới thiệu chi phí đáng kể. Vì vậy, quá trình này thường được dành riêng cho các nỗ lực khắc phục sự cố trang web trực tiếp. Chi phí quan sát, như đã biết, là chi phí giám sát một ứng dụng đang chạy. Trong một số trường hợp, chi phí này có thể chỉ là một vài phần trăm điểm sử dụng CPU, nhưng trong các trường hợp khác, như nắm bắt các kế hoạch thực thi thực tế, nó có thể làm chậm đáng kể hiệu suất truy vấn riêng lẻ. Việc lập hồ sơ thừa tự trong công cụ của SQL Server có thể tạo ra tổng phí lên đến 75% để thu thập thông tin truy vấn, trong khi hồ sơ nhẹ có tổng phí tối đa khoảng 2%.
Trong phiên bản đầu tiên của hồ sơ nhẹ, nó thu thập số hàng và thông tin sử dụng I/O (số lượng đọc và viết lô-gic và vật lý được thực hiện bởi công cụ cơ sở dữ liệu để thỏa mãn một truy vấn nhất định). Ngoài ra, một sự kiện mở rộng query_thread_profile được giới thiệu để cho phép kiểm tra dữ liệu từ từng toán tử trong kế hoạch truy vấn. Trong phiên bản ban đầu của lập hồ sơ nhẹ, việc sử dụng tính năng này yêu cầu gắn cờ theo dõi 7412 được bật toàn cầu.
Nếu tính năng lập hồ sơ nhẹ không được bật trên toàn cầu, USE HINTQUERY_PLAN_PROFILE bạn có thể sử dụng gợi ý truy vấn để bật tính năng ghi hồ sơ nhẹ ở mức truy vấn. Khi một truy vấn với gợi ý này hoàn thành thực thi, một query_plan_profile mở rộng được tạo ra, cung cấp một kế hoạch thực hiện thực tế. Dưới đây là ví dụ về truy vấn với gợi ý sau:
SELECT [stockItemName]
,[UnitPrice] * [QuantityPerOuter] AS CostPerOuterBox
,[ QuantityonHand]
FROM [Warehouse].[StockItems] s
JOIN [Warehouse].[StockItems] sh ON s.StockItemID = sh.StockItemID
ORDER BY CostPerOuterBox
OPTION(USE HINT ('QUERY_PLAN_PROFILE'));
Thống kê kế hoạch truy vấn cuối cùng
Tính năng ghi hồ sơ trọng lượng nhẹ được bật theo mặc định trong cả Cơ sở dữ liệu SQL Server 2019 và Azure SQL cũng như phiên bản được quản lý. Lược tả nhẹ cũng có sẵn dưới dạng tùy chọn cấu hình phạm vi cơ sở dữ liệu, được gọi là LIGHTWEIGHT_QUERY_PROFILING. Với tùy chọn phạm vi cơ sở dữ liệu, bạn có thể tắt tính năng này đối với bất kỳ cơ sở dữ liệu người dùng nào của bạn độc lập với nhau.
Ngoài ra, còn có một hàm quản sys.dm_exec_query_plan_statslý động được gọi là , có thể hiển thị cho bạn kế hoạch thực hiện truy vấn thực tế đã biết gần đây nhất cho một điều khiển kế hoạch nhất định. Để xem kế hoạch truy vấn thực tế được biết đến gần đây nhất thông qua hàm, bạn có thể bật cờ theo dõi trên toàn máy chủ 2451. Ngoài ra, bạn có thể bật chức năng này bằng cách sử dụng tùy chọn cấu hình có phạm vi cơ sở dữ liệu được gọi là LAST_QUERY_PLAN_STATS.
Bạn có thể kết hợp hàm này với các đối tượng khác để có kế hoạch thực thi cuối cùng cho tất cả các truy vấn được lưu trong bộ đệm ẩn:
SELECT *
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
CROSS APPLY sys.dm_exec_query_plan_stats(plan_handle) AS qps;
GO
Chức năng này cho phép bạn nhanh chóng xác định số liệu thống kê thời gian chạy cho việc thực thi cuối cùng của bất kỳ truy vấn nào trong hệ thống của bạn, với chi phí tối thiểu. Hình ảnh sau đây cho thấy cách truy xuất kế hoạch. Nếu bạn chọn XML kế hoạch thực thi, vốn sẽ là cột kết quả đầu tiên, nó sẽ hiển thị kế hoạch thực thi được hiển thị trong hình ảnh thứ hai bên dưới.
Như bạn có thể thấy từ các thuộc tính của Quét Chỉ mục Columnstore trong hình ảnh sau đây, kế hoạch truy xuất từ bộ đệm ẩn có số hàng thực tế được truy xuất trong truy vấn.
