索引自動化作業腳本

我們的生產實例正在執行 SQL Server 2014。需要一個自動化的索引碎片整理作業，該作業將根據特定數據庫中特定表的碎片級別重新組織或重建索引。由於大數據負載和我們最近面臨的性能問題，這項工作需要每周安排和執行一次。

不幸的是，在 2014 年的維護計劃中，我無法設置何時應該重建或重組索引的百分比限制。我曾嘗試測試我們的 Ola Hallengren 腳本，但在 2016 年或 2017 年時效果最好。

我有一個檢測碎片級別的腳本：

SELECT S.name as 'Schema',
T.name as 'Table',
I.name as 'Index',
DDIPS.avg_fragmentation_in_percent,
DDIPS.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS DDIPS
INNER JOIN sys.tables T on T.object_id = DDIPS.object_id
INNER JOIN sys.schemas S on T.schema_id = S.schema_id
INNER JOIN sys.indexes I ON I.object_id = DDIPS.object_id
AND DDIPS.index_id = I.index_id
WHERE DDIPS.database_id = DB_ID()
and I.name is not null
AND DDIPS.avg_fragmentation_in_percent > 0
ORDER BY DDIPS.avg_fragmentation_in_percent desc

現在我需要做的就是結合其餘的要求：

-if avg_fragmentation_in_percent > 5 AND < 30 , then ALTER INDEX REORGANIZE

-if avg_fragmentation_in_percent > 30, then ALTER INDEX REBUILD

有人可以幫我查詢嗎？提前致謝。

**編輯/更新：

當我執行此查詢時：

SELECT S.name as 'Schema',
T.name as 'Table',
I.name as 'Index',
DDIPS.avg_fragmentation_in_percent,
DDIPS.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS DDIPS
INNER JOIN sys.tables T on T.object_id = DDIPS.object_id
INNER JOIN sys.schemas S on T.schema_id = S.schema_id
INNER JOIN sys.indexes I ON I.object_id = DDIPS.object_id
AND DDIPS.index_id = I.index_id
WHERE DDIPS.database_id = DB_ID()
and I.name is not null
and T.name= 'WSSSync_Partition25'
ORDER BY DDIPS.avg_fragmentation_in_percent desc

這是我的輸出（記下 99% 的碎片）：

然後我執行 Ola 的腳本：

EXECUTE dbo.IndexOptimize
@Databases = 'WSS_UsageApplication',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@Indexes = 'WSS_UsageApplication.dbo.WSSSync_Partition25'

該腳本執行完美，但是當我重新執行第一個查詢以查找碎片級別時。碎片率保持不變，為 99.6%。

我會說 Ola 腳本也適用於 SQL 2014。

這是你應該做的：

EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30

無論如何，我建議您也使用更高的門檻值，例如 50-80。使用現代儲存，最好將 IO 集中在統計更新上。

對於您上面的查詢…

SELECT S.name as 'Schema',
T.name as 'Table',
I.name as 'Index',
DDIPS.avg_fragmentation_in_percent,
DDIPS.page_count
CASE
 WHEN avg_fragmentation_in_percent BETWEEN 5 AND  30  then 'ALTER INDEX REORGANIZE'
 WHEN avg_fragmentation_in_percent > 30, then 'ALTER INDEX REBUILD'
 ELSE ''
END AS CommandType
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS DDIPS
INNER JOIN sys.tables T on T.object_id = DDIPS.object_id
INNER JOIN sys.schemas S on T.schema_id = S.schema_id
INNER JOIN sys.indexes I ON I.object_id = DDIPS.object_id
AND DDIPS.index_id = I.index_id
WHERE DDIPS.database_id = DB_ID()
and I.name is not null
AND DDIPS.avg_fragmentation_in_percent > 0
ORDER BY DDIPS.avg_fragmentation_in_percent desc

請注意，此參數存在於 Ola 重新索引腳本中：

@MinNumberOfPages

設置大小，以頁為單位；跳過頁數較少的索引進行索引維護。預設值為 1000 頁。這是基於 Microsoft 的建議。IndexOptimize 檢查 sys.dm_db_index_physical_stats 中的 page_count 以確定索引的大小。

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