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 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|||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:
> > [...]
> > 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 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? Can
> 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 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: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 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: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 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|||> 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:
> > "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?
Can
> > 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
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: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
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: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
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...
> > 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
> 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: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
> > 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.
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: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 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?|||> 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/prodtechnol/sql/2000/maintain/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: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
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/prodtechnol/sql/2000/maintain/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:
> > "Jonathan Levine" <myfoo@.nospam.nospam> wrote in message
> > news:70809313-9024-428C-BD37-3D035CB44453@.microsoft.com...
> > > 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
> > 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: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
> > > 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.
> 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: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
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|||Paul,
Thanks for your quick reply.
"Paul S Randal [MS]" wrote:
> Why do you consider Scan Density so important? What's the access pattern
> over this index?
Sorry if I wasn't clear. I don't know whether Scan Density is important or not. The KB articles are great about Logical Fragmentation, but they don't really seem to address Scan Density. I just observed that (a) it was low and (b) INDEXDEFRAG didn't seem to reduce the total number of extents as effectively as DBREINDEX. I don't know if the total number of pages is important, but I wonder how the fragmentation can be low and the average page density high when there seem to be a significant difference in the total number of extents... is the reason that DBREINDEX tends to group pages together better physically? And is that important?
> 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.
Yes, the query performance seems to be inversely proportional to the fragmentation of the indexes. I don't have stats on individual queries, but the process which does the queries has become considerably faster after instituting nightly defragmentations. The insert process has not; however, I'm hoping that tuning the FILLFACTOR will help that.
-- J|||You shouldn't worry about Scan Density or Extent Scan Fragmentation. The
main cause of query slowdown is ineffective readahead during range scans
caused by Logical Scan Fragmentation. Extent Scan Fragmentation has only
minimal effect on readahead and so we're de-emphasizing its importance.
--
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:3D9896FF-FD3A-4DF3-8F14-610E7C856F7F@.microsoft.com...
> Paul,
> Thanks for your quick reply.
> "Paul S Randal [MS]" wrote:
> > Why do you consider Scan Density so important? What's the access pattern
> > over this index?
> Sorry if I wasn't clear. I don't know whether Scan Density is important
or not. The KB articles are great about Logical Fragmentation, but they
don't really seem to address Scan Density. I just observed that (a) it was
low and (b) INDEXDEFRAG didn't seem to reduce the total number of extents as
effectively as DBREINDEX. I don't know if the total number of pages is
important, but I wonder how the fragmentation can be low and the average
page density high when there seem to be a significant difference in the
total number of extents... is the reason that DBREINDEX tends to group
pages together better physically? And is that important?
> > 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.
> Yes, the query performance seems to be inversely proportional to the
fragmentation of the indexes. I don't have stats on individual queries, but
the process which does the queries has become considerably faster after
instituting nightly defragmentations. The insert process has not; however,
I'm hoping that tuning the FILLFACTOR will help that.
> -- J