Discover how to better manage your index fragmentation process. Try SentryOne SQL Sentry, and evaluate its Fragmentation Manager for free today.
SQL Sentry is the premier performance monitoring solution for Microsoft SQL Server and is part of the SentryOne Platform.
“We have 70 production instances monitored with SentryOne SQL Sentry. It has improved the DBA team's ability to identify potential issues before they become a problem. SQL Sentry has been a great asset to the team..”
What is SQL index fragmentation? Why is it so important? These may be questions that you are asking yourself. SQL Server fragmentation occurs when data is sorted in a non-contiguous way. There are two types of SQL index fragmentation: internal fragmentation and external fragmentation.
Internal fragmentation is when the records are not stored contiguously inside of the page. When there is unused space between records on a page, which occurs through data modification that is made against the table and to the indexes defined on the table, this is internal SQL index fragmentation. Modifications are not distributed evenly across rows of the table as well as the indexes, the empty space on pages varies over time. The empty space on the pages can lead to poor query performance because of more I/O and cache utilization.
The second type of SQL index fragmentation, external fragmentation, happens when the physical storage of pages is un-contiguous which can cause higher disk rotations creating another form of SQL Server fragmentation.
SQL Server fragmentation can create SQL Server performance issues. When you have large amounts of free space on the pages, this means the index is bigger than it should be. Large amounts of free space mean more pages that are necessary and those pages may not be full. Therefore, every time you need to do a scan on the index it takes longer than it should. You will also need more memory to cache that data because SQL Server has to cache empty space on every page.
Luckily, SQL Server fragmentation doesn’t have to take up a lot of time and waste resources. SentryOne's Fragmentation Manager allows you to make the best decisions about when and how to defragment your indexes. Create an environment-wide schedule to automate the process of index defragmentation operations. Customize the schedule to suit your needs for specific instances, tables, databases and respective indexes. With concurrent operations, you can perform index defragmentation faster and more productively.
Identify your most expensive operators when performing SQL query optimization operations with insightful color coding. Focus on specific resources operating costs by I/O or CPU.
Fragmentation Manager also allows you to save time when defragmenting your indexes by reducing maintenance window times as well as running concurrent defrag operations.