Most of the organization use Ola hallengren script for doing index maintenance. It’s a very good script with multiple options which you can use as per your environment. If you have not read about it or if you want to download it, here is the link that you can use.
Another best part of this script is that along with index maintenance, it also help in updating statistics. However, if you don’t know what option is good for your environment, then it could lead to problem as well.
Below is the basic code, you can use to rebuild/reorganize indexes.
EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES'
This code will execute IndexOptimize stored procedure and will reorganize or rebuild indexes based on default fragmentation values ( i.e., < 5% – no action , > 5 and < 30 – reorganize and > 30% – rebuild). However, this will not update statistics as default set to null.
There are some very important parameters that you should consider while calling this stored procedure, which you would like while updating statistics.
@UpdateStatistics = 'ALL/COLUMNS/INDEX',
@StatisticsSample = 100/Any value,
@OnlyModifiedStatistics = 'Y/N',@updateStatistics is by default null. However, best recommended practice is to update all the statistics. One important thing to note here is that once index is rebuilt, index statistics are recreated, which means they are already updated and hence at places where you are only rebuilding the indexes, you should use @updateStatistics = ‘COLUMNS’.
But, in real world, you may have to opt for ‘ALL’ as indexes are rarely rebuild for low fragmentation.
Another important parameter is @statisticsSample. By default, it uses default sampling percentage which is OK for smaller tables, but for large tables with millions of rows, this will prove inadequate.
Please go through Microsoft article for understanding how sampling is considered.
Hence, recommended value will depend on your environment. If you want best, then go for sampling percentage of 100, but, do note that this will increase overall duration of execution.
Last option we will talk about is @OnlyModifiedStatistics . This by default is No. In real world scenario, you want optimal strategy, which doesn’t take too much of time by focusing on statistics which has really been modified since last statistics update. In such case, this option can prove to be very useful. If you run with @OnlyModifiedStatistics = ‘Y’ then it will update the statistics only if any rows have been modified since the most recent statistics update.
One bonus suggestion is to use @LogToTable = ‘Y’, which will each execution history in a commandlog table which gets created when run implement the Ola Hallengren solution. This will help you to check which index or statistics got updated.
Below is the script I use in normal situation.
EXECUTE dbo.IndexOptimize
@Databases = 'ALL_DATABASES',
@UpdateStatistics = 'ALL',
@StatisticsSample = 100,
@OnlyModifiedStatistics = 'Y',
@LogToTable = 'Y'For very specific cases or for specific business requirement, you can tweak this and create multiple SQL agent job for that specific purpose.
Happy Learning!