August 11, 2008 at 12:22 pm
Hi Everyone,
I've got a some questions about SQL fragmentation that I hope you can help sort out.
I've got a large, heavily-used SQL 2005 DB that the vendor recommended daily index reorganizations and weekly index rebuilds for.
This hasn't been a problem when the database was small, but at its present size, the reorg takes hours and creates a huge log file. The rebuild does the same, but I have the option to sort in tempdb, which I will set.
I found an MSDN script that I am evaluating which checks the fragmentation level and will reorg/rebuild based on set percentage levels, which should make for a faster job with a lighter impact on the trans log file.
However, I have noticed that some of the indexes that showed as having fragmentation are not always "defragged" after reorganizing the index. The vendor DBA told me this is because the table itself is fragmented, so the index is reflecting the table fragmentation, and I need to defrag the table.
I talked to an MS SQL Server support guy, and he told me that datafiles do not need to be defragged, that the index will point to the disk location where the information is stored, so fragmentation does not matter.
I compared the results of a DMV query for fragmentation for a specific index against the info generated by the deprecated DBCC showcontig and found that the index reported 18.1111 percent fragmentation before and after a reorganization and a rebuild, but the showcontig showed:
TABLE level scan performed.
- Pages Scanned................................: 472
- Extents Scanned..............................: 59
- Extent Switches..............................: 58
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 100.00% [59:59]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 6.78%
- Avg. Bytes Free per Page.....................: 1187.1
- Avg. Page Density (full).....................: 85.33%
I don't see anything in the table stats that indicate 18.1111% fragmentation in the data, so I'm not sure how to correlate the two findings.
Considering how much data is written to the database each day, it may be unrealistic to think I'll ever see a query return 0% avg fragmentation for all indexes.
Do SQL datafiles need to be defragmented at regular intervals? If a reorg/rebuild does not reduce the fragmentation level, what does that indicate?
Thanks in advance for your tips and advice.
August 11, 2008 at 12:59 pm
However, I have noticed that some of the indexes that showed as having fragmentation are not always "defragged" after reorganizing the index. The vendor DBA told me this is because the table itself is fragmented, so the index is reflecting the table fragmentation, and I need to defrag the table.
Um, no. Index fragmentation as shown by ShowContig always shows the fragmentation for that index, not affected by anything else. Smaller tables do often show non-zero fragmentation after a defrag. If the table is under 100 pages (as shown by showcontig or sys.dm_db_index_physical_stats) don't worry too much.
If the table is a heap (index 0) ie there is no clustered index, then it cannot be defragmented as there is no index. If there is a clustered index (index 1) then that is the table (there's no separate location for the table) and if can be defraged.
I talked to an MS SQL Server support guy, and he told me that datafiles do not need to be defragged, that the index will point to the disk location where the information is stored, so fragmentation does not matter.
There's a difference between internal fragmentation (out of order index pages) which the DBCCs fix and external (file-level) fragmentation which I hope is what the MS support person was talking about.
Considering how much data is written to the database each day, it may be unrealistic to think I'll ever see a query return 0% avg fragmentation for all indexes.
Highly un likely. You can minimise fragmentation with well-chosen index keys (specifically the clustered index here), but I have never seen 0% for all indexes. If all indexes are below 20% fragmentation I'd be very happy.
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
August 11, 2008 at 1:34 pm
On small tables, SQL will sometimes not even bother doing anything if you tell it to defrag the index, because the fragmentation doesn't matter. On really small tables (I think the threshold is 200 rows, but I could be misremembering that), it generally won't even use indexes, much less defrag them.
The particular table you checked, of course, is large enough for fragmentation to matter and for index to matter, based on the pages count you posted. In that case, I'm going to say that the index defrag probably ran into lock contention issues, and had to leave some rows alone.
- 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 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply