{"id":2960,"date":"2022-06-03T07:00:00","date_gmt":"2022-06-03T04:00:00","guid":{"rendered":"https:\/\/www.dataplatform.gr\/?p=2960"},"modified":"2024-03-08T20:04:26","modified_gmt":"2024-03-08T17:04:26","slug":"pos-aytomatopoioyme-ti-diadikasia-rebuild-t","status":"publish","type":"post","link":"https:\/\/www.dataplatform.gr\/en\/pos-aytomatopoioyme-ti-diadikasia-rebuild-t\/","title":{"rendered":"How do we automate the index rebuild process in SQL Server without using a maintenance plan"},"content":{"rendered":"

As we have seen in older article<\/a> for the easiest way to access data in databases we use indexes and statistics.<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

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 <\/strong>which has been made by Hallengren<\/a>.<\/p>\n\n\n\n

Of course this work is done by using T-SQL<\/strong> e.g. alter index all rebuild<\/strong> the maintenance plans<\/a><\/strong> which SQL Server has built-in but the above has some limitations.<\/p>\n\n\n\n

What more does it offer?<\/h3>\n\n\n\n

With the indexOptimize <\/strong>you can perform a different action depending on the fragmentation that each index has. Such as reorganize<\/strong>, rebuild <\/strong>and updating statistics<\/strong>.<\/p>\n\n\n\n

It also offers us greater control with the parameters it accepts during its execution.<\/p>\n\n\n\n

The installation<\/h2>\n\n\n\n

Download from here the package with the procedures we will need:<\/p>\n\n\n\n

MaintenanceSolution<\/a>Download<\/a><\/div>\n\n\n\n

or directly from the creator's site here<\/a>.<\/p>\n\n\n\n

After it is downloaded, we execute the entire script in the base we want to call from it, for example in a custom base we have made for monitoring or in the system master base.<\/p>\n\n\n\n

Then to start the process, we simply execute the procedure with the parameters as below:<\/p>\n\n\n\n

EXECUTE dbo.IndexOptimize \n @Databases = 'ALL_DATABASES',\n @FragmentationLow = NULL,\n @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',\n @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',\n @FragmentationLevel1 = 5,\n @FragmentationLevel2 = 30,\n @UpdateStatistics = 'ALL',\n @OnlyModifiedStatistics = 'Y',\n @LogToTable = 'Y',\n @SortInTempdb = 'Y',\n @MaxDOP = 0\n<\/pre>\n\n\n\n
What exactly does each parameter define?<\/h2>\n\n\n\n