Resolving SQL Index Fragmentation Issues with Fragmentation Manager

SentryOne Fragmentation Manager helps you make smarter decisions about defragmenting indexes.

Try SentryOne Fragmentation Manager today for free and discover how to better manage your index fragmentation process.

Let's get started

“One of my favorite things about Plan Explorer is the ability to copy the plan's XML and paste it in. When I'm working in client environments and they don't have PE installed, it's incredibly helpful.”

Erin Stellato

SQL Index Fragmentation

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 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 means more pages than 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.

How Can Fragmentation Manager Help?

Make better decisions about defragmentation operations

Luckily, SQL Server fragmentation doesn’t have to take up a lot of time and waste resources. SentryOne 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. But don’t just take our word for it, check out what some of our customers have to say!