asked
Breaking down DBCC CHECKTABLE by individual index?
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][1] 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. [1]:
http://msdn.microsoft.com/en-us/library/ms174338.aspx
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.
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 ...
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....
@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
ahh see! maybe we need someone here who understands the internals, even whether reads are a good measure!?
