Sql-Server

Ola Hallengren 索引優化腳本不重建索引

  • May 6, 2019

我已經下載了 Ola Hallengren 的腳本並部署到 master 數據庫。我使用下面的執行它…

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

我在 SSMS 中得到了這個輸出,但是索引沒有被重建。碎片化還是很高的。我錯過了什麼嗎?

Date and time: 2015-03-01 14:07:24
Server: TestSvr
Version: 10.50.2500.0
Edition: Standard Edition (64-bit)
Procedure: [master].[dbo].[IndexOptimize]
Parameters: @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, 
@PageCountLevel = 1000, @SortInTempdb = 'N', 
@MaxDOP = NULL, @FillFactor = NULL, @PadIndex = NULL, 
@LOBCompaction = 'Y', @UpdateStatistics = NULL, 
@OnlyModifiedStatistics = 'N', @StatisticsSample = NULL, 
@StatisticsResample = 'N', @PartitionLevel = 'Y', 
@MSShippedObjects = 'N', 
@Indexes = NULL, @TimeLimit = NULL, @Delay = NULL, 
@WaitAtLowPriorityMaxDuration = NULL, 
@WaitAtLowPriorityAbortAfterWait = NULL, @LockTimeout = NULL, 
@LogToTable = 'N', @Execute = 'Y'
Source: https://ola.hallengren.com

Date and time: 2015-03-01 14:07:24
Database: [TestData]
Status: ONLINE
Standby: No
Updateability: READ_WRITE
User access: MULTI_USER
Is accessible: Yes
Recovery model: SIMPLE

在此處輸入圖像描述

您的索引只有 679 頁。Ola 的解決方案設置為忽略少於 1000 頁的索引(見@PageCountLevel參數)。您可以覆蓋它,以便它關心少於 1000 頁的索引,但為什麼呢?恕我直言,浪費精力。

我不會再擔心像這樣的小表了——讓 Ola 的解決方案來完成它的工作,當你可以證明它確實對特定索引造成了明顯的性能問題時,我會擔心碎片。“碎片化程度高”本身不是問題。

引用自:https://dba.stackexchange.com/questions/94110