How do we automate the index rebuild process in SQL Server without using a maintenance plan
Certifications:
- How do we automate the process of checking the integrity of databases in SQL Server without using a maintenance plan - 10 May 2024
- How can we connect Oracle Database to SQL Server using Oracle Gateway on Windows - 5 April 2024
- How can we get email whenever PGA memory exceeds a limit in Oracle database - 1 March 2024
As we have seen in older article for the easiest way to access data in databases we use indexes and statistics.
Indexes are essentially an index that helps us find what we are looking for faster. But as you make changes to the tables, the indexes become fragmented and the information in that index is no longer sorted. On the other hand, in statistics we have information such as the number of records and the distribution of different values in each field of a table.
In the article we will see the easiest and most efficient way to keep indexes and statistics as efficient as possible. We will do this using the procedure indexOptimize which has been made by Hallengren.
Of course this work is done by using T-SQL e.g. alter index all rebuild ή maintenance plans which SQL Server has built-in but the above has some limitations.
What more does it offer?
With the indexOptimize you can perform a different action depending on the fragmentation that each index has. Such as reorganize, rebuild and updating statistics.
It also offers us greater control with the parameters it accepts during its execution.
The installation
Download from here the package with the procedures we will need:
or directly from the creator's site here.
After it is downloaded, we run the entire script on the base we want to call from it, for example on a custom base we have made for monitoring or on the master system base.
Then to start the process, we simply execute the procedure with the parameters as below:
EXECUTE dbo.IndexOptimize @Databases = 'ALL_DATABASES', @FragmentationLow = NULL, @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationLevel1 = 5, @FragmentationLevel2 = 30, @UpdateStatistics = 'ALL', @OnlyModifiedStatistics = 'Y', @LogToTable = 'Y', @SortInTempdb = 'Y', @MaxDOP = 0
What exactly does each parameter define?
- @Databases we define the task will be performed on all databases of the instance.
- @FragmentationLow we specify that no change is made to any indexes that have low hashing.
- @FragmentationMedium we define for any indexes that have medium fragmentation to perform a rearrangement of the indexes and in case this is not possible to do an online rebuild (ie the table is accessible during the process) and if this is not possible either (e.g. not enterprise edition) to go offline (not to have the table accessible during the process).
- @FragmentationHigh = we define for those indexes that have high fragmentation to perform an online rebuild (ie the table is accessible during the process) and if it is not possible (e.g. not enterprise edition) to be done offline (the table is not accessible during the process) .
- @FragmentationLevel1 we define the lowest level that you consider medium hashing.
- @FragmentationLevel2 we define the lowest level that you consider the hash to be high.
- @UpdateStatistics we set the statistics to be updated in both tables and indexes
- @OnlyModifiedStatistics we set it to update statistics only on those tables that have been modified since the last time it ran.
- @LogToTable we define whether indexOptimize commands will be recorded in the commandlog table created when we installed the script.
- @SortInTempdb we define whether tempdb will be used during the process.
- @MaxDOP we define the maximum parallelism of the cpu cores allowed, with 0 we define that we want it to use as many as are available.
We can see the rest of the parameters in detail in the manual on the official site here.
How do I automate the process?
We can add the execution of the procedure to a SQL Server Agent Job.
To do this we go to Objects Explorer, SQL Server Agent, Right click on Jobs, New Job…
In the window that appears, go to the tab Steps and we choose New.
There we choose the master database where we had installed the procedure and add the script of the procedure.
In the tab Schedules we can define the frequency that the task will run. It would be good to choose a time that does not have a heavy workload with access to data on the instance.
To see in detail each action that the procedure has done and when it did it
We simply make a select in the commandlog table:
select * from master..commandlog;
Bonus
With the following script that I have made, with just one click, it installs it MaintenanceSolution by Ola Hallengren along with ready configured jobs for Backup ,Database Integrity and Index Optimize with optimal parameters and timing.
Backup jobs are created disabled as we may have another solution for backup with a 3rd party tool. It also checks for Availability Group so that they run only if it is the Preferred Backup Replica (for backup / database integrity) and if it is Primary Replica (for Index Optimize).
All we need to do is run the following script on each SQL Server Instance:
