Try SentryOne Fragmentation Manager today for free and discover how to better manage your index fragmentation process.
“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.”
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
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.
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!