Long running DBCC

Long running DBCC

Post by Gaynell Noe » Fri, 21 Feb 1997 04:00:00



I have a 1300MB database running on Sybase Sytem10.  About a year ago the
database was converted from Microsoft SQL Server by bcping the data out and
in.  After the conversion the dbcc ran alot faster than it did before.  But
every day when the dbcc runs the time it takes to run increases by about 2
mins.  It now runs for close to 5 hrs.  Is there some way to speed up the
dbcc?  I know bcp is an option but some of these tables have over 400,000
rows and I would prefer to avoid doing that.  Any ideas or suggestions will
be appreciated.

Gaynell Noel

 
 
 

Long running DBCC

Post by Anthony Mandi » Sat, 22 Feb 1997 04:00:00



> I have a 1300MB database running on Sybase Sytem10.  About a year ago the
> database was converted from Microsoft SQL Server by bcping the data out and
> in.  After the conversion the dbcc ran alot faster than it did before.

        This was purely and simply because the data and indexes you have
        defined were repacked after the migration.

Quote:> But every day when the dbcc runs the time it takes to run increases by about 2
> mins.  It now runs for close to 5 hrs.

        I have 2 databases that are roughly equal in size at about 2GB each.
        One take about 7 hours to do the full suite of recommended dbcc's
        while the other take almost twice as long. I'll explain why below.

        I would suggest not running your checks nightly now. Switch to weekly.

Quote:> Is there some way to speed up the dbcc?  I know bcp is an option but some
> of these tables have over 400,000 rows and I would prefer to avoid doing that.
> Any ideas or suggestions will be appreciated.

        The easiest way is to drop and recreate your indexes. However, this
        doesn't buy you that much in the long run. The best thing is to live
        with it and only rebuild the indexes when performance becomes
        unacceptable. Your times will keep increasing as you add more data
        and page and index splits occur. This is known as fragmentation and
        is caused by the keys chosen for your indexes. Natural keys will
        cause greater fragmentation.

-am

 
 
 

Long running DBCC

Post by Mike Ma » Sat, 22 Feb 1997 04:00:00




>> I have a 1300MB database running on Sybase Sytem10.  About a year ago the
>> database was converted from Microsoft SQL Server by bcping the data out and
>> in.  After the conversion the dbcc ran alot faster than it did before.

>    This was purely and simply because the data and indexes you have
>    defined were repacked after the migration.

>> But every day when the dbcc runs the time it takes to run increases by about 2
>> mins.  It now runs for close to 5 hrs.

>    I have 2 databases that are roughly equal in size at about 2GB each.
>    One take about 7 hours to do the full suite of recommended dbcc's
>    while the other take almost twice as long. I'll explain why below.

>    I would suggest not running your checks nightly now. Switch to weekly.

>> Is there some way to speed up the dbcc?  I know bcp is an option but some
>> of these tables have over 400,000 rows and I would prefer to avoid doing that.
>> Any ideas or suggestions will be appreciated.

>    The easiest way is to drop and recreate your indexes. However, this
>    doesn't buy you that much in the long run. The best thing is to live
>    with it and only rebuild the indexes when performance becomes
>    unacceptable. Your times will keep increasing as you add more data
>    and page and index splits occur. This is known as fragmentation and
>    is caused by the keys chosen for your indexes. Natural keys will
>    cause greater fragmentation.

>-am

Anthony's points are well taken.  

However, let's start with the dbcc commands themselves.  DBCC
checkalloc begins by validating that all allocation pages are intact
and if it finds errors will stop before checking further.  If the
allocation pages are correct it then goes on to follow the pagelinkage
of every table and index and verify that the allocations agree with
the linkages.  The important point is that while the pagelinkage is
checked only allocation information relating to the pagelinkage is
checked aside from the pagelinkage itself.  Thus checkalloc runs much
more quickly than does checkdb which will be described below.    If
you had two databases of equal size but one with 2x the rows in
sysindexes of the others one might take 2x as long to run as the other
but probably not much more than that.

DBCC checkdb, however is a completely different situation.  Checkdb
runs checktable on every table in the db. How long it takes depends
more on how many indexes there are in each table than anything else.
This is because while it too checks pagelinkages, it also verifies
that every pointer in every index is correct.  For the clustered index
this will be relatively quick because the pointers only point to pages
and not to rows, thus if there are on average 20 rows per page it will
only have to read in one page to verify integrity for every 20 rows.
Nonclustered indexes however contain pointers to every row not to
every page and again given a hypothetical 20 rows per page there are
immediately 20x the number of reads to be done.  However, it is likely
to be even worse than this since each pointer on the nonclustered
index points to a row that is presumably not in clustered index order
each pointer will result in the need to read in a new page to memory
for every row.  In the worst case we could end up with the number of
datapagesx20 reads to verify a nonclustered index.  It is readily
evident that the time needed to verify pointers can easily increase
geometrically rather than the more or less arithmetic increase we see
in checkalloc.  The more nonclustered indexes there are the slower
dbcc's will run.

My recommendation to those who can't run complete dbcc's every night
it to split the dbcc's up over the week rather than try to run them on
weekends.  For example on Monday run dbcc checktable and dbcc
tablealloc on 20% of the db, on Tuesday run them on the next 20% etc.

As far as dropping and recreating indexes or bcp'ing data in and out
-- if you are going to do that consider planning the use of your space
in a way that will allow this kind of reorg to be done as seldom as
necessary.  User segments were implemented in Sybase at least
partially to satisfy this need.  I would recommend though that if you
decide to use user segments that you maintain a 1:1 relationship
between the usersegments and the physical devices in use by the
database.

Mike

 
 
 

Long running DBCC

Post by Gaynell Noe » Sat, 22 Feb 1997 04:00:00


Mike and Anthony thanks for your suggestions.

I did some testing on a copy of the production database.  I did notice that
the checkdb ran a lot longer than the checkalloc.  Checkdb ran for over 4
hrs and checkalloc ran in 7 mins.  I tried dropping and creating the
indexes - this cut an hour off of the checkdb and made no difference to the
checkalloc.  I read somewhere that running checkdb with the skip_ncindex
decreases the run time.  I also tried that, and checkdb went down to 22
mins !!!  This is obviously a big improvement.  But what are the
consequences of not checking non-clustered indexes?  I was thinking about
running dbcc's Mon - Fri with skip_ncindex and a full checkdb on weekends.
Is this a good idea?

Gaynell

Quote:> >       The easiest way is to drop and recreate your indexes. However, this
> >       doesn't buy you that much in the long run. The best thing is to live
> >       with it and only rebuild the indexes when performance becomes
> >       unacceptable. Your times will keep increasing as you add more data
> >       and page and index splits occur. This is known as fragmentation and
> >       is caused by the keys chosen for your indexes. Natural keys will
> >       cause greater fragmentation.

> >-am

> Anthony's points are well taken.  

> However, let's start with the dbcc commands themselves.  DBCC
> checkalloc begins by validating that all allocation pages are intact
> and if it finds errors will stop before checking further.  If the
> allocation pages are correct it then goes on to follow the pagelinkage
> of every table and index and verify that the allocations agree with
> the linkages.  The important point is that while the pagelinkage is
> checked only allocation information relating to the pagelinkage is
> checked aside from the pagelinkage itself.  Thus checkalloc runs much
> more quickly than does checkdb which will be described below.    If
> you had two databases of equal size but one with 2x the rows in
> sysindexes of the others one might take 2x as long to run as the other
> but probably not much more than that.

> DBCC checkdb, however is a completely different situation.  Checkdb
> runs checktable on every table in the db. How long it takes depends
> more on how many indexes there are in each table than anything else.
> This is because while it too checks pagelinkages, it also verifies
> that every pointer in every index is correct.  For the clustered index
> this will be relatively quick because the pointers only point to pages
> and not to rows, thus if there are on average 20 rows per page it will
> only have to read in one page to verify integrity for every 20 rows.
> Nonclustered indexes however contain pointers to every row not to
> every page and again given a hypothetical 20 rows per page there are
> immediately 20x the number of reads to be done.  However, it is likely
> to be even worse than this since each pointer on the nonclustered
> index points to a row that is presumably not in clustered index order
> each pointer will result in the need to read in a new page to memory
> for every row.  In the worst case we could end up with the number of
> datapagesx20 reads to verify a nonclustered index.  It is readily
> evident that the time needed to verify pointers can easily increase
> geometrically rather than the more or less arithmetic increase we see
> in checkalloc.  The more nonclustered indexes there are the slower
> dbcc's will run.

> My recommendation to those who can't run complete dbcc's every night
> it to split the dbcc's up over the week rather than try to run them on
> weekends.  For example on Monday run dbcc checktable and dbcc
> tablealloc on 20% of the db, on Tuesday run them on the next 20% etc.

> As far as dropping and recreating indexes or bcp'ing data in and out
> -- if you are going to do that consider planning the use of your space
> in a way that will allow this kind of reorg to be done as seldom as
> necessary.  User segments were implemented in Sybase at least
> partially to satisfy this need.  I would recommend though that if you
> decide to use user segments that you maintain a 1:1 relationship
> between the usersegments and the physical devices in use by the
> database.

> Mike

 
 
 

Long running DBCC

Post by Michael Ma » Sun, 23 Feb 1997 04:00:00


: Mike and Anthony thanks for your suggestions.

: I did some testing on a copy of the production database.  I did notice that
: the checkdb ran a lot longer than the checkalloc.  Checkdb ran for over 4
: hrs and checkalloc ran in 7 mins.  I tried dropping and creating the
: indexes - this cut an hour off of the checkdb and made no difference to the
: checkalloc.  I read somewhere that running checkdb with the skip_ncindex
: decreases the run time.  I also tried that, and checkdb went down to 22
: mins !!!  This is obviously a big improvement.  But what are the
: consequences of not checking non-clustered indexes?  I was thinking about
: running dbcc's Mon - Fri with skip_ncindex and a full checkdb on weekends.
: Is this a good idea?

: Gaynell

Yes, this is probably sufficient.  If you have time on the weekends and you
run dbcc checktable, rebuild the indexes and run dbcc checktable again and find
that speed increases you might even consider dropping and rebuilding indexes
regularly on the weekends.  checktable has to check every index pointer in
every index and verify that the row and offset to which it points is valid on
the data pages.  This check is probably the cleanest way to measure the impact
of fragmentation since most of the work is the io that has to be done.  If the
io is faster it means the disks are being used more efficiently.

Mike
--
Mike Maas                                               (510) 658-0487
Masimo Consulting                                       2701 Woolsey
specializing in Sybase DBA Issues and Data Recovery     Berkeley, CA 94705-2606

 
 
 

Long running DBCC

Post by Anthony Mandi » Sun, 23 Feb 1997 04:00:00



> Mike and Anthony thanks for your suggestions.

> I did some testing on a copy of the production database.  I did notice that
> the checkdb ran a lot longer than the checkalloc.  Checkdb ran for over 4
> hrs and checkalloc ran in 7 mins.  I tried dropping and creating the
> indexes - this cut an hour off of the checkdb and made no difference to the
> checkalloc.  I read somewhere that running checkdb with the skip_ncindex
> decreases the run time.  I also tried that, and checkdb went down to 22
> mins !!!  This is obviously a big improvement.

        The background on this is that checking non-clustered indexes
        doesn't just check the index pages themselves but goes right
        thru to the data pages as well. This is unavoidable. The final
        link to the data has to be checked. Considering the nature of
        NC indexes this explains why it takes significantly longer.
        The more data you have and the more NC indexes you have the
        longer it will take.

Quote:> But what are the consequences of not checking non-clustered indexes?

        See above. You won't know if the final link to the data is valid.

Quote:> I was thinking about running dbcc's Mon - Fri with skip_ncindex
> and a full checkdb on weekends. Is this a good idea?

        This is a viable compromise. You only need to ensure that
        your data pages are consistent. If anything gets hosed,
        you can always rebuild your indexes provided that the
        data is OK. As the times continue to increase you can
        switch to alternative strategies as have been suggested.
        Minimising fragmentation isn't a long term viable solution.
        Look to restricting your NC indexes to be bare minimum
        and choose your clustered index carefully.

-am

 
 
 

1. Long running DBCC

Hi,

I've got a problem with the length of time it takes to run DBCC
CheckDB. I've investigated several sources and all I can find are
vague statements about how it may take a long time but no guidelines
on how long, what is normal and why it takes so long.

We have three production databases,on SQL6.5 SP5, each about 4.5G. In
these databases there are two tables with about 10 million rows each.
Each table has a clustered index and one table has two non-clustered
indexes, the other one four. Unfortunately, we cannot run CheckDB on
these databases without the NOINDEX option since it would run for
days, obviously interfering with there use.

As a test I've run CheckTable on some extracts from these main tables
with just one non-clustered index with the following results:

1.4 million rows: Time - 2 min., I/O(Reads) - 26 thousand
3.0 million rows: Time - 1 hr. 27 min., I/O(Reads) - 700 thousand
6.9 million rows: Time - 11 hr. 22 min., I/O(Reads) - 4.4 million
10.8 million rows: Time - 32 hr. 2min., I/O(Reads) - 9.9 million

At these rates it would take over a week to run CheckDB. These tables
have had some problems in the past with index corruption and one had
to be completely dropped and rebuilt but recently we've been lucky,
since we've been forced to run our maintenance tasks NOINDEX.

Is this "normal"? I've heard rumours about a SQL Server bug but can't
find any reference to one anywhere authoratative.

Any help is profusely thanked for in advance.

Stephen Martin
EMSoft Solutions Inc.
Toronto, ON

2. Table PARADOX with password in D3

3. Help Help My DBCC CheckDB run a long long time

4. ProView updated (bug fix)

5. DBCC running too long?

6. Connect to SQL Server 6.5 via TCP/IP

7. DBCC - how long to run?

8. If test in sql??

9. Running dbcc dbreindex on sql 6.5 run out of logs

10. Schedule Jobs takes longer to run than running direct

11. status when running an long run insert query

12. how long does dbcc checkdb take

13. dbcc inputbuffer Statements to long to read...