April 25, 2006 at 5:05 am
Hi
I'm curious as to why dbcc dbreindex will not improve scan density. The table is very small, (approx 300 rows), and the scan density on the clustered index is being reported as 25% before and after dbcc dbreindex.
This isn't causing me a production issue, I'm just curious!
Thanks
Andy
April 25, 2006 at 5:21 am
think it's something to do with small tables that fit in a page or two. I spent ages trying to get one to improve from 50% and decided it was a waste of time !! see if the table fits one or two pages.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
April 26, 2006 at 2:22 am
the table occupies 4 pages. Is it coincidence that scan density is being reported as 25%? Did your table fit into 2 pages and report 50%?
April 27, 2006 at 6:24 am
could well have done, if I get time I'll run some checks - I generally exclude small tables from reindexing.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
April 27, 2006 at 6:25 am
sorry, just thought it may be the table has to span an extent to be reported correctly?
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
April 27, 2006 at 11:53 am
For tables less than one extent, 8 pages or 64 kb, improvement does not much matter. Remember SQL Server reads 'extents' and writes 'pages'.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
April 28, 2006 at 2:53 am
And in addition to Rudy's comment for table less then 8 pages there is no defragmentation done!
So you will never see any improvement for tables less then 8 pages
Bye
Gabor
May 4, 2006 at 7:17 am
I have a note in my selective index rebuild routine that states that for a clustered index rebuild a 3 page table did nothing but a 7 page table did. That said I agree with Rudy about the read ahead, but it always looks better to remove fragmentation , even if it doesn't really do anything else. !!
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
May 4, 2006 at 9:54 am
Hi All
Thanks for your responses. Very useful
Andy
January 15, 2008 at 8:12 am
I have run dbcc dbreindex on one table in sql server 2000 but I can not see any improvement on page dencity.the page size of that table is 36,8 extent.
Is it expected behaviour.
January 15, 2008 at 10:19 am
Do your table has a clustered index on?
Because table pages are rearranged only if a clustered index is on that table
Bye
Gabor
January 16, 2008 at 1:45 am
This is MSSQL2000 not 2005.And the table has clusterd index.
January 16, 2008 at 7:59 am
I was talking about MSSQL2000 also.
And if your table has a clustered index on and the size is over 1 extent (8 pages) then the dbcc dbreindex must compact the table.
Could you send us the result of your dbcc dbreindex statement (where it shows the result)
Bye
Gabor
January 16, 2008 at 8:13 am
Defragging tiny tables is a waste of time and effort. I usually set defrag scripts to ignore tables with less than somewhere between 50 and 500 pages, depending on the client's structures and data.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 16, 2008 at 1:46 pm
if the table has a fillfactor this will remain unless you force it to 100%. This may give what appears a poor density. If you get values of 25%, 50%, 75% + sometimes 33% and 67% these are usually small tables which don't rebuild that way.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply