在以前使用Oracle時，只要遇到效能不好時，前輩就教我們一個最簡單的方式進行，就是將資料透過Oracle中提供的 imp/exp(Import/Export)的指令將某表格的資料重新的載出再載入，執行完成後，非常神奇的，果然效能馬上提升，這個原理其實也是相同，由於資料重新的載入，所以可以將資料更密集的排放在一起。
PS：Oracle也是有許多調整資料庫的作法，由於並非我的強項，在此僅介紹以往的一個小方法給大家參考。
資料庫也是有相同的問題，運作的過程中不斷的有資料進行新增、修改、刪除的動作，相對的，是否有需要進行重組，這個答案是肯定的，當然是需要的，但是透過磁碟重組是否也可以完成，首先我們先簡單的分成兩個部分進行說明：
- MDF與LDF檔案：一個資料庫的組成通常有這兩個檔案，相信有看過我的另一篇文章 [SQL Server 儲存設備 (Storage) 最佳調整作業] 的人一定知道，盡可能的規畫MDF與LDF的初使大小與檔案數量，不要太仰賴自動成長，因為每次檔案的成長會造成大量的I/O，所以請盡量注意初使值的設定。那是否要在進行磁碟重組，其實個人不太建議進行，而且也不太需要，因為磁碟重組需要大量的I/O可能會造成效能的問題，如果您真的有需要進行的話，最好執行完成後，透過 DBCC CHECKDB 的指令檢查一下資料庫，這樣會比較保險。
- Index與Data：這個也是本篇的重點，指的就是寫入到MDF檔中的資料與索引，由於彼此的資料結構不相同，所以需要透過SQL Server中的重組方式來進行，在下列我們會接續介紹。
在什麼樣的的情況下需要進行 Index 的重整與重建，其實最主要的就是進行索引碎片(Index Fragmentation) 的分析，而索引碎片 (Index Fragmentation) 主要分成下列兩個部份：
- 外部碎片 (External Fragmentation)：外部碎片主要發生於資料存放的索引頁 (Index Leaf Page) 不連續所造成，當有新的索引產生時，會先檢查索引頁中是否有空間可以進行存放，如果沒有時，就會產生一個新的索引頁，然後再將資料放入，因此也就造成索引存放不連續，當進行索引掃描 (Index Scan) 時，會造成讀寫頭過多的移動，造成 I/O 的浪費。
- 內部碎片 (Internal Fragmentation)：內部碎片主要發生在當填滿因素 (fill factor) 不為 0 或 100 %的時候，比如說設定填滿因素 (fill factor) 為 80%，剩下的20%就是內部碎片，但是填滿因素到底要設定多少才是合適的，你可以參考我的另外一篇文章 [效能調整 - 填滿因數對資料庫的影響] 裡面會有詳細的說明與實驗比較。
關於碎片的分析可以透過下列的指令進行，可以得知目前使用中的資料庫所有資料表的索引情況，此處外部碎片主要是看 Avg_fragmentation_in_percent 的欄位，而內部碎片主要是看 Avg_page_space_used_in_percent 的欄位，兩個欄位的判斷條件如下：
- Avg_fragmentation_in_percent：
- 如果此欄位的值 > 10 and < 30 的話請進行索引的重整 (REORGANIZE) 即可。
- 如果此欄位的值 > 30 的話請進行索引的 索引重建 (REBUILD) 。
- Avg_page_space_used_in_percent ：
- 如果此欄位的值 > 60 and < 75 的話請進行索引的重整 (REORGANIZE) 即可。
- 如果此欄位的值 > 30 的話請進行索引的 索引重建 (REBUILD) 。
PS：請特別注意第二段(Avg_page_space_used_in_percent)的部份，當您的 fill factor 設定不為預設值 0 或 100 時，請再自行調整值與比率的部份，而且一般不會判斷此欄位的值，就進行重整或重建。
索引碎片分析語法：
SELECT CAST(DB_NAME(database_id) AS VARCHAR(20)) AS [DATABASE Name],
CAST(OBJECT_NAME(OBJECT_ID) AS VARCHAR(20)) AS [TABLE NAME], Index_id,
Index_type_desc, Avg_fragmentation_in_percent,
Avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(N'AdventureWorks2008R2'),
NULL,NULL,NULL,'Detailed')
WHERE index_id > 0
索引重整 (REORGANIZE)：
ALTER INDEX IX_SalesOrderDetail ON Sales.SalesOrderDetail
REORGANIZE WITH (FILLFACTOR = 100);
索引重建 (REBUILD)：
ALTER INDEX IX_SalesOrderDetail ON Sales.SalesOrderDetail
REBUILD WITH (FILLFACTOR = 100);PS：FILLFACTOR可以不指定，如果不指定時，系統會透過預設的 fill factor 進行。
最後我在分享一個在官網上分享的一段自動分析索引碎片進行重整與重建的動作，你可以透過下列的語法進行，請特別注意，此段語法主要會根據你目前使用的資料庫進行分析，所以請在執行時確認一下即可。
-- Ensure a USE <databasename> statement has been executed first.
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
SELECT
object_id AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;
-- Open the cursor.
OPEN partitions;
-- Loop through the partitions.
WHILE (1=1)
BEGIN;
FETCH NEXT
FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag;
IF @@FETCH_STATUS < 0 BREAK;
SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
FROM sys.objects AS o
JOIN sys.schemas as s ON s.schema_id = o.schema_id
WHERE o.object_id = @objectid;
SELECT @indexname = QUOTENAME(name)
FROM sys.indexes
WHERE object_id = @objectid AND index_id = @indexid;
SELECT @partitioncount = count (*)
FROM sys.partitions
WHERE object_id = @objectid AND index_id = @indexid;
-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
IF @frag < 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
IF @frag >= 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
IF @partitioncount > 1
SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
EXEC (@command);
PRINT N'Executed: ' + @command;
END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;
-- Drop the temporary table.
DROP TABLE #work_to_do;
GO
相關文章：
維護記錄檔與索引的技巧
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 Index Fragmentation and Its Resolution
http://www.sql-server-performance.com/2004/index-fragmentation/
What is disk defragmentation?
http://windows.microsoft.com/en-US/windows-vista/What-is-disk-defragmentation
Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://technet.microsoft.com/en-us/library/cc966523.aspx
DBCC INDEXDEFRAG (Transact-SQL)
http://technet.microsoft.com/zh-tw/library/ms177571.aspx
sys.dm_db_index_physical_stats (Transact-SQL)
http://msdn.microsoft.com/zh-tw/library/ms188917.aspx
Index Fragmentation in SQL Server 2005
http://sql-articles.com/articles/performance-tunning/index-fragmentation-in-sql-server-2005/
How to Detect Table Fragmentation in SQL Server 2000 and 2005
http://www.sql-server-performance.com/2006/detect-fragmentation-sql2000-sql2005/
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
關鍵字：SQL Server、Index Maintenance、Index Fragmentation、Index Defragmentation、REORGANIZE、REBUILD、Internal Fragmentation、External Fragmentation
沒有留言:
張貼留言