Skip to content

Question sur les performances de la maintenance des index et des statistiques du serveur SQL

C'est la réponse la plus précise que vous trouverez, mais étudiez-la d'abord attentivement et voyez si elle convient à votre projet.

Solution :

Quelqu'un peut-il me faire part de son expérience dans l'utilisation d'IndexOptimize pour une
base de données avec un très grand nombre d'index ?

Vous avez raison, le script d'Ola récupère d'abord les données de tous les DMV liés à l'index et les insère à l'intérieur de... @tmpIndexesStatistics, même si une seule table ou un seul index est spécifié.
Pour beaucoup d'index, il se bloque sur cette première commande, qui est une requête assez énorme. Voir ci-dessous pour la requête et encore plus loin pour une correction possible de ce problème.


TL;DR

Dans l'une des requêtes, les vues de gestion dynamique sont appelées plusieurs fois, chacune avec plusieurs filtres.

Tout ceci résulte en un plan d'exécution assez gros.

Créer des tables temporaires et stocker les données des DMV avant d'exécuter la requête est une solution de contournement. Vous pourriez optimiser davantage avec des index sur ces tables temporaires / Réécritures.

Données de test : >100K tables vides + >200k index vides.

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

En modifiant les paramètres pour travailler sur tous les index, et en imprimant au lieu d'exécuter l'instruction ( @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';

Cette énorme requête entre en jeu

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

Maintenant, quand nous essayons de trouver ce qui déclenche cette commande à l'intérieur... [dbo].[IndexOptimize]

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

Avec @ActionsPreferred correspondant aux actions du niveau de fragmentation et @UpdateStatistics étant la mise à jour des statistiques. Ce qui signifie qu'il s'exécutera chaque fois que nous ferons une mise à jour des statistiques ou une reconstruction / réorganisation de l'index.

aucun filtrage n'est appliqué, la requête s'exécute sur tous les index de la base de données.

Par conséquent, à l'heure actuelle, nous devrons passer par tous ces problèmes, même si nous spécifions une seule table.

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'

enter image description here

Le filtrage réel des index se produit après l'exécution de la commande huge :

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

et met à jour les tmpIndexesStatistics table en conséquence, comme pour exécuter des opérations d'indexation sur ces index.

Cette requête a fonctionné plus de 60 minutes sur mon petit serveur de développement avant de le tuer.


Quel pourrait être un correctif pour cela ?

Eh bien, vous devriez modifier le script d'ola, mais une solution avec des tables temporaires a fonctionné pour moi, et s'est exécutée en 1 minute. Sans aucun doute, il pourrait être optimisé davantage :

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)

Pour modifier cela dans la procédure d'optimisation de l'index

Désolé Ola cela va faire mal à regarder

Quand j'ai scripté la procédure, c'était de la ligne 1430 à la ligne 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

En testant et en réexécutant la procédure avec une seule table,elle s'est exécutée en une minute :

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';

Durée 00:01:18

Des filtres antérieurs / des tables temporaires avec index / ... pourraient être utilisés pour améliorer encore la requête.

notes et avis

À la fin de cet article, vous pouvez trouver les références des autres administrateurs système, vous avez toujours le pouvoir de laisser le vôtre si vous l'aimez.



Utilisez notre moteur de recherche

Ricerca
Generic filters

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée.