July 16, 2008 at 6:31 am
Hi all
Can anyone please help me understand this situation?
I have a table with 4 indexes on it: 1 is the Primary Key and therefore clustered. The other 3 are complex indexes made of 3-5 columns, non-clustered, and they all have the date column in them. Records are inserted into the table in a rate of 1650 records every 2 minutes. I've created the indexes with a fill factor of 50. At the beginning I have seen they were 50% full with an almost 0% fragmentation. After only 3 hours I get a fragmentation level of around 90% for the indexes.
DBCC SHOCONTIG for the table resulted in a high scan density (Around 95%, that's good, right?) but also a very high extent scan fragmentation (close to 95%).
This occurs also when I try to rebuild the indexes when the table has close to 2 Million records stored in it. After rebuild- the index fragmentation level is low, and gets close to 100% after a very short time.
Can anyone point me to possible causes and how to further investigate this issue? (Or maybe supply some explanations if I'm missing something out here)
Thank you
Nili
July 16, 2008 at 7:12 am
I have a table with 4 indexes on it: 1 is the Primary Key and therefore clustered.
The primary key doesn't have to be clustered, and in many cases that's a bad decision... especially if your primary key is a GUID, as inserts will be to random places in the table, likely causing fragmentation in the process.
What is the datatype(s) of your primary key?
July 16, 2008 at 7:21 am
The PK is on an id column. Many (most) queries in our system are id-oriented and therefore it seemed like a good choice. Most of them use the id column in their where clause.
July 16, 2008 at 7:50 am
You might need to set up some sort of table partitioning. If you had today's data in one partition and older data in another (or others), the indexes on the older data wouldn't fragment too badly (though the new would). It would depend on what most of your selects deal with: current or old or both.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 16, 2008 at 8:45 am
Hi Nili
Try giving more fill factor, fill factor affects scan as well, since it has to scan more pages, i would say for 2 million records say 80%, saying that u might also need to consider how the table is been used
Cheers
July 16, 2008 at 9:02 am
1650 records every 2 minutes
with that in mind, it is obvious that you have index fragmentation. and considering your Fillfactor, it is again obvious the page spits are happening Quick. Try Increasing your Fill Factor.. work a bit on the FillFactors and try to reach a optimal value which you think, is a Reasonable value,and doesnt give way to much fragmentation...
Avoid having many indexes on a table that is frequently updated.
To improve performance, minimize the total width of the indexed columns.
Also check if all your covering columns are in your where clause, if not remove them..
I would recommend you RUN a profiler Trace and track the Query Execution. then use the Database Engine Tuning Advisor(DTA) to check for recommendations on INDEX Management.
Maninder
www.dbanation.com
July 16, 2008 at 9:12 am
Thanks a lot for all your great tips. I did try to increase the fillfactor up to 90% but still fragmentation is SO QUICK. I'll try and work with the profiler and find the bottlenecks and try to fix them. I'm aware of the fact that there are many indexes on the table, plus they are combined of many columns. This was done in order to avoid going to the table in the system's main queries but even just the index scans are getting too heavy.
Thanks, keep them coming 🙂
Nili.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply