Skip to content

Suggested change for IndexOptimize #991

@AlexMortola

Description

@AlexMortola

Description of the feature
Change to an existing feature

The IndexOptimize stored procedure, at line 1544 corresponding to the comment "-- Select indexes in the current database", starts componing the SQL statement to discover the indexes to manage.
The statement actually is a UNION of three queries. In particular the first query, in database with a very large number of tables (e.g. 60000), can last for hours preventing from maintaining the indexes in a limited time window.
I noticed that the filter for @MinNumberOfPages is applied after the discovery has been completed.
What if we injected the filter into the discovering query? In this way the discovery phase lasts for a very short time.

Referring to the current version of the procedure, the following is what I have tried successfully in my environment:
Starting from line 1598:

    + ' 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]'

	--Begin of the suggested patch
	+ ' inner join (select object_id, index_id
					from sys.dm_db_partition_stats ps 
					group by object_id, index_id
					having sum(ps.used_page_count) >= ' + cast(@MinNumberOfPages as varchar(10)) + ') ps on ps.object_id = indexes.object_id
														and ps.index_id = indexes.index_id'
	--End of the suggested patch

    + ' 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]'

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions