DBCC - how long to run?

DBCC - how long to run?

Post by Frits van Ever » Mon, 13 Oct 1997 04:00:00



Can anybody give me a ballpark figure for how long dbcc checktable
should take to run? On my machine (an SGI Indigo with 64MB RAM; SQL
Server version 11.0.2) it takes more than 10 hours to run on a 250 MB
table (3 columns, 5 million rows), even though there are no other users
during this time. This seems a bit excessive. dbcc checkcatalog and dbcc
checkalloc do not reveal any errors.

Thanks,

Frits

 
 
 

DBCC - how long to run?

Post by Pablo Sanche » Tue, 14 Oct 1997 04:00:00


Frits>
Frits> Can anybody give me a ballpark figure for how long
Frits> dbcc checktable should take to run? On my machine (an
Frits> SGI Indigo with 64MB RAM; SQL Server version 11.0.2)
Frits> it takes more than 10 hours to run on a 250 MB table
Frits> (3 columns, 5 million rows), even though there are no
Frits> other users during this time. This seems a bit
Frits> excessive. dbcc checkcatalog and dbcc checkalloc do
Frits> not reveal any errors.
Frits>

Much too long!  Turn off asyc I/O on the uniprocessor box:

$SYBASE/$DSQUERY.cfg

    allow sql server async i/o = DEFAULT

to

    allow sql server async i/o = 0
--
Pablo Sanchez              | Ph # (650) 933.3812          Fax # (650) 933.2821

============= Please include "not spam" in your "Subject: " line ==============
I am accountable for my actions.   http://reality.sgi.com/pablo [ /Sybase_FAQ ]

 
 
 

DBCC - how long to run?

Post by Renaud Tillen » Tue, 14 Oct 1997 04:00:00


10 hours to check a 250 MB table is possible, especially if this table
has many indexes.  Personally, I only perform a checkalloc which will
detect most errors and execute a checktable/checkdb if there's a problem
that I definitely want to investigate further. So far I've come across
one single error that was identified by checkdb but not by chechalloc.
It was some corruption inside an index page and in that case, the SQL
Server simply decided to ignore that index (probably causing a
performance problem but no data corruption).

Renaud Tillens - Disney Consumer Products Europe


> Can anybody give me a ballpark figure for how long dbcc checktable
> should take to run? On my machine (an SGI Indigo with 64MB RAM; SQL
> Server version 11.0.2) it takes more than 10 hours to run on a 250 MB
> table (3 columns, 5 million rows), even though there are no other users
> during this time. This seems a bit excessive. dbcc checkcatalog and dbcc
> checkalloc do not reveal any errors.

> Thanks,

> Frits

 
 
 

DBCC - how long to run?

Post by Margaret Kiepe » Tue, 14 Oct 1997 04:00:00



> Can anybody give me a ballpark figure for how long dbcc checktable
> should take to run? On my machine (an SGI Indigo with 64MB RAM; SQL
> Server version 11.0.2) it takes more than 10 hours to run on a 250 MB
> table (3 columns, 5 million rows), even though there are no other users
> during this time. This seems a bit excessive. dbcc checkcatalog and dbcc
> checkalloc do not reveal any errors.

> Thanks,

> Frits

frits,

the amount of time dbcc takes to run also depends on how fragmented your
table is.  if the table is very fragmented, the extents will be very far
apart on disk, and this will contribute to the time taken to do the
dbcc.  in this case, if you rebuild the clustered index (or create and
drop one for a heap table), then run dbcc again, the time should be
reduced.

recently, for a table about 1 gig in size that was very fragmented (had
not been rebuilt in a year and a half - don't ask), the dbcc time was
reduced by a factor of five after the index rebuild.

hope this helps,

--margie kieper

 
 
 

DBCC - how long to run?

Post by Greg Linda » Wed, 15 Oct 1997 04:00:00



> the amount of time dbcc takes to run also depends on how fragmented your
> table is.  if the table is very fragmented, the extents will be very far
> apart on disk, and this will contribute to the time taken to do the
> dbcc.  in this case, if you rebuild the clustered index (or create and
> drop one for a heap table), then run dbcc again, the time should be
> reduced.

A while back I posted a perl script which computes the fragmentation
of a table. I think it made it into the FAQ. This is a key point which
many Sybase DBA's are unaware of, and which the Sybase documentation
doesn't talk about at all.

-- g

 
 
 

DBCC - how long to run?

Post by Pablo Sanche » Wed, 15 Oct 1997 04:00:00


Greg>
Greg> A while back I posted a perl script which computes the
Greg> fragmentation of a table. I think it made it into the
Greg> FAQ. This is a key point which many Sybase DBA's are
Greg> unaware of, and which the Sybase documentation doesn't
Greg> talk about at all.
Greg>

It did make it to the FAQ (thanks!)... I think it doesn't
work for System 11 though...
--
Pablo Sanchez              | Ph # (650) 933.3812          Fax # (650) 933.2821

============= Please include "not spam" in your "Subject: " line ==============
I am accountable for my actions.   http://reality.sgi.com/pablo [ /Sybase_FAQ ]

 
 
 

DBCC - how long to run?

Post by Anthony Mandi » Tue, 21 Oct 1997 04:00:00



> Can anybody give me a ballpark figure for how long dbcc checktable
> should take to run? On my machine (an SGI Indigo with 64MB RAM; SQL
> Server version 11.0.2) it takes more than 10 hours to run on a 250 MB
> table (3 columns, 5 million rows), even though there are no other users
> during this time. This seems a bit excessive. dbcc checkcatalog and dbcc
> checkalloc do not reveal any errors.

        Sorry for the delayed response (my newsfeed seems to be getting
        slower and slower), but I thought I'd reply since none of other
        followups were totally satisfactory.

        Firstly, the best answer to your question is: its depends.
        Of hand, I would say its far too slow, but I've seem some
        strange stuff with dbcc commands. There are a few issues
        you need to address. I don't know if Pablo's comment about
        async i/o is appropriate, but you can test it. The physical
        issues to look at are: processor speed, speed of the disk i/o
        subsystem and memory (64MB is a little on the lite side, once
        you take away memory for the OS and miscellaneous other
        requirements, you're not left with very much for the server).

        Logical issues are the index and table design, since they
        have a direct effect on the time taken for dbcc checktable.
        With only 3 columns on your table, I would have to presume
        that you wouldn't have too many indexes defined, but you
        never know. If you only have a clustered index defined,
        then table fragmentation could be a likely cause. Dropping
        and recreating the clustered index will help in realigning
        the table extents again - but the effect is only temporary.
        This, of course, depends on whether the clustered index
        was defined on a natural key or a primary key. On the
        latter, no fragmentation ensues, so recreating the index
        will have negligible effect. On the former, fragmentation
        will begin to reoccur with future inserts. Nonclustered
        indexes could well take the bulk of the processing time
        with the dbcc command. This is naturally due to the fact
        that the left level isn't in line, so more time is taken
        by page searching than with clustered indexes. Having a
        larger data cache may help here. Larger I/O sizes would
        help with clustered indexes and the reading in of index
        pages for nonclustered indexes.

        One way to test the above is to call dbcc indexalloc
        instead (its a subset of tablealloc, and both are
        subsets of checkalloc). Run it for each index on the
        table in turn and note the seperate times for each.
        Then rebuild the clustered index and run it again on
        that index id only. The results should indicate where
        the crux of the problem is. There's no fix for non-
        clustered indexes other than reevaluating their design.

-am

 
 
 

DBCC - how long to run?

Post by Frits van Ever » Sat, 25 Oct 1997 04:00:00


Anthony:

Thanks for your detailed response. I'm going to try everything you
suggest -as well as switching ASYNC I/O off- when I get back from next
week's trip. I'll post the results.

Frits

 
 
 

DBCC - how long to run?

Post by Anthony Mandi » Mon, 27 Oct 1997 03:00:00



> Anthony:

> Thanks for your detailed response. I'm going to try everything you
> suggest -as well as switching ASYNC I/O off- when I get back from next
> week's trip. I'll post the results.

> Frits

        Good luck. I'll look forward to seeing your findings.