December 11, 2006 at 6:22 pm
Hi,
Just wondering if anyone can offer some explaination as to why after I have done an index rebuild my index fragmantation increases again within a couple of hours.
The database is an OLTP db but doesn't have stupidly high numbers of transactions - well not enough that would see me expecting it to go from less than 10% fragmentation to 99.xxx% within a couple of hours.
Any thoughts? FYI this is 2k5 and am using sys.dm_db_index_physical_stats to see fragmentation.
Keen to here others thoughts.
Thanks
Troy
December 12, 2006 at 1:16 pm
How many updates / inserts / deletes are the columns in the index undergoing in that couple of hours?
A.J.
DBA with an attitude
December 12, 2006 at 1:47 pm
What is the size of the table?
What is the value before reindex, after reindex and after one hour...?
Try to check the fragmentation wiht DBCC SHOWCONTIG command...to see is there any difference between dbcc and dmv...
MohammedU
Microsoft SQL Server MVP
December 12, 2006 at 2:08 pm
Hi
Firstly thanks for the replies.
@adam-2 - there is approx 300 or so inserts into the table, which is the only time this index would be altered. It is a PK index on one field. There are no updates and no deletes which would effect this index.
@mohammed - Rows in the table = 272476. The value of the fragmentation went down to something in the 9% range. When I checked it a couple of hours later, I was suprised to see if return back to it's original figure (or thereabouts) in the 99+% range.
DBCC SHOWCONTIG returns similar values:
******************************************
DBCC SHOWCONTIG scanning 'DAILY_JOB' table...
Table: 'DAILY_JOB' (526624919); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 18615
- Extents Scanned..............................: 2433
- Extent Switches..............................: 2697
- Avg. Pages per Extent........................: 7.7
- Scan Density [Best Count:Actual Count].......: 86.25% [2327:2698]
- Logical Scan Fragmentation ..................: 99.87%
- Extent Scan Fragmentation ...................: 25.20%
- Avg. Bytes Free per Page.....................: 1019.0
- Avg. Page Density (full).....................: 87.41%
DBCC execution completed. If DBCC printed error messages, contact your system administrator
December 12, 2006 at 4:55 pm
Scan Density [Best Count:Actual Count].......: 86.25% [2327:2698]
Scan density looks fine to me...
did you DETAIL option when you are running a query to get the fragmentation?
MohammedU
Microsoft SQL Server MVP
December 12, 2006 at 5:51 pm
Hi Mohammed,
No I didn't use DETAIL only LIMITED (Default).
Well that is what I thought - ie re the scan density, as this sounded good, but then looking at the fragmentation I was sortof confused - I would have thought that the fragmentation levels being so high wouldn't have been a good thing.
And possibly more important from my perspective why is a high fragmentation figure being maintained? Could it be table design or something else? Or am I worrying about something I shouldn't be?
Cheers
Troy
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply