線上和離線重建索引的不同結果

我在類型為 的外鍵列上有一個非聚集、非唯一索引bigint。當我線上重建索引時，平均碎片下降到 3%，有 2 個碎片和 30 頁。

當我離線執行相同的重建索引時，平均碎片為 25%，有 4 個碎片和 28 頁。

我認為FILLFACTOR是 90。數據庫是 77GB。我不是 DBA 或類似人員，我是 C# 開發人員，所以我並不完全熟悉所有術語。此表上沒有任何活動，這發生在我們的開發環境中。

這是查詢，名稱已編輯。

ALTER INDEX [IX] ON [dbo].[Table]
REBUILD WITH
(
   PAD_INDEX  = OFF, 
   STATISTICS_NORECOMPUTE  = OFF, 
   ALLOW_ROW_LOCKS  = ON, 
   ALLOW_PAGE_LOCKS  = ON, 
   ONLINE = ON, 
   SORT_IN_TEMPDB = ON
);

什麼可能導致這種差異？同樣的情況發生在多個表上。

這絕不是一個完整的答案，但如果您嘗試類似的事情並報告您的結果，可能會有所進展。

我無法複製它們。用下面的測試表

CREATE TABLE [dbo].[Table]
(
Col BIGINT
)

CREATE NONCLUSTERED INDEX IX ON [dbo].[Table](Col)

INSERT INTO [dbo].[Table]
SELECT top 12000 ROW_NUMBER() OVER (ORDER BY @@SPID)
FROM master..spt_values v1, master..spt_values v2

並多次執行以下腳本

USE FragTest;

DECLARE @DbccPage TABLE (
 ParentObject VARCHAR(255),
 Object       VARCHAR(255),
 Field        VARCHAR(255),
 VALUE        VARCHAR(255))

DECLARE @sp_index_info TABLE (
 PageFID         TINYINT,
 PagePID         INT,
 IAMFID          TINYINT,
 IAMPID          INT,
 ObjectID        INT,
 IndexID         TINYINT,
 PartitionNumber TINYINT,
 PartitionID     BIGINT,
 iam_chain_type  VARCHAR(30),
 PageType        TINYINT,
 IndexLevel      TINYINT,
 NextPageFID     TINYINT,
 NextPagePID     INT,
 PrevPageFID     TINYINT,
 PrevPagePID     INT,
 PRIMARY KEY (PageFID, PagePID));

DECLARE @I INT = 0

WHILE @I < 2
 BEGIN
     DECLARE @Online VARCHAR(3) = CASE
         WHEN @I = 0 THEN 'OFF'
         ELSE 'ON'
       END

     EXEC('ALTER INDEX [IX] ON [dbo].[Table]
REBUILD WITH
(
   PAD_INDEX  = OFF, 
   STATISTICS_NORECOMPUTE  = OFF, 
   ALLOW_ROW_LOCKS  = ON, 
   ALLOW_PAGE_LOCKS  = ON, 
   ONLINE = ' + @Online + ', 
   SORT_IN_TEMPDB = ON
);')

     INSERT INTO @sp_index_info
     EXEC ('DBCC IND ( FragTest, ''[dbo].[Table]'', 2)' );

     ; WITH T
          AS (SELECT *,
                     PagePID - ROW_NUMBER() OVER (PARTITION BY PageType, IndexLevel ORDER BY PagePID) AS Grp
              FROM   @sp_index_info)
     SELECT PageType,
            MIN(PagePID) AS StartPID,
            MAX(PagePID) AS EndPID,
            COUNT(*)     AS [count],
            IndexLevel
     FROM   T
     GROUP  BY Grp,
               PageType,
               IndexLevel
     ORDER  BY PageType DESC,
               StartPID

     DECLARE @DynSQL NVARCHAR(4000)

     SELECT @DynSQL = N'DBCC PAGE (FragTest, ' + LTRIM(PageFID) + ',' + LTRIM(PagePID) + ',3) WITH TABLERESULTS'
     FROM   @sp_index_info
     WHERE  PageType = 10

     INSERT INTO @DbccPage
     EXEC(@DynSQL)

     SELECT VALUE AS SinglePageAllocations
     FROM   @DbccPage
     WHERE  VALUE <> '(0:0)'
            AND Object LIKE '%IAM: Single Page Allocations%'

     SELECT avg_page_space_used_in_percent,
            avg_fragmentation_in_percent,
            fragment_count,
            page_count,
            @Online                                                   AS [Online],
            (SELECT COUNT(*)
             FROM   @DbccPage
             WHERE  VALUE <> '(0:0)'
                    AND Object LIKE '%IAM: Single Page Allocations%') AS SinglePageAllocations
     FROM   sys.dm_db_index_physical_stats(db_id(), object_id('[dbo].[Table]'), 2, NULL, 'DETAILED')
     WHERE  index_level = 0

     DELETE FROM @sp_index_info

     DELETE FROM @DbccPage

     SET @I = @I + 1
 END 

我一直得到類似的結果

線上 = 關閉

PageType StartPID    EndPID      count       IndexLevel
-------- ----------- ----------- ----------- ----------
10       119         119         1           NULL
2        2328        2351        24          0
2        2352        2352        1           1
2        2384        2392        9           0


SinglePageAllocations
----------------------

(0 row(s) affected)


avg_page_space_used_in_percent avg_fragmentation_in_percent fragment_count       page_count           Online SinglePageAllocations
------------------------------ ---------------------------- -------------------- -------------------- ------ ---------------------
98.8139362490734               0                            2                    33                   OFF    0

線上 = 開

PageType StartPID    EndPID      count       IndexLevel
-------- ----------- ----------- ----------- ----------
10       115         115         1           NULL
2        114         114         1           0
2        118         118         1           1
2        2416        2449        34          0



SinglePageAllocations
-----------------------
(1:114)
(1:118)


avg_page_space_used_in_percent avg_fragmentation_in_percent fragment_count       page_count           Online SinglePageAllocations
------------------------------ ---------------------------- -------------------- -------------------- ------ ---------------------
97.4019644180875               2.85714285714286             2                    35                   ON     2

至少在測試中，我做了兩個平衡碎片之間的差異（儘管與您的測試類似，我確實發現線上重建索引會導致更高的頁數。）。

我發現該Online = OFF版本總是使用統一的擴展區並且單頁分配為零，而Online = ON似乎總是將索引根頁和第一個索引葉頁放在混合擴展區中。

將第一個索引葉頁放在混合區中，其餘的放在連續的統一區中會導致片段計數為 2。

該Online = OFF版本避免了由單獨的索引葉頁引起的碎片，但葉頁的連續性被共享相同範圍的索引根頁破壞，並且碎片數也為 2。

我在一個有 1 GB 可用空間且沒有並發活動的新創建的數據庫上執行我的測試。也許該Online = OFF版本更容易受到並發分配的影響，導致它被賦予非連續的統一範圍。

引用自：https://dba.stackexchange.com/questions/35671