Breaking down DBCC CHECKTABLE by individual index?

Question

If I loop through the indexes of a table and do a DBCC CHECKTABLE (tablename, index_id) on each, is that equivalent to doing just a DBCC CHECKTABLE (tablename)? Or am I doing a lot of overlapping work?

This Books Online entry kind of implies that using the index_id will also do a check on the cluster (or HEAP):

"If index_id is specified, DBCC CHECKTABLE runs integrity checks only on that index, together with the heap or clustered index."

So if I do:

DBCC CHECKTABLE (tablename, cluster_id)
DBCC CHECKTABLE (tablename, non_clustered_1)
DBCC CHECKTABLE (tablename, non_clustered_2)

Have I actually just checked the clustered index 3 times?

Our environment has SQL 2000, 2005, and 2008 servers, so applies to all those versions.

Answer 1

0

That's what I believe, given the BOL text.

Makes sense if you think about it - the physical data is actually a heap or a clustered-index, so checking a particular index would by default check the data in whatever structure it is held.

answered Nov 11 '10 at 09:00 AM

Kev Riley ♦♦
46.1k ● 39 ● 43 ● 69

Suppose that makes sense, to verify the index is valid, it has to verify it against the data in the cluster.

Nov 11 '10 at 09:33 AM BradC

I'm not so certain. Just running it through profiler shows same reads for DBCC CHECKTABLE (tablename, clustered_id),(tablename, non_clustered_1) and DBCC CHECKTABLE (tablename, non_clustered_2) each time they are run and they are always less than DBCC CHECKTABLE (tablename). In fact, running each one causes a few more reads than DBCC CHECKTABLE (tablename) but only 20% more. I used the sales.individual table in adventureworks. Not an expert on this so happy to be put straight but ...

Nov 11 '10 at 11:00 AM Fatherjack ♦♦

so that would show that the same(ish) work is being done for the CIX as well as the NCIX - meaning the CIX is checked 3 times. Not sure why they would be less heavy than the full table though....

Nov 11 '10 at 11:35 AM Kev Riley ♦♦

@kev, think I wasnt clear - eg: whole table =100 reads, ci=70reads, nc1=20reads,nc2=30reads. doing each index in turn = 120reads, not 300 reads

Nov 11 '10 at 11:43 AM Fatherjack ♦♦

ahh see! maybe we need someone here who understands the internals, even whether reads are a good measure!?

Nov 11 '10 at 12:03 PM Kev Riley ♦♦

(comments are locked)

Breaking down DBCC CHECKTABLE by individual index?

Follow this question

Related Questions