SQL Index Fragmentation and Maintenance (4295446)

One of the most critical variables for database performance is SQL Index Fragmentation. We may face blocking, deadlocks, IO issues, Disk spill issues if the fragmentation level is not appropriately managed. It can also lead to improper execution plan as well.

DMVs

In SQL Server, we can use the DMV sys.dm_db_index_physical_stats to get the information about index fragmentation level, page counts, and index types using this.

SSMS

We can also use the in-built SQL Server reports 'Index Physical Statistics' in SSMS

We get the Index Physical Statistics for the database in following format.

We are limited to running this report on database level, and if we have multiple databases in the instance and many instances to manage, it becomes time and resources consuming task to collect the details, analyse the status based on our threshold status and then decide on the rebuild or reorganise the SQL index.

Custom scripts

We can use custom scripts as well to detect and fix the fragmentation issues, but it is also a complicated process to establish and analyse the results manually. We might want all of this information in a to centralize place and then decide the index maintenance policy. We can use the Alter Index commands to remove the fragmentation level. For small databases, we do not care much about the page counts while rebuilding the index however for large databases we need to consider these as well.

3^rd party tools

We can easily monitor, analyse and remove the existing SQL index fragmentation using the ApexSQL Defrag. We can manage multiple instances and databases in a graphical tool. We get the following benefits from this tool.

• It provides a graphical user interface to monitor and analyse the index fragmentation
• We can do Index maintenance for all SQL Server instances and databases's
• It provides the way to configure the custom reports based on our requirement
• We can configure policies to check and perform index maintenance tasks as per the custom metrics
• We can analyze (fast or deep) index fragmentation, set fill factor, online index maintenance
• We can prepare the reports in desired formats (CSV or HTML)

Once you install the ApexSQL Defrag tool and configure an instance to monitor, it shows the details SQL index fragmentation status quickly.

We should avoid SQL index rebuild or reorganize operations if not required. If we have very large indexes, then it might take a long time and more system resources to analyse. ApexSQL Defrag gives the option to check the fragmentation.

• Limited: In this scan mode, SQL Server collects limited information about the fragmentation. It does not cause any performance issue to the system. It is default mode in ApexSQL Defrag
• Sampled: In this mode, it takes a sample portion of the SQL index and collects the fragmentation information
• Detailed: We get the detailed knowledge about the all index pages; it might cause some overhead to the system resources however, we can use it if we observe the high fragmentation with the above scan modes

Similarly, we can set the fragmentation level in the High, Medium and Low category.

You can also set your won fragmentation threshold to analyse the indexes.

Once we analysed the result set, right click on the particular SQL index and you can have multiple options to choose.

• Analyze (fast)
• Analyze (Deep)
• Reorganize\Rebuild\Rebuild online
• Set fill factor
• We can pause, stop or cancel if any job is running for that index. It is useful if we see any performance issues or blocking due to a particular index, we can cancel that job itself

ApexSQL Defrag allows creating the policies from the pre-defined templates or by the custom parameters. The template is also suitable for most of the SQL index defragmentation requirements.

Using these templates, we can decide to defrag all indexes or index based on threshold level in offline or online mode. For example, I want to create the policy to Rebuild online top 50% most fragmented indexes over 30% or reorganize if fragmented over 10%.

We can configure the resources threshold regarding CPU, Memory load, transaction log usage, active transactions as well. We do not get this level of control on index maintenance using the maintenance wizard jobs or custom scripts.

If the resource is occupied, we can set the retry timing as well. I have seen the requirements in some users who do not want to execute the maintenance after their lean database usage period is over. If we are doing the maintenance, we need to stop the job manually, or we need to create another SQL agent job to stop the index maintenance job at a particular time.

ApexSQL Defrag provides overhead to configure and monitor multiple jobs. We can customize to cancel the index maintenance job after 'N' number of hours.

In the above screenshot, you can see that we can further customize SQL index maintenance operation by the fragmentation level, Index Size and the page counts as well. You can define the locked object delay by a particular time. Sometimes we want to exclude the index where we have index page counts smaller or larger than specified counts.

You can make the changes and configure them as a template to reuse. Once we have performed index maintenance, in the Activities tab, you get an excellent overview of the index fragmentation stats before and after the maintenance. It is essential to know whether the index fragmentation level is under control or not after the maintenance. We usually need to capture the fragmentation level again, but it saves the overhead for us with this information.

In the Reports section, we can view the reports from the server level, overall report.

We can also get a nice view of the top 10 clustered and non-clustered indexes as well.

We are also interested to know the CPU and Memory behaviour during the index maintenance is running. We can get this useful information and keep an eye on the system resources as well.

We can export all the reports and configure the export option. We might want limited information to be extracted while exploring it. ApexSQL Defrag gives you the option to choose what information to display such as charts, statistics, SQL index statistic charts we want to include in the report.

Conclusion:

ApexSQL Defrag tool makes it easy for the DBA's to do the administrative task with better SQL index monitoring and maintenance. You get all the information is a graphical tool with custom visuals, policies along with various informative reports.