index fragmentation?

Question

Hi All, What is index fragmentation in sql server? and when we are using it? Thanks All

Rajneesh Chaubey · Answer

Fragmentation is a phenomenon in which storage space is used inefficiently, reducing capacity or performance and often both. The exact consequences of fragmentation depend on the specific system of storage allocation in use and the particular form of fragmentation. In many cases, fragmentation leads to storage space being "wasted", and in that case the term also refers to the wasted space itself. For other systems (e.g. the FAT file system) the space used to store given data (e.g. files) is the same regardless of the degree of fragmentation (from none to extreme). Fragmentation Examples Imagine there are two data pages for a table with a clustered index. The data is ordered and the pages are full as shown in the following figure. A new row with a primary key of "5" needs to be inserted, and since it is a clustered index, the new row is inserted in order. Because the target page is full enough that the new row does not fit, SQL Server splits the page roughly in half and inserts the new data on the new page, as shown in the following figure. Now, the logical order of the index does not match the physical order, and the index has become fragmented. I strongly recommend you to visit this site:-https://www.brentozar.com/archive/2009/02/index-fragmentation-findings-part-1-the-basics/

index fragmentation?

Insert Link

Embed YouTube Video

Table Options

Insert Image

Answers (1)