x

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
more ▼

asked Nov 11, 2010 at 08:55 AM in Default

BradC gravatar image

BradC
41 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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

answered Nov 11, 2010 at 09:00 AM

Kev Riley gravatar image

Kev Riley ♦♦
52.7k 47 49 76

Suppose that makes sense, to verify the index is valid, it has to verify it against the data in the cluster.
Nov 11, 2010 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, 2010 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, 2010 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, 2010 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, 2010 at 12:03 PM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x47
x12

asked: Nov 11, 2010 at 08:55 AM

Seen: 1842 times

Last Updated: Nov 11, 2010 at 08:55 AM