使用Ola Hallengren脚本对索引视图进行索引维护

Question

我在我的环境中使用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’

Msg 50000，Sev 16，State 1，第153行: Msg 2706，表‘_abcd’不存在。SQLSTATE 42000

“_abcd”实际上是一个视图，存储过程无法更新视图上的统计信息，并且失败。我们在这个视图上有索引，请指导我如何解决这个问题

Answer 1

我从SQL Agent运行Ola的脚本，使用它们的作业属于具有sysadmin服务器角色的ID。你也可以问奥拉他的想法。

Answer 2

虽然没有尝试调试脚本，但我从参数中可以看到它的本质功能。有许多在线脚本可以用来完成我经常使用的脚本：

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重新构建或重新组织。在线或离线，取决于版本。希望它能帮上忙