Sql-Server

SQL Server 索引和統計維護性能問題

  • July 10, 2019

我嘗試IndexOptimize在具有大量索引（> 250.000）的數據庫上使用儲存過程。儲存過程收集需要處理的數據的初始步驟需要幾個小時，即使我設置了@Indexes 參數來縮小工作範圍。

SQL Server 維護解決方案版本：2019-02-10 10:40:47 SQL Server 2017 標準版，安裝了最新的 CU14。

在一位客戶那裡，我看到一個具有 > 500.000 個索引的數據庫。12 小時後，數據收集步驟仍在執行。

我希望如果我將 @Indexes 設置為單個索引執行應該立即開始。

這是我的儲存過程呼叫的範例。

EXECUTE dbo.IndexOptimize
@Databases = 'db',
@Indexes =  'db.dbo.'
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 30,
@FragmentationLevel2 = 50,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y',
@LogToTable = 'Y',
@LockTimeout = 60

有人可以與我分享他將 IndexOptimize 用於具有大量索引的數據庫的經驗嗎？

有人可以與我分享他將 IndexOptimize 用於具有大量索引的數據庫的經驗嗎？

你是對的，Ola 的腳本首先從所有與索引相關的 DMV 中獲取數據並將它們插入其中@tmpIndexesStatistics，即使指定了一個表或一個索引。對於很多索引，它會卡在第一個命令上，這是一個相當大的查詢。請參閱下面的查詢，甚至在下面進一步了解可能的解決方法。

TL; 博士

在其中一個查詢中，動態管理視圖被呼叫了幾次，每次都有多個過濾器。

所有這些都導致​​了一個相當大的執行計劃。

在執行查詢之前創建臨時表並儲存來自 DMV 的數據是一種解決方法。您可以使用這些臨時表/重寫上的索引進一步優化。

測試數據：>100K 空表 + >200k 空索引

USE Test2
GO
SET NOCOUNT ON;
DECLARE @SQL NVARCHAR(MAX)
DECLARE @i int = 0

DECLARE @counter int = 1000
WHILE @i <+ @counter
BEGIN
SET @SQL = N'CREATE TABLE dbo.'+QUOTENAME(cast(@i as nvarchar(20)))+'( id int, val varchar(255));'
EXEC(@sql)
SET @SQL = N'CREATE INDEX IX_'+cast(@i as nvarchar(20))+ ' ON '+QUOTENAME(cast(@i as nvarchar(20)))+'(id) '
EXEC(@sql)
SET @SQL = N'CREATE INDEX IX_'+cast(@i as nvarchar(20))+ '_2 ON '+QUOTENAME(cast(@i as nvarchar(20)))+'(val) '
EXEC(@sql)
set @I +=1

IF @i = @counter
BEGIN

 IF @counter < 100000
 BEGIN
       SET @counter += 1000;
 END
END
END

select count(*) from sys.tables;
--100731

select count(*) from sys.indexes  where index_id != 0;
--201614

當更改參數以對所有索引起作用，並列印而不是執行語句時 ( @Execute='N')

EXECUTE MNGDB.dbo.IndexOptimize
@Databases = 'test2',
@Indexes =  'test2.dbo.',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 30,
@FragmentationLevel2 = 50,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y',
@LogToTable = 'Y',
@LockTimeout = 60,
@Execute='N';

這個巨大的查詢開始發揮作用

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
USE [test2]; 
SELECT SchemaID, SchemaName, ObjectID, ObjectName, ObjectType, IsMemoryOptimized, IndexID, IndexName, IndexType, AllowPageLocks, IsImageText, IsNewLOB, IsFileStream, IsColumnStore, IsComputed, IsTimestamp, OnReadOnlyFileGroup, ResumableIndexOperation, StatisticsID, StatisticsName, NoRecompute, IsIncremental, PartitionID, PartitionNumber, PartitionCount, [Order], Selected, Completed 
FROM (SELECT schemas.[schema_id] AS SchemaID, schemas.[name] AS SchemaName, objects.[object_id] AS ObjectID, objects.[name] AS ObjectName, RTRIM(objects.[type]) AS ObjectType, tables.is_memory_optimized AS IsMemoryOptimized, indexes.index_id AS IndexID, indexes.[name] AS IndexName, indexes.[type] AS IndexType, indexes.allow_page_locks AS AllowPageLocks, 
CASE WHEN indexes.[type] = 1 AND EXISTS(SELECT * FROM sys.columns columns INNER JOIN sys.types types ON columns.system_type_id = types.user_type_id WHERE columns.[object_id] = objects.object_id AND types.name IN('image','text','ntext')) THEN 1 ELSE 0 END AS IsImageText, 
CASE WHEN indexes.[type] = 1 AND EXISTS(SELECT * FROM sys.columns columns INNER JOIN sys.types types ON columns.system_type_id = types.user_type_id OR (columns.user_type_id = types.user_type_id AND types.is_assembly_type = 1) WHERE columns.[object_id] = objects.object_id AND (types.name IN('xml') OR (types.name IN('varchar','nvarchar','varbinary') AND columns.max_length = -1) OR (types.is_assembly_type = 1 AND columns.max_length = -1))) THEN 1 WHEN indexes.[type] = 2 
           AND EXISTS(SELECT * FROM sys.index_columns index_columns INNER JOIN sys.columns columns ON index_columns.[object_id] = columns.[object_id] 
           AND index_columns.column_id = columns.column_id 
           INNER JOIN sys.types types ON columns.system_type_id = types.user_type_id OR (columns.user_type_id = types.user_type_id AND types.is_assembly_type = 1) WHERE index_columns.[object_id] = objects.object_id AND index_columns.index_id = indexes.index_id AND (types.[name] IN('xml') OR (types.[name] IN('varchar','nvarchar','varbinary') AND columns.max_length = -1) OR (types.is_assembly_type = 1 AND columns.max_length = -1))) 
           THEN 1 ELSE 0 END AS IsNewLOB, 
CASE WHEN indexes.[type] = 1 AND EXISTS(SELECT * FROM sys.columns columns WHERE columns.[object_id] = objects.object_id  AND columns.is_filestream = 1) THEN 1 ELSE 0 END AS IsFileStream, 
CASE WHEN EXISTS(SELECT * FROM sys.indexes indexes WHERE indexes.[object_id] = objects.object_id AND [type] IN(5,6)) THEN 1 ELSE 0 END AS IsColumnStore, 
CASE WHEN EXISTS(SELECT * FROM sys.index_columns index_columns INNER JOIN sys.columns columns ON index_columns.object_id = columns.object_id AND index_columns.column_id = columns.column_id WHERE (index_columns.key_ordinal > 0 OR index_columns.partition_ordinal > 0) AND columns.is_computed = 1 AND index_columns.object_id = indexes.object_id AND index_columns.index_id = indexes.index_id) THEN 1 ELSE 0 END AS IsComputed, 
CASE WHEN EXISTS(SELECT * FROM sys.index_columns index_columns INNER JOIN sys.columns columns ON index_columns.[object_id] = columns.[object_id] AND index_columns.column_id = columns.column_id INNER JOIN sys.types types ON columns.system_type_id = types.system_type_id 
WHERE index_columns.[object_id] = objects.object_id AND index_columns.index_id = indexes.index_id AND types.[name] = 'timestamp') THEN 1 ELSE 0 END AS IsTimestamp, 
CASE WHEN EXISTS (SELECT * FROM sys.indexes indexes2 INNER JOIN sys.destination_data_spaces destination_data_spaces ON indexes.data_space_id = destination_data_spaces.partition_scheme_id INNER JOIN sys.filegroups filegroups ON destination_data_spaces.data_space_id = filegroups.data_space_id WHERE filegroups.is_read_only = 1 AND indexes2.[object_id] = indexes.[object_id] AND indexes2.[index_id] = indexes.index_id AND destination_data_spaces.destination_id = partitions.partition_number) THEN 1 WHEN EXISTS (SELECT * FROM sys.indexes indexes2 INNER JOIN sys.filegroups filegroups ON indexes.data_space_id = filegroups.data_space_id WHERE filegroups.is_read_only = 1 AND indexes.[object_id] = indexes2.[object_id] AND indexes.[index_id] = indexes2.index_id) THEN 1 WHEN indexes.[type] = 1 AND EXISTS (SELECT * FROM sys.tables tables INNER JOIN sys.filegroups filegroups ON tables.lob_data_space_id = filegroups.data_space_id WHERE filegroups.is_read_only = 1 AND tables.[object_id] = objects.[object_id]) THEN 1 ELSE 0 END AS OnReadOnlyFileGroup, CASE WHEN EXISTS(SELECT * FROM sys.index_resumable_operations index_resumable_operations WHERE state_desc = 'PAUSED' AND index_resumable_operations.object_id = indexes.object_id AND index_resumable_operations.index_id = indexes.index_id AND (index_resumable_operations.partition_number = partitions.partition_number OR index_resumable_operations.partition_number IS NULL)) THEN 1 ELSE 0 END AS ResumableIndexOperation, stats.stats_id AS StatisticsID, stats.name AS StatisticsName, stats.no_recompute AS NoRecompute, stats.is_incremental AS IsIncremental, partitions.partition_id AS PartitionID, partitions.partition_number AS PartitionNumber, IndexPartitions.partition_count AS PartitionCount, 0 AS [Order], 0 AS Selected, 0 AS Completed FROM sys.indexes indexes INNER JOIN sys.objects objects ON indexes.[object_id] = objects.[object_id] INNER JOIN sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id] LEFT OUTER JOIN sys.tables tables ON objects.[object_id] = tables.[object_id] LEFT OUTER JOIN sys.stats stats ON indexes.[object_id] = stats.[object_id] AND indexes.[index_id] = stats.[stats_id] LEFT OUTER JOIN sys.partitions partitions ON indexes.[object_id] = partitions.[object_id] AND indexes.index_id = partitions.index_id LEFT OUTER JOIN (SELECT partitions.[object_id], partitions.index_id, COUNT(DISTINCT partitions.partition_number) AS partition_count FROM sys.partitions partitions GROUP BY partitions.[object_id], partitions.index_id) IndexPartitions ON partitions.[object_id] = IndexPartitions.[object_id] AND partitions.[index_id] = IndexPartitions.[index_id] WHERE objects.[type] IN('U','V') AND objects.is_ms_shipped = 0 AND indexes.[type] IN(1,2,3,4,5,6,7) AND indexes.is_disabled = 0 AND indexes.is_hypothetical = 0 

UNION 

SELECT schemas.[schema_id] AS SchemaID, schemas.[name] AS SchemaName, objects.[object_id] AS ObjectID, objects.[name] AS ObjectName, RTRIM(objects.[type]) AS ObjectType, tables.is_memory_optimized AS IsMemoryOptimized, NULL AS IndexID, NULL AS IndexName, NULL AS IndexType, NULL AS AllowPageLocks, NULL AS IsImageText, NULL AS IsNewLOB, NULL AS IsFileStream, NULL AS IsColumnStore, NULL AS IsComputed, NULL AS IsTimestamp, NULL AS OnReadOnlyFileGroup, NULL AS ResumableIndexOperation, stats.stats_id AS StatisticsID, stats.name AS StatisticsName, stats.no_recompute AS NoRecompute, stats.is_incremental AS IsIncremental, NULL AS PartitionID, dm_db_incremental_stats_properties.partition_number AS PartitionNumber, NULL AS PartitionCount, 0 AS [Order], 0 AS Selected, 0 AS Completed FROM sys.stats stats INNER JOIN sys.objects objects ON stats.[object_id] = objects.[object_id] INNER JOIN sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id] LEFT OUTER JOIN sys.tables tables ON objects.[object_id] = tables.[object_id] OUTER APPLY sys.dm_db_incremental_stats_properties(stats.object_id, stats.stats_id) dm_db_incremental_stats_properties WHERE objects.[type] IN('U','V') AND objects.is_ms_shipped = 0 AND NOT EXISTS(SELECT * FROM sys.indexes indexes WHERE indexes.[object_id] = stats.[object_id] AND indexes.index_id = stats.stats_id)) IndexesStatistics

現在，當我們試圖找出是什麼觸發了這個命令時[dbo].[IndexOptimize]

IF EXISTS(SELECT * FROM @ActionsPreferred) OR @UpdateStatistics IN('ALL','INDEX')

@ActionsPreferred對應於碎片級別的動作和正在@UpdateStatistics更新的統計資訊。這意味著它會在我們進行統計更新或索引重建/重組時執行。

沒有應用過濾，查詢會遍歷數據庫中的所有索引。

結果，目前我們將不得不經歷所有這些麻煩，即使我們指定一個表。

EXECUTE MNGDB.dbo.IndexOptimize
@Databases = 'test2',
@Indexes =  'test2.dbo.[83631]',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 30,
@FragmentationLevel2 = 50,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y',
@LogToTable = 'Y',
@LockTimeout = 60,
@EXECUTE= 'N'

在此處輸入圖像描述

實際的索引過濾發生在巨大的命令執行之後：

IF @Indexes IS NULL
     BEGIN
       UPDATE tmpIndexesStatistics
       SET tmpIndexesStatistics.Selected = 1
       FROM @tmpIndexesStatistics tmpIndexesStatistics
     END
     ELSE
     BEGIN
...

並相應地更新tmpIndexesStatistics表，以便對這些索引執行索引操作。

這個查詢在我的小型開發伺服器上執行了 60 多分鐘，然後才殺死它。

有什麼可能解決這個問題？

好吧，您將不得不更改 ola 的腳本，但是帶有臨時表的解決方案對我有用，並在 1 分鐘內執行。毫無疑問，它可以進一步優化：

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
USE [test2]; 

select * 
INTO #Indexes
from sys.indexes;


select *  
INTO #Index_columns
FROM sys.index_columns;

SELECT * 
INTO #columns
FROM SYS.columns;

SELECT * 
INTO #types 
FROM
sys.types;

SELECT *
INTO #destination_data_spaces
FROM sys.destination_data_spaces;


SELECT *
INTO #filegroups
FROM sys.filegroups;


SELECT *
INTO #stats
FROM sys.stats;

SELECT *
INTO #objects
FROM sys.objects;


SELECT *
INTO #partitions
FROM sys.partitions;


SELECT *
INTO #tables
FROM sys.tables;


SELECT *
INTO #index_resumable_operations
FROM sys.index_resumable_operations 

SELECT *
INTO #schemas
FROM sys.schemas 

+ rerun the query with temp tables instead (char limit)

在索引優化過程中改變它

抱歉，奧拉，這看起來會很痛

當我編寫 proc 腳本時，這是從第 1430 行到 1537

IF DATABASEPROPERTYEX(@CurrentDatabaseName,'Status') = 'ONLINE'
   AND (@CurrentIsDatabaseAccessible = 1 OR @CurrentIsDatabaseAccessible IS NULL)
   AND DATABASEPROPERTYEX(@CurrentDatabaseName,'Updateability') = 'READ_WRITE'
   BEGIN

     -- Select indexes in the current database
     IF (EXISTS(SELECT * FROM @ActionsPreferred) OR @UpdateStatistics IS NOT NULL) AND (GETDATE() < DATEADD(ss,@TimeLimit,@StartTime) OR @TimeLimit IS NULL)
     BEGIN
       SET @CurrentCommand01 = 'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;'
                             + 'USE ' + QUOTENAME(@CurrentDatabaseName) + ';
                               select * 
                               INTO #Indexes
                               from sys.indexes;
                               select *  
                               INTO #Index_columns
                               FROM sys.index_columns;
                               SELECT * 
                               INTO #columns
                               FROM SYS.columns;
                               SELECT * 
                               INTO #types 
                               FROM
                               sys.types;
                               SELECT *
                               INTO #destination_data_spaces
                               from sys.destination_data_spaces;
                               SELECT *
                               INTO #filegroups
                               FROM sys.filegroups;
                               SELECT * kill 64
                               INTO #stats
                               from sys.stats;
                               SELECT *
                               INTO #objects
                               from sys.objects;       
                               SELECT *
                               INTO #partitions
                               from sys.partitions;
                               SELECT *
                               INTO #tables
                               FROM sys.tables;
                               SELECT *
                               INTO #index_resumable_operations
                               FROM sys.index_resumable_operations;
                               SELECT *
                               INTO #schemas
                               FROM sys.schemas; '
                             + ' SELECT SchemaID, SchemaName, ObjectID, ObjectName, ObjectType, IsMemoryOptimized, IndexID, IndexName, IndexType, AllowPageLocks, IsImageText, IsNewLOB, IsFileStream, IsColumnStore, IsComputed, IsTimestamp, OnReadOnlyFileGroup, ResumableIndexOperation, StatisticsID, StatisticsName, NoRecompute, IsIncremental, PartitionID, PartitionNumber, PartitionCount, [Order], Selected, Completed'
                             + ' FROM ('

       IF EXISTS(SELECT * FROM @ActionsPreferred) OR @UpdateStatistics IN('ALL','INDEX')
       BEGIN
         SET @CurrentCommand01 = @CurrentCommand01 + 'SELECT schemas.[schema_id] AS SchemaID'
                                                   + ', schemas.[name] AS SchemaName'
                                                   + ', objects.[object_id] AS ObjectID'
                                                   + ', objects.[name] AS ObjectName'
                                                   + ', RTRIM(objects.[type]) AS ObjectType'
                                                   + ', ' + CASE WHEN @Version >= 12 THEN 'tables.is_memory_optimized' ELSE '0' END + ' AS IsMemoryOptimized'
                                                   + ', indexes.index_id AS IndexID'
                                                   + ', indexes.[name] AS IndexName'
                                                   + ', indexes.[type] AS IndexType'
                                                   + ', indexes.allow_page_locks AS AllowPageLocks'

                                                   + ', CASE WHEN indexes.[type] = 1 AND EXISTS(SELECT * FROM #columns columns INNER JOIN #types types ON columns.system_type_id = types.user_type_id WHERE columns.[object_id] = objects.object_id AND types.name IN(''image'',''text'',''ntext'')) THEN 1 ELSE 0 END AS IsImageText'

                                                   + ', CASE WHEN indexes.[type] = 1 AND EXISTS(SELECT * FROM #columns columns INNER JOIN #types types ON columns.system_type_id = types.user_type_id OR (columns.user_type_id = types.user_type_id AND types.is_assembly_type = 1) WHERE columns.[object_id] = objects.object_id AND (types.name IN(''xml'') OR (types.name IN(''varchar'',''nvarchar'',''varbinary'') AND columns.max_length = -1) OR (types.is_assembly_type = 1 AND columns.max_length = -1))) THEN 1'
                                                   + ' WHEN indexes.[type] = 2 AND EXISTS(SELECT * FROM #Index_columns index_columns INNER JOIN #columns columns ON index_columns.[object_id] = columns.[object_id] AND index_columns.column_id = columns.column_id INNER JOIN #types types ON columns.system_type_id = types.user_type_id OR (columns.user_type_id = types.user_type_id AND types.is_assembly_type = 1) WHERE index_columns.[object_id] = objects.object_id AND index_columns.index_id = indexes.index_id AND (types.[name] IN(''xml'') OR (types.[name] IN(''varchar'',''nvarchar'',''varbinary'') AND columns.max_length = -1) OR (types.is_assembly_type = 1 AND columns.max_length = -1))) THEN 1 ELSE 0 END AS IsNewLOB'

                                                   + ', CASE WHEN indexes.[type] = 1 AND EXISTS(SELECT * FROM #columns columns WHERE columns.[object_id] = objects.object_id  AND columns.is_filestream = 1) THEN 1 ELSE 0 END AS IsFileStream'

                                                   + ', CASE WHEN EXISTS(SELECT * FROM #Indexes indexes WHERE indexes.[object_id] = objects.object_id AND [type] IN(5,6)) THEN 1 ELSE 0 END AS IsColumnStore'

                                                   + ', CASE WHEN EXISTS(SELECT * FROM #Index_columns index_columns INNER JOIN #columns columns ON index_columns.object_id = columns.object_id AND index_columns.column_id = columns.column_id WHERE (index_columns.key_ordinal > 0 OR index_columns.partition_ordinal > 0) AND columns.is_computed = 1 AND index_columns.object_id = indexes.object_id AND index_columns.index_id = indexes.index_id) THEN 1 ELSE 0 END AS IsComputed'

                                                   + ', CASE WHEN EXISTS(SELECT * FROM #Index_columns index_columns INNER JOIN #columns columns ON index_columns.[object_id] = columns.[object_id] AND index_columns.column_id = columns.column_id INNER JOIN #types types ON columns.system_type_id = types.system_type_id WHERE index_columns.[object_id] = objects.object_id AND index_columns.index_id = indexes.index_id AND types.[name] = ''timestamp'') THEN 1 ELSE 0 END AS IsTimestamp'

                                                   + ', CASE WHEN EXISTS (SELECT * FROM #Indexes indexes2 INNER JOIN #destination_data_spaces destination_data_spaces ON indexes.data_space_id = destination_data_spaces.partition_scheme_id INNER JOIN #filegroups filegroups ON destination_data_spaces.data_space_id = filegroups.data_space_id WHERE filegroups.is_read_only = 1 AND indexes2.[object_id] = indexes.[object_id] AND indexes2.[index_id] = indexes.index_id' + CASE WHEN @PartitionLevel = 'Y' THEN ' AND destination_data_spaces.destination_id = partitions.partition_number' ELSE '' END + ') THEN 1'
                                                   + ' WHEN EXISTS (SELECT * FROM #Indexes indexes2 INNER JOIN #filegroups filegroups ON indexes.data_space_id = filegroups.data_space_id WHERE filegroups.is_read_only = 1 AND indexes.[object_id] = indexes2.[object_id] AND indexes.[index_id] = indexes2.index_id) THEN 1'
                                                   + ' WHEN indexes.[type] = 1 AND EXISTS (SELECT * FROM #tables tables INNER JOIN #filegroups filegroups ON tables.lob_data_space_id = filegroups.data_space_id WHERE filegroups.is_read_only = 1 AND tables.[object_id] = objects.[object_id]) THEN 1 ELSE 0 END AS OnReadOnlyFileGroup'

                                                   + ', ' + CASE WHEN @Version >= 14 THEN 'CASE WHEN EXISTS(SELECT * FROM #index_resumable_operations index_resumable_operations WHERE state_desc = ''PAUSED'' AND index_resumable_operations.object_id = indexes.object_id AND index_resumable_operations.index_id = indexes.index_id AND (index_resumable_operations.partition_number = partitions.partition_number OR index_resumable_operations.partition_number IS NULL)) THEN 1 ELSE 0 END' ELSE '0' END + ' AS ResumableIndexOperation'

                                                   + ', stats.stats_id AS StatisticsID'
                                                   + ', stats.name AS StatisticsName'
                                                   + ', stats.no_recompute AS NoRecompute'
                                                   + ', ' + CASE WHEN @Version >= 12 THEN 'stats.is_incremental' ELSE '0' END + ' AS IsIncremental'
                                                   + ', ' + CASE WHEN @PartitionLevel = 'Y' THEN 'partitions.partition_id AS PartitionID' WHEN @PartitionLevel = 'N' THEN 'NULL AS PartitionID' END
                                                   + ', ' + CASE WHEN @PartitionLevel = 'Y' THEN 'partitions.partition_number AS PartitionNumber' WHEN @PartitionLevel = 'N' THEN 'NULL AS PartitionNumber' END
                                                   + ', ' + CASE WHEN @PartitionLevel = 'Y' THEN 'IndexPartitions.partition_count AS PartitionCount' WHEN @PartitionLevel = 'N' THEN 'NULL AS PartitionCount' END
                                                   + ', 0 AS [Order]'
                                                   + ', 0 AS Selected'
                                                   + ', 0 AS Completed'
                                                   + ' FROM #Indexes indexes'
                                                   + ' INNER JOIN #objects objects ON indexes.[object_id] = objects.[object_id]'
                                                   + ' INNER JOIN #schemas schemas ON objects.[schema_id] = schemas.[schema_id]'
                                                   + ' LEFT OUTER JOIN #tables tables ON objects.[object_id] = tables.[object_id]'
                                                   + ' LEFT OUTER JOIN #stats stats ON indexes.[object_id] = stats.[object_id] AND indexes.[index_id] = stats.[stats_id]'
         IF @PartitionLevel = 'Y'
         BEGIN
           SET @CurrentCommand01 = @CurrentCommand01 + ' LEFT OUTER JOIN #partitions partitions ON indexes.[object_id] = partitions.[object_id] AND indexes.index_id = partitions.index_id'
                                                     + ' LEFT OUTER JOIN (SELECT partitions.[object_id], partitions.index_id, COUNT(DISTINCT partitions.partition_number) AS partition_count FROM #partitions partitions GROUP BY partitions.[object_id], partitions.index_id) IndexPartitions ON partitions.[object_id] = IndexPartitions.[object_id] AND partitions.[index_id] = IndexPartitions.[index_id]'
         END

         SET @CurrentCommand01 = @CurrentCommand01 + ' WHERE objects.[type] IN(''U'',''V'')'
                                                   + CASE WHEN @MSShippedObjects = 'N' THEN ' AND objects.is_ms_shipped = 0' ELSE '' END
                                                   + ' AND indexes.[type] IN(1,2,3,4,5,6,7)'
                                                   + ' AND indexes.is_disabled = 0 AND indexes.is_hypothetical = 0'
       END

用一張表測試並重新執行程序時，它在一分鐘內執行：

EXECUTE dbo.IndexOptimize
@Databases = 'test2',
@Indexes =  'test2.dbo.[83631]',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 30,
@FragmentationLevel2 = 50,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y',
@LogToTable = 'Y',
@LockTimeout = 60,
@EXECUTE= 'N';

持續時間 00:01:18

較早的過濾器/帶有索引的臨時表/ …可用於進一步改進查詢。

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

相關問答

Sql-Server

IndexOptimize 後查詢和更新極慢

  • September 4, 2019
檢視問答
Sql-Server

重建索引時除了“刪除和重新創建索引”之外會發生什麼，而重組命令中不會發生這種情況？

  • April 6, 2022
檢視問答
Sql-Server

嘗試回收未使用空間導致 SQL Server 中已用空間顯著增加

  • March 7, 2022
檢視問答
Sql-Server

SQL Server：如何跟踪 CREATE INDEX 命令的進度？

  • January 24, 2022
檢視問答
Sql-Server

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

  • December 14, 2021
檢視問答
Sql-Server

Ola Hallengren 維護解決方案

  • November 28, 2021
檢視問答