Sample Header Ad - 728x90

What is fragmenting my index on a table with stable traffic?

1 vote
1 answer
421 views
I am using Ola Hallengren's solution for optimizing indexes. I run it on Sunday every week. The index has had low fragmentation for the last 6 months and it hasn't needed reorganization. The way it's being used hasn't changed either. For the last three weeks every time it runs it reports that the clustered index on the database's biggest table is getting between 5-12% fragmented. Strangely enough this is not occurring during weekdays, so it must be there sometime before the maintenance job runs. My table has a timestamp on every row so I know that the traffic has stayed on the same level for months. Usually it's under 1% fragmentation per month. I have two questions: 1. What else could be fragmenting my index? 2. Is there an automated solution I can set up to track changes? Ideally something not disruptive since this is a production box. The table has 134 columns contains 14gb worth of data, and the primary key is not an identity (*sigh*) The index in question looks like this: * Average row size: 1413 * Depth: 4 * Leaf-level rows: 9884500 * Maximum row size: 2303 * Minimum row size: 746 * Pages: 1904907 * Partition ID: 1 CREATE UNIQUE CLUSTERED INDEX [FOOINDEX] ON [dbo].[FOOTABLE] ( [FOONO] ASC, [ID] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
Asked by Nick Patsaris (113 rep)
Oct 25, 2014, 01:46 PM
Last activity: Jan 24, 2023, 12:08 PM