SQL Server Archive

How to Find and Fix SQL Fragmentation

What is  Fragmentation?

As information is altered at a database, the database along with its own indicators become fragmented. As indicators become fragmented, ordered data recovery becomes much less efficient and decreases database operation.

Understanding the Different kinds of Fragmentation

There are numerous kinds of fragmentation that could occur and affect SQL Server performance and space use. Notice that logical page and order density problems exist on indexes and tables included in SQL Server. These problems cannot be solved by operating system level fragmentation tools since the fragmentation exists inside the documents, as opposed to in the record level itself.

Record fragmentation in the operating system Level

When deletes and inserts have been completed above time, pages eventually become fragmented as the actual sequence of information pages no more fits their logical purchase. This fragmentation occurs at the record allocation level and could be addressed using program tools. For those who are in possession of a little to medium size program and you don’t have a SAN, you must manage a system fragmentation tool prior to covering logical sequence and webpage density fragmentation within SQL Server.

Page density fragmentation

This matter, also called topical fragmentation within SQL Server, which is much like file fragmentation in the operating system level. When information is deleted, inserted, and altered through the years, an index may cause pages to be out of sequence, where the upcoming logical page isn’t the same as the upcoming physical page.This matter, also called internal fragmentation, happens as webpages split to make room to get info added to your page, there might be excess spare space left on the webpages. This excess space may cause SQL Server to browse more pages than needed to execute specific tasks. SQL Defrag Manager fragments the leaf amount of the index in order to the physical sequence of this webpages matches the left-to-right plausible order of their leaf pages. This procedure enhances index scan performance and all information recovery actions.

Fragmentation Examples

The data is arranged as well as the pages are complete as revealed in the next figure. Since the goal page is complete enough the new row doesn’t match, SQL Server splits the webpage about in half and inserts the new info on the newest page, as displayed in the next figure. Now, the logical sequence of this index doesn’t match the physical sequence, and the index is now fragmented.