使用 Ola Hallengren 腳本對索引視圖進行索引維護

我在我的環境中使用了 Ola Hallengren 的以下腳本，但它失敗並出現錯誤：

USE DBA
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,
   @UpdateStatistics = ‘ALL’,
   @PageCountLevel = 0 ,
   @LogToTable = ‘N’

消息 50000，Sev 16，狀態 1，第 153 行：消息 2706，表 ‘_abcd’ 不存在。

$$ SQLSTATE 42000 $$

‘_abcd’ 實際上是一個視圖，儲存過程無法更新視圖的統計資訊，因此失敗。我們在這個視圖上有索引請指導我如何解決這個問題

我從 SQL 代理執行 Ola 的腳本，使用它們的作業由具有 sysadmin 伺服器角色的 ID 擁有。你也可以問奧拉他的想法。

Havent 真的試圖調試腳本，但我可以從參數中看到它的本質。網上有很多腳本可以用來做這件事我經常使用的一個是：

DECLARE @MaxFragmentation TINYINT=30
,@MinimumPages SMALLINT=1000
,@SQL nvarchar(max)
,@ObjectName NVARCHAR(300)
,@IndexName NVARCHAR(300)
,@CurrentFragmentation DECIMAL(9, 6)
DECLARE @FragmentationState TABLE
(
SchemaName SYSNAME
,TableName SYSNAME
,object_id INT
,IndexName SYSNAME
,index_id INT
,page_count BIGINT
,avg_fragmentation_in_percent FLOAT
,avg_page_space_used_in_percent FLOAT
,type_desc VARCHAR(255)
)
INSERT INTO @FragmentationState
SELECT
s.name as SchemaName
,t.name as TableName
,t.object_id
,i.name as IndexName
,i.index_id
,x.page_count
,x.avg_fragmentation_in_percent
,x.avg_page_space_used_in_percent
,i.type_desc
FROM sys.dm_db_index_physical_stats(db_id(), NULL, NULL, NULL, 'SAMPLED') x
INNER JOIN sys.tables t ON x.object_id = t.object_id
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN sys.indexes i ON x.object_id = i.object_id AND x.index_id = i.index_id
WHERE x.index_id > 0 AND i.type_desc <> 'NONCLUSTERED COLUMNSTORE' and i.type <4
AND alloc_unit_type_desc = 'IN_ROW_DATA'
DECLARE INDEX_CURSE CURSOR LOCAL FAST_FORWARD FOR
SELECT QUOTENAME(x.SchemaName)+'.'+QUOTENAME(x.TableName)
,CASE WHEN x.type_desc = 'CLUSTERED' THEN 'ALL'
ELSE QUOTENAME(x.IndexName) END
,x.avg_fragmentation_in_percent
FROM @FragmentationState as x
LEFT OUTER JOIN @FragmentationState y ON x.object_id = y.object_id AND y.index_id = 1
WHERE (
x.type_desc = 'CLUSTERED'
AND y.type_desc = 'CLUSTERED'
)
OR y.index_id IS NULL
ORDER BY x.object_id
,x.index_id
OPEN INDEX_CURSE
WHILE 1=1
BEGIN
FETCH NEXT FROM INDEX_CURSE INTO @ObjectName, @IndexName
,@CurrentFragmentation
IF @@FETCH_STATUS <> 0
BREAK
SELECT @SQL='ALTER INDEX '
+@IndexName+' ON '+@ObjectName
+CASE WHEN @CurrentFragmentation <= 30
THEN ' REORGANIZE;'
ELSE ' REBUILD'
+CASE WHEN CONVERT(VARCHAR(100), SERVERPROPERTY('Edition')) LIKE 'Data Center%'
OR CONVERT(VARCHAR(100), SERVERPROPERTY('Edition')) LIKE 'Enterprise%'
OR CONVERT(VARCHAR(100), SERVERPROPERTY('Edition')) LIKE 'Developer%'
THEN ' WITH (ONLINE=ON, SORT_IN_TEMPDB=ON) '
END+';'
END
EXEC sp_ExecuteSQL @SQL
END
CLOSE INDEX_CURSE
DEALLOCATE INDEX_CURSE

根據您的參數，它將對索引進行碎片整理，根據@MaxFragmentation 重建或重組。線上或離線，取決於版本。希望能幫助到你

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