I tried to use IndexOptimize
stored procedure on a database with a very high number of indexes (> 250.000). The initial step where stored procedure collects data what needs to be processed takes hours, even if I set @Indexes parameter to narrow down the work.
SQL Server Maintenance Solution Version: 2019-02-10 10:40:47 SQL
Server 2017 Standard Edition with latest CU14 installed.
At a customer I saw a database with > 500.000 indexes. After 12 hours data collection step was still running.
I would expect if I set @Indexes to a single index execution should be started immediately.
Here is an example of my stored procedure call.
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
Can somebody share with me his experience in using IndexOptimize for a database with a very high number of indexes ?
Best Answer
You are right, Ola's script first gets data from all the index related DMV's and inserts them inside of
@tmpIndexesStatistics
, even if one table or one index is specified. For a lot of indexes, it gets stuck on this first command, which is a fairly huge query. See below for the query and even further below for a possible fix for this.TL;DR
In one of the queries, the dynamic management views are called a couple of times each with multiple filters.
All this results in a fairly big execution plan.
Creating temp tables and storing the data from the DMV's prior to running the query is a workaround. You could optimize further with indexes on these temp tables / Rewrites.
Test data: >100K empty tables + >200k empty indexes
When changing the parameters to work on all the indexes, and printing instead of executing the statement (
@Execute='N'
)This huge query comes into play
Now, when we try to find what triggers this command inside
[dbo].[IndexOptimize]
With
@ActionsPreferred
corresponding to the fragmentationlevel actions and@UpdateStatistics
being update statistics. Meaning that it will run whenever we do a stat update or a index rebuild / reorganize.no filtering is applied, the query runs over all the indexes in the database.
As a result, at the moment we will have to go through all that trouble, even if we specify one table.
The actual index filtering happen after the the huge command runs:
and updates the
tmpIndexesStatistics
table accordingly, as to execute index operations on these indexes.This query was running over 60 minutes on my small dev server before killing it.
What could possibly be a fix for this?
Well, you would have to change ola's script, but a solution with temp tables worked for me, and executed in 1 minute. Without a doubt, it could be optimized further:
To change this in the index optimization procedure
Sorry Ola this will hurt to look at
When I scripted the proc, this was from line 1430 Until 1537
When testing and rerunning the procedure with one table,it executed in one minute:
Duration 00:01:18
Earlier filters / temp tables with indexes / ... could be used to improve the query further.