I installed SQL 2016 standard on a new machine.
As a part of it I created a new maintenance plan (all default settings) to rebuild indexes every two weeks.
I ran the maintenance plan and it took quite a long time to finish (34 minutes), way longer then in SQL 2014 where I used a script to do this. (10 Minutes)
After some further investigation I noticed that regardless what I set in the "fragmentation %" and "page count" on the "Rebuild Index Task"page the time that it takes to run it is the same.
If I set "fragmentation %" to 99% it takes 30-34 minutes if I set it to 10% it takes 30-34 minutes.
In other words it looks like this is a bug as it ignores these parameters and it runs the rebuild for all indexes.
By the way if I ran the script on the SQL 2016 I am back to 10 minutes.
Can someone confirm these findings or tell me what am I doing wrong.
In order for SQL Server to determine whether or not an index meets the requirements - it has to query the index stats. That process is what is taking all of the time and will always take at least that long on your systems.
The actual index rebuild/reorganize process is probably only taking about 10 minutes (or less - depending on what is selected).
Maybe the script was far more sophisticated? Our script is very selective about what it decides to rebuild, and it will reorganise in favour of reindex for large indexes. If yours is reindexing it may be having to extend the DB (particularly if you have any kind of SHRINK that you use)
Thanks for your replies.
I thought that the state of the index is stored in sys.dm_db_index_physical_stats.
Just by querying it one can get the fragmentation level. This would imply that there is no need for maintenance plan to do some additional checks except to get the numbers from sys.dm_db_index_physical_stats.
My script uses this query to determine what indexes to rebuild
SELECT object_id AS objectid,OBJECT_NAME(object_id) as name, index_id AS indexid, partition_number AS partitionnum, avg_fragmentation_in_percent AS frag,fragment_count, page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 20.0 And page_count>1000
For the tables with avg_fragmentation_in_percent > 5.0 and < 20 the script reorganises the indexes.
One would have thought that with these new parameters on the maintenance plan the same can be achieved.
My results until now show that the maintenance plan ignores these parameters and rebuilds all indexes, regardless what parameters are set.
Can you use a script instead of the maintenance plan?
I haven't used any maintenance plans for decades - they used to be so bad (huge amounts of unsubtle brute-force) that I don't know of any DBAs who use them ... but maybe they have improved?
If your requirement is reasonably large / sophisticated maybe you need something that isn't a bog standard maintenance plan?
Does Minion have an Index Rebuild feature? I like the lean-and-mean nature of their stuff, plus its very config-light, so easy to get it going (safely).
Other than that its a step up to Ola Hallengren's scripts, but i find them very heavyweight - including getting them going, with sensible/safe parameters, initially.
The DMV used has quite a bit of overhead to determine whether or not an index is fragmented. The data isn't stored it is calculated when you execute it.
Depending on the table size and the options selected it could take a long time to determine the index fragmentation.
How are you determining that the maintenance plan is in fact selecting all indexes and rebuilding them? It probably is just the process to gather the data from the DMZ that is taking a long time and it can depend on how the process is written.