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

more ▼

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

avatar 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

avatar image

Kev Riley ♦♦
64.2k 48 62 81

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.

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:

x54
x15

asked: Nov 11, 2010 at 08:55 AM

Seen: 2117 times

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

Copyright 2016 Redgate Software. Privacy Policy