> 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.