Very Computer

I have run a DBCC SHOWCONTIG on the Customer Ledger table and in Production
this is reporting an 88.91 % Extent Scan Fragmentation, however in TEST the
same table only reports a 0.59% frag. I am assuming that this fragmentation
in PROD is greatly contributing to the "slowness" of the statements. I run
Maintenance Plans every weekend for the indexes, but this does not seem to
make any difference, so I am assuming that it is data that is badly
fragmented.

Is the best way to sort this out to copy the data out to a new table and
drop the original and rename it back. Or does anybody else have any
suggestions

Thanks

Simon

To "fix" your database state you can try to deop and
recreate all your tables' clustered indexes, or creata and
drop if you don't have clustered indexes. Your condition
might have been reached because the inserts were ocurring
on tables with no space at the page level to store the new
rows, when this condition exists sql splits the page in
half to make room uterly causing fragmentation.

Hope this helps

Mario

KlK, MCSE

Quote:>-----Original Message-----
>We have a Production database and a Test database. When
statements are run
>in Production they take in excess of 30 hours, however
when run in Test
>(copied from Production the night before) they only take
11 hours.

>I have run a DBCC SHOWCONTIG on the Customer Ledger table
and in Production
>this is reporting an 88.91 % Extent Scan Fragmentation,
however in TEST the
>same table only reports a 0.59% frag. I am assuming that
this fragmentation
>in PROD is greatly contributing to the "slowness" of the
statements. I run
>Maintenance Plans every weekend for the indexes, but this
does not seem to
>make any difference, so I am assuming that it is data
that is badly
>fragmented.

>Is the best way to sort this out to copy the data out to
a new table and
>drop the original and rename it back. Or does anybody
else have any
>suggestions

>Thanks

>Simon

>.

Quote:> I have been researching similiar issue's with UDB DBs on
> NT and found a program on sysinternals.com
> called 'contig'.  It will at least report how many
> fragments your MDF is into, although not how contigous
> they are.

> KlK, MCSE

> >-----Original Message-----
> >We have a Production database and a Test database. When
> statements are run
> >in Production they take in excess of 30 hours, however
> when run in Test
> >(copied from Production the night before) they only take
> 11 hours.

> >I have run a DBCC SHOWCONTIG on the Customer Ledger table
> and in Production
> >this is reporting an 88.91 % Extent Scan Fragmentation,
> however in TEST the
> >same table only reports a 0.59% frag. I am assuming that
> this fragmentation
> >in PROD is greatly contributing to the "slowness" of the
> statements. I run
> >Maintenance Plans every weekend for the indexes, but this
> does not seem to
> >make any difference, so I am assuming that it is data
> that is badly
> >fragmented.

> >Is the best way to sort this out to copy the data out to
> a new table and
> >drop the original and rename it back. Or does anybody
> else have any
> >suggestions

> >Thanks

> >Simon

> >.

Simon

HIH

--------------------
| Content-Class: urn:content-classes:message



| Subject: Data Fragmentation in SQL 7.0
| Date: Mon, 11 Mar 2002 08:37:26 -0800
| Lines: 45

| MIME-Version: 1.0
| Content-Type: text/plain;
|       charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Thread-Index: AcHJGwb2rJMx8Sd7RBilYFnbE9vZcQ==
| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200
| Newsgroups: microsoft.public.sqlserver.server
| Path: cpmsftngxa07
| Xref: cpmsftngxa07 microsoft.public.sqlserver.server:200896
| NNTP-Posting-Host: TKMSFTNGXA07 10.201.232.166
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| I have been researching similiar issue's with UDB DBs on
| NT and found a program on sysinternals.com
| called 'contig'.  It will at least report how many
| fragments your MDF is into, although not how contigous
| they are.
|
| KlK, MCSE
|

Quote:| >-----Original Message-----
| >We have a Production database and a Test database. When
| statements are run
| >in Production they take in excess of 30 hours, however
| when run in Test
| >(copied from Production the night before) they only take
| 11 hours.
| >
| >I have run a DBCC SHOWCONTIG on the Customer Ledger table
| and in Production
| >this is reporting an 88.91 % Extent Scan Fragmentation,
| however in TEST the
| >same table only reports a 0.59% frag. I am assuming that
| this fragmentation
| >in PROD is greatly contributing to the "slowness" of the
| statements. I run
| >Maintenance Plans every weekend for the indexes, but this
| does not seem to
| >make any difference, so I am assuming that it is data
| that is badly
| >fragmented.
| >
| >Is the best way to sort this out to copy the data out to
| a new table and
| >drop the original and rename it back. Or does anybody
| else have any
| >suggestions
| >
| >Thanks
| >
| >Simon
| >
| >
| >
| >
| >.
| >
|

This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.