Indexes play a critical role in SQL Server query performance. SQL Sentry Fragmentation Manager helps you make intelligent decisions about index management based on the table and index information collected.
In this blog post, I’m going to discuss index fragmentation, several ways you can identify it, and why a more granular approach to index maintenance can save time and allow you to focus on other tasks during maintenance windows.
The Indexes tab has been a part of SQL Sentry for quite some time, but we still see customers not taking full advantage of this feature. Prior to SQL Sentry release 2020.8.31, the Indexes tab was disabled by default, and many users may not have enabled index collection for fear of it “rebuilding everything instantly.” Other SQL Sentry users may have an index maintenance plan that works, and nobody is complaining, which they take as a good sign!
Before we walk through how to enable this feature and the potential benefits of doing so, let’s talk about index fragmentation, identifying fragmentation, and why many choose to use SQL Sentry Fragmentation Manager.
Understanding Index Fragmentation
Many available resources (e.g., the Microsoft Index Design Basics) speak to the structure of indexes and why making use of indexes instead of forcing SQL Server to locate data via scans will improve performance. There are times when scans make more sense, but in most cases, SQL Server can process queries faster by leveraging an index.
There are two types of fragmentation—internal and external. Let’s look at each type in detail.
Internal Fragmentation
Internal fragmentation relates to empty space on a data page. By default, SQL Server will try to use all available space on a page. When maintaining your indexes, there’s an option, Fill Factor, to leave free space on a page, but I won’t discuss that in detail today. The two scenarios below will help explain what can cause internal fragmentation. (There are certainly other examples, but to keep this post relatively short and to the point, let’s stick with these scenarios.)
- INSERT: As you insert data into tables, all this data is stored in pages. Depending on the amount of data you’ve inserted, SQL Server will allocate pages to store this data, and a full page may not be needed. For example, let’s look at a shopping mall directory. I have labelled each column in the picture as a page number; note page 6 has free space to add more data if required.
Shopping mall directory with page numbers listed above each column
- DELETE: Working off the same example, some shops have now shut down. I’ve seen this at many shopping malls where the name is removed or just covered up. In this case, empty space is generated on the page by deleting that row.
Shopping mall directory: Stores in Page 2 deleted, which creates free space
External Fragmentation
External fragmentation is when the physical order of pages doesn’t match the logical order. A page split occurs at this point and can be tied to when users are inserting or updating data. These page splits cause performance issues because SQL Server can’t read data sequentially. (Learn more about read-ahead reads.) Let’s look at two examples to help explain this in simpler terms.
- INSERT: Continuing to use the previous example of a shopping mall directory, if you wanted to add stores into the Fashion category, you would need to add these elsewhere in the table because there’s no free space on Page 1. This is a page split and a simple example of how inserts can cause fragmentation. Note, just tracking the page splits metric in perfmon can falsely point you to fragmentation within your environment, as SQL Server logs a page split when a new page is created.
- UPDATE: In SQL Server, if you had a null value specified and you now want to populate the field, you can update the row(s). Remember, data is stored on pages, and if the page is already full, updating a row on the page to make it larger will also cause external fragmentation.
Identifying Index Fragmentation
As data is modified through DML, INSERT, UPDATE, DELETE, tables with indexes can become more and more fragmented over time. The rate at which fragmentation grows across your environment will differ depending on DML patterns on different tables.
Even with just this level of knowledge, if you’re implementing a maintenance plan that’s rebuilding or reorganizing indexes whether it’s needed, this can often lead to the needless use of resources and an increase in transaction log size, and you may not be making the best use of maintenance windows available.
A more deliberate approach, outlined below, can help you maximize the time you have available for maintenance.
SSMS + sys.dm_db_index_physical_stats
By using a more refined approach, you can reorganize/rebuild indexes only when needed. There are several ways to review fragmentation, and the first is by making use of sys.dm_db_index_physical_stats. There are different scanning modes and parameter values you can pass through to ensure you’re detecting fragmentation. More information about this system function can be found here. This information can also be reviewed through SQL Server Management Studio (SSMS) by expanding on the Table –> Indexes –> Right Click Index –> Properties in Object Explorer.
Identifying index fragmentation via SSMS
SQL Sentry Fragmentation Manager
SQL Sentry customers can use Fragmentation Manager (accessed via the SQL Sentry Indexes tab) to identify index fragmentation. For customers who have been using SQL Sentry prior to the release of version 2020.8.31, this will be disabled initially, and we’ll walk throughs steps to enable this capability now. For customers who are newer to SQL Sentry, this feature will be enabled by default and you can skip ahead.
To enable Fragmentation Manager, open the SQL Sentry Performance Analysis Dashboard for the required target and click the Indexes tab.
Enabling SQL Sentry Fragmentation Manager
At this point, you’ll now have to decide if you want SQL Sentry to rebuild and reorganize your indexes or perform only an analysis. Having SQL Sentry analyze your indexes throughout the week can provide value for identifying issues with your current maintenance plan. As mentioned earlier, you may not need to be rebuilding/reorganizing indexes as frequently, you may have some questionable fill factors applied, or you may not be rebuilding/reorganizing as frequently as you should.
Fragmentation Manager options: analyze only vs. analyze and defrag indexes
The last step is to add a schedule for the analysis of your indexes. Although the overhead associated with this analysis shouldn’t be a concern, we do recommend scheduling index analysis during off hours but not during other actual maintenance tasks.
Another consideration would be how often SQL Sentry is configured to collect this information. If you collect index information right after rebuilds/reorganizes, the information collected may not be of any use. To make best use of this feature, I recommend setting up daily index data collection, which will allow you to review fragmentation throughout the week. For example, you may be doing rebuilds/reorgs over the weekend, which reduces fragmentation, but come Monday, you’re in the exact same place again after a massive DML operation. This can be a sign that for certain tables/indexes, your plan is inadequate.
Once index data collection and/or fragmentation analysis/management is enabled, you’ll start to see the Indexes tab populate, as shown below.
Fully populated Indexes tab in the SQL Sentry desktop client
You can review the settings you’ve applied by selecting the target in the Navigator pane and then selecting View –> Settings, as shown in the screenshot below. You can also disable this feature by right clicking the target in the Navigator pane.
Demonstrating the ability to change index collection settings at a target level
Conclusion
In my next blog post, we’ll review data collected by SQL Sentry Fragmentation Manager and look at how you can apply different settings at an individual database or index level. In the meantime, if you want to review what data has been collected, check out Lori Edwards’ blog post here. I’ve also listed some resources below if you’re interested in learning more about index fragmentation.
Additional Resources
