要了解索引碎片(Index Fragment)，首先要了解Internal Fragment/Internal Fragment/Page Split。

Internal Fragment vs Internal Fragment

Internal fragmentation — refers to the empty space on a page

操作DML時，page內部"data與data"未排滿而”不連續”。

Fill Fator本身就造成了internal fragment

External fragmentation — refers to pages being out of order

操作DML時,資料寫入不連續的page,導致"page與page"間”不連續”.

Page Split

page split 的造成，是為了要維持data的正確順序而產生(為了將data保持正確的順序，需寫入正確的Page順序)，但頁面已滿只好新生成page，此時就有Page Split。

因此，Database有Page Split是一件正常的事。

但page split有好有壞。

好的Page Split ： 完全依照順序split，不會產生External Fragment，只有Internal Fragment(原本的fill factor或者資料原本就填不滿)，不會造成額外的I/O與CPU與索引資訊紀錄

壞的Page Split ： exteranl fragment產生(page不連續了)，internal fragment產生(舊page與新page皆有internal freagment)，而且壞的page split會產生額外的i/o與cpu與索引資訊紀錄。

建議

面對索引碎片的心態：

Here’s the deal– the DBA was right.

Fragmentation wasn’t the root cause of the performance problem.

The DBA was a super smart person and very talented at performance tuning, too! (And no, I’m not secretly talking about myself here– this is not the “royal she”.)

But she made a strategic miscalculation: she should have set up occasional automated index maintenance to align with her team’s normal practices and standards.

Why you need automated index maintenance?

When performance gets bad, one of the very first things people look at is whether systems involved are configured according to best practices. If you’re not following a best practice, you need to have a really good reason for it.

Regular index maintenance still has a lot of merit: even in Shangri-La, where your data all fits into memory and your storage system is a rockstar with random IO, index maintenance can help make sure that you don’t have a lot of empty space wasting loads of memory.

It’s still a good idea to automate index maintenance. Absolutely don’t go too crazy with it– monitor the runtime and IO use and run it only at low volume times to make sure it helps more than it hurts. Be careful, but don’t skip it.

Monitoring wait time

What should you do instead?

Brent advises setting the fill factor to the default of 100% (or at least 80% or higher) and then rebuilding the indexes to pack them again.

Next, focus on monitoring the right performance tuning number — wait time.

One of the best ways to view various aspects of wait time across your database instances is by using a performance monitoring tool to pinpoint exactly where processes are bogging down.

參考來源:

「-日复一日,精益求精★」索引老失效，碎片率99%以上，是怎么回事？-疑难问题-MS-SQL Server_程序代码_整容说文库
此表在正式库中访问量（更新）比较大，数据库是SQL SERVER2008，分别有索引： INDEX_1 {FIELD_A（INT）、FIELD_B(VARCHAR)} 不唯一，非聚集 INDEX_2…www.zhengrongshuo.com索引的碎片整理SQL语句_chelen_jak的专栏-CSDN博客
原文出处：https://blog.csdn.net/iteye_2210/article/details/81622929SET NOCOUNT ONDECLARE @tablename VARCHAR (128)DECLARE…blog.csdn.netsql server 2008:如何防止99％的日常指数碎片化
我有一个100,000名玩家的高分榜，每天插入2次，每位玩家一张.在一天结束时，该表中索引的索引碎片为99％.有没有办法通过调整设置来阻止这种情况？ CREATE TABLE HighScore( [id] [int]…codebug.vip江湖急救2！生产环境INDEX重建后，1天之内碎片99% - MS SQL Server - ITPUB论坛－中国专业的IT技术社区
江湖急救2！生产环境INDEX重建后，1天之内碎片99% ,ITPUB论坛－中国专业的IT技术社区www.itpub.netHow Bad are Bad Page Splits? - SQLServerCentral
A look at bad page splits and how they affect your database.www.sqlservercentral.comSQl Server Internal & External Fragmentation Explained
According to Microsoft SQL Master, Brent Ozar, he's made some terrible database performance tuning decisions throughout…www.quest.comPage Split
According to the Microsoft TechNet SQL Server Glossary, a page split is "the process of moving half the rows or entries…www.idera.comSQl Server Internal & External Fragmentation Explained
According to Microsoft SQL Master, Brent Ozar, he's made some terrible database performance tuning decisions throughout…www.quest.comRebuild or Reorganize: Configure SQL Server Index Maintenance
Editors Note: this post was updated in June 2014 to link to an online index rebuild bug that can cause corruption. Once…www.brentozar.comhttps://solutioncenter.apexsql.com/why-when-and-how-to-rebuild-and-reorganize-sql-server-indexes/

• Tags:
• SQL Server

• ← 如何檢查SQL Server的CPU Cores & License - SQL Server
• Reorganize index vs Rebuild Index? Option Online vs Offline? - SQL Server →