March 30, 2009 at 11:00 am
i know there are many articles on this forum based on dbcc reindex.. i did read couple of them and did a test on my db ..this is what is got:
i ran dbcc showcontig i got :
Pages Scanned................................: 20
- Extents Scanned..............................: 5
- Extent Switches..............................: 4
- Avg. Pages per Extent........................: 4.0
- Scan Density [Best Count:Actual Count].......: 60.00% [3:5]
- Logical Scan Fragmentation ..................: 10.00%
- Extent Scan Fragmentation ...................: 40.00%
- Avg. Bytes Free per Page.....................: 146.5
- Avg. Page Density (full).....................: 98.19%
DBCC SHOWCONTIG scanning 'Products' table...
after running EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"..then running dbcc showcontig..i got the following :
- Pages Scanned................................: 25
- Extents Scanned..............................: 6
- Extent Switches..............................: 9
- Avg. Pages per Extent........................: 4.2
- Scan Density [Best Count:Actual Count].......: 40.00% [4:10]
- Logical Scan Fragmentation ..................: 28.00%
- Extent Scan Fragmentation ...................: 33.33%
- Avg. Bytes Free per Page.....................: 1736.4
- Avg. Page Density (full).....................: 78.55%
After rebuilding the indexes i see that the logical fragmentation,xtent fragmentation has increased and avg page density has decreased...i am confused now i thought it would do the opposite after rebuilding the indexes..can any one please say me what parameters should i look for before and after rebuilding indexes and how much should it be..thanks in advance..
March 30, 2009 at 12:44 pm
Hi,
Can you run sys.dm_db_index_physical_stats please? and look for avg_fragmentation_in_percent which gives you the fragmentation levels in your table.
It really depends on your system when you should defrag and when you should rebuild them but as a thumb of rule:
<30%- Defrag
>30%-Rebuild
Thanks.
March 30, 2009 at 1:02 pm
thank you krishna for your response..i did run the view ..i see for couple fo tables it is more than 30%..then i ran the following statement:
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
and the i re-ran the view..i still see the fragmentation percentage is more than 30%..any idea what could be the reason..
March 30, 2009 at 1:25 pm
Hi,
Sorry I din't realize this before. Your table is very small 25 pages only and the fragmentation on these tables does not count much only with pages >1000 i guess the fragmentation will hit performance and also the fragmentation levels are not controllable on small tables because the pages of small indexes are stored on mixed extents.
March 30, 2009 at 1:59 pm
so krishna..should i first execute sys.dm_db_index_physical_stats view and the look at the fragmentation level...and if the avg percentage is mroe than 30 % i will rebuild using "EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)""..i know that this only used in 2005 ..in 2000 we use show contig..in that case what should i look for?,,thanks again..
March 30, 2009 at 2:21 pm
iqtedar (3/30/2009)
should i first execute sys.dm_db_index_physical_stats view and the look at the fragmentation level...and if the avg percentage is mroe than 30 %
Yes, but it really depends on your system and you should be able to decide what fragmentation %'age is acceptable in your environment to reorganize/rebuild, what I have told you is a rule of thumb.
i will rebuild using "EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)""..i know that this only used in 2005 ..
No, this is used for 2000. In 2005, you should use ALTER INDEX REBUILD clause to rebuild. Have a look through the forum to find a script.
in 2000 we use show contig..in that case what should i look for?,,thanks again..
[/quote]
I am not so sure what to look at in 2000 but will post you once i found it or if someone can answer it then well and fine
March 30, 2009 at 10:51 pm
In 2000 it's pretty much same thing...
Check the Scan Density [Best Count:Actual Count] to see how much hopping around a scan will have to do; this value should be close to 100% as possible. Again for small indexes you will not be able to archive that. And Logical Scan Fragmentation is the pain counter here indicating the fragmentation at the Leaf level of the index. Same as above just on extent level Extent Scan Fragmentation; both these should be close to 0% as possible.
This is used to indicate the fill factor of the page, Avg. Page Density (full).
Hope that helps a bit 🙂
Mohit.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply