Sunday, February 19, 2012

dbcc checkdb on a large table

We have a 200 GB database with a single table that spans 36 partitions.
Each time we run a dbcc checkdb on this database, the process takes
close to 40 hours to finish, and in the course of doing it expands
tempdb to almost 200 GB. What are the recommended options to speed up
the dbcc process? What kind of dbcc's can be done? Suggestions are
welcome.
The database runs on SQL Server 2005 - there are 4 CPUs on the box and
the total memory is 8 GB.
Thanks,
PrakashHi,
DBCC CHECKDB on my 320 GB database on 8 CPU X 8 GB machine is taking me only
1 hour. 40 hours is too long on a 4 CPU machine. Just see the Disk trasfer
and Queueu length at that point of time. Check with your hardware person to
do a hardware check.
From you side you could enable the degree of paralleism (DOP) during the DBC
C
and see if that helps. Hope you have already enabled the AWE memory since
you have 8 GB RAM.
Thanks
Hari
SQL Server MVP
"pnat331@.gmail.com" wrote:

> We have a 200 GB database with a single table that spans 36 partitions.
> Each time we run a dbcc checkdb on this database, the process takes
> close to 40 hours to finish, and in the course of doing it expands
> tempdb to almost 200 GB. What are the recommended options to speed up
> the dbcc process? What kind of dbcc's can be done? Suggestions are
> welcome.
> The database runs on SQL Server 2005 - there are 4 CPUs on the box and
> the total memory is 8 GB.
> Thanks,
> Prakash
>

No comments:

Post a Comment