我有大约 700 万条记录的表。索引碎片约为 90。
我尝试为所有索引重建索引。它工作正常，但在每晚工作碎片化后，其中一个none cluster index增长到 30 。每天晚上我都会截断这个表并向其中插入新记录，然后更新一些列（非聚簇索引（不是我提到的列））。我不知道为什么 index( IX_Customer_Code) 的碎片增长得很快。桌上的工作量：
1-每晚截断。2-插入约700万条记录。3- 更新Customer_Key（在插入时得到 Null）。
我有问题IX_CustomerCode
name index_type_desc page_count
IX_CustomerCode NONCLUSTERED INDEX 17,323
IX_Customer_Key NONCLUSTERED INDEX 15,608
Ix_Date NONCLUSTERED INDEX 15,561
PK_MDT_Customer_Order_Debit_Date_Infos_Tmp CLUSTERED INDEX 436,675
表结构：
CREATE TABLE [dbo].[MDT_Customer_Order_Debit_Date_Infos](
[OrderDebit_SeqID] [bigint] IDENTITY(1,1) NOT NULL,
[Date_ID] [int] NOT NULL,
[Dist_Zone_Code] [int] NULL,
[Branch_Code] [int] NULL,
[Dist_Area_Code] [int] NULL,
[Dist_Path_Code] [int] NULL,
[Sale_Zone_Code] [int] NULL,
[Region_Code] [int] NULL,
[Office_Code] [int] NULL,
[Area_Code] [int] NULL,
[Path_Code] [int] NULL,
[Visitor_Code] [int] NULL,
[Customer_Code] [int] NULL,
[Customer_Master_Code] [varchar](12) NULL,
[Order_Office_Code] [int] NOT NULL,
[Order_No] [varchar](10) NOT NULL,
[Factor_No] [varchar](10) NULL,
[Order_Status] [tinyint] NULL,
[Sub_Order_Type] [int] NULL,
[Payment_Type] [tinyint] NULL,
[Order_Type] [tinyint] NULL,
[Return_Reason_Code] [int] NULL,
[Order_Price] [numeric](18, 0) NULL,
[PromotionActual_Price] [numeric](18, 0) NULL,
[Order_DutyPlusTax] [numeric](18, 0) NULL,
[ReturnPromotionActual_Price] [numeric](18, 0) NULL,
[Return_Discount] [numeric](18, 0) NULL,
[PromotionDiscount_Price] [numeric](18, 0) NULL,
[Discount] [numeric](18, 0) NULL,
[Calc_Discount] [numeric](18, 0) NULL,
[ReturnOrder_Price] [numeric](18, 0) NULL,
[Return_DutyPlusTax] [numeric](18, 0) NULL,
[Cash_AvgAge] [numeric](18, 2) NULL,
[Check_AvgAge] [numeric](18, 2) NULL,
[Indirect_AvgAge] [numeric](18, 2) NULL,
[Money_Amount] [numeric](18, 0) NULL,
[Indirect_Amount] [numeric](18, 0) NULL,
[Check_Amount] [numeric](18, 0) NULL,
[ReturnCheck_Amount] [numeric](18, 0) NULL,
[RetChqSettle_Cash] [numeric](18, 0) NULL,
[RetChqSettle_Check] [numeric](18, 0) NULL,
[RemainCheck_Debit] [numeric](18, 0) NULL,
[Order_Payable] [numeric](18, 0) NULL,
[Order_Debit] [numeric](18, 0) NULL,
[OrderRemain_ReturnCheck] [numeric](18, 0) NULL,
[RealRemain_Debit] [numeric](18, 0) NULL,
[RealRemain_Check] [numeric](18, 0) NULL,
[Debit_Age] [numeric](18, 2) NULL,
[Payment_Age] [numeric](18, 2) NULL,
[Factor_Date] [int] NULL,
[Order_Date] [int] NULL,
[Visit_Date] [int] NULL,
[Give_Date] [int] NULL,
[Office_Key] [int] NULL,
[Customer_Key] [int] NULL,
[During_Check_Amount] [numeric](18, 0) NULL,
[Collected_Check_Amount] [numeric](18, 0) NULL,
[Dist_Branch_Key] [int] NULL,
[AllCheck_AvgAge] [numeric](18, 2) NULL,
[Cheque_Age] [int] NULL,
[Sale_Remain_Debit] [numeric](18, 0) NULL,
[Dist_Remain_Debit] [numeric](18, 0) NULL,
[HandMoney_Amount] [numeric](18, 0) NULL,
[AllCheck_Amount] [numeric](18, 0) NULL,
[Indirect_Discount_Amount] [numeric](18, 0) NULL,
[PosMoney_Amount] [numeric](18, 0) NULL,
[Portfolio_Code] [int] NULL,
[Portfolio_Set_Code] [int] NULL,
[Portfolio_Type] [tinyint] NULL,
[Company_id] [smallint] NULL,
CONSTRAINT [PK_MDT_Customer_Order_Debit_Date_Infos_Tmp] PRIMARY KEY CLUSTERED
(
[OrderDebit_SeqID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_Customer_Key] ON [dbo].[MDT_Customer_Order_Debit_Date_Infos]
(
[Customer_Key] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO
/****** Object: Index [IX_CustomerCode] Script Date: 1/20/2014 6:02:52 PM ******/
CREATE NONCLUSTERED INDEX [IX_CustomerCode] ON [dbo].[MDT_Customer_Order_Debit_Date_Infos]
(
[Customer_Code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO
/****** Object: Index [Ix_Date] Script Date: 1/20/2014 6:02:59 PM ******/
CREATE NONCLUSTERED INDEX [Ix_Date] ON [dbo].[MDT_Customer_Order_Debit_Date_Infos]
(
[Date_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO
/****** Object: Index [PK_MDT_Customer_Order_Debit_Date_Infos_Tmp] Script Date: 1/20/2014 6:03:07 PM ******/
ALTER TABLE [dbo].[MDT_Customer_Order_Debit_Date_Infos] ADD CONSTRAINT [PK_MDT_Customer_Order_Debit_Date_Infos_Tmp] PRIMARY KEY CLUSTERED
(
[OrderDebit_SeqID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO
非聚集索引很可能与基表具有不同的顺序，因此对该列的插入/更新将使索引碎片化。
FILLFACTOR（和PAD_INDEX）设置仅在重建索引时应用，而不是在正常操作期间应用。我的建议是在加载新数据之前删除表中的所有非聚集索引，然后在处理完会影响它们的数据后重新创建索引。
在您的案例中，碎片化是否是一个问题值得商榷。虽然表相当宽，但如果您需要从磁盘扫描该索引，则消除非聚集索引的碎片只是一个问题，如果索引已经在内存中，则不会发生这种情况。与表相比，非聚集索引非常小。您需要在您的系统上弄清楚重建索引之前/之后查询性能是否存在可衡量的差异，但正如我上面提到的，您可能会更进一步删除/重新创建索引，这将消除碎片，并且让你用个100%
FILLFACTOR。当所有这些因素都成立时，您只需要担心碎片化：