DBCC INDEXDEFRAG

I have SQL Server 2000 Enterprise Edition on my server.
I first execute DBCC SHOWCONTIG on my database.
Which outputs logicaldefrag, scan density, and etc.
I apply the DBCC INDEXDEFRAG (0, table_id, index_id) to
every table in my database.
Next I apply the sp_updatestats.
Lastly, I execute DBCC SHOWCONTIG to the database.
When I compare the DBCC SHOWCONTIG before and after
results from applying DBCC INDEXDEFRAG. I noticed the
logicalfrag has the same values?
Please help me fix this issue.
Thanks,
MikeWell a couple things comes to mind. First off do these tables all have a
clustered index on them? How many pages in these tables? Can you show an
example of before and after?
Andrew J. Kelly SQL MVP
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:27d5a01c46449$23fbc7f0$a601280a@.phx
.gbl...
> I have SQL Server 2000 Enterprise Edition on my server.
> I first execute DBCC SHOWCONTIG on my database.
> Which outputs logicaldefrag, scan density, and etc.
> I apply the DBCC INDEXDEFRAG (0, table_id, index_id) to
> every table in my database.
> Next I apply the sp_updatestats.
> Lastly, I execute DBCC SHOWCONTIG to the database.
> When I compare the DBCC SHOWCONTIG before and after
> results from applying DBCC INDEXDEFRAG. I noticed the
> logicalfrag has the same values?
> Please help me fix this issue.
> Thanks,
> Mike|||"Mike" wrote:
> [...]
> I first execute DBCC SHOWCONTIG on my database.
> Which outputs logicaldefrag, scan density, and etc.
> I apply the DBCC INDEXDEFRAG (0, table_id, index_id) to
> every table in my database.
> Next I apply the sp_updatestats.
> Lastly, I execute DBCC SHOWCONTIG to the database.
> When I compare the DBCC SHOWCONTIG before and after
> results from applying DBCC INDEXDEFRAG. I noticed the
> logicalfrag has the same values?
>
Mike,
I have noticed the same thing with indexes on large tables. I'm using the s
ample script from Books Online to run a nightly DBCC INDEXDEFRAG on indexes
with higher than a certain level of fragmentation, but I notice that certain
indexes are above the t
hreshold every night. If I run a DBCC DBREINDEX, I have much better results
(the fragmentation usually goes down below 1%).
Normally, I would think it was my own mistake... but the strange thing is th
at many indexes seem to have their fragmentation reduced when running DBCC I
NDEXDEFRAG on them. I'm now considering giving up on INDEXDEFRAG for my dat
a warehouse DBs...
Regards,
Jonathan|||What's the reported number of pages moved when running DBCC INDEXDEFRAG? Can
you post sample DBCC SHOWCONTIG output please?
Thanks
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jonathan Levine" <myfoo@.nospam.nospam> wrote in message
news:9E111CD5-2CD0-4071-B3A9-39E3D5CA4978@.microsoft.com...
> "Mike" wrote:
> Mike,
> I have noticed the same thing with indexes on large tables. I'm using
the sample script from Books Online to run a nightly DBCC INDEXDEFRAG on
indexes with higher than a certain level of fragmentation, but I notice that
certain indexes are above the threshold every night. If I run a DBCC
DBREINDEX, I have much better results (the fragmentation usually goes down
below 1%).
> Normally, I would think it was my own mistake... but the strange thing
is that many indexes seem to have their fragmentation reduced when running
DBCC INDEXDEFRAG on them. I'm now considering giving up on INDEXDEFRAG for
my data warehouse DBs...
> Regards,
> Jonathan|||Paul,
"Paul S Randal [MS]" wrote:
> "Jonathan Levine" <myfoo@.nospam.nospam> wrote in message
> news:9E111CD5-2CD0-4071-B3A9-39E3D5CA4978@.microsoft.com...
> What's the reported number of pages moved when running DBCC INDEXDEFRAG? C
an
> you post sample DBCC SHOWCONTIG output please?
Thanks for your reply.
I'm still looking into this myself, and it's a bit more complicated than I o
riginally reported.
I have a data warehouse that's updated early in the morning. The larger tab
les are in the order of 5 to 25 million rows. The updates vary in size from
100,000 to 2 million rows.
Before the update, I run INDEXDEFRAG on fragmented indexes. Then I do the u
pdate, and it's after the update that the index appears to be refragmented.
So, for example, on a 26 million row table:
Before Defrag:
DBCC SHOWCONTIG scanning 'Historical_Rankings' table...
Table: 'Historical_Rankings' (2018106230); index ID: 14, database ID: 8
LEAF level scan performed.
- Pages Scanned........................: 121895
- Extents Scanned.......................: 15316
- Extent Switches.......................: 88061
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 17.30% [15237:88062
]
- Logical Scan Fragmentation ..............: 25.04%
- Extent Scan Fragmentation ...............: 44.72%
- Avg. Bytes Free per Page................: 3012.7
- Avg. Page Density (full)................: 62.78%
INDEXDEFRAG scanned 121891, moved 76889, removed 44996
After defrag:
Table: 'Historical_Rankings' (2018106230); index ID: 14, database ID: 8
LEAF level scan performed.
- Pages Scanned........................: 76899
- Extents Scanned.......................: 9670
- Extent Switches.......................: 37754
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 25.46% [9613:37755]
- Logical Scan Fragmentation ..............: 0.01%
- Extent Scan Fragmentation ...............: 61.40%
- Avg. Bytes Free per Page................: 38.3
- Avg. Page Density (full)................: 99.53%
DBCC execution completed. If DBCC printed error messages, contact your syste
m administrator.
After inserting 270,000 rows:
DBCC SHOWCONTIG scanning 'Historical_Rankings' table...
Table: 'Historical_Rankings' (2018106230); index ID: 14, database ID: 8
LEAF level scan performed.
- Pages Scanned........................: 123077
- Extents Scanned.......................: 15464
- Extent Switches.......................: 88745
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 17.34% [15385:88746
]
- Logical Scan Fragmentation ..............: 26.34%
- Extent Scan Fragmentation ...............: 65.66%
- Avg. Bytes Free per Page................: 3009.3
- Avg. Page Density (full)................: 62.82%
So... it's very strange to me that, after inserting about 1% new rows, and n
ot deleting or updating any rows, the fragmentation could go from .01% to 26
.34%.
I haven't had a chance to fully document this yet, but it seems to me that D
BREINDEX results in a substantially better Scan Density, and that subsequent
inserts do not result in such extreme changes in Logical Scan Fragmentation
. If you think it's helpfu
l, I'll pursue that.
Regards,
Jonathan|||> So... it's very strange to me that, after inserting about 1% new rows, and
not deleting or updating any rows, the fragmentation >could go from .01% to
26.34%.
This isn't strange at all since your pages are basically 100% full before
the inserts. That means that almost any insert will cause a page split and
the results of the showcontig shows that there was a lot of it. If your
going to do a lot of inserts you should consider setting an appropriate fill
factor and possibly Padindex as well. Leaving more free space on the page
will mean more pages in the database but will result in less pagesplits and
lots less work during the inserts.
Andrew J. Kelly SQL MVP
"Jonathan Levine" <myfoo@.nospam.nospam> wrote in message
news:70809313-9024-428C-BD37-3D035CB44453@.microsoft.com...
> Paul,
> "Paul S Randal [MS]" wrote:
Can[vbcol=seagreen]
> Thanks for your reply.
> I'm still looking into this myself, and it's a bit more complicated than I
originally reported.
> I have a data warehouse that's updated early in the morning. The larger
tables are in the order of 5 to 25 million rows. The updates vary in size
from 100,000 to 2 million rows.
> Before the update, I run INDEXDEFRAG on fragmented indexes. Then I do the
update, and it's after the update that the index appears to be refragmented.
So, for example, on a 26 million row table:
> Before Defrag:
> DBCC SHOWCONTIG scanning 'Historical_Rankings' table...
> Table: 'Historical_Rankings' (2018106230); index ID: 14, database ID: 8
> LEAF level scan performed.
> - Pages Scanned........................: 121895
> - Extents Scanned.......................: 15316
> - Extent Switches.......................: 88061
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 17.30% [15237:880
62]
> - Logical Scan Fragmentation ..............: 25.04%
> - Extent Scan Fragmentation ...............: 44.72%
> - Avg. Bytes Free per Page................: 3012.7
> - Avg. Page Density (full)................: 62.78%
> INDEXDEFRAG scanned 121891, moved 76889, removed 44996
> After defrag:
> Table: 'Historical_Rankings' (2018106230); index ID: 14, database ID: 8
> LEAF level scan performed.
> - Pages Scanned........................: 76899
> - Extents Scanned.......................: 9670
> - Extent Switches.......................: 37754
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 25.46% [9613:3775
5]
> - Logical Scan Fragmentation ..............: 0.01%
> - Extent Scan Fragmentation ...............: 61.40%
> - Avg. Bytes Free per Page................: 38.3
> - Avg. Page Density (full)................: 99.53%
> DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
> After inserting 270,000 rows:
> DBCC SHOWCONTIG scanning 'Historical_Rankings' table...
> Table: 'Historical_Rankings' (2018106230); index ID: 14, database ID: 8
> LEAF level scan performed.
> - Pages Scanned........................: 123077
> - Extents Scanned.......................: 15464
> - Extent Switches.......................: 88745
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 17.34% [15385:887
46]
> - Logical Scan Fragmentation ..............: 26.34%
> - Extent Scan Fragmentation ...............: 65.66%
> - Avg. Bytes Free per Page................: 3009.3
> - Avg. Page Density (full)................: 62.82%
> So... it's very strange to me that, after inserting about 1% new rows, and
not deleting or updating any rows, the fragmentation could go from .01% to
26.34%.
> I haven't had a chance to fully document this yet, but it seems to me that
DBREINDEX results in a substantially better Scan Density, and that
subsequent inserts do not result in such extreme changes in Logical Scan
Fragmentation. If you think it's helpful, I'll pursue that.
> Regards,
> Jonathan|||"Andrew J. Kelly" wrote:
> "Jonathan Levine" <myfoo@.nospam.nospam> wrote in message
> news:70809313-9024-428C-BD37-3D035CB44453@.microsoft.com...
> system administrator.
> This isn't strange at all since your pages are basically 100% full before
> the inserts. That means that almost any insert will cause a page split an
d
> the results of the showcontig shows that there was a lot of it. If your
> going to do a lot of inserts you should consider setting an appropriate fi
ll
> factor and possibly Padindex as well. Leaving more free space on the page
> will mean more pages in the database but will result in less pagesplits an
d
> lots less work during the inserts.
Hi Andrew.
I guess that 270K inserts seems like a lot in absolute terms, but relative t
o the size of the table, it's pretty small. So I guess I'm surprised that o
ne in 6 inserts would cause a page split when I'm changing the table by only
1%. I'm also suprised tha
t simply splitting pages would cause the fragmentation to increase so dramat
ically.
I had originally created the index with no FILLFACTOR specified. (I thought
this was supposed to default to a FILLFACTOR of 80). On your suggestion, I
tried rebuilding the index with FILLFACTOR explicitly set to 80. Now DBCC
SHOWCONTIG returns:
DBCC SHOWCONTIG scanning 'Historical_Rankings' table...
Table: 'Historical_Rankings' (2018106230); index ID: 14, database ID: 8
LEAF level scan performed.
- Pages Scanned........................: 96535
- Extents Scanned.......................: 12114
- Extent Switches.......................: 12113
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 99.61% [12067:12114
]
- Logical Scan Fragmentation ..............: 0.02%
- Extent Scan Fragmentation ...............: 49.70%
- Avg. Bytes Free per Page................: 1610.7
- Avg. Page Density (full)................: 80.10%
DBCC execution completed. If DBCC printed error messages, contact your syste
m administrator.
Note the great improvement in the index's Scan Density. And I also see this
kind of improvement in Scan Density when I run DBCC DBREINDEX.
So... I'll keep an eye on it for the next few days. I'll also check into wh
ether my other big indexes have similar FILLFACTOR issues. In the meantime,
is Scan Density something that I should be worrying about?|||> Hi Andrew.
> I guess that 270K inserts seems like a lot in absolute terms, but relative
to the size of the table, it's pretty small. So I guess I'm >surprised that
one in 6 inserts would cause a page split when I'm changing the table by
only 1%. I'm also surprised that simply
> splitting pages would cause the fragmentation to increase so dramatically.
>
When a page is that full (or usually even close) just one new row will cause
a page split since there simply isn't enough room for the new data. A
non-clustered index is essentially a table with a clustered index on the
index expression. That means the data at the leaf level have to basically
be in physical order. So when the data coming in is random (meaning the
value of the column(s) the index is based on) it usually gets placed in
between existing rows. If there is not enough room on the page it splits it
and moves half to another page. When you add new rows in index expression
order (such as a datetime that is in chronological order) they can simply be
appended to the end of the current page. When that page is full it creates
a new page and continues appending from there. This is not a page split and
essentially does not change the logical fragmentation since all the new
pages come directly after the previous ones. This KB is great for this type
stuff:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx

> I had originally created the index with no FILLFACTOR specified. (I
thought this was supposed to default to a FILLFACTOR of 80). On your
suggestion, I tried rebuilding the index with FILLFACTOR explicitly set to
80. Now DBCC SHOWCONTIG returns:
> DBCC SHOWCONTIG scanning 'Historical_Rankings' table...
> Table: 'Historical_Rankings' (2018106230); index ID: 14, database ID: 8
> LEAF level scan performed.
> - Pages Scanned........................: 96535
> - Extents Scanned.......................: 12114
> - Extent Switches.......................: 12113
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.61% [12067:121
14]
> - Logical Scan Fragmentation ..............: 0.02%
> - Extent Scan Fragmentation ...............: 49.70%
> - Avg. Bytes Free per Page................: 1610.7
> - Avg. Page Density (full)................: 80.10%
> DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
> Note the great improvement in the index's Scan Density. And I also see
this kind of improvement in Scan Density when I run DBCC DBREINDEX.
> So... I'll keep an eye on it for the next few days. I'll also check into
whether my other big indexes have similar FILLFACTOR issues. In the
meantime, is Scan Density something that I should be worrying about?
Logical fragmentation is the most important factor that you should be
worried about. Especially if you have more than 1 file in the filegroup. In
that case Logical is the only one that you can really trust anyway. See the
KB I mentioned.
Andrew J. Kelly SQL MVP|||Andrew's explanations are spot on.
Why do you consider Scan Density so important? What's the access pattern
over this index?
Do you notice query performance dropping after the insert activity? It's
entirely possible that everything you're doing is a waste of time - akin to
continually shrinking a database after it grows every day. Please read the
whitepaper below which explains a bunch about when to take action on
fragmentation.
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Thanks and regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jonathan Levine" <myfoo@.nospam.nospam> wrote in message
news:05DA17D7-942D-452A-8973-ACCB2DB7A98C@.microsoft.com...
> "Andrew J. Kelly" wrote:
8[vbcol=seagreen]
8[vbcol=seagreen]
and[vbcol=seagreen]
.01% to[vbcol=seagreen]
before[vbcol=seagreen]
and[vbcol=seagreen]
fill[vbcol=seagreen]
page[vbcol=seagreen]
and[vbcol=seagreen]
> Hi Andrew.
> I guess that 270K inserts seems like a lot in absolute terms, but relative
to the size of the table, it's pretty small. So I guess I'm surprised that
one in 6 inserts would cause a page split when I'm changing the table by
only 1%. I'm also suprised that simply splitting pages would cause the
fragmentation to increase so dramatically.
> I had originally created the index with no FILLFACTOR specified. (I
thought this was supposed to default to a FILLFACTOR of 80). On your
suggestion, I tried rebuilding the index with FILLFACTOR explicitly set to
80. Now DBCC SHOWCONTIG returns:
> DBCC SHOWCONTIG scanning 'Historical_Rankings' table...
> Table: 'Historical_Rankings' (2018106230); index ID: 14, database ID: 8
> LEAF level scan performed.
> - Pages Scanned........................: 96535
> - Extents Scanned.......................: 12114
> - Extent Switches.......................: 12113
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.61% [12067:121
14]
> - Logical Scan Fragmentation ..............: 0.02%
> - Extent Scan Fragmentation ...............: 49.70%
> - Avg. Bytes Free per Page................: 1610.7
> - Avg. Page Density (full)................: 80.10%
> DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
> Note the great improvement in the index's Scan Density. And I also see
this kind of improvement in Scan Density when I run DBCC DBREINDEX.
> So... I'll keep an eye on it for the next few days. I'll also check into
whether my other big indexes have similar FILLFACTOR issues. In the
meantime, is Scan Density something that I should be worrying about?
>
>|||Thanks, Andrew. Very helpful stuff.
-- J