Help with Logical Fragmentation

I am having a look at the performance issues on a clients Website analysis
tool database which currently stands at 440 GB and as a result have not run
the optimisation or integrity checking parts of the maintenance plan for a
long time. When I run DBCC SHOWCONTIG (finland_Visits) WITH FAST,
TABLERESULTS, ALL_INDEXES, NO_INFOMSGS, one of the results returned has a
blank Indexname and also reports a 99.993% Logical Fragmentation. I presume
that this is what is hampering the query perfomance. I ran DBCC DBREINDEX
(finland_Visits, '', 0) which reduced the fragmentation of the 4 indexes on
the table but did not touch the first row. So what I want to know is A) What
this first row represents and check that it should be as low as possible and
B) what command will reduced it.
Thanks in Advance RussellThis is on a SQL Server 2000 SP3a server.
"Russell" wrote:
> I am having a look at the performance issues on a clients Website analysis
> tool database which currently stands at 440 GB and as a result have not run
> the optimisation or integrity checking parts of the maintenance plan for a
> long time. When I run DBCC SHOWCONTIG (finland_Visits) WITH FAST,
> TABLERESULTS, ALL_INDEXES, NO_INFOMSGS, one of the results returned has a
> blank Indexname and also reports a 99.993% Logical Fragmentation. I presume
> that this is what is hampering the query perfomance. I ran DBCC DBREINDEX
> (finland_Visits, '', 0) which reduced the fragmentation of the 4 indexes on
> the table but did not touch the first row. So what I want to know is A) What
> this first row represents and check that it should be as low as possible and
> B) what command will reduced it.
> Thanks in Advance Russell|||The largest table with issue is globalvb_Visits and when I run DBCC
SHOWCONTIG (globalvb_Visits) WITH FAST, TABLERESULTS, NO_INFOMSGS. The
output I get is listed below.
ObjectId
1382950102
IndexName
IndexId
0
Level
0
Pages
547631
Rows
27929035
MinimumRecordSize
149
MaximumRecordSize
149
AverageRecordSize
149
ForwardedRecords
0
Extents
68465
ExtentSwitches
68464
AverageFreeBytes
395.0390015
AveragePageDensity
95.11936188
ScanDensity
99.9839334
BestCount
68454
ActualCount
68465
LogicalFragmentation
99.99981689
ExtentFragmentation
27.22558975|||From the results ...it looks globalvb_Visits has no indexes?
The scandensity looks fine.
That should be between the 75 and 100%.
"Russell" wrote:
> The largest table with issue is globalvb_Visits and when I run DBCC
> SHOWCONTIG (globalvb_Visits) WITH FAST, TABLERESULTS, NO_INFOMSGS. The
> output I get is listed below.
> ObjectId
> 1382950102
> IndexName
> IndexId
> 0
> Level
> 0
> Pages
> 547631
> Rows
> 27929035
> MinimumRecordSize
> 149
> MaximumRecordSize
> 149
> AverageRecordSize
> 149
> ForwardedRecords
> 0
> Extents
> 68465
> ExtentSwitches
> 68464
> AverageFreeBytes
> 395.0390015
> AveragePageDensity
> 95.11936188
> ScanDensity
> 99.9839334
> BestCount
> 68454
> ActualCount
> 68465
> LogicalFragmentation
> 99.99981689
> ExtentFragmentation
> 27.22558975
>|||I would also check out your OS disk file fragmentation. If default settings
for size and growth were used for the initial database, you will have
several hundred thousand 1MB fragments of your data file all over the disk.
I had a client with that problem and a simple defrag resulted in an 18%
throughput improvement!!
--
TheSQLGuru
President
Indicium Resources, Inc.
"Russell" <Russell@.discussions.microsoft.com> wrote in message
news:697EE670-697D-4484-A105-9B1D3A9CDCEB@.microsoft.com...
>I am having a look at the performance issues on a clients Website analysis
> tool database which currently stands at 440 GB and as a result have not
> run
> the optimisation or integrity checking parts of the maintenance plan for a
> long time. When I run DBCC SHOWCONTIG (finland_Visits) WITH FAST,
> TABLERESULTS, ALL_INDEXES, NO_INFOMSGS, one of the results returned has a
> blank Indexname and also reports a 99.993% Logical Fragmentation. I
> presume
> that this is what is hampering the query perfomance. I ran DBCC DBREINDEX
> (finland_Visits, '', 0) which reduced the fragmentation of the 4 indexes
> on
> the table but did not touch the first row. So what I want to know is A)
> What
> this first row represents and check that it should be as low as possible
> and
> B) what command will reduced it.
> Thanks in Advance Russell|||I will have a look at the disk fragmentation.
There are 4 indexes on the table and running DBCC DBRENINDEX against the
table reduces their logical fragmentation to 0%.
"TheSQLGuru" wrote:
> I would also check out your OS disk file fragmentation. If default settings
> for size and growth were used for the initial database, you will have
> several hundred thousand 1MB fragments of your data file all over the disk.
> I had a client with that problem and a simple defrag resulted in an 18%
> throughput improvement!!
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Russell" <Russell@.discussions.microsoft.com> wrote in message
> news:697EE670-697D-4484-A105-9B1D3A9CDCEB@.microsoft.com...
> >I am having a look at the performance issues on a clients Website analysis
> > tool database which currently stands at 440 GB and as a result have not
> > run
> > the optimisation or integrity checking parts of the maintenance plan for a
> > long time. When I run DBCC SHOWCONTIG (finland_Visits) WITH FAST,
> > TABLERESULTS, ALL_INDEXES, NO_INFOMSGS, one of the results returned has a
> > blank Indexname and also reports a 99.993% Logical Fragmentation. I
> > presume
> > that this is what is hampering the query perfomance. I ran DBCC DBREINDEX
> > (finland_Visits, '', 0) which reduced the fragmentation of the 4 indexes
> > on
> > the table but did not touch the first row. So what I want to know is A)
> > What
> > this first row represents and check that it should be as low as possible
> > and
> > B) what command will reduced it.
> >
> > Thanks in Advance Russell
>
>|||Hi Russell
IndexID 0 indicates this table is a heap, which by definition has no
organization to it.
The purpose of DBCC INDEXDEFRAG is to make the physical order of pages in an
INDEX match the logical order, but since your table has no logical order,
DBCC INDEXDEFRAG will have no effect.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Russell" <Russell@.discussions.microsoft.com> wrote in message
news:9E83AC0A-1706-46A9-A525-DCD1B8E9B392@.microsoft.com...
> The largest table with issue is globalvb_Visits and when I run DBCC
> SHOWCONTIG (globalvb_Visits) WITH FAST, TABLERESULTS, NO_INFOMSGS. The
> output I get is listed below.
> ObjectId
> 1382950102
> IndexName
> IndexId
> 0
> Level
> 0
> Pages
> 547631
> Rows
> 27929035
> MinimumRecordSize
> 149
> MaximumRecordSize
> 149
> AverageRecordSize
> 149
> ForwardedRecords
> 0
> Extents
> 68465
> ExtentSwitches
> 68464
> AverageFreeBytes
> 395.0390015
> AveragePageDensity
> 95.11936188
> ScanDensity
> 99.9839334
> BestCount
> 68454
> ActualCount
> 68465
> LogicalFragmentation
> 99.99981689
> ExtentFragmentation
> 27.22558975
>