Làm thế nào để bạn đọc các kế hoạch thực hiện? Từ phải sang trái, từ trái sang phải, hay kiểm tra chi phí? Hoặc những đối tượng như quét chỉ mục, quét bảng và xem lại thì sao? Blog này thảo luận về cách đọc kế hoạch thực thi Microsoft® SQL Server.
Giới thiệu
Mặc dù SQL Server thường tạo ra một kế hoạch tốt, nhưng đôi khi nó không đủ thông minh để xác thực các kế hoạch của nó và sửa chữa các kế hoạch kém.
Bạn có thể nhận được một kế hoạch thực thi ước tính và một máy chủ SQL SQL Server thực thi đồ họa thực tế. Tạo các kế hoạch này bằng cách sử dụng lệnh ctrl M hoặc ctrl L hoặc bằng cách sử dụng các biểu tượng, được đặt ở bên phải của biểu tượng thực thi trên thanh công cụ tiêu chuẩn của SQL Server Management Studio (SSMS). SQL Server có các loại kế hoạch khác, nhưng những kế hoạch đó không được đề cập trong bài đăng này.
Kế hoạch thực hiện ước tính và thực tế
Có hai loại kế hoạch thực hiện:
-
Kế hoạch thực hiện ước tính :Các kế hoạch ước tính cung cấp ước tính về công việc mà máy chủ SQL dự kiến sẽ thực hiện để lấy dữ liệu.
-
Kế hoạch thực hiện thực tế :Các kế hoạch thực thi thực tế được tạo sau khi truy vấnTransact-SQL hoặc các lô được thực thi. Do đó, một kế hoạch thực thi hành động chứa thông tin thời gian chạy, chẳng hạn như số liệu sử dụng tài nguyên thực tế và bất kỳ cảnh báo thời gian chạy nào.
Xử lý dữ liệu
Bạn đã bao giờ nhận thấy sự khác biệt giữa kế hoạch ước tính và kế hoạch thực tế cho cùng một truy vấn chưa? Hầu hết thời gian nó sẽ giống nhau, nhưng nó có thể khác nhau vì những thay đổi thống kê, những thay đổi liên quan đến lược đồ hoặc những thay đổi trong dữ liệu. Bạn phải luôn kiểm tra kế hoạch thực thi thực tế khi khắc phục sự cố.
Đọc kế hoạch thực hiện một cách chính xác để xác định điểm thực tế. Bắt đầu bằng cách tìm kiếm luồng dữ liệu hơn là chi phí. Đừng nghĩ về các luồng logic hoặc vật lý. Điều quan trọng là phải giảm số lượng hoạt động nhập / xuất (I / O). Là một quản trị viên cơ sở dữ liệu (DBA), bạn biết rằng truy cập bộ nhớ là tài nguyên phần cứng chậm nhất, vì vậy bạn nên cố gắng giảm thiểu hoạt động đó. Vậy bạn khám phá số liệu thống kê như thế nào và kế hoạch thực hiện có hiển thị nó không? Có, đúng vậy! Kiểm tra các đường chỉ hướng bằng cách di chuột qua dấu từ phải sang trái. Nó hiển thị số lượng bản ghi và kích thước dữ liệu. Mỗi dòng mỏng hơn hoặc mỏng hơn dựa trên khối lượng dữ liệu được trả về bởi hoạt động như được minh họa trong hình minh họa sau:
Nếu bạn có nhiều đối tượng, bạn cần một cách tốt hơn để có được cái nhìn tổng quan về lượng dữ liệu được xử lý bởi mỗi thao tác. Tải xuống SentryOne Plan Explorer và xem kế hoạch bằng công cụ này để có cái nhìn tổng quan một cách dễ dàng.
Sau khi bạn tải xuống và định cấu hình SentryOne trên hệ thống của mình, hãy mở kế hoạch thực thi với nó. SentryOne có nhiều chế độ xem và mô tả, bạn có thể sử dụng nếu cần. Để có chế độ xem xử lý dữ liệu, hãy thay đổi chế độ xem bằng cách chọn Data size in MB tùy chọn như thể hiện trong hình ảnh sau đây. Mục tiêu của bạn là tìm cơ hội để giảm việc xử lý dữ liệu tổng thể.
Nếu bạn cần giảm căng thẳng I / O, bạn có thể xem phần SET STATISTICS IO ON Giá trị T-SQL để có được ý tưởng tổng thể về việc sử dụng I / O cho truy vấn. Bạn nên đặt cài đặt này trước khi thực thi truy vấn trong SSMS để xem kết quả chuyển sang thông báo trong kết quả bảng điều khiển. Nó sẽ trông giống như kết quả sau:
(356 row(s) affected)
Table 'Table3'. Scan count 1, logical reads 5, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0,
lob read-ahead reads 0.
Số lần đọc logic 5 kết quả cho thấy SQL Server đọc trang 40 KB (5 * 8 KB) để lấy dữ liệu từ bộ nhớ. Khi tối ưu hóa truy vấn, đừng bỏ qua các luồng logic vì điều này có thể là vật lý cũng như logic trên các máy chủ trực tiếp. Bạn không bao giờ biết liệu các trang mà bạn đang đề cập đến là trên bộ nhớ hay trên đĩa. Mục tiêu phải là giảm số lượng các thao tác đọc tích lũy.
Kế hoạch ước tính so với kế hoạch thực tế
Sau khi kế hoạch thực tế được tạo, hãy di chuột qua bất kỳ đơn vị vận hành nào trong kế hoạch thực thi. Tham khảo hình ảnh sau để xem các kiểu hoạt động vật lý, chẳng hạn như Tìm kiếm hoặc quét chỉ mục theo cụm kết quả. Tìm kiếm số lần thực thi và số lượng hàng ước tính và thực tế. Tránh xem xét chi phí của các hoạt động riêng lẻ vì đó là ước tính không nhanh dựa trên phần cứng cũ và có thể không cung cấp chi tiết chính xác. Trong hình ảnh tiếp theo, Tìm kiếm chỉ mục theo cụm toán tử đã thực thi một lần để lấy 100 bản ghi và SQL ước tính 356 bản ghi. Sự khác biệt có thể là do thống kê hoặc hiệu suất truy vấn quá cập nhật.
Lặp lại thực thi
Hình ảnh sau đây cho thấy số lần thực thi ước tính là 1154121 lần trên một chỉ mục cụm để có được một bản ghi. Đó là điều đáng kể. Mặc dù chi phí vận hành thấp hơn, và chúng tôi có thể đơn giản bỏ qua nó, thay vào đó nhìn vào các hoạt động khác với chi phí cao hơn, điều này có thể đã được chứng minh là một sự lựa chọn tốn kém. Các hoạt động như vậy có tác động cao đến hiệu suất truy vấn mặc dù có chỉ mục cụm. Tra cứu ID hàng (RID) là một thao tác tương tự đối với heap.
Cơ sở dữ liệu tạm thời
Sự khác biệt giữa số lượng bản ghi ước tính và thực tế là một điều khác mà bạn nên xem xét. Ước tính sai có thể dẫn đến phân bổ bộ nhớ hạn chế truy vấn. Khi điều đó xảy ra, nó sử dụng cơ sở dữ liệu tạm thời (tempdb) để hoàn thành công việc. Việc lựa chọn sai một toán tử hoặc kế hoạch của SQL Server Có thể dẫn đến các hoạt động chậm và trích xuất các truy vấn, như được hiển thị trong hình ảnh sau đây. Số lượng bản ghi thực tế là như nhau, nhưng các ước tính khác nhau. Điều này có thể là do số liệu thống kê đã lỗi thời hoặc bị thiếu. Lưu ý rằng bảng biến không có thống kê, vì vậy kế hoạch luôn trả về 1 và 1K trong các bản phát hành mới cho đến khi tùy chọn biên dịch lại được sử dụng. Do đó, các biến bảng không phải là một lựa chọn tốt cho một số lượng lớn các bản ghi.
Sắp xếp toán tử
Bạn cần xem xét tác động của việc sắp xếp. Toán tử sắp xếp được sử dụng chủ yếu cho các chức năng sau:tổng hợp, kết hợp hợp nhất hoặc sắp xếp theo mệnh đề. Điều này có thể không có tác động chỉ với một vài bản ghi, nhưng với mỗi bản ghi bổ sung, quá trình xử lý sẽ chậm lại. Cố gắng tránh sắp xếp hoặc không sử dụng thứ tự theo mệnh đề. Nếu cần phải sắp xếp, hãy sử dụng lưới ứng dụng để thực hiện việc sắp xếp thay vì gửi dữ liệu đã phân loại đến một ứng dụng.
Hình ảnh sau đây cho thấy chi phí sắp xếp:
Spool operator
Một toán tử quan trọng khác mà bạn cần xem xét là spool. Spool là một trình làm việc chậm vì nó lưu trữ các đối tượng ẩn hoặc tạm thời hoặc bảng công việc intempdb. Điều này cũng làm chậm với các toán tử cụ thể khiến nó phải tua đi tua lại. Có nhiều loại cuốn sách trong SQL Server, chẳng hạn như Háo hức, Lười biếng, Bảng / Chỉ mục, v.v. SQL Server sử dụng bộ đệm khi tham chiếu đến bảng làm việc atemp tốt hơn là quay trở lại bảng nguồn cho các bộ kết quả trung gian. Hình ảnh sau đây cho thấy một ví dụ:
Với ống chỉ, điều quan trọng là phải để ý số vòng tua và vòng tua máy. Rewindis tốn kém hơn rebind. Ví dụ, trong hình ảnh sau, toán tử hiển thị 2674 tua lại, ngụ ý rằng truy vấn được thực hiện lại 2674 lần để lấy dữ liệu. Nó trả về từng toán tử từ ống đệm bảng để quét bảng để có được từng tua lại của bản ghi. Rebind có nghĩa là nó lấy dữ liệu từ cuộn và không quay lại để quét bảng.
Các toán tử băm và vòng lặp lồng nhau
Bởi vì chúng hoạt động tốt trên các tập bản ghi nhỏ, băm và các vòng lặp lồng nhau là các toán tử tiếp theo mà bạn nên xem xét. Tuy nhiên, đối với các bộ kỷ lục lớn hoặc khi có sự khác biệt lớn giữa kế hoạch ước tính và thực tế, những người điều hành này có thể tạo ra tác động rất lớn vì họ có thể sử dụng tempdb thay vì bộ nhớ. SQL Server đăng cảnh báo sau trong chi tiết về toán tử:“tempdb đã được sử dụng để làm tràn dữ liệu trong quá trình thực thi”. Nếu điều này xảy ra, hãy chú ý đến số liệu thống kê. Với một ước tính sai, vòng lặp bị thiếu hụt về cấp phát bộ nhớ, nó cứ lặp đi lặp lại. Nhìn vào bộ nhớ được cấp phát để thực thi. Để phân bổ vùng nhớ, hãy mở hộp thuộc tính bằng cách chọn điểm bắt đầu của kế hoạch thực thi (từ trái sang phải). Nếu không có gì bất thường, thì điều chỉnh truy vấn sẽ là lựa chọn lý tưởng.
Kết luận
Mục tiêu đầu tiên của bạn để tối ưu hóa truy vấn phải là giảm số lần đọc và ghi tổng thể (nghĩa là I / O trên đĩa). Đừng quên các phép đọc logic cho bộ nhớ đọc và ghi. Giảm I / O giải quyết hầu hết các vấn đề và các truy vấn chạy nhanh hơn nhiều.
Tiếp theo, hãy xem các hoạt động khác tốn kém do hoạt động ontempdb. Hãy nhớ rằng tempdb được sử dụng cho nhiều hoạt động và luôn đắt đỏ.
Chúng đắt khi được sử dụng với tempdb. Đừng quên xem xét cảnh báo trên từng nhà điều hành vì những điều đó cung cấp manh mối tốt. Mặc dù bài đăng này không thảo luận về toán tử chỉ mục bị thiếu, điều đó không có nghĩa là bạn bỏ qua nó.
Xem lại nó, nhưng đừng tạo chỉ mục một cách mù quáng. Kiểm tra các chỉ mục khác có sẵn trên cùng các cột và xem xét tác động đối với các truy vấn đang chạy trong cơ sở dữ liệu của bạn.
Sử dụng tab Phản hồi để đưa ra bất kỳ nhận xét hoặc đặt câu hỏi nào. Bạn cũng có thể bắt đầu cuộc trò chuyện với chúng tôi.