Saturday, February 25, 2012

DBCC DBREINDEX and DBCC INDEXDEFRAG not defragging indexes

I am running DBCC DBREINDEX and DBCC INDEXDEFRAG against
my database tables and they are not defragging any of my
indexes. Why not?Why are you running both? Also, what command exact are you running? And,
does the tables have clustered indexes? And how do you determine that the
fragmentation isn't reduced? And, remember that there is no idea wasting
time with fragmentation unless an index uses at least somewhere between 500
and 1000 pages.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Colby Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:b8bc01c407b2$c7b3d0c0$a101280a@.phx.gbl...
> I am running DBCC DBREINDEX and DBCC INDEXDEFRAG against
> my database tables and they are not defragging any of my
> indexes. Why not?|||Please post a before and after listing of DBCC SHOWCONTIG for one of the
indexes you are concerned about.
Andrew J. Kelly SQL MVP
"Colby Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:b8bc01c407b2$c7b3d0c0$a101280a@.phx.gbl...
> I am running DBCC DBREINDEX and DBCC INDEXDEFRAG against
> my database tables and they are not defragging any of my
> indexes. Why not?|||most likely because the tables in question dont have clustered indexes.
Heaps cannot be defragged.
Greg Jackson
PDX, Oregon|||Looks like the number of pages being used is too low for
th ereindexing to make any difference. Thanks for the
info...
>--Original Message--
>Why are you running both? Also, what command exact are
you running? And,
>does the tables have clustered indexes? And how do you
determine that the
>fragmentation isn't reduced? And, remember that there is
no idea wasting
>time with fragmentation unless an index uses at least
somewhere between 500
>and 1000 pages.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"Colby Jones" <anonymous@.discussions.microsoft.com> wrote
in message
>news:b8bc01c407b2$c7b3d0c0$a101280a@.phx.gbl...
>
>.
>|||The following is from after running DBREINDEX.
DBCC SHOWCONTIG scanning 'PA33203' table...
Table: 'PA33203' (186964984); index ID: 1, database ID: 10
TABLE level scan performed.
- Pages Scanned........................: 361
- Extent Switches.......................: 45
- Scan Density [Best Count:Actual Count]......: 100.00%
[46:46]
- Logical Scan Fragmentation ..............: 0.00%

>--Original Message--
>Please post a before and after listing of DBCC SHOWCONTIG
for one of the
>indexes you are concerned about.
>--
>Andrew J. Kelly SQL MVP
>
>"Colby Jones" <anonymous@.discussions.microsoft.com> wrote
in message
>news:b8bc01c407b2$c7b3d0c0$a101280a@.phx.gbl...
>
>.
>|||That looks perfect! No fragmentation at all!
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
<anonymous@.discussions.microsoft.com> wrote in message
news:bdc301c40847$ec11db70$a401280a@.phx.gbl...
> The following is from after running DBREINDEX.
> DBCC SHOWCONTIG scanning 'PA33203' table...
> Table: 'PA33203' (186964984); index ID: 1, database ID: 10
> TABLE level scan performed.
> - Pages Scanned........................: 361
> - Extent Switches.......................: 45
> - Scan Density [Best Count:Actual Count]......: 100.00%
> [46:46]
> - Logical Scan Fragmentation ..............: 0.00%
>|||As Tibor states the fragmentation is 0 so how much better do you want<g>?
Besides a table this small is hardly worth spending a lot of effort on.
Andrew J. Kelly SQL MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:bdc301c40847$ec11db70$a401280a@.phx.gbl...
> The following is from after running DBREINDEX.
> DBCC SHOWCONTIG scanning 'PA33203' table...
> Table: 'PA33203' (186964984); index ID: 1, database ID: 10
> TABLE level scan performed.
> - Pages Scanned........................: 361
> - Extent Switches.......................: 45
> - Scan Density [Best Count:Actual Count]......: 100.00%
> [46:46]
> - Logical Scan Fragmentation ..............: 0.00%
>
> for one of the
> in message|||OK, looks like I picked a bad example. How about this one?
DBCC SHOWCONTIG scanning 'POP30000' table...
Table: 'POP30000' (4195065); index ID: 0, database ID: 10
TABLE level scan performed.
- Pages Scanned........................: 25
- Extents Scanned.......................: 21
- Extent Switches.......................: 20
- Avg. Pages per Extent..................: 1.2
- Scan Density [Best Count:Actual Count]......: 19.05%
[4:21]
- Extent Scan Fragmentation ...............: 95.24%
- Avg. Bytes Free per Page................: 2410.2
- Avg. Page Density (full)................: 70.22%

>--Original Message--
>As Tibor states the fragmentation is 0 so how much better
do you want<g>?
>Besides a table this small is hardly worth spending a lot
of effort on.
>--
>Andrew J. Kelly SQL MVP
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:bdc301c40847$ec11db70$a401280a@.phx.gbl...
10
SHOWCONTIG
wrote
against
of my
>
>.
>|||And another:
DBCC SHOWCONTIG scanning 'SOP40500' table...
Table: 'SOP40500' (16719112); index ID: 0, database ID: 10
TABLE level scan performed.
- Pages Scanned........................: 161
- Extents Scanned.......................: 86
- Extent Switches.......................: 85
- Avg. Pages per Extent..................: 1.9
- Scan Density [Best Count:Actual Count]......: 24.42%
[21:86]
- Extent Scan Fragmentation ...............: 96.51%
- Avg. Bytes Free per Page................: 1650.9
- Avg. Page Density (full)................: 79.60%

>--Original Message--
>As Tibor states the fragmentation is 0 so how much better
do you want<g>?
>Besides a table this small is hardly worth spending a lot
of effort on.
>--
>Andrew J. Kelly SQL MVP
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:bdc301c40847$ec11db70$a401280a@.phx.gbl...
10
SHOWCONTIG
wrote
against
of my
>
>.
>

No comments:

Post a Comment