效能調整 - 填滿因數對資料庫的影響

索引是資料庫中非常重要的一環，它可以有效的加快查詢時的速度，但是相對的，可能也會造成新增資料時的負擔，在SQL Server中有許多相關的參數中其實可以進行調整，並針對各個不同的公司的使用情況來加以調整，藉以讓索引發揮更大的效能。

而 Fill Factor 是在索引中非常重要的一個參數設定，詳細的說明整理如下 (來自 MSDN)，簡單的說如果你的系統如果為 OLAP 類型時，那你就可以將設定值設定為 0 或 100 (0 為預設值，且 0 與 100 的設定皆相同)，因為執行的語法類型全部都是查詢，但如果是 OLTP 則可以自行觀察並調整設定值，底下我分成幾個部份進行實驗，藉以讓大家更了解 Fill Factor 參數調整與 IO的影響。

Fill Factor
使用 fill factor 選項，主要指定 Microsoft SQL Server 在使用現有的資料建立新索引時，應該在每一頁填滿多少空間。填滿因數會影響效能，因為當頁面填滿之後，SQL Server 就必須花費時間進行頁面的分割。

PS：
A、填滿因數的預設值為 0；有效值介於 0 到 100 之間。當 FILL FACTOR 設定為 0 或 100 時，分葉層級會完全填滿。
B、填滿因數只會用於建立或重建索引時。所有分頁都不會保持在特定的填滿程度。

重建索引：
ALTER INDEX IX_SalesOrderDetail ON Sales.SalesOrderDetail
REBUILD WITH (FILLFACTOR = 50, SORT_IN_TEMPDB = ON,
              STATISTICS_NORECOMPUTE = ON);


頁面分割
選擇正確的填滿因數值，可以減少可能的分頁分割，因為當基礎資料表中加入資料時，將有足夠的空間來進行索引擴充。

將新的資料列加入全文檢索頁時，Database Engine 會將幾乎一半的資料列移到新的分頁，以留出空間給新的資料列。這個重組動作稱為分頁分割。分頁分割可留出空間給新的記錄，但是執行時需要時間，而且是一項耗用大量資源的作業。此外，它也可能會造成資料片段過多，因而增加 I/O 作業。如果分頁分割次數過於頻繁，可以使用新的或現有的填滿因數值重建索引，藉以重新分配資料。

雖然 0 以外的較低填滿函數值能夠減少索引成長時進行分頁分割的需求，但是索引將需要更多的儲存空間，而且可能會降低讀取效能。即使對於會大量執行插入和更新作業的應用程式而言，讀取資料庫的次數通常比寫入資料庫的次數還要高，因數為 5 比 10。因此，指定預設值以外的填滿因數，將可能以與填滿因數設定呈反比的數量，降低資料庫的讀取效能。例如，一個 50 的填滿因數值，可能會使資料庫的讀取效能降低兩倍。讀取效能降低是因為索引包含更多的分頁，造成擷取資料所需的磁碟 IO 作業增加所致。


實驗測試：
1、使用空間上的比較：
sp_spaceused 'Sales.SalesOrderDetail';

fill factor -> 50%

fill factor -> 100%

在 fill factor 設定為50%的時候，當有新的資料加入時，為了保留額外空間給日後 Index 異動時快速加入，所以在 Index 的部份，你會看到系統會佔用了較大的空間。


2、資料讀取上的比較
查詢語法：
SELECT TOP 1000 [SalesOrderID]
      ,[SalesOrderDetailID]
      ,[CarrierTrackingNumber]
      ,[OrderQty]
      ,[ProductID]
      ,[SpecialOfferID]
      ,[UnitPrice]
      ,[UnitPriceDiscount]
      ,[LineTotal]
      ,[rowguid]
      ,[ModifiedDate]
  FROM [AdventureWorks2008R2].[Sales].[SalesOrderDetail]
  where ProductID = 776 and OrderQty > 2

I/O分析：
fill factor -> 50%
89 row(s) affected)
Table 'SalesOrderDetail'. Scan count 1, logical reads 285, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

fill factor -> 100%
(89 row(s) affected)
Table 'SalesOrderDetail'. Scan count 1, logical reads 283, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

從上述的結果來看，由於 Fill Factory 設為50%時，為了預留較多的空間，所以索引會較為分散，當然在查詢時，logical reads 的次數就會比較多，相對的也會比較慢，根據官方的說法，實際上可能會有2倍的落差，所以請小心評估後，再設定這個值。


3、資料新增上的比較：
新增語法：
INSERT INTO [AdventureWorks2008R2].[Sales].[SalesOrderDetail]
           ([SalesOrderID]
           ,[CarrierTrackingNumber]
           ,[OrderQty]
           ,[ProductID]
           ,[SpecialOfferID]
           ,[UnitPrice]
           ,[UnitPriceDiscount]
           )
     VALUES
           (43909
           ,'9467-4A37-A4'
           ,5
           ,776
           ,1
           ,100.114
           ,0)

I/O分析：
fill factor -> 50%
Table 'SpecialOfferProduct'. Scan count 0, logical reads 2, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesOrderHeader'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesOrderDetail'. Scan count 0, logical reads 17, physical reads 3, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

fill factor -> 100%
Table 'SpecialOfferProduct'. Scan count 0, logical reads 2, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesOrderHeader'. Scan count 0, logical reads 3, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesOrderDetail'. Scan count 0, logical reads 22, physical reads 6, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

佔用空間：
fill factor -> 50%

table name	count	Reserved	data	index_size	unused
SalesOrderDetail	121317	20072 KB	9888 KB	9432 KB	752 KB
	121318	20080 KB	9888 KB	9440 KB	752 KB

fill factor -> 100%

table name	count	Reserved	data	index_size	unused
SalesOrderDetail	121317	18032 KB	9888 KB	7400 KB	744 KB
	121318	18048 KB	9888 KB	7416 KB	744 KB

執行時間：

Time Statistics
Fill Factor	100%	50%
Client processing time	12	11
Total execution time	43	31
Wait time on server replies	31	20

在如同上述的 I/O 分析，你可以看出在 Insert 的狀況下， Fill Factory 50% 可以很快的加入表格中， logical reads 只需要17即可，比 Fill Factory 100% 的 22還要快，這是因為 50% 的情況下，頁面未填滿，所以可以很快的將 index 加入。


4、頁面分散程度分析：
select * from sys.dm_db_index_physical_stats
 (DB_ID(N'AdventureWorks2008R2'), OBJECT_ID(N'SalesOrderDetail'),
 NULL, NULL , 'DETAILED');


fill factor	name	avg fragmentation in_percent	fragment count	avg_page space_used in_percent
50%	IX_SalesOrderDetail	0.196850394	2	50.13369162
	IX_SalesOrderDetail	0	1	72.15221151
	IX_SalesOrderDetail	0	1	0.543612553
100%	IX_SalesOrderDetail	0	2	99.89869039
	IX_SalesOrderDetail	0	1	72.43637262

透過 sys.dm_db_index_physical_stats 可以查出索引的分散程度，你可以看出 avg_page_space_used_in_percent 就是大約你設定的索引分散程度，當然你可以透過索引的重組或重建來進行調整，關於詳細的細節，請參考 [索引分析與維護建議]。

5、索引頁面分散程度監視
當 fill factor 設定太低時，可能會造成索引頁面過於分散，可以透過效能監視器中的 SQLServer:Access Methods -> Page Splits/sec來進行觀察，此值建議要低於20以下，如果這個值太高的話，建議你可以降低 fill factor 的值，藉以改善問題。

Object: - SQLServer:Access Methods
Counter: - Page Splits/sec
Preferred Value: - < 20

總結：
從上述的幾個章節，你可以很快的了解到 fill factor 參數對整體效能的影響，當然你可以依舊使用原本的預設值，依然可以有很好的表現，但是由於每一個系統的讀寫頻率比重不一，所以建議你可以監視資料表的使用情況，再加以調整後會讓你的系統運作的更好。


相關文章：
維護記錄檔與索引的技巧
http://caryhsu.blogspot.com/2011/05/blog-post.html
SQL Server 上大型資料表的索引效能調整與維護
http://caryhsu.blogspot.com/2011/08/sql-server.html
效能調整 - 填滿因數對資料庫的影響
http://caryhsu.blogspot.com/2012/02/blog-post.html
索引分析與維護建議
http://caryhsu.blogspot.com/2012/02/blog-post_14.html

參考連結：
SQL Server 的 Access Methods 物件
http://technet.microsoft.com/zh-tw/library/ms177426.aspx
What is a page split? What happens? Why does it happen? Why worry?
 http://sqlblogcasts.com/blogs/tonyrogerson/archive/2007/06/28/what-is-a-page-split-what-happens-why-does-it-happen-why-worry.aspx
How to check Fragmentation on SQL Server 2005
http://blogs.msdn.com/b/jorgepc/archive/2007/12/09/how-to-check-fragmentation-on-sql-server-2005.aspx
sys.dm_db_index_physical_stats (Transact-SQL)
http://msdn.microsoft.com/zh-tw/library/ms188917.aspx
Understanding SQL Server Index Fill Factor Setting
http://www.mssqltips.com/sqlservertip/1940/understanding-sql-server-index-fill-factor-setting/
SET STATISTICS IO (Transact-SQL)
http://technet.microsoft.com/zh-tw/library/ms184361.aspx
填滿因數
http://technet.microsoft.com/zh-tw/library/ms177459(SQL.90).aspx
Performance Counters
http://sqlserverplanet.com/sql-optimization/performance-counters



關鍵字：SQL Server、Fill Factor、Index、IO Statistics、填滿因數