September 17, 2009 at 10:14 am
Hi all,
I was reading up on indexes today and came across a DM function, sys.dm_db_index_physical_stats. I wanted to see how it worked, so I chose a table that I knew had been somewhat problematic for us in the past and ran the function.
I looked at the avg_fragmentation_in_percent column and saw that two of the indexes were very high - one at 100% and one at 60%. One index contains 4 columns and the other contains 9 columns.
My question is this: Does anyone know what would cause so much fragmenting to occur? This table gets reindexed twice daily, and there are only 71,255 records in the table. It seems like the indexes are getting fragmented awfully quickly. In fact, the index that was at 60% only an hour ago is now at 83%. Any input would be appreciated.
Thanks!
September 17, 2009 at 10:17 am
Smaller tables often cannot be completely defragmented.
Here's another thread with some info:
http://qa.sqlservercentral.com/Forums/Topic710123-360-1.aspx
September 17, 2009 at 11:10 am
What is your frag % immediately after a defrag? As suggested in the other thread, how many pages do you have in these indexes? What is your fillfactor? And do you have a clustered index?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 17, 2009 at 11:21 am
Indexes on some types of columns in highly transactional tables can often fragment very quickly.
There are a few possible solutions, depending on how the indexes are being used, and what kind of transactions are being done on the data.
If, for example, the leading edge (first column) in an index is a column that gets updated frequently, fragmentation can be very, very rapid. One possible solution for that is to make the leading edge a column that doesn't get updated as often. The viability of that solution depends on the queries the index is supporting, of course. Another is to partition the table, if the majority of the rows are likely to remain static after a certain period of time, or variations on that. That solution depends on what percentage of the table is volatile. Other solutions have similar caveats.
I'd have to know considerably more about your database to give more specific advice, but that's the general gist of where to start, is looking at the use pattern for the data and the index.
- 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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply