Tìm hiểu Execution Plan trong Sql Server 2008 (Phần 1)

ĐÔI NÉT VỀ EXECUTION PLAN

Thời gian ban đầu làm việc với Sql Server 2008, tôi chẳng quan tâm Execution Plan là gì, chẳng màng đọc các thông số, chẳng biết vai trò của Execution Plan đối với hệ thống Sql Server. Qua quá trình tìm hiểu để tăng tốc hiệu năng cho Sql Server (Sql Performance Tuning), nhận ra vai trò cực kỳ quan trọng của Execution Plan trong việc tối ưu hệ thống Sql nên tôi chia sẻ một vài kiến thức về Execution Plan để chia sẻ kiến thức với mọi người. Xin cho vài ví dụ:

• Thời gian thực thi câu truy vấn=thời gian tạo Execution Plan + thời gian thực thi câu truy vấn dựa trên Execution Plan đó. Do đó, giảm chi phí tạo Execution Plan rất quan trọng, góp phần giảm tải công việc cho Sql Server.
• Dựa vào Execution Plan ta có thể biết một câu truy vấn có dùng Index hay không, và hiệu quả của Index đó. 
• Để tạo ra Execution Plan cần tốn chu trình CPU để tìm ra Execution được cho là ‘tốt’ và không gian bộ nhớ (Ram) để lưu trữ Execution Plan đó để dùng lại lần sau.

Tôi đã thực hiện bài thực hành trên Database của tôi, viết 1 câu truy vấn và cho chạy 5 lần với 5 tham số khác nhau. 

Lúc chưa có nhiều kiến thức về Execution Plan thì tôi nghĩ rằng sau khi chạy câu truy vấn lần 1, Sql tạo ra Execution Plan và lưu trong bộ nhớ, 4 lần sau Sql sẽ dùng lại Execution Plan đó. 

Nhưng tôi đã lầm, nếu tôi viết không cẩn thận, cả 5 lần thực thi câu truy vấn, Sql Server sẽ tạo ra đến 5 Execution Plan và lưu vào bộ nhớ. Như vậy, tôi đã bắt CPU phải làm việc đến 5 lần để tạo ra 5 Execution Plan có nội dung giống nhau, tốn bộ nhớ gấp 5 lần để lưu Execution Plan đó. Trong khi nếu tôi viết đúng cách, chỉ cần 1 Execution Plan cho 5 lần thực thi câu truy vấn, và chỉ tốn bộ nhớ để lưu 1 Execution Plan. Ở đây chỉ là 5, còn hệ thống Sql Server lớn với hàng trăm, hàng ngàn câu truy vấn được thực thi liên tục thì lượng hao phí là quá lớn không thể chấp nhận được.

	Viết đúng cách	Viết không đúng cách
Số lần thực thi câu truy vấn	5	5
Số Execution Plan tạo ra	1	5
Số lần bắt CPU làm việc để tạo ra Execution Plan	1	5
Số Execution Plan được lưu trong bộ nhớ	1	5

EXECUTION PLAN ĐƯỢC TẠO RA TỪ ĐÂU?

Có một thành phần quan trọng trong Sql Server là Query Optimizer, nhiệm vụ của Query Optimizerlà tìm ra Execution Plan được cho là là ‘tốt’, chứ không phải ‘tốt nhất’, vì có thể cùng một câu truy vấn, nhưng có hàng chục, hàng trăm Execution Plan khác nhau có thể được tạo ra để phục vụ câu truy vấn đó.

EXECUTION PLAN ĐƯỢC LƯU Ở ĐÂU?

• Execution Plan được lưu trong Plan Cache hay còn gọi là Procedure Cache.
• Procedure Cache và một phần trong Sql Server Buffer Cache.
• Việc lưu Plan trong bộ nhớ cho phép Sql Server tránh phải lập lại toàn bộ quy trình tối ưu câu truy vấn khi nhiều câu truy vấn giống nhau được submit.

EXECUTION PLAN ĐƯỢC LƯU NHƯ THẾ NÀO?

Khi một Execution Plan mới được lưu vào Procedure Cache, tất nhiên kích cỡ của Procedure Cache sẽ phát triển, hay nói cách khác, lượng bộ nhớ để lưu trữ Execution Plan sẽ tăng lên. Bộ nhớ có giới hạn, không thể lưu hết tất cả Execution Plan được tạo ra, Sql Server sẽ tự động điều chỉnh số lượng Execution Plan trong Procedure Cache bằng cách giữ lại những Execution Plan thường được sử dụng, loại bỏ những Execution Plan không được sử dụng sau một khỏang thời gian nhất định. 

Vậy Sql Server nhận biết các Execution Plan thường được sử dụng như thế nào? Câu trả lời làSql Server sẽ gán tuổi cho các Execution Plan được tạo ra, tuổi của Execution Plan chính là chi phí để tạo ra Execution Plan đó. Một câu truy vấn phức tạp sẽ có tuổi cao hơn một câu truy vấn đơn giản, vì chi phí tạo ra Execution Plan của câu truy vấn phức tạp cao hơn câu truy vấn đơn giản.
Sau một khỏang thời gian xác định, tuổi của các Execution Plan sẽ bị giảm bởi tiến trình Lazy Writer. Tiến trình Lazy Writer chịu trách nhiệm quản lý các tiến trình chạy ngầm trong SQL Server. 

Nếu một Execution Plan không được sử dụng trong một thời gian dài, tuổi của nó sẽ giảm xuống còn 0. Chi phí tạo ra Execution Plan càng rẻ, tuổi của nó sẻ càng nhanh chóng giảm xuống còn 0, và sẽ bị loại bỏ khỏi bộ nhớ nếu SQL Server cần bộ nhớ cho những việc khác. Tuy nhiên, nếu hệ thống còn dư bộ nhớ, những Execution Plan có tuổi là 0 vẫn sẽ được giữ lại một thời gian dài để sử dụng lại nếu cần.

Mỗi khi Execution Plan được tái sử dụng, tuổi của nó sẽ tăng lên bằng cách cộng thêm chi phí tạo ra Execution Plan đó. Ví dụ tôi có một Execution Plan được tạo ra với chi phí là 100, nó sẽ có tuổi là 100. Khi Execution Plan này được tái sử dụng, tuổi của nó sẽ tăng lên là 200. Nếu không được tái sử dụng thường xuyên, tuổi của Execution Plan sẽ bị giảm dần bởi tiến trình Lazy Writer như đã nói ở trên.

LÀM THẾ NÀO XEM THÔNG TIN EXECUTION PLAN TRONG BỘ NHỚ

Ta có thể lấy được nhiều thông tin về Execution Plan trong Procedure Cache bằng cách dùng dynamic management view sys.dm_exec_cached_plans

SELECT * FROM sys.dm_exec_cached_plans

Trong đó có những thông tin quan trọng như:

• Usecounts: số lần Execution Plan này được sử dụng từ khi nó được thêm vào bộ nhớ
• Size_in_bytes: kích cỡ của Plan lưu trong bộ nhớ
• Cacheobjtype: loại Execution Plan. Có nhiều loại Execution Plan như:
  • Compiled plan
  • Compiled plan stub: được dùng cho ad hoc query
  • Parse tree

• Objtype: loại đối tượng tạo ra plan. Ví dụ:
  • Proc
  • Prepared
  • Adhoc
  • View

• Plan_handle: id của plan trong bộ nhớ. Dựa vào id này ta có thể lấy toàn bộ nội dung câu truy vấn ban đầu của Execution Plan

Kết hợp với DMV sys.dm_exec_cached_plans, ta có thể dùng thêm 2 DMV sau:

• sys.dm_exec_query_plan: cho ta Execution Plan dưới dạng XML
• sys.dm_exec_sql_text: cho ta nội dung câu truy vấn ban đầu tạo ra Execution Plan.

Các nguồn tham khảo:
SQL Server 2008 Query Performance Tuning Distilled