May 6, 2011 at 9:51 am
First let me say I am not 100% sure I should ask this question on a SQL forum or a SharePoint Forum. We have a SharePoint site that the databases are running on an instance of SQL 2008 R2. We have noticed that even after rebuilding the indexes using a maintenance plan that some show 50% + fragmentation. This of course makes no sense since rebuilding the indexes should eliminate fragmentation. My believe with my experience is that this is more in line with corrupt data in the tables than an issue with the indexes. My question is what utilities can I use to hunt down the corruption in the tables? This DB is over 180 GB so these tables aren't small (at least not when talking about SharePoint).
Another question I have is that if an index has a foreign key do you need to rebuild the foreign index also to completely eliminate the fragmentation in the fragmented index?
May 6, 2011 at 10:07 am
Rebuild of the index assuming a good amount of page should eliminate it (maybe not 0%, but certainly not 99% left).
Run this to get all the corruptions errors in the db (will take a while on a 200 GB DB).
DBCC CHECKDB('DB-Name') WITH NO_INFOMSGS, ALL_ERRORMSGS
May 6, 2011 at 1:59 pm
For small indexes you may not be able to remove fragmentation because they might be allocated on mixed extents. How many pages in each of the indexes that are still showing 50% or more fragmentation?
To answer your question about foreign keys. A foreign key is NOT an index. It is fairly normal for a column that is part of a foreign key to have an index built on it to help improve join performance, but that index would be rebuilt as part of your regular index maintenance, it does not require a separate step.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 6, 2011 at 2:19 pm
Good question so I checked. Each Clustered Index that still showed fragmentation has either 6 pages or less. Most had either 2 or 3 pages only. I included an example
May 6, 2011 at 2:31 pm
That means that they are all on mixed extents so you aren't like to get the fragmentation reduced because SQL Server isn't going to use uniform extents until the indexes can fill an extent.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 6, 2011 at 2:33 pm
JoeS 3024 (5/6/2011)
his of course makes no sense since rebuilding the indexes should eliminate fragmentation. My believe with my experience is that this is more in line with corrupt data in the tables than an issue with the indexes.
Corruption will not cause indexes to remain fragmented. Corruption causes severity 24 errors when the damaged pages are accessed in any way.
Either the indexes are too small to benefit from rebuilds or there's a shrink operation somewhere (manual or auto) that's re-fragmenting them.
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 6, 2011 at 2:43 pm
Thank you both as now I have learned something new. Have been wasting time thinking it was corruption when it wasn't all along. I'll have to check about the shrink as others also work on this but I have read that shrinking a database is not the best idea.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply