Tăng Tốc SQL Server: Bí Quyết Sử Dụng Procedure Cache, Plan Cache, Và Buffer Cache Hiệu Quả

Trong quá trình tối ưu hóa hiệu suất SQL Server, 3 khái niệm quan trọng liên quan đến quản lý bộ nhớ và tối ưu hóa query là Procedure Cache, Plan Cache, và Buffer Cache. Hiểu rõ từng khái niệm và cách chúng hoạt động sẽ giúp bạn cải thiện hiệu suất hệ thống.

Buffer Cache

• Là vùng bộ nhớ được SQL Server sử dụng để lưu trữ các page data đã được truy cập gần đây từ disk.
• Khi một page data được query, SQL Server sẽ cố gắng tìm nó trong Buffer Cache trước. Nếu page đã có sẵn trong cache, nó sẽ được phục vụ từ đó mà không cần truy cập disk.
• Tối ưu hóa Buffer Cache:

Chỉ số quan trọng Buffer Cache Hit Ratio thể hiện tần suất data được lấy trực tiếp từ cache thay vì phải truy xuất từ disk. Chỉ số này càng cao thì càng tốt, vì nó cho thấy rằng SQL Server đang lấy dữ liệu từ buffer cache nhiều hơn, giúp cải thiện hiệu suất query.

-- Kiểm tra Buffer Cache Hit Ratio:

SELECT 
    object_name, 
    counter_name, 
    cntr_value AS BufferCacheHitRatio
FROM 
    sys.dm_os_performance_counters
WHERE 
    counter_name = 'Buffer cache hit ratio';

Nếu chỉ số này thấp (dưới 90%), có thể bạn cần  tăng dung lượng bộ nhớ (RAM hoặc max server memory SQL Server) hoặc tối ưu hóa query để dữ liệu có thể nằm trong bộ nhớ lâu hơn.        

Plan Cache

• Plan Cache là bộ nhớ mà SQL Server sử dụng để lưu trữ các kế hoạch thực thi (execution plans). Khi một query SQL được thực thi lần đầu, SQL Server sẽ xây dựng excution plan và lưu nó vào Plan Cache.
• Nếu cùng một query (hoặc query tương tự) được thực thi lại, SQL Server sẽ tái sử dụng plan đã lưu trong Plan Cache, thay vì xây dựng lại từ đầu.
• Tối ưu hóa Plan Cache:

- Tăng cường tái sử dụng các plan query bằng cách sử dụng stored procedures hoặc parameterized queries(query có parameter thay vì insert trực tiếp giá trị vào query - hệ thống sẽ cache được excution plan của query).

- Tránh query plan pollution (ô nhiễm bộ nhớ cache plan) bằng cách tránh sử dụng quá nhiều query với giá trị literal hard-coded (thay vào đó, sử dụng tham số hóa).

query plan pollution: hiện tượng khi bộ nhớ cache excution plan của SQL Server bị làm đầy bởi các excution plan khác nhau cho các query tương tự nhưng có các giá trị đầu vào khác nhau

-- Check các excution plan đang được lưu trữ:
SELECT 
    cp.plan_handle, 
    cp.objtype, 
    st.text AS sql_text
FROM 
    sys.dm_exec_cached_plans AS cp
CROSS APPLY 
    sys.dm_exec_sql_text(cp.plan_handle) AS st
WHERE 
    cp.objtype = 'Adhoc'; -- Lọc ra các plan query  không được tái sử dụng, check và tối ưu query

--Xóa excution plan bằng plan handle:
DBCC FREEPROCCACHE(plan_handle);

-- xóa plan cache: Việc sử dụng DBCC FREEPROCCACHE trên môi trường PROD thực sự có thể gây tác động tiêu cực, đặc biệt là tăng tải CPU khi các truy vấn phải tạo lại execution plan từ đầu, khuyến nghị việc xoá plan cache theo từng Plan Handle cụ thể nếu cần thiết
DBCC FREEPROCCACHE;
        

Procedure Cache

• Procedure Cache cũng lưu trữ các excution plan, nhưng tập trung vào các stored procedures. Các stored procedures khi thực thi sẽ tạo ra một plan và plan này được lưu trữ trong Procedure Cache để tái sử dụng sau này.
• Tái sử dụng execution plan: Khi một stored procedure được gọi với tham số, SQL Server tạo một execution plan cho Procedure. Nếu thiết kế hợp lý, plan này sẽ được lưu trong bộ nhớ cache và tái sử dụng cho các lần gọi sau với các giá trị tham số khác nhau, giúp giảm thời gian tạo plan mới và cải thiện hiệu suất.
• Sự khác biệt chính giữa Plan Cache và Procedure Cache là: Plan Cache có thể chứa nhiều loại excution plan (query ad-hoc, view, chức năng,...), trong khi Procedure Cache chỉ chứa plan của stored procedures.

-- check cache trong SQL Server:
SELECT 
    cacheobjtype, 
    objtype, 
    usecounts, 
    size_in_bytes, 
    text
FROM 
    sys.dm_exec_cached_plans
CROSS APPLY 
    sys.dm_exec_sql_text(plan_handle);        

Tổng kết và Tối ưu hóa Hiệu Suất

1. Buffer Cache:

- Tăng dung lượng RAM nếu Buffer Cache Hit Ratio thấp.

- Xem xét cấu hình max server memory để đảm bảo SQL Server sử dụng bộ nhớ hiệu quả.

2. Plan Cache:

- Sử dụng tham số hóa truy vấn và stored procedures để tăng tái sử dụng kế hoạch thực thi.

- Kiểm tra và xóa bỏ các kế hoạch thừa thãi hoặc không hiệu quả trong Plan Cache.

3. Procedure Cache:

- Đảm bảo rằng các stored procedures được thiết kế để tối ưu hóa việc tái sử dụng các kế hoạch.

Điều chỉnh đúng mức các cache này sẽ giúp bạn cải thiện hiệu suất của SQL Server, giảm thiểu thời gian truy cập disk và tối ưu hóa thời gian xử lý query.