提高 volatile 表上索引重組的性能

這發生在 SQL Server 2016（企業版）上。範例表結構：

CREATE TABLE [dbo].[Foo](
   [FooId] [bigint] IDENTITY(1,1) NOT NULL,
   [FirstFkId] [bigint] NULL,
   [SecondFkId] [bigint] NULL,
   [ThirdFkId] [bigint] NULL,
   [FourthFkId] [bigint] NOT NULL,
   [ParentId] [bigint] NULL,
   [Flag1] [bit] NOT NULL,
   [Flag2] [bit] NOT NULL,
   [From] [datetime2](7) NOT NULL,
   [Until] [datetime2](7) NOT NULL,
   [CreatedBy] [int] NOT NULL,
   [CreatedOn] [datetime2](7) NOT NULL,
   [ChangedBy] [int] NOT NULL,
   [ChangedOn] [datetime2](7) NOT NULL,
CONSTRAINT [PK_Foo] PRIMARY KEY CLUSTERED 
(
   [FooId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[FooDetail](
   [FooDetailId] [bigint] IDENTITY(1,1) NOT NULL,
   [FooId] [bigint] NOT NULL,
   [FirstFkId] [bigint] NOT NULL,
   [SecondFkId] [bigint] NULL,
   [Column1] [bigint] NOT NULL,
   [Column2] [nvarchar](250) NULL,
   [Column3] [nvarchar](250) NULL,
   [Column4] [datetime2](7) NULL,
   [Column5] [datetime2](7) NULL,
   [Column6] [datetime2](7) NULL,
   [Column7] [datetime2](7) NULL,
   [Column8] [datetime2](7) NULL,
   [CreatedBy] [int] NOT NULL,
   [CreatedOn] [datetime2](7) NOT NULL,
   [ChangedBy] [int] NOT NULL,
   [ChangedOn] [datetime2](7) NOT NULL,
CONSTRAINT [PK_FooDetail] PRIMARY KEY CLUSTERED 
(
   [FooDetailId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[FooDetail]  WITH CHECK ADD  CONSTRAINT [FK_FooDetail_Foo] FOREIGN KEY([FooId])
REFERENCES [dbo].[Foo] ([FooId])

該表具有來自滑動視窗的數據。有一個每天在表中插入一個月的過程，以及一個刪除舊數據的不同過程。使用者可以插入/更新行。

兩個表都有大約 125.000 行。

數據是這樣讀取的：

SELECT -- columns
FROM Foo f
INNER JOIN FooDetail fd on fd.FooId = f.FooId
WHERE f. -- Some condition

在調查時，我們發現為聚集索引重新組織索引FooDetail需要 90 分鐘到 120 分鐘。使用以下命令：

ALTER INDEX [PK_FooDetail] ON [DbName].[dbo].[FooDetail] REORGANIZE WITH (LOB_COMPACTION = ON)

擴展活動資訊：

<ExtendedInfo>
 <PageCount>3739</PageCount>
 <Fragmentation>5.00134</Fragmentation>
</ExtendedInfo>

我們使用 ola hallengren 解決方案進行索引優化（預設設置）。使用以下命令：

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[IndexOptimize] @Databases = 'USER_DATABASES', @LogToTable = 'Y'" -b

計時

報告數據庫和報告臨時數據庫從頭到尾打開了大約 200 個使用者連接。他們會生成全天使用的報告，並且會給系統帶來沉重的負擔。動態創建報告不是一種選擇。它們必須可用。就像在許多情況下，一天 24 小時很短，給每個流程自己的時間視窗。

磁碟速度（毫秒）數據文件：

• 平均讀取失速：6
• 平均寫失速：33

磁碟速度（毫秒）日誌文件：

• 平均讀取失速：5
• 平均寫失速：6

我的問題是如何加快索引重建速度，如何才能獲得最佳改進？

一些想法：

• 當我們知道寫入使用較少時，我們是否應該將表從一般索引重建中排除並有一個單獨的工作來重建這些表的索引？減少鎖定的痛苦。（該應用程序 24/7 全天候使用，因此鎖定始終是一個問題。）
• 由於數據非常不穩定，因此刪除聚集索引並改用堆是個好主意嗎？（我們對FooDetailfor有不同的索引FooId）

這裡有一些令人難以置信的錯誤。你的桌子很小！

只是為了比較，我對一個有 450,000 行和 22,000 頁具有 42% 碎片級別的聚群表進行了重組。花了1秒鐘。這是在我 3 歲的筆記型電腦上。

您需要在此處進行一些故障排除。

我會將數據庫從此類重組之前的備份中恢復到新機器上，並在該機器上進行重組。根據該結果，您將深入探勘。

如果在那台機器上它也很慢，那麼這張桌子有一些特別之處。也許您有 LOB 頁面，即使您的文章表明您沒有？我不能說為什麼重組應該在安靜的機器上花費那麼長時間，但你想以此作為基線。

假設在恢復的副本上重組很快，您需要確定為什麼它在生產機器上如此超慢。是在那個時間段內嗎？也許你有阻塞？那個時候還有什麼在執行？例如，您可以使用任何可用的 DMV（sys.dm_exec_session_wait_stats 或 sys.dm_os_wait_stats，取決於哪個更方便）輪詢等待統計資訊。或者只是在您知道沒有大量工作正在進行的期間手動執行重組。

當然，重建是一種選擇。或者一開始就不做碎片整理（也許你並沒有真正從碎片整理中獲得任何東西）。

但是考慮到您已經確定了一些不同尋常的方法，您希望在考慮跳過碎片整理或進行重建之前找到它的根源。

另一種解決方案是不再擔心索引碎片。在我的公司，我們在大型 ERP 數據庫和 SQL Server 2016 Enterprise 中的另一個大型數據倉庫數據庫上遇到索引重建阻塞和資源密集型的類似問題。從那以後，我們已經完全停止了索引重建和重組，而是每天更新統計資訊。性能沒有明顯下降。

我認為這種方法的缺點是它需要更多的儲存空間，但我們願意在這種情況下為更好的性能進行權衡。

這些數據庫中的一些較大的表有 100-2 億行，即使聚集索引有 60% 碎片化，查詢性能也沒有任何問題。

Microsoft 關於索引重組和重建的指南支持這一點：

例如，如果給定索引主要用於掃描操作，則刪除碎片可以提高這些操作的性能。對於主要用於查找操作的索引，性能優勢可能並不明顯。類似地，去除堆（沒有聚集索引的表）中的碎片對於非聚集索引掃描操作特別有用，但對查找操作影響不大。

https://docs.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-2016#detecting-fragmentation-of-rowstore-indexes

如果你必須重建，因為你說你有企業版，你可以做線上索引重建，這應該有助於減少阻塞。 REBUILD WITH (ONLINE = ON).

https://docs.microsoft.com/en-us/sql/relational-databases/indexes/perform-index-operations-online?view=sql-server-2016

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