Bộ tối ưu SQL phụ thuộc nhiều vào các index được định nghĩa cho bảng đặc thù. Index có 2 tác dụng: không index sẽ làm chậm tốc độ truy vấn SELECT, quá nhiều index sẽ làm chậm các truy vấn DML. Do đó, cần phải lựa chọn cân bằng các index. Bên cạnh số lượng index, các trường và thứ tự của chúng cũng rất quan trọng.

Khi tạo các index, ước lượng số lượng các giá trị duy nhất của cột sẽ có với từng trường riêng biệt. Ví dụ, idxCity index trong ví dụ không thực sự tốt để đánh index. Khi bạn muốn tìm kiếm khách hàng trong thành phố New York, nó có thể trả về hàng nghìn dòng, sau đó được tìm kiếm tuần tự. Các index này giúp tăng tốc truy vấn SELECT và giảm thời gian phản hồi cho câu lệnh DML.

Composite index – Các index bao gồm nhiều hơn 1 trường được gọi là composite index. Loại index này nên được tạo nếu bạn viết câu truy vấn trong đó có nhiều trường trong mệnh đề WHERE và tất cả các trường này sẽ cho kết quả ít dòng hơn so với dùng 1 trường.
Ví dụ, trong CSDL mẫu trên, index “IdxCustDate” là 1 composite index. Index này chỉ thích hợp nếu tỉ lệ giữa khách hàng và số lượng đặt hàng của họ là cao nghĩa là trung bình khách hàng đặt hàng nhiều hơn 1000 đơn hàng.

Clustered index – 1 clustered index xác định thứ tự vật lý của dữ liệu trong bảng – nghĩa là dữ liệu thực được sắp xếp dựa vào trường trong index. Điều này tương tự như thư mục điện thoại, trong đó sắp xếp dữ liệu bởi trường tên. Chỉ có duy nhất 1 clustered index trong 1 bảng. Những index này đặc biệt hiệu quả với cột trong đó thường được tìm kiếm cho 1 dải giá trị.

Rebuild và Reorganize Index
Với những bảng có sử dụng Index mà những thao tác cập nhật, thêm dữ liệu (UPDATE, INSERT) xảy ra nhiều thì những Index trên bảng đó sẽ bị phân mảnh. Sự phân mảnh của Index được chia làm 2 loại:

Internal Fragmentation: Khi trang nhớ (Page) lưu Index có nhiều khoảng trống, dẫn đến việc SQL Server sẽ phải mất thêm nhiều chi phí (cần phải đọc nhiều trang hơn) khi quét qua toàn bộ Index.

Để dễ hình dung, bạn có thể hiểu nếu toàn bộ nội dung của Index có kích thước tương đương với 5 trang nhớ, nhưng mỗi trang nhớ chỉ lưu được 50%, như vậy cần tới 10 trang đế lưu index và bạn sẽ phải quét qua tổng cộng 10 trang này khi cần đọc toàn bộ index. Chi phí sẽ tăng lên gấp đôi so với trường hợp tối ưu.

External Fragmentation: Là hiện tượng khi thứ tự logic của các trang nhớ không khớp với thứ tự vật lý của các trang nhớ, và nó cũng tạo ra các khoảng trống trong trang nhớ.

Sự ảnh hưởng của External Fragamentation làm SQL Server không thể đọc liên tục các trang nhớ mà thường có thêm thao tác “jump” giữa các trang nhớ. Dĩ nhiên nếu toàn bộ các trang nhớ đã nằm trong Buffer Pool thì các thao tác Jump là không còn do SQL Server sẽ đọc thẳng từ Buffer Pool mà không cần truy xuất vào các file trên ổ đĩa.
Để giải quyết vấn đề phân mảnh nói trên, ta thường có 2 cách giải quyết là Rebulid hoặc Reorganize Index:
Rebulid Index: Sắp xếp lại các giá trị trong Index và sử dụng một tập các trang nhớ mới. Thường sử dụng phương pháp này nếu độ phân mảnh của Index > 30%.
Reorganize Index: Vẫn sử dụng các trang nhớ lưu Index có sẵn những sẽ hoán đổi dữ liệu có trong các trang nhớ, thao tác này sẽ tốn ít chi phí hơn việc Rebulid Index và luôn thực hiện online. Phương pháp này thường sử dụng khi độ phân mảnh của Index <=30%.
Chú ý: 30% là con số khuyên dùng của một số chuyên gia, tuy nhiên con số này có thể thay đổi tùy thuộc tính chất của hệ thống và dữ liệu cụ thể.