Wednesday, 4 April 2012

SQL Server - Index Fragmentation - Understanding Fragmentation

When I had a discussion with couple of my friends about index fragmentation, I realized that they have different understanding about index fragmentation. In this post I will try my level best to explain different types of fragmentation.Understanding the concept of index fragmentation is important  for detecting and removing fragmentation efficiently.

What is Fragmentation

Fragmentation can be defined as any condition that cause more than optimal amount of disk I/O to be performed in accessing a table or cause the longer  disk I/O. Optimal performance of SELECT queries occurs when the data pages of tables are contiguous  as possible and pages are fully packed as possible.Fragmentation breaks this rule and reduce the performance of the queries. Fragmentation can happen in two level . One is file system level fragmentation which is called as Logical/Physical Disk Fragmentation and Index level fragmentation. Each of them are described in the below sections.

Logical/Physical  Disk Fragmentation

Logical fragmentation is the fragmentation of database file in the file system itself like any other files.This occurs when the file system is not able allocate contiguous space for database file.As a result, disk head has to move back and forth to read from the database files. SQL server is completely unaware about this kind of fragmentation and it is not possible to measure the logical disk fragmentation using any script. Logical disk fragmentation can happen due to various reason like
  • Placing  database file in the same disk where other files( like OS files and other application files)are kept.
  • Frequent growth of the database file in smaller chunks.
To remove logical fragmentation we can use the windows fragmentation tool but note that we need to stop the SQL server while running the defragmentation tools.Otherwise the defragmentation tool will skip database file as it is used by the SQL server. 

The best ways to to avoid the logical fragmentation are :
  • Keep the database files in a separate disk isolated from other application files and log files.
  • While creating new database,estimate the size of database file and allocate enough space to avoid the frequent growth of data files.
  • Specify the database growth option to allocate larger chunks rather than small chunks frequently.

Index Level Fragmentation

Index level fragmentation comes in two flavors : Internal Fragmentation and External Fragmentation. If Index level fragmentation is high ,it may prevents optimizer from using the available indexes in optimal way.

Internal Fragmentation

Internal fragmentation is measured in average page fullness of the index(Page density). A page that is 100% full has no internal fragmentation.In other words, internal fragmentation occur when there is empty space in the index page and this can happen due to insert/update/delete DML operation.Every index page can hold a certain number of records based on the size of the index, but that does not guaranteed that the page always hold maximum number records.  Internal fragmentation is normally reported as a percentage of fullness in bytes, not in records. An index page that has internal fragmentation 90% may be full in terms of record. The remaining 10% bytes of the pages may not be enough to hold one more record. In a 8KB  pages,  maximum of  8060 bytes can be used by data.Rest of space are used by page header and row offset array.Let us assume that we have index with fixed size of 100 bytes and the index has 800 entries. So we can can store 8060/100= 80 records per page by leaving 60 bytes empty as it is not enough to hold one more records and this index requires 10 pages to store the entire index structure.If you calculate the average fullness of this index, in ideal scenario it will come as 99.26%. Let us see how it will look like in Fig 1.

Fig 1

Let us assume that we are deleting the half of the entries randomly of this table which reduce the total number of entries in this index to 400.Now the pages will look like as given  in Fig 2 with total of 40600 bytes free space across 10 pages. If you calculate the the average fullness as Total data size*100/Total page size = 4000*100/80600= 49.62% . It clearly says that, half of the spaces are empty and the index has internal fragmentation. 
Fig 2

How Internal Fragmentation will affect the performance of the SQL server ?

  1. Internal Fragmentation will increase the I/O. When you run queries that scan part or complete table/index, if you have internal fragmentation on that table/index, it causes additional page reads. In our example, the entire data can be stored in 5 pages. When the query needs to do index scan it has to read 10 pages instead of 5 pages. Which means 50% more I/O.
  2. Internal Fragmentation reduce the efficiency of buffer cache.When indexes has internal fragmentation, it need more space to fit in the buffer.In our case this single index will use 5 additional pages to fit into the buffer which should have used to store other index pages. This will reduce the cache hit ratio.  In turn  it will increase the physical I/O. It also increase the logical reads.
  3. This also increase the size of the database file. It need more disk space to store the additional pages and reduce the performance of Backup and Restore.

External Fragmentation

External Fragmentation happens when the logical order of the pages does not match the physical order of the pages. External fragmentation refers to the lack of correlation between the logical sequence of an index and its physical sequence. It is  measured as the percentage of out-of-order pages in the leaf pages of an index. An out-of-order page is a page for which the next physical page allocated to the index is not the page pointed to by the next-page pointer in the current leaf page.Let us see the Fig 3 below. It is representation of index with three pages.Data is stored in sequential page. In other terms logical order and physical order are same and it store the index keys from 1 to 16 a. All pages are completely full except the Page 3

Fig 3

Let us see what will happen if we insert the value 4 to the underlying table in the Fig 4.
Fig 4

While inserting the value 4 into the table it has to place in the Page 1 between value 3 and 5 but unfortunately Page 1 does not have any free space to occupy one more record. The only option is perform a page split by dividing the Page 1 evenly by leaving half of the data in Page 1 and moving half of the data to new  page (Page 4). From Fig 4 we can understand that the logical order of the Page 4 is not matching with the physical order. External Fragmentation can happen due to various reasons:

  1. While allocating pages for new table , SQL server allocate pages from mixed extend till it reaches the 8 pages. There is possibility of having the first 8 pages from 8 different extents. 
  2. When all records are deleted from a page, the page will be de-allocated from the index(The de-allocation  of pages will not happen immediately) which create gap and increase the fragmentation.
  3. Once object reached 8 pages size, SQL server will start allocating uniform extent to the objects.While allocation uniform extent to an index, next sequential extent to the current extent might be already allocated to other objects/indexes.

How External Fragmentation will affect the performance of the SQL server ?

While reading individual rows, external fragmentation will not affect the performance as it directly go to the page and fetch the data.Unordered scans also will not affected by the external fragmentation as it use the IAM pages to find which extents need to be fetched. In the case of ordered index scan ,external fragmentation might become a degrading factor for performance. The degradation of the performance is because the disk drive's heads have to jump around on the physical disk, rather than performing just contiguous read operations.Also note that external fragmentation will not affect the performance once the pages are loaded into the buffer pool.

I will explain how to detect/measure the fragmentation in the next post.

If you liked this post, do like my page on FaceBook at


  1. Respected Sir,
    This journal is very helpful to get an full understanding over fragmentation on SQL Server. This article also give some knowledge from the operating system point of view.

    1. Thank you Subrat for your inspiring comments.



    2. - Đi giúp Tử Điện Mãng cùng Yêu Cơ.
      đồng tâm
      game mu
      cho thuê nhà trọ
      cho thuê phòng trọ
      nhac san cuc manh
      số điện thoại tư vấn pháp luật miễn phí
      văn phòng luật
      tổng đài tư vấn pháp luật
      dịch vụ thành lập công ty trọn gói
      chém gió
      Nhạc Thành nhìn Khiếu Thiên Hổ nói, Yêu Cơ cùng Tử Điện Mãng, Thông Thiên Thử dù là chiếm cứ thượng phong, nhưng chặn giết đối thủ vẫn có một chút rắc rối, có thề tất yếu trì hoãn một ít thời gian.

      - Các ngươi ba người dĩ nhiên đối phó một tên còn không có ra gì, thật là vô dụng.

      Khiếu Thiên Hổ vâng lệnh Nhạc Thành, sau đó thân ảnh như điện, thân thể khổng lồ của nó nhanh chóng đi tới ba người Tử Điện Mãng cười nhạo một chút.

      - Thúi quá, con mèo bệnh này.

      Tử Điện Mãng hét lớn một tiếng, lập tức hung hăng một chưởng bổ lên.

      - Đáng chết, cái này có phiền toái.

      Huyễn Linh môn ngũ tinh Đấu Tông sắc mặt vô cùng trầm trọng, hắn đối với

  2. Hi Nelson,

    you said at the and "Also note that external fragmentation will affect the performance once the pages are loaded into the buffer pool". How is that possible because data is in the memory and there is no use of disk drive's heads?

    Best regards,

    1. That was mistake even after multiple reading before hitting the publish button. I have corrected that . Thank you for pointing it out.

  3. I must disagree with many of the definitions stated, as "internal fragmentation" is an aspect of selecting fillfactor, and for indexes that aren't either read-only or written solely in a sequential fashion, necessary for good performance.

    "external fragmentation" is a term I would assign to filesystem and storage (disk, SAN) level fragmentation, which I don't believe was covered at all.

    I almost must disagree with speaking of "physical files" unless one is addressing the actual physical storage; if you're not talking about the particular volume of matter whose physical state changes at a molecular or atomic level, you're not talking about a physical file. SANs can do amazing things... some of which result in the OS seeing a contiguous file that is actually not contiguous on the actual disk platters in question.

    1. Alexander Suprun20 May 2012 at 13:21

      I will strongly disagry on that point. All the terms used are very well known in SQL server community, and coming up with some new definitions will certanly confuse most of the readers.

  4. Alexander Suprun20 May 2012 at 13:28

    Hi Nelson,

    Very well written article, as usual. But there is one inaccuracy I have to point out. The optimizer never ever considers any kind of fragmentations when creates an execution plan. The reason is simple. It would have to scan all the tables and indexes participated in the query to figure out what the current fragmentations are, and as you can imagine it will take unacceptably long time during a plan optimization process.

    1. Thank you for pointing it out. You are 100% correct. The change in the execution plan in our environmnet due to the statistics got updated while rebuilding the indexes. I have removed the statement from the post. Thank you once again..

      Nelson Johhn A

    2. Alexander Suprun14 May 2013 at 08:55

      Hi Nelson,

      My appologies, but you might be partly correct on this one. Although the optimizer cannot scan the tables to find out the exact fragmentation levels, it still indirectly uses fragmentation to chose execution plan. When internal fragmentation increases the index becomes bigger, and the bigger the index the smaller the chance that optimizer will use it.

      Alexander Suprun

  5. Really great work.Can you please let us know next post URL


  6. why is that 8kb limit?
    why not 16 or 32kb :-D
    why *Exactly* 8?

    1. :) nice question. but that depends on microprocessors addressing capability, network packets and 10,000 other things.... but funny enough a lot of it is based on rule of thumb, or more like best guess.... this link explains in detail -->

      it depends on the average amount of I/O sql does, if with time there are substantial changes in the average, they can increase the page size too. also you dont want to have a big page, because then again, the amount of CPU cycles to get to the data buried in one big page will be high. if you have a smaller page, then that increases the amount of pages again the cycles for lateral scans increase..... so yeah, best guess to be honest :) and rule of thumb and some theories mentioned in the article above... but thats a good question :)

    2. Thanks for pointing to a nice article ...

  7. Even if I kept a fill factor of 70 for the indexes, why the above mentioned insert is not helping from page split ?

  8. Great work! Really helpful!!

  9. Spectacular article nelson sir keep it up and nice to meeting you.

  10. Hi ,

    I was to try that fragmentation will not cause different execution plan but ended with something difference than what i read here. I would like you to explain if i missed any of your points with misunderstandig or am doing anything wrong.

    Create Table BigTable1 ( id int identity , c2 char(100) default replicate('a', 100))

    INSERT INTO BigTable1 default values
    go 1500

    --Now I’m creating one NCI with heavy fragmentation
    CREATE NONCLUSTERED INDEX [idx_BigTable_SomeColumn1] ON [dbo].[BigTable1] (c2 ASC)
    with fillfactor=2

    --The following query cover the index

    Select c2 from BigTable1 where c2 like 'aa%' option(recompile)
    --Based on ur thoughts the query should use the index (should not look into fragmentation)
    --but optimizer uses the table scan. Since its better than NCI

    --Now I’m going to rebuild the index
    ALTER INDEX [idx_BigTable_SomeColumn1] ON [dbo].[BigTable1] REBUILD
    with (fillfactor = 100)

    --Again check the same query
    Select c2 from BigTable1 where c2 like 'aa%' option(recompile)
    --Now its using NCI seek

  11. Buenas tardes, Existe la posobildad de hacer la desfragmentacion de una base de datos por medio de linea de comandos en MSDOS.


  12. Thanks Nelson John. Its very descriptive on index fragmentation.
    How can we detect index fragmentation and get rid of it? Rebuilding the index should solve the index fragmentation right?


  13. "When the query needs to do index scan it has to read 10 pages instead of 5 pages. Which means 50% more I/O."

    No, that is 100% more, (so) twice as much!
    It's true that 5 is 50% of ten, but the base number is 5, not 10.

  14. +1 on Anonymous's comment of 4 April 2016. This is 100% more I/O, which is to say twice as much, not half again as much.


  15. I loved the way you discuss the topic great work thanks for the share, Let me share this, Hadoop training in pune


  16. The blog or and best that is extremely useful to keep I can share the ideas
    of the future as this is really what I was looking for, I am very comfortable and pleased to come here. Thank you very much.
    tanki online | 2048 game|


  17. I loved the way you discuss about the fragmentation topic great work thanks for the share
    oracle fusion procurement training

  18. This web site is usually a walk-through its the data you wished concerning this and didn?t know who ought to. Glimpse here, and you?ll definitely discover it. Wd Hard Disks - Hard Disks, Printers & Monitors

  19. Wedding photographer in jaipur
    Great post and very helpful for me.Thanks for share this post

  20. This comment has been removed by the author.

  21. thanks for your blogs it very helps fully for me. I have given escort girls services in Jaipur you visit my site
    Jaipur Best Escorts
    Escorts in Jaipur
    jaipur Escorts

  22. [CENTER][SIZE=5]
    ارخص شركات تنظيف المنازل والشقق بجدة

    يبحث الجميع وراء أعمال التنظيف الجيد ولكن البعض لا يعلم إلى أين يذهب حتى يحصل على أفضل خدمات وأعمال التنظيف، فكل هذا لا يتواجد سوى لدى شركة تيجان التي تتميز بالخبرة وأعمال التنظيف الذي لا يوجد لهل مثيل بأي مكان بجدة، فالشركة لها الكثير من الفروع الموجودة بجميع أرجاء المملكة.
    [URL=]شركة تنظيف بجدة[/URL]
    تعد شركة تنظيف بجدة واحدة من أكبر وأهم شركات التنظيف بجدة، كما أن الشركة تحرص على أن تقدم إلى عملاء الشركة الكثير من الخدمات والعروض التي تحصل على إعجاب معظم سكان جدة وعملائها.
    [URL=]شركة تنظيف شقق بجدة[/URL]
    الشقق من أكثر الأماكن التي تحتاج إلى عمليات تنظيف متميزة، ولهذا يكون من الأفضل اللجوء إلى شركتنا شركة تنظيف شقق بجدة، لأنها الشركة الوحيدة القادرة على تقديم افضل أعمال تنظيف شقق بجدة، كما أنها تقوم باستخدام أجود مواد التنظيف وأحدث الأجهزة.
    [URL=]شركة تنظيف منازل بجدة[/URL]
    تعتبر المنازل من الأماكن السكنية الأكثر استخداماً في جدة، وتحتاج إلى التنظيف المستمر نظراً لتراكم الأتربة والأوساخ بالمفروشات والعفش والأثاث، ولهذا تستعين معظم نساء جدة بشركة تنظيف منازل بجدة لأنها من الشركات التي تعمل بجد واجتهاد عن طريق استخدامها لأحدث المعدات والأجهزة.
    [URL=]شركة تنظيف فلل بجدة[/URL]
    تتميز شركتنا بأنها توفر خدمة تنظيف الفلل بجدة وتلك الخدمة يحتاج إلى معظم قانطين الفلل والقصور، وهي من أكثر الأماكن التي تحتاج لفريق عمل كبير ومتميز لديها الخبرة فهذا النوع من أعمال التنظيف، فلدينا عمال متخصصين في تنظيف الحدائق والمسابح، كما نعمل على تخصيص فريق العمل كلاً منهم له العمل المخصص له والذي يلتزم بالقيام به على أحسن ما يكون.
    [URL=]شركات تنظيف بجدة[/URL]
    تتميز معظم شركات التنظيف بجدة بعملها الجيد والممتاز وأجهزها ومعداتها المتطورة، كما أن تلك الشركات تعمل على تطوير ذاتها باستمرار لذا تقوم باستخدام أحدث المعدات والأجهزة التي تجعلهم يحصلون على أفضل أعمال التنظيف.
    تهتم الشركة بأن تقوم باستخدام مواد التنظيف العالمية لجودتها العالية، كما أنها تقوم باستخدام التنظيف بالبخار الذي يعتبر الأفضل والآمن على الأغراض والمفروشات.
    التنظيف بالبخار بجدة هو من أفضل الاختيارات الذي يهتم العميل بأن يحصل عليها من قبل شركة تنظيف بجدة، فهو من أحدث الوسائل التي تستخدم في تنظيف السجاد والكنب والستائر والمفروشات بأكملها.
    [URL=]شركة تنظيف سجاد بجدة[/URL]
    تنظيف السجاد من الأعمال الصعبة جداً على سيدة المنزل فيتراكم به الأتربة والأوساخ والبقع الصعبة التي تحتاج لكثير من المواد للعمل على إزالتها والتخلص منها، ولذا يستعين أغلب السيدات لأفضل شركات تنظيف سجاد جدة نظراً لقيامها بأعمال تنظيف السجاد بجدة على أفضل ما يممكن، فلدينا فريق عمل متخصص في أعمال تنظيف السجاد بجدة، كما تحرص الشركة على القيام بتنظيف السجاد بالبخار فهو الحل الأفضل الذي لا يترك أي أثر سلبي على السجاد والمفروشات.
    [URL=]شركة تنظيف كنب بجدة[/URL]
    للكنب أهمية كبيرة في حياة كل شخص فيستخدم الكنب في المنازل والفلل والقصور والشركات وخلافه، كما أن الكنب من الأغراض التي تتعرض لكثير من الأوساخ والأتربة والبقع الذي يكون من الصعب إزالتها باستخدام المساحيق العادية، فدائماً أنت بحاجة إلى شركة تنظيف كنب بجدة للحصول على أفضل أعمال للتنظيف فأنت دائماً تكون بحاجة إلى واحدة مثل شركتنا لقدرتها الفعالة في أعمال تنظيف الكنب والمفروشات.
    [URL=]شركة تنظيف بالبخار بجدة[/URL]
    يعد التنظيف بالبخار من أحدث الوسائل التي تستخدمها شركات تنظيف بجدة، كما أنها آمنة للغاية ولا تترك أي أثر سلبي على المفروشات فالتنظيف بالبخار لا يقتصر استخدامه على الكنب والسجاد فقط فهو أيضاً يستخدم في تنظيف الأسطح والأرضيات الخاصة بالمطبخ والحمام، كما أنه يساهم في التخلص من الروائح الكريهة الموجودة بالمنازل، ويعمل على التطهير والتعقيم وإبادة الحشرات بجدة.

  23. ما هي خدمة صيانة وايت ويل
    يقدم مركز صيانة وايت ويل
    ويل لخدمات صيانة وايت ويل
    خدمات الصيانة والإصلاح والتشخيصات لكل انواع أجهزة وايت ويل ، وهي تضمن لكم تقديم خدمات على أعلى مستوى بأسعار معقولة، الفنيون الذين يعملون في مركز صيانة وايت ويل مدربون على أحدث التقنيات ولديهم أحدث المعدات التشخيصية لتحديد أية أخطاء موجودة بأجهزة وايت ويل بأسرع وأدق درجة ممكنة ، قطع الغيار التى يتم استبدالها ستكون على نفس الدرجة من الجودة تماما مثل الأجزاء الأصلية التي كانت موجودة بجهاز وايت ويل ، تعمل شبكة توكيل وايت ويل لخدمات صيانة وايت ويل طبقا للمعايير المهنية وايت ويل لخدمات صيانة وايت ويل
    ، تم الحصول على موافقة معايير حمايةوخدمة المستهلك The Consumer Codes Approval التي تديرها معهد المعايير التجارية Trading Standards Institute على المعايير المهنية الخاصة بمراكز صيانة وايت ويل لحدمات صيانة أجهزة وايت ويل ، هذا التعهد تجاه العميل يمنحك راحة البال بعلمك بأنك لن تحصل فقط على أفضل خدمة فنية، وإنما أفضل خدمة عملاء أيضا، بتكلفة أقل مع أعلى معايير الخدمات الفنية وخدمة العملاء، لم لا تتصل إذن بمركز صيانة وايت ويل لخدمات صيانة أجهزة وايت ويل ؟
    صيانة سامسونج
    صيانة ال جي
    صيانة توشيبا
    صيانة سيمنس
    صيانة بوش
    صيانة دايو
    صيانة اريستون
    صيانة سيمنس

  24. gamekiller
    gamekiller app
    game killer
    gamekiller apk
    gamekiller download
    A killer app needs to be appealing enough to get you to buy the ... A killer app makes many people think of a game with universal appeal.

  25. شركة تنظيف منازل
    لو تريد حل قوي وفعال في التخلص من الحشرات في منزلك ليس امامك افضل من شركة مكافحة حشرات بجدة وفي حاجة إلى شركة رائدة تخلصك من الحشرات المنتشرة حولك فأليك الأن أقوى شركات منطقة جدة ومكة والطائف شركة مكافحة حشرات بجدة وكذلك في مكة المكرمة لدينا شركة مكافحة حشرات بمكة المكرمة ولدينا شركة رش مبيدات بالطائف متخصصة و شركة مكافحة حشرات بالطائف تعمل على مكافحة الحشرات المختلفة في المنازل والمزارع والحدائق والشوارع والفلل والقصور وغيرها من الأماكن المختلفة حيث توفر لك الخدمات اللازمة في حل مشاكلك الصعبة المتعلقة بالحشرات المنزلية.

  26. Useful and interesting to read this article, I would like to thank you so much, for share this blog.
    Jeans Supplier in Delhi

  27. I am goanna try that every time I post going forward. Thanks for your valuable knowledge because of this i use to updated, thanks for sharing this wonderful article.
    logo painting on wall

  28. Thanks for your valuable knowledge information because of useful updated for me, thank you for share this wonderful article.
    web develoment company in delhi

  29. Superb and valuable knowledge information because of useful updated for me, thank you so much for share this wonderful article.
    Motorcycle Tours in India

  30. These are amazing and valuable knowledge information because of useful for me, thank you so much for share this wonderful article.
    Lifestyle Magazine India

  31. Awesome article and valuable information because it’s useful for me, thank you so much for share with us.
    Custom Broker in India

  32. Alot of blogs I see these days don't really provide anything that I'm interested in, but I'm most definitely interested in this one. Just thought that I would post and let you know. Nice! thank you so much! Thank you for sharing.
    Website Development Company in Delhi
    Website Designing Company in Delhi
    Mobile App Development Company
    Mobile App Development Company in India

  33. I am really happy to say this I am deeply read your article, I am searching like this type valuable information, it’s really helpful for me, I am happy to found it, thank you so much for share this blog, great work, keep sharing like this type of article, thank you so much for read my comment, if any one searching website designing company in India please visit my website
    Website Designing Company in Delhi