Sql-Server

如何辨識聚集索引候選

  • October 17, 2016

我發現自己處於繼承了數據庫並且人們抱怨數據庫性能的情況。我確定了大約十幾個沒有聚集索引或沒有精心規劃的聚集索引的表，其中一些表包含大量數據並且經常使用。我實際上正在努力找出為堆實施 CI 的最佳人選。經過大量閱讀後，我仍然不知道該怎麼做。我知道這取決於。所以我想給一張桌子作為樣本，希望得到一些建議。

該表有 122 列。是的，我知道…它包含大約 800 000 行。結構（摘錄）：

CREATE TABLE [dbo].[tO](
[ID] [uniqueidentifier] NOT NULL,
[MaID] [uniqueidentifier] NULL,
[OrID] [uniqueidentifier] NOT NULL,
[UsID] [uniqueidentifier] NOT NULL,
[CrUsID] [uniqueidentifier] NOT NULL,
[SuID] [uniqueidentifier] NULL,
[SOID] [uniqueidentifier] NULL,
[DeA1ID] [uniqueidentifier] NULL,
[DeA2ID] [uniqueidentifier] NULL,
[PayID] [uniqueidentifier] NULL,
[MAdID] [uniqueidentifier] NULL,
[ShID] [uniqueidentifier] NULL,
[SessID] [varchar](32) NULL,
[OrUID] [uniqueidentifier] NULL,
[PurOfUID] [uniqueidentifier] NULL,
[AddiUID] [uniqueidentifier] NULL,
[SupUID] [uniqueidentifier] NULL,
[PayTID] [uniqueidentifier] NULL,
[OPSID] [uniqueidentifier] NULL,
[StoRSID] [uniqueidentifier] NULL,
[PayMeID] [uniqueidentifier] NULL,
...
[OrderDate] [datetime] NULL,
[CreateDate] [datetime] NOT NULL,
[UpdateDate] [datetime] NULL,
[RowVersion] [timestamp] NOT NULL,
[Deleted] bit NOT NULL,
...
[CTR] int itentity(1,1) NOT NULL
CONSTRAINT [PK_Order] PRIMARY KEY NONCLUSTERED <= uses column [ID]

此表上存在 29 個非聚集索引。我猜他們中的一些人是在絕望中創造出來的……

我知道這個和其他表的設計不是很好。它太寬了，應該分開。這在我的名單上。

查詢幾乎總是在第 th 列上過濾

$$ OrID $$. 此列不是唯一的（具有單個 OrID 的最高行數 > 310 000）。所有查詢都在 DELETED = 0 上進行過濾。它們也在不同的數字列上進行過濾。 在不同的 uid 列上執行連接。

該表包含 FK，也在 uid 列上，並且具有父表，它們在使用 PK 連接的 uid 和子表之一上連接

$$ id $$. 在此表上嘗試了一些跟踪的使用者查詢後，我發現它們通常會過濾一行，而且性能一點也不差。查看執行計劃，我發現通常會查找其中一個非聚集索引，然後執行 RID 查找。這將花費大約 30% - 70% 的查詢。

所以我想它可以表現得更好。此外，如果有一個聚集索引，我假設可以刪除幾個非聚集索引。

編輯 10/16/15 附加資訊：

sp_BlitzIndex說：“22651751 轉發獲取，0 對堆刪除：dbo.tO（0）自我厭惡索引：具有轉發記錄或刪除的堆”

編輯結束

現在我對這個（和其他樣本）的問題是我們到處都有那些醜陋的唯一標識符。我閱讀了很多可比較的文章，但仍然不知道如何處理。

  • 我是否應該停止思考並向主鍵添加聚集索引

$$ ID $$所以遵循一般建議，即在所有情況下，有一個聚集索引基本上比沒有更好，即使它不完全符合 CI 標準（不是狹窄，不是不斷增加）？

  • 我是否應該更好地使用一個不那麼獨特但不斷增加的列

$$ CreateDate $$?

  • 我應該添加一個新的 IDENTITY 列並將 CI 放在這個列上嗎？

其他桌子上的情況可以與那張桌子相比。

**編輯 Okt-17 2016：**按照評論中的要求，我添加了實際存在的（混淆的）索引。（我知道其中一些可以很容易地被刪除而不需要任何工作，例如

$$ p $$被 100% 覆蓋$$ s $$…但這不是堆/ CI問題的核心）。

CREATE UNIQUE NONCLUSTERED INDEX [a]
   ON [dbo].[tO]([Deleted] ASC, [Status] ASC, [ID] ASC)
   INCLUDE([NumericA], [CreateDate], [char3], [Bool1], [Bool2], [varchar1], [OrderDate], [INT1], [SuID], [varcharT], [UsID], [MaID], [varcharS], [TinyINTd], [PurOfUID], [AddiUID]) WITH (FILLFACTOR = 90);            
GO
CREATE NONCLUSTERED INDEX [b]
   ON [dbo].[tO]([ID] ASC, [UsID] ASC, [SuID] ASC, [Deleted] ASC, [Status] ASC, [OrID] ASC, [TINYINTm] ASC, [varchar1] ASC, [varcharT] ASC, [OrderDate] ASC, [CreateDate] ASC, [PAN_decimal] ASC, [char3] ASC, [INT1] ASC, [NumericA] ASC)
   INCLUDE([Bool1], [Bool2]);
GO
CREATE NONCLUSTERED INDEX [c]
   ON [dbo].[tO]([ID] ASC, [Status] ASC, [Deleted] ASC, [UsID] ASC, [SuID] ASC, [OrID] ASC, [TINYINTm] ASC, [varchar1] ASC, [varcharT] ASC, [OrderDate] ASC, [CreateDate] ASC)
   INCLUDE([INT1], [char3], [PAN_decimal], [NumericA], [Bool1], [Bool2]);
GO
CREATE NONCLUSTERED INDEX [d]
   ON [dbo].[tO]([MaID] ASC);
GO
CREATE NONCLUSTERED INDEX [e]
   ON [dbo].[tO]([UsID] ASC);
GO
CREATE NONCLUSTERED INDEX [f]
   ON [dbo].[tO]([SuID] ASC);
GO
CREATE NONCLUSTERED INDEX [g]
   ON [dbo].[tO]([AddiUID] ASC, [OrID] ASC, [INT_CNR] ASC, [Deleted] ASC)
   INCLUDE([ID]);
GO
CREATE NONCLUSTERED INDEX [h]
   ON [dbo].[tO]([varchar1] ASC);
GO
CREATE NONCLUSTERED INDEX [i]
   ON [dbo].[tO]([OrID] ASC, [INT_CNR] ASC, [Deleted] ASC)
   INCLUDE([ID], [MaID], [md_Date], [ouID], [Status], [AddiUID], [PurOfUID], [varcharS], [UsID]);
GO
CREATE NONCLUSTERED INDEX [j]
   ON [dbo].[tO]([OrderDate] ASC);
GO
CREATE NONCLUSTERED INDEX [k]
   ON [dbo].[tO]([SCC_int] ASC);
GO
CREATE NONCLUSTERED INDEX [l]
   ON [dbo].[tO]([PurOfUID] ASC);
GO
CREATE NONCLUSTERED INDEX [m]
   ON [dbo].[tO]([OrID] ASC, [INT_CNR] ASC, [UsID] ASC, [Deleted] ASC)
   INCLUDE([ID]);
GO
CREATE NONCLUSTERED INDEX [n]
   ON [dbo].[tO]([Status] ASC)
   INCLUDE([ID], [UsID], [SuID], [varchar1], [INT1], [char3], [OrderDate], [CreateDate], [Bool1]);
GO
CREATE NONCLUSTERED INDEX [o]
   ON [dbo].[tO]([INT_CNR] ASC, [Status] ASC)
   INCLUDE([ID], [OrID], [UsID], [SuID], [SessionID], [varchar1], [char3], [TinyINTd], [OrderDate], [CreateDate], [RowVersion], [Deleted]);
GO
CREATE NONCLUSTERED INDEX [p]
   ON [dbo].[tO]([Deleted] ASC, [INT_CNR] ASC)
   INCLUDE([ID]);
GO
CREATE NONCLUSTERED INDEX [q]
   ON [dbo].[tO]([OrID] ASC, [Deleted] ASC)
   INCLUDE([ID], [SuID], [varchar1], [ouID]);
GO
CREATE NONCLUSTERED INDEX [r]
   ON [dbo].[tO]([OrID] ASC, [Status] ASC, [Deleted] ASC, [INT_CNR] ASC)
   INCLUDE([ID], [UsID], [varchar1], [varcharT], [varcharS], [PurOfUID], [AddiUID]);
GO
CREATE NONCLUSTERED INDEX [s]
   ON [dbo].[tO]([Deleted] ASC, [INT_CNR] ASC, [Status] ASC, [OrderDate] ASC)
   INCLUDE([ID], [cfct_decimal]);
GO
CREATE NONCLUSTERED INDEX [t]
   ON [dbo].[tO]([UsID] ASC, [INT_CNR] ASC, [Deleted] ASC, [Status] ASC, [OrderDate] ASC)
   INCLUDE([NumericA], [cfct_decimal]);
GO
CREATE NONCLUSTERED INDEX [u]
   ON [dbo].[tO]([SOID] ASC, [Status] ASC, [Deleted] ASC, [INT_CNR] ASC)
   INCLUDE([ID], [MaID], [varchar1]);
GO
CREATE NONCLUSTERED INDEX [v]
   ON [dbo].[tO]([Status] ASC, [Deleted] ASC, [ISD_Bool] ASC, [OrderDate] ASC)
   INCLUDE([ID], [MaID], [varchar1]);
GO
CREATE NONCLUSTERED INDEX [w]
   ON [dbo].[tO]([OrID] ASC, [INT_CNR] ASC, [Status] ASC, [Deleted] ASC, [ISD_Bool] ASC)
   INCLUDE([ID], [MaID], [varchar1], [CreateDate], [OrderDate], [SuID], [UGRID], [ouID], [UsID], [DeA1ID], [md_Date], [MAdID], [PurOfUID]);

以下是使用此表的極少數且沒有優先級的範例查詢（或其他範例查詢）：

...Select tO.ID From tO WHERE tO.[OrID] = @OrID AND [tO].[INT_CNR ] = 0 AND tO.[UsID] = @UserID AND tO.[Deleted] = 0    ...

... ID, [Deleted], [RowVersion] FROM [tO] LEFT JOIN O_details ON [tO].[ID] = [O_details].[OID] ...
   WHERE tO.INT_CNR = 0 AND tO.RowVersion > @rv ...

... INNER JOIN [tO] ON [tO].ID = xyz.OID ...


...From O_Details d LEFT JOIN [tO] ON d.OID = [tO].ID
                               WHERE [tO].[Status] = 4 
                               AND d.AID is not null
                               AND [tO].[ID] IN(...)...


... UPDATE [tO] SET [SomeVarcharCol]='...' WHERE [ID]=@id...


...UPDATE [tO] SET [NumericA]=@a, [PAN_decimal]=@b, [anydecimal]=@c, [UpdateDate]=@UpdateDate WHERE [ID]=@ID AND [RowVersion]=@RowVersion ...

編輯結束

原帖

老實說，在您的情況下，我會繼續將

$$ ID $$. 令人擔憂的是，它似乎沒有預設設置，但與此同時，如果幸運的話，它可能會填充某種形式的NEWSEQUENTIALID呼叫。如果不是，這不是世界末日，如果正在維護索引/統計資訊，則 < 1MM 行不應該成為阻礙。 這樣做可以讓您開始專注於修改一些過濾/包含索引，當您完成這些時，您可能會有 4 個痛點而不是 12 個。

除此之外的任何建議都可能會導致您的問題出現“冰山一角”分類 - 正如您似乎很清楚，問題實際上是關於糟糕的設計，而不是引擎性能。

更新

我想不出辦法清楚地回答你的評論，所以這裡的資訊太多了。

如前所述，如果您有一個細的、可訪問的增量列，例如一IDENTITY列，請在其上放置一個聚集索引 - 這是不費吹灰之力的。

然而：

如果不存在這樣的列，並且您正在查看具有 10 多個索引的堆（並且 - 仍然 - 最終會進行 RID 查找！！），那麼ID現在只需將其分群，即使它是UNIQUEIDENTIFIER. 最重要的是，您需要清理儲存使用率，這將從集群 - 某事 - 開始，如果只是將前向獲取數據拉回與行的其餘部分一致。即使您之後立即刪除聚集索引，您至少現在已經組織了堆，顯著減少了前向指針（如果不是完全消除）並最終減少了（不必要的過度工作）I/O 子系統的爭用。這樣做實際上沒有缺點，所以就這樣做吧。然後你可以弄清楚為什麼還有 29 個可用的索引和查詢仍然需要進行 RID 匹配。

忽略索引蔓延並專注於僅對堆進行分群，使用FILLFACTOR大約 70 應該可以很好地處理UNIQUEIDENTIFIER值。INSERT這是因為 B 樹（SQL Server 用於索引的實際資料結構）在僅隨機值操作的情況下傾向於大約 69% 的節點使用率。這個數字是一對非常聰明的傢伙雷曼和姚明在他們研究這個主題時證明的一個數學真理。將足夠DELETE數量的 s 投入混合可以降低節點使用率目標，但作為一般規則，aFILLFACTORof 70 是開始調整非順序/隨機聚集索引的好地方，因為您實際上是在告訴 SQL Server 甚至不必費心嘗試填充每頁的 30%，因為無論如何您都不會使用該部分 - 因為從數學上講，你不會。

關於使用非最佳集群鍵的潛在頁面拆分，雖然這是一個有效的問題，但如果您碰巧可以訪問 Sql Server 2012+ 安裝，您可以跟隨此展示，從設置擴展事件開始跟踪頁面拆分的會話：

DROP EVENT SESSION [TrackPageSplits]
ON  SERVER;
GO

CREATE EVENT SESSION [TrackPageSplits]
ON    SERVER
ADD EVENT sqlserver.transaction_log(
   WHERE operation = 11  -- LOP_DELETE_SPLIT 
     AND database_id = 2 -- Watch TempDB;
)
ADD TARGET package0.histogram(
   SET filtering_event_name = 'sqlserver.transaction_log',
       source_type = 0, -- Event Column
       source = 'alloc_unit_id');
GO

-- Start the Event Session Again
ALTER EVENT SESSION [TrackPageSplits]
ON SERVER
STATE=START;
GO

一旦啟動並執行，您可以設置並播種一些表，以了解不同填充因子下不同類型的集群鍵對索引密度的影響，以及插入其中的頁面拆分數量可能導致的影響。例如，我使用了一個很好的分群範例 withINTEGER和IDENTITYunder FILL_FACTOR 100，一個糟糕的分群範例 with UNIQUEIDENTIFIERand non-sequential NEWID()under FILL_FACTOR 100，然後在 s 下使用了另外三個非順序UNIQUEIDENTIFIER分群鍵FILL_FACTOR，分別為 75、70 和 65，每個都以 2.5MM 為種子記錄。

USE tempdb;
GO

IF NOT EXISTS ( SELECT  1
               FROM    sys.objects
               WHERE   name = 'PageSplitIdentity'
                   AND type = 'U' )
BEGIN
   --DROP TABLE dbo.PageSplitIdentity;
   CREATE TABLE dbo.PageSplitIdentity
   (
       PageSplitIdentity_PK    INTEGER IDENTITY( 1, 1 ) NOT NULL,
       Foo                     VARBINARY( 512 ) NOT NULL
   );

   ALTER TABLE dbo.PageSplitIdentity
   ADD CONSTRAINT PK__PageSplitIdentity
       PRIMARY KEY CLUSTERED ( PageSplitIdentity_PK )
   WITH ( DATA_COMPRESSION = PAGE, FILLFACTOR = 100 )
   ON  [PRIMARY];

   ALTER TABLE dbo.PageSplitIdentity
   ADD CONSTRAINT DF__PageSplitIdentity__Foo
       DEFAULT CONVERT( VARBINARY( 512 ), REPLICATE( 0x01, 512 ) ) FOR Foo;

   SET NOCOUNT ON;
   DECLARE @i                      INTEGER = 0;
   WHILE ( @i &lt; 2500000 )
   BEGIN
       INSERT INTO dbo.PageSplitIdentity DEFAULT VALUES;
       SET @i = @i + 1;
   END;
   SET NOCOUNT OFF;
END;
GO

IF NOT EXISTS ( SELECT  1
               FROM    sys.objects
               WHERE   name = 'PageSplitNewID'
                   AND type = 'U' )
BEGIN
   --DROP TABLE dbo.PageSplitNewID;
   CREATE TABLE dbo.PageSplitNewID
   (
       PageSplitNewID_PK       UNIQUEIDENTIFIER NOT NULL,
       Foo                     VARBINARY( 512 ) NOT NULL
   );

   ALTER TABLE dbo.PageSplitNewID
   ADD CONSTRAINT PK__PageSplitNewID
       PRIMARY KEY CLUSTERED ( PageSplitNewID_PK )
   WITH ( DATA_COMPRESSION = PAGE, FILLFACTOR = 100 )
   ON  [PRIMARY];

   ALTER TABLE dbo.PageSplitNewID
   ADD CONSTRAINT DF__PageSplitNewID__PageSplitNewID_PK
       DEFAULT NEWID() FOR PageSplitNewID_PK;

   ALTER TABLE dbo.PageSplitNewID
   ADD CONSTRAINT DF__PageSplitNewID__Foo
       DEFAULT CONVERT( VARBINARY( 512 ), REPLICATE( 0x01, 512 ) ) FOR Foo;

   SET NOCOUNT ON;
   DECLARE @i                      INTEGER = 0;
   WHILE ( @i &lt; 2500000 )
   BEGIN
       INSERT INTO dbo.PageSplitNewID DEFAULT VALUES;
       SET @i = @i + 1;
   END;
   SET NOCOUNT OFF;
END;
GO

IF NOT EXISTS ( SELECT  1
               FROM    sys.objects
               WHERE   name = 'PageSplitNewIDFillFactor75'
                   AND type = 'U' )
BEGIN
   --DROP TABLE dbo.PageSplitNewIDFillFactor75;
   CREATE TABLE dbo.PageSplitNewIDFillFactor75
   (
       PageSplitNewIDFillFactor75_PK
                               UNIQUEIDENTIFIER NOT NULL,
       Foo                     VARBINARY( 512 ) NOT NULL
   );

   ALTER TABLE dbo.PageSplitNewIDFillFactor75
   ADD CONSTRAINT PK__PageSplitNewIDFillFactor75
       PRIMARY KEY CLUSTERED ( PageSplitNewIDFillFactor75_PK )
   WITH ( DATA_COMPRESSION = PAGE, FILLFACTOR = 75 )
   ON  [PRIMARY];

   ALTER TABLE dbo.PageSplitNewIDFillFactor75
   ADD CONSTRAINT DF__PageSplitNewIDFillFactor75__PageSplitNewIDFillFactor75_PK
       DEFAULT NEWID() FOR PageSplitNewIDFillFactor75_PK;

   ALTER TABLE dbo.PageSplitNewIDFillFactor75
   ADD CONSTRAINT DF__PageSplitNewIDFillFactor75__Foo
       DEFAULT CONVERT( VARBINARY( 512 ), REPLICATE( 0x01, 512 ) ) FOR Foo;

   SET NOCOUNT ON;
   DECLARE @i                      INTEGER = 0;
   WHILE ( @i &lt; 2500000 )
   BEGIN
       INSERT INTO dbo.PageSplitNewIDFillFactor75 DEFAULT VALUES;
       SET @i = @i + 1;
   END;
   SET NOCOUNT OFF;
END;
GO

IF NOT EXISTS ( SELECT  1
               FROM    sys.objects
               WHERE   name = 'PageSplitNewIDFillFactor70'
                   AND type = 'U' )
BEGIN
   --DROP TABLE dbo.PageSplitNewIDFillFactor70;
   CREATE TABLE dbo.PageSplitNewIDFillFactor70
   (
       PageSplitNewIDFillFactor70_PK
                               UNIQUEIDENTIFIER NOT NULL,
       Foo                     VARBINARY( 512 ) NOT NULL
   );

   ALTER TABLE dbo.PageSplitNewIDFillFactor70
   ADD CONSTRAINT PK__PageSplitNewIDFillFactor70
       PRIMARY KEY CLUSTERED ( PageSplitNewIDFillFactor70_PK )
   WITH ( DATA_COMPRESSION = PAGE, FILLFACTOR = 70 )
   ON  [PRIMARY];

   ALTER TABLE dbo.PageSplitNewIDFillFactor70
   ADD CONSTRAINT DF__PageSplitNewIDFillFactor70__PageSplitNewIDFillFactor70_PK
       DEFAULT NEWID() FOR PageSplitNewIDFillFactor70_PK;

   ALTER TABLE dbo.PageSplitNewIDFillFactor70
   ADD CONSTRAINT DF__PageSplitNewIDFillFactor70__Foo
       DEFAULT CONVERT( VARBINARY( 512 ), REPLICATE( 0x01, 512 ) ) FOR Foo;

   SET NOCOUNT ON;
   DECLARE @i                      INTEGER = 0;
   WHILE ( @i &lt; 2500000 )
   BEGIN
       INSERT INTO dbo.PageSplitNewIDFillFactor70 DEFAULT VALUES;
       SET @i = @i + 1;
   END;
   SET NOCOUNT OFF;
END;
GO

IF NOT EXISTS ( SELECT  1
               FROM    sys.objects
               WHERE   name = 'PageSplitNewIDFillFactor65'
                   AND type = 'U' )
BEGIN
   --DROP TABLE dbo.PageSplitNewIDFillFactor65;
   CREATE TABLE dbo.PageSplitNewIDFillFactor65
   (
       PageSplitNewIDFillFactor65_PK
                               UNIQUEIDENTIFIER NOT NULL,
       Foo                     VARBINARY( 512 ) NOT NULL
   );

   ALTER TABLE dbo.PageSplitNewIDFillFactor65
   ADD CONSTRAINT PK__PageSplitNewIDFillFactor65
       PRIMARY KEY CLUSTERED ( PageSplitNewIDFillFactor65_PK )
   WITH ( DATA_COMPRESSION = PAGE, FILLFACTOR = 65 )
   ON  [PRIMARY];

   ALTER TABLE dbo.PageSplitNewIDFillFactor65
   ADD CONSTRAINT DF__PageSplitNewIDFillFactor65__PageSplitNewIDFillFactor65_PK
       DEFAULT NEWID() FOR PageSplitNewIDFillFactor65_PK;

   ALTER TABLE dbo.PageSplitNewIDFillFactor65
   ADD CONSTRAINT DF__PageSplitNewIDFillFactor65__Foo
       DEFAULT CONVERT( VARBINARY( 512 ), REPLICATE( 0x01, 512 ) ) FOR Foo;

   SET NOCOUNT ON;
   DECLARE @i                      INTEGER = 0;
   WHILE ( @i &lt; 2500000 )
   BEGIN
       INSERT INTO dbo.PageSplitNewIDFillFactor65 DEFAULT VALUES;
       SET @i = @i + 1;
   END;
   SET NOCOUNT OFF;
END;
GO

對於初始播種後的索引密度，使用以下查詢：

SELECT  si.name, sips.avg_fragment_size_in_pages, 
       sips.page_count, sips.fragment_count, 
       sips.avg_fragmentation_in_percent,
       sips.avg_page_space_used_in_percent
FROM    sys.indexes si
CROSS APPLY sys.dm_db_index_physical_stats( DB_ID(), si.object_id, si.index_id, DEFAULT, 'DETAILED' ) sips 
WHERE   si.name IN ( 'PK__PageSplitIdentity', 'PK__PageSplitNewID', 
           'PK__PageSplitNewIDFillFactor75', 'PK__PageSplitNewIDFillFactor70', 'PK__PageSplitNewIDFillFactor65' )
   AND sips.index_level = 0;

以下是密度結果（注意 s 最後一列中的 ~69% UNIQUEIDENTIFIER）：

種子密度

正如您所料，非順序索引在 2.5MM 插入後非常非常碎片化並且沒有維護。通過檢查我們之前設置的擴展事件繼續檢查發生的頁面拆分，我們可以使用以下查詢：

SELECT  si.name, sc.split_count, si.fill_factor
FROM (  SELECT  allocation_unit_id = slot.value( '( value )[ 1 ]', 'BIGINT' ),
               split_count = slot.value( '( @count )[ 1 ]', 'BIGINT' )
       FROM (  SELECT  target_data = CONVERT( XML, target_data ) 
               FROM    sys.dm_xe_sessions xs
               INNER JOIN sys.dm_xe_session_targets xst
                   ON xs.address = xst.event_session_address
               WHERE   xs.name = 'TrackPageSplits'
                   AND xst.target_name = 'histogram' ) s
       CROSS APPLY s.target_data.nodes( 'HistogramTarget/Slot' ) n ( slot ) ) sc
INNER JOIN sys.allocation_units sau
   ON  sc.allocation_unit_id = sau.allocation_unit_id
INNER JOIN sys.partitions sp
   ON  sau.container_id = sp.partition_id
INNER JOIN sys.indexes si
   ON  sp.object_id = si.object_id
   AND sp.index_id = si.index_id;

結果？

種子分裂

好的集群有 13 個頁面拆分，每個非連續的頁面拆分超過 12500。這在所有非順序情況下都很糟糕，但正如最初提到的，維護索引是完成這項工作的重要部分，所以讓我們清理它們。

ALTER INDEX PK__PageSplitIdentity
   ON  dbo.PageSplitIdentity REBUILD;

ALTER INDEX PK__PageSplitNewID
   ON  dbo.PageSplitNewID REBUILD;

ALTER INDEX PK__PageSplitNewIDFillFactor75
   ON  dbo.PageSplitNewIDFillFactor75 REBUILD;

ALTER INDEX PK__PageSplitNewIDFillFactor70
   ON  dbo.PageSplitNewIDFillFactor70 REBUILD;

ALTER INDEX PK__PageSplitNewIDFillFactor65
   ON  dbo.PageSplitNewIDFillFactor65 REBUILD;

現在：

維護後

好的，有了新索引，頁面拆分現在消失了，每個表的碎片基本相同，並且它們符合它們FILL_FACTOR的 s. 由於大小和密度差異的原因，非順序表比順序表對 I/O 施加的壓力更大，但仍可以接受。現在我們可以模擬對這些表的一些負載，並查看與頁面拆分相關的性能問題是否可能首先克服集群表的好處：

SET NOCOUNT ON;
DECLARE @i                      INTEGER = 0;
WHILE ( @i &lt; 50000 )
BEGIN
   INSERT INTO dbo.PageSplitIdentity DEFAULT VALUES;
   SET @i = @i + 1;
END;
SET NOCOUNT OFF;
GO

SET NOCOUNT ON;
DECLARE @i                      INTEGER = 0;
WHILE ( @i &lt; 50000 )
BEGIN
   INSERT INTO dbo.PageSplitNewID DEFAULT VALUES;
   SET @i = @i + 1;
END;
SET NOCOUNT OFF;
GO

SET NOCOUNT ON;
DECLARE @i                      INTEGER = 0;
WHILE ( @i &lt; 50000 )
BEGIN
   INSERT INTO dbo.PageSplitNewIDFillFactor75 DEFAULT VALUES;
   SET @i = @i + 1;
END;
SET NOCOUNT OFF;
GO

SET NOCOUNT ON;
DECLARE @i                      INTEGER = 0;
WHILE ( @i &lt; 50000 )
BEGIN
   INSERT INTO dbo.PageSplitNewIDFillFactor70 DEFAULT VALUES;
   SET @i = @i + 1;
END;
SET NOCOUNT OFF;
GO

SET NOCOUNT ON;
DECLARE @i                      INTEGER = 0;
WHILE ( @i &lt; 50000 )
BEGIN
   INSERT INTO dbo.PageSplitNewIDFillFactor65 DEFAULT VALUES;
   SET @i = @i + 1;
END;
SET NOCOUNT OFF;

結果：

插入後

在插入另外 50000 行後，唯一受頁面拆分影響的表是非順序UNIQUEIDENTIFIER的 under FILL_FACTOR 100。這絕對可能會導致性能問題。但是，如圖所示，任何其他測試FILLFACTOR級別都不會導致額外的頁面拆分。實際上，我需要在表中再插入一個 0.5MM，然後才能dbo.PageSplitNewIDFillFactor75在測試期間開始在表上拆分頁面，而且無論如何，這個值已經超過了數學門檻值。

所以，重申一下，很明顯，如果可以選擇一個精簡的、順序的集群鍵，那是最好的選擇。但是，正如我最初所說，如果負責任地完成，沒有更好的選擇，對非順序鍵進行分群是完全可行的做法。您所說的性能問題不是頁面拆分問題，而是堆上的前向提取問題，可以通過添加聚集索引來解決。所以加一個。如果你的堆有一個非集群主鍵，UNIQUEIDENTIFIER它是在你的軟體套件、集群FILL_FACTOR 70中使用的，並且從那裡開始測量和調整——你花在滅火和解釋性能指標上的時間越多，時間就越少您必須將模型調整為更合適的形式。

不要忘記放棄您的擴展活動會話！

DROP EVENT SESSION [TrackPageSplits]
ON  SERVER;
GO

在任何情況下，分群 UniqueIdentifiers 始終是禁忌。看來您的表結構不適合聚集索引，我不會強制聚集索引“只是為了擁有一個”。從長遠來看，將表保留為堆（無聚集索引）並簡單地將適當的非聚集索引應用於必要的列會更好，並為您提供您正在尋找的性能優勢。

每次創建新行時，對 UniqueIdentifier 列進行分群將導致潛在的大量頁面拆分/數據重新洗牌。這最終可能只會增加性能問題，特別是取決於表之間的使用（插入）。

如果您想要在這些類型的表上使用聚集索引，我建議創建一個新列（如果可能），它是一個 IDENTITY 列（這將需要對錶進行一些手動重建），然後您可以在該列上應用聚集索引IDENTITY 列。

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

相關問答

Sql-Server

在 SQL Server 2012 中索引 PK GUID

  • August 21, 2020
檢視問答
Sql-Server

3 列的聚集索引是否太大？

  • June 27, 2022
檢視問答
Sql-Server

索引中關鍵列的順序

  • January 14, 2022
檢視問答
Sql-Server

為什麼此列有排序

  • January 14, 2022
檢視問答
Sql-Server

部分重疊的索引下降？

  • July 18, 2021
檢視問答
Sql-Server

通過對包含列的索引進行大量更新來節省性能

  • January 26, 2021
檢視問答