May 11, 2011 at 7:09 am
Hi,
The issue I am facing is that Index fragmentation on 15 tables on my database is high from 39%-95%. Even If I rebuild the index the fragmentation drops to 65% from 95% & gets to the same (95%) within an hour or 2. What could be the reason for this. 7 tables on the database have fragmentation % above 80%. Please advise.
Regards,
Nithin
May 11, 2011 at 7:12 am
Do they have more than 1000 pages of data?
What's the leading column of the index? If it's a guid then yes it's normal.
It's also possible if this is some sort of queue table.
May 11, 2011 at 7:25 am
Autoshrink on?
Manual shrink jobs?
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
May 11, 2011 at 7:31 am
No of pages: 1024
May 11, 2011 at 7:34 am
Index definition including the datatypes please.
Is this table really heavy on insert / deletes?
May 11, 2011 at 7:36 am
No. Auto shrink is off & no shrinks applied as of now.
There is a correction: I had mentioned 18 tables. It should be 18 Indexes. and the Indexes are spread across 3 tables. One table alone accounting for 12 indexes.
May 11, 2011 at 7:38 am
kr.nithin (5/11/2011)
No. Auto shrink is off & no shrinks applied as of now.There is a correction: I had mentioned 18 tables. It should be 18 Indexes. and the Indexes are spread across 3 tables. One table alone accounting for 12 indexes.
Ok... still waiting on previous question but it looks more and more like a queue table.
May 11, 2011 at 7:44 am
All indexes (12 indexes) are non-unique & non-clustered.
10 out of 12 indexes are of Decimal(18,2) & 2 have INT on them.
This table is not heavy on inserts or updates. Total 5000 records.
May 11, 2011 at 7:46 am
Maybe Gail has a better crystal ball that I do, but without seeing the columns, what type of data you keep in there and how the table is used it's really hard to tell you what's wrong.
Can we at least see the table definition with the keys?
May 11, 2011 at 7:53 am
No primary key on the table . 1 foreign key.
May 11, 2011 at 7:54 am
Where are the indexes, and what types?
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
May 11, 2011 at 7:56 am
Without a clustered index, the server doesn't need to maintain any order. So those table will always be fragmented (even if fragment means the order is not respected).
May 11, 2011 at 7:59 am
All the Indexes are non-unique & non-clustered.
May 11, 2011 at 8:02 am
kr.nithin (5/11/2011)
All the Indexes are non-unique & non-clustered.
Like I said, if you don't ask the server to keep then ordered, you can't complain that they are un-ordered
Seriously. Put a clustered index on that table. It certainly needs one.
May 11, 2011 at 8:05 am
Thank you!!!!!!!!!!!!!
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply