January 13, 2011 at 1:42 pm
I havefew indexes in the database that are being fragmenmted on a daily basis. Though rebuilding indexes is happenning on a daily basis, i do notice that they are being fragmented.
Here are my observations:
These are nonclustered Indexes. These index has around 4 columns. Currently there is no fill factor.
What can i do so that the fragmentation will not happen on a daily basis? Appreciate your input on this.
Satish
January 13, 2011 at 1:45 pm
Are you doing range scans on the indexes, or just seeks? (There are DMVs that tell you that.) If it's just seeks, then fragmentation won't matter much.
- 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
January 13, 2011 at 1:47 pm
I do not know whether the application is doing range match or seeks. Do you remember the DMV name by chance?
January 13, 2011 at 1:49 pm
Is autoshrink on? Are you shrinking the DB manually?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 13, 2011 at 1:53 pm
I do not have AutoShrink on. However, i have performed ShrinDB once. What happened is we migrated 8 years of data from some other system to our database. I then took backup and purged 6 years of data. After that, I had to perform ShrinkDB in order to reduce the physical data file sizes.
Do you see any pattern here? Thanks for the help.
January 13, 2011 at 2:40 pm
If the fill factor on the indexes is so high that there is no room to insert new rows without splitting the page you can get a lot of fragmentation from inserts or updates to the index columns.
You might be able to reduce the fragmentation by lowering the fill factor of the indexes.
January 13, 2011 at 3:08 pm
There is no fillfactor specified. So, I assume there is no room. Do you suggest fillfactor value of 80 or so?
January 14, 2011 at 10:24 am
You can see the fillfactor with this query. If it is 0 or 100, that means to completely fill the pages.
You can try reducing the fill factor by 10 percent until you get to the point where it does not fragment a lot in a short period of time.
select
a.name as table_name,
b.name as index_name,
b.fill_factor
from
sys.tables a
join
sys.indexes b
on a.object_id = b.object_id
where
b.type_desc in ('CLUSTERED','NONCLUSTERED')
order by
a.name,
b.name
From SQL Server 2008 Books Online:
"CREATE INDEX (Transact-SQL)
...
FILLFACTOR = fillfactor
Specifies a percentage that indicates how full the Database Engine should make the leaf level of each index page during index creation or rebuild. fillfactor must be an integer value from 1 to 100. The default is 0. If fillfactor is 100 or 0, the Database Engine creates indexes with leaf pages filled to capacity.
Note:
Fill factor values 0 and 100 are the same in all respects.
The FILLFACTOR setting applies only when the index is created or rebuilt. The Database Engine does not dynamically keep the specified percentage of empty space in the pages. To view the fill factor setting, use the sys.indexes catalog view.
Creating a clustered index with a FILLFACTOR less than 100 affects the amount of storage space the data occupies because the Database Engine redistributes the data when it creates the clustered index."
January 14, 2011 at 12:08 pm
Thanks for the information. I will start playing with FillFactor value for those indexes which are being fragmented frequently and see how it goes.
January 14, 2011 at 3:07 pm
I read following link to fix the fragmentation. This should help you
MS SQL SERVER 2008 Fix Fragmented Index
-anu
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply