Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

i have table with about 7 milion records.index fragmentation was about 90 .

i try rebuild index for all index. and it works fine but after nightly job fragmentation for one of none cluster index growth to 30 . every night i truncate this table and insert new record to it and i update some column(non cluster index(not column that i mention)). i don't know why fragmentation for that index(IX_Customer_Code) growth fast. the workload on table:

1- every night truncate. 2-insert about 7000,000 record. 3- update Customer_Key(in insert it get Null).

i have problem With 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 

Table Sructure:

    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
share|improve this question
2  
Is fragmentation actually causing a noticeable performance issue, or do you just think trying to maintain a low number looks better? –  Aaron Bertrand Jan 20 at 14:16
    
What does your table look like? What's the definition of the index? What is the typical data modification workload on this table? Until you analyze all of those, showing why you have so much fragmentation will merely be a guess. –  Thomas Stringer Jan 20 at 14:23

2 Answers 2

up vote 3 down vote accepted

The nonclustered index very likely has a different order than the base table, and so inserts/updates to that column will fragment the index. FILLFACTOR (and PAD_INDEX) settings only apply when the index is rebuilt, not during normal operations.

What I would suggest is to drop all the nonclustered indexes on the table before loading the new data, and then recreate the indexes after you're done fiddling with the data that would affect them.

Whether or not the fragmentation is an issue in your case is debatable. While the table is fairly wide, eliminating fragmentation of the nonclustered index is only a concern if you need to scan that index from disk, which won't happen if the index is already in memory. The nonclustered index is very small compared to the table. You would need to figure out on your system if there's a measurable difference in query performance before/after rebuilding the index, but as I mentioned above, you may be further ahead to drop/recreate the index anyway, which would eliminate the fragmentation and also let you use a 100% FILLFACTOR.

share|improve this answer
    
thanks. your solution is good.and i think it work.the only thing i want is an explanation for this behavior maybe it is because the none cluster index is very small compared to table –  abianari Jan 20 at 15:53
    
@abianari: Fragmentation is a difference between logical and physical order, and random data inserts/updates will cause this to occur. Even if the base table is updated in sequential order, the order of the nonclustered index is different, and so the inserts/updates to the nonclustered index are random. This is exacerbated in your case since all the rows start with NULL instead of the index being empty before actual values go in. –  Jon Seigel Jan 20 at 17:03

You only need to concern about fragmentation when all of these factors are true:

  1. The index is large enough but randomly reading the pages (extents) is too slow.
  2. The index will be scanned, The index pages (extents) will be physically read from disk (i.e., they are not already in the buffer pool)
  3. The storage subsystem is poor at handling random reads (i.e., spindle-based, and the bytes won't come out of cache).
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.