Tham Số Fill Factor

Guess Post: Vũ Minh Tâm

Trong buổi Seminar lần trước, có bạn đã hỏi ý nghĩa của tham số FILL FACTOR là gì và sử dụng nó có ý nghĩa như thế nào. Bẵng đi gần 2 năm, gần đây tôi có làm việc lại với SQL Server và đụng chạm đến tham số này.

I. 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.
Khoảng trống trong trang nhớ xuất hiện do nhiều nguyên nhân, ở đây tôi sẽ liệt kê 2 nguyên nhân chính:
- Do cài đặt tham số FILL FACTOR (sẽ giải thích kĩ hơn ở phần sau)
- Do các thao tác DELETE, UPDATE làm thay đổi các giá trị được đánh Index, dẫn đến việc các giá trị được đánh Index không liên tục. Những giá trị mới thêm vào sẽ được chèn vào các vị trí thích hợp đảm bảo sự sắp xếp của Index. Ví dụ:
Trang nhớ 1 lưu các giá trị 1,2,3,4
Trang nhớ 2 lưu các giá trị 5,6,7
Nếu tiến hành xóa giá trị 2,3,4 thì:
Trang nhớ 1 sẽ lưu giá trị 1 và còn trống 75%
Trang nhớ 2 lưu các giá trị 5,6,7
Nếu thêm giá trị 8 vào Index, nó sẽ được lưu vào trang nhớ 2 để đảm bảo thứ tự:
Trang nhớ 1 lưu giá trị 1
Trang nhớ 2 lưu giá trị 5,6,7,8
Do đó khoảng trống ở trang nhớ 1 chỉ được lấp đầy khi thêm vào các giá trị nằm trong khoảng 1 đến giá trị 5
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ớ. Ví dụ:
Index bao gồm 3 trang nhớ, trong đó thứ tự logic và vật lý của dữ liệu là như nhau. Mọi trang nhớ đều lưu đủ dữ liệu ngoại trừ trang nhớ 3.

Khi thêm dữ liệu có giá trị 4, giá trị này phải được thêm vào Trang nhớ 1, vị trí giữa giá trị 3 và 5, tuy nhiên trang nhớ 1 đã đầy và SQL Server phải thực hiện thao tác “page-split” và ta có hình bên dưới mô tả trạng thái của cả Index.
Ta có thể thấy thứ tự logic của Index không còn khớp với thứ tự vật lý nữa.

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ể.

II. FILL FACTOR

Khi Rebulid lại Index, ta có thể khai báo thêm tham số FILLFACTOR. Việc khai báo tham số này nhằm đảm bảo hạn chế External Fragmentation.
FILLFACTOR là một giá trị quyết định phần trăm dung lượng tại mỗi trang nhớ (leaf-level) sẽ được dành để lưu dữ liệu. Giá trị của nó từ 0-100, trong đó giá trị mặc định 100, hoặc giá trị 0 cho phép 1 trang nhớ có thể lưu dữ liệu đến mức tối đa.
Xem lại phần External Fragmentation, ta thấy rằng ở chế độ mặc định là không tốt lắm, nếu các giá trị trong Index là không liên tục, điều này sẽ dẫn đến việc “page split” và xuất hiện cả 2 hiện tượng Fragmentation. Nhất là với các dữ liệu Index mà giá trị là kiểu chuỗi kí tự.
Vì vậy, nếu thiết lập giá trị FILLFACTOR ngoài giá trị mặc định, ví dụ FILLFACTOR = X sẽ có những tác dụng sau:
- Rebulid Index sẽ sắp xếp lại các trang nhớ và dữ liệu và đảm bảo mỗi trang nhớ sẽ dành X% cho việc lưu dữ liệu
- Khoảng trống còn lại, sẽ dành cho những thao tác thêm dữ liệu mới sau này, điều đó đảm bảo khi có dữ liệu mới, dữ liệu này sẽ được thêm vào đúng vị trí trong index mà không xuất hiện tình trạng External Fragmentation.

III. Đặt giá trị nào cho FILLFACTOR

Tổng kết lại, qua một số nguồn ta có thể đặt giá trị FILLFACTOR như sau, với một số tình huống cụ thể:

Tính chất Giá trị FILL FACTOR Giải thích
Bảng tĩnh 0 hoặc 100  
Bảng ít cập nhật 95  
Bảng hay thay đổi 70 đến 90 Giá trị cho những bảng này cần tự kiểm tra và lựa chọn giá trị thích hợp với từng hệ thống
Bảng có Clustered Index trên cột Identity hoặc dựa trên cột Identity 0 hoặc 100 Những bảng có tính chất như vậy không bao giờ xuất hiện External Fragmentation do giá trị mới luôn nhận giá trị lớn nhất, và sẽ được thêm vào vị trí cuối cùng trong bảng

References:
http://www.practicalsqldba.com/2012/04/sql-server-index-fragmentation.html
http://sqlmag.com/blog/what-fill-factor-index-fill-factor-and-performance-part-1
http://sqlmag.com/blog/what-best-value-fill-factor-index-fill-factor-and-performance-part-2




2 Comments
Posted on 9/10/2013 | Categories: Bên trong SQL Server, Index, Performance tuning

Các bài viết tương tự

Comments
  • Nguyễn Văn Sỹ (09/01/2014 12:27 pm)

    anh cho em hỏi với ạ. Hiện tại database của cty em khá lớn. Nên có kế hoạch là cứ 1 tháng sẽ reindex và shrink database 1 lần. Theo em hiểu thì reindex nghĩa là mình sẽ tổ chức lại các page sao cho không bị phân mảnh nữa, để cho quá trình đọc index được tối ưu hơn, như vậy rebuid index sẽ làm cho dung lượng của databse giảm đi đúng ko ạ ? Nhưng sau khi rebiud index ở các bảng xong em thấyđung lượng của file .mdf lại tăng lên khá nhiều. Em cũng chưa hiểu nguyên nhân tại sao, sau đó e tiến hành shrink file mdf thì thấy dung lượng giảm đi, nhưng kiểm tra lại index thì lại thấy các index bị phân mảnh như cũ và phải reindex lại ?. em reindex lại và shrink database thì thấy dung lượng cũng không giảm đi gì nhiều, vậy xin anh giải thích giúp em trường hợp này, tại sao khi reindex thì dung lượng file .mdf lại tăng lên, theo e hiểu thì nó phải giảm đi chứ ?, và tai sao khi shrink file mdf thì index lại bị phân mảnh trở lại ?. em cảm ơn nhiều !

  • Vũ Huy Tâm (09/01/2014 5:09 pm)

    Có nhiều lý do để KHÔNG NÊN shrink mdf (trừ khi nó chiếm hết đĩa gây ảnh hưởng đến ứng dụng khác). Một trong những lý do đó chính là làm index bị phân mảnh. Bạn chỉ cần rebuild index là yên tâm rồi. Dung lượng file tăng lên có thể do cần dùng không gian tạm trong lúc rebuild index, phần không gian này sẽ được dùng để lưu dữ liệu mới, nó sẽ không bị lãng phí và bạn không cần shrink.
    Theo mình nếu rebuild index hàng tháng thì hơi lâu, bạn nên chạy hàng ngày hoặc cùng lắm là hàng tuần. Nhưng khi rebuild thì nên đọc check fragmentation, nếu >30% thì rebuild, nếu nhỏ hơn thì bỏ qua hoặc reorg, tức là chạy có chọn lọc chứ không phải nhắm mắt nhắm mũi rebuild hết 1 loạt.

Leave a Reply

Hướng dẫn: Để nhập mã T-SQL bạn dùng thẻ <pre lang="tsql"> và </pre>.
Ví dụ: <pre lang="tsql">SELECT * FROM MyTable</pre>