One of the most important task of a database administrator (NYSE:DBA) is to make sure that the indexes are regularly maintained which is termed as Index Maintenance or Index Development. Fragmentation is one of the major reasons why the need to reorganize the index periodically arises. Reorganizing of the index is done if the fragmentation is between 10% to 20% and if the fragmentation is above 20% then rebuilding of the entire index becomes essential. In index maintenance measuring fragmentation and carrying out appropriate measures when required is straightforward for databases without any resource constraints.
Index Maintenance solution:
An index maintenance solution measure fragmentation and carries out defragmentation in an iterative and incremental manner. Initially it measures the fragmentation level of a fixed number of indexes, records the information and then reorganizes or rebuilds indexes as needed, here too working on a fixed number of indexes as needed. Once an entire cycle of measuring and defragmentation is done, the entire process repeats all over again.
The index maintenance solution comprises of three main components viz. a table, a T-SQL stored procedure, and a pre-scheduled job to execute the stored procedure. A table has details about the index and the table itself like the index names, table size, the fragmentation details for the indexes like the level of fragmentation and when the last refresh/rebuild was undertaken. In index maintenance if the table is not existing then it will be created by another main component i.e. a T-SQL stored procedure named usp IndexDefrag. Listing 1 displays the lines of code that creates a table with usp_IndexDefrag. Other than creating a table the usp_IndexDefrag stored procedure also measures and records fragmentation level. One can preset the number of indexes to process at one particular time with the @HowManyIndexInfoToPull parameter. Keeping this parameter to 0 makes the job process every index available. For example, keeping it 15, results in 15 indexes processed simultaneously.
Once the measurement of the level of fragmentation information has been accumulated for every index, the solution transitions to undertaking index maintenance, reorganizing/rebuilding indexes as and when appropriate. In this index maintenance solution one can preset how many indexes to defragment at a time with the @HowManyIndexToDefrag parameter. If the reported fragmentation level is below than what is mentioned in the @avg_fragmentation_in_percent_limit_Reorg parameter, then no defragmentation will take place. Conversely if the fragmentation level is above the mentioned level in the @avg_fragmentation_in_percent_limit_Rebuild parameter then the index is rebuilt. If the level of fragmentation is between the two limits specified, then the index is reorganized.
To make sure that the solution cannot badly affect database availability and optimal performance, one can set a time limit which will override the @HowManyIndexToDefrag and @HowManyIndexInfoToPull values. One can set the @DurationH and @DurationM parameters to specify how many hours and minutes, to limit the defragment and measurement operations.
The solution's last main component is the scheduled job to execute the usp_IndexDefrag stored procedure. It calls the stored procedure based on the preset parameters one has set. Each database has to have a dedicated separate job for them. Under normal circumstances the jobs are scheduled for execution every night barring exception for a few when a new code deployment is taking place.
This is a very sophisticated and well channeled solution for index maintenance and index development. There are many more ways to achieve what is discussed above but the most streamlined solution for index maintenance is given above.
The author of this article is a Computer Engineer with 3 years of work experience in the IT industry. On the job the author has extensive hands on experience of index development . Being an expert in servers and SQL, the author has keen and undivided nag for various index maintenance solutions and coming up with new and better solutions as and when possible.