1. Execution Plan là gì?

Execution plan được hiểu như danh sách những việc phải làm để hoàn thành một câu lệnh truy vấn. 

Khi bạn gửi một câu lệnh SELECT đến SQL Server, sẽ có nhiều cách thực hiện những công việc cần thiết để cho ra kết quả mong muốn và Query Optimizer chịu trách nhiệm cho việc chọn cách truy vấn thế nào cho phù hợp với những tham số đầu vào (tìm ra Execution Plan được cho là là ‘tốt’, chứ không phải ‘tốt nhất’). Query Optimizer sẽ sử dụng các thông tin cần thiết như những indexes nào trên bảng đó có thể dùng, statistics của các cột liên quan như thế nào, có constraint gì không,… để xây dựng những plan khả thi và chọn một trong số đó mà nó cảm thấy đủ tốt trong một khoảng thời gian hữu hạn. Kết quả của quá trình này tạo ra compiled plan, tiếp theo SQL Server sẽ làm theo những bước mô tả trong plan này để đạt được kết quả mong muốn, execution plan là run time objects của compiled plan. Việc tạo ra các compiled plan này khá tốn chi phí nên nó sẽ được lưu lại (trong plan cache) để sử dụng cho những lần sau.

Tham khảo sách: SQL Server Execution Plans, Third Edition, by Grant Fritchey

Free eBook download (PDF): Download here.
Download code samples: Download here. 

trong đó:

• Phần statistics time:

  • CPU time: thời gian CPU đã sử dụng để cho ra kết quả dữ liệu, đơn vi là millisecond

  • elapsed time: thời gian tính từ lúc SQL Server nhận được câu truy vấn cho đến khi trả xong dòng dữ liệu cuối cùng cho client.

  • và: elapsed time (duration) = CPU Time + Wait time

• Phần statistics IO:

  • Scan count: số lần đọc thêm (có thể là seek hoặc scan) để đảm bảo ko bỏ sót dòng dữ liệu nào từ các page liền kề. Thông số này hơi khó hình dung nhưng một khi bạn hiểu rõ cấu tạo index trong SQL Server và cách index được truy cập sẽ mường tượng ra cách nó đếm giá trị này.

  • Logical reads: Số lượng data pages (8KB page) được đọc từ bộ nhớ (buffer cache) để lấy các dòng dữ liệu cần thiết. Đây là giá trị chúng ta cần quan tâm vì số lượng pages càng lớn câu truy vấn của bạn càng dùng nhiều CPU hơn và thời gian dài hơn. Để dễ hình dung có thể liên tưởng giá trị này là số lượng công việc phải làm.

  • Physical reads: số lượng data pages (8KB page) được đọc lên từ disk để phục vụ câu truy vấn. Khi xử lý câu truy vấn, nếu page cần đọc đã ở trên buffer cache thì giá trị logical read sẽ được count, nếu chưa có thì storage engine sẽ gửi request I/O để đọc page đó từ disk và physical read đươc count, sau đó đến logical read được count.

  • Read-ahead read: số lượng data pages được đọc lên từ disk theo kiểu dự đoán.

Xem thêm thông tin trên trang Microsoft cho statistics time và statistics io.

3. SQL Server INDEX

3.1 Index là gì?

Index là một kiểu đánh chỉ mục trên 1 bảng hoặc view nhằm mục đích tăng tốc truy vấn. Một Index bao gồm các key được lấy từ các cột trong table hoặc view. Những key này được lưu theo cấu trúc (B-tree) cho phép SQL Server có thể tìm thấy các row liên quan một cách nhanh chóng và hiệu quả nhất. Có 2 loại Index là Clustered Index và Non Clustered Index.

3.2 Clustered Index

• Clustered index sắp xếp và lưu data rows trong table hoặc view dựa trên giá trị của key, gồm các cột trong định nghĩa index. Chỉ có 1 clustered index mỗi table, bởi vì các data rows chỉ có thể lưu 1 lần theo 1 thứ tự (lưu vật lý).

• Data rows chỉ được lưu theo 1 một thứ tự nhất định (sorted order) khi bảng đó có chứa clustered index. Khi một table có clustered index, table đó được gọi là clustered table. Nếu table không có clustered index, các data rows của nó được lưu theo một cấu trúc không có thứ tự gọi là heap.

3.3 Non Clustered Index

• Nonclustered index có cấu trúc độc lập với data rows. Một nonclustered index chứa các giá trị key nonclustered index và mỗi giá trị key này trỏ tới 1 data row.

• Việc trỏ từ một index row ở nonclustered index đến 1 data row được gọi là row locator. Cấu trúc của row locator phụ thuộc vào việc dữ liệu được lưu ở heap hay là clustered table. Đối với head, một row locator là một con trỏ trỏ đến data row, còn với clustered table, một row locator chính là clustered index key.

Cả 2 clustered index và nonclustered index đều có thể định nghĩa duy nhất, với keyword là unique, nghĩa là không có 2 data row nào có index key giống nhau. Còn nếu index không unique, thì sẽ có nhiều data row có thể có các index key giống nhau.

Số lượng column trong clustered (hoặc non-clustered) index có thể có ảnh hưởng đáng kể đến performance khi thực hiện các hành động INSERT, UPDATE, DELETE.

3.4 Các kiểu SCAN

Table Scan

• When: Table Scan xảy ra khi select dữ liệu ở một table không có clustered index. Do việc lưu các data row không theo thứ tự nào nên SQL server sẽ thực hiện query bằng cách scan qua toàn bộ table.

• Good or bad: Nếu lượng data nhỏ thì ta không thấy sự khác biệt ở tốc độ select. Tuy nhiên với một lượng data lớn, vấn đề performance sẽ xảy ra.

• Action item: Tạo ra 1 clustered index.

Clustered Index Scan

• When: Table với clustered index được truy cập, table đó không có non-clustered index hoặc query đó không sử dụng nonclustered index.

• Good or bad: Nó khá là xấu. Trừ khi lượng data rows trả về nhiều, còn nếu không thì nó cũng ảnh hưởng performance khá nhiều, vì nó cũng scan hết các index key values.

• Action item: nên cân nhắc các index key để tối ưu index, không phải scan toàn bộ index key values.

Clustered Index Seek

• When: Một table với clustered index được truy cập và cấu trúc B-tree có thể làm lọc được các kết quả trả về, giúp chúng ta lấy được 1 số lượng nhất định rows từ table.

• Good or bad: rất tốt để thấy Clustered index seek.

• Action item: đánh giá khả năng xảy ra mà query sử dụng nonclustered index, để có thể loại bỏ và tận dụng clustered index seek.

Tham khảo thêm ở bài viết gốc (LINK).

Cách tạo index

Cú pháp đơn giản: CREATE [UNIQUE] INDEX ten_index ON ten_bang (cot_hoac_danh_sach_cot);

4. Tối ưu hóa truy vấn?

• Chọn lựa và tối ưu hóa các chỉ mục (index)

• Tránh các câu truy vấn con trong đó có sử dụng các giá trị từ câu truy vấn cha

• Câu lệnh SELECT chỉ lấy những cột cần thiết

• Tối ưu hóa toán tử trong mệnh đề WHERE. Ví dụ:

  • Các toán tử “IS NULL”, “<>”, “!=”, “!>”, “!<“, “NOT”, “NOT EXISTS”, “NOT IN”, “NOT LIKE” không sử dụng đặc tính index mà thay vì thế nó sẽ dò tìm toàn bảng gây ảnh hưởng đến tốc độ của câu truy vấn.

  • Toán tử “LIKE ‘%abc'” không sử dụng index mà nó sẽ scan qua toàn bộ bảng; còn nếu bạn dùng “LIKE ‘abc%'” thì… nó lại dùng đến INDEX.

• Hạn chế sử dụng function trên column built trực tiếp. Ví dụ:

  • Không nên: SELECT * FROM KhachHang WHERE UPPER(Ten) = 'TIEN';

  • Mà dùng: SELECT * FROM KhachHang WHERE Ten= UPPER('tien');

• Nên sử dụng EXISTS() để kiểm tra truy vấn có trả về dữ liệu hay không? Sử dụng IF EXISTS thay cho COUNT(*) hoặc COUNT(DISTINCT).

• Không nên dùng con trỏ (cursor) khi cần xử lý từng dòng dữ liệu.

• Sử dụng store procedure

Tham khảo thêm:

• https://www.toptal.com/sql/sql-database-tuning-for-developers

• https://www.sisense.com/blog/8-ways-fine-tune-sql-queries-production-databases

• https://www.brentozar.com/sql/sql-server-performance-tuning