DBCC and disabled indexes

Question

DBCC and disabled indexes

Steve Jones - SSC Editor

SSC Guru

Points: 736430
More actions
June 17, 2015 at 11:40 pm

#299069

Comments posted to this topic are about the item DBCC and disabled indexes
June 18, 2015 at 4:10 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

TomThomson SSC Guru Points: 104773 More actions · Answer 1

Interesting question.

Both dbcc checkalloc and dbcc checkcatalog run without complaint when there are tables with disabled clustered indexes in the database, and DBCC CHECKDB runs both of those, and I though that would mean that at least some metadata concerning the table is checked. It's dbcc checktable (called by dbcc checkdb) that reports that reports that the clustered index is disabled, not dbcc checkdb itself. So I picked the first option, since some metadata associated with the table is checked normally. No points for me today. 😉

Tom

patricklambin SSCrazy Eights Points: 9994 More actions · Answer 2

Easy question as I remembered a session from a senior member of the french support ( who created SQL Server Nexus ) 2 years ago. But I was not sure that it is supported in SQL Server 2014 and 2016.

I have discovered in the BOL ( 2012/2014/2016 ) this sentence :

"•If a clustered index is disabled, DBCC CHECKDB cannot return information about the underlying table; instead, the statement reports that the clustered index is disabled".

So it is true that at least the existence of a disabled index ( clustered ) is detected.

In the page related to DBCC CHECKTABLE , I am discored ( for compatibility >= 100 ) :

"?Unless NOINDEX is specified, DBCC CHECKTABLE performs both physical and logical consistency checks on a single table and on all its nonclustered indexes."

Nothing about clustered indexes ( enabled or disabled ).

As I am far to be a specialist of DBCC , I am unable to say whether some metadata are returned during a DBCC CHECKDB ( according to me , the fact that it is showing the existence of a disabled index is only an information ).

But I would appreciate any clearer information about this question.

Nota : I have reread the Kalen Delaney's book about the SQL Server 2012 Internals , but I have found nothing ( surely because my reading has been too quick.

Ed Wagner SSC Guru Points: 287026 More actions · Answer 3

I learned something new today as well. Thanks.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

gary.mazzone SSCarpal Tunnel Points: 4741 More actions · Answer 4

Why are we assuming that the Clustered index is the PK for the table that is not a requirement..... What happens if the PK is not the clustered index and some other index is. If that index is disabled it is not the same.

peter.vanbennekom SSC Veteran Points: 226 More actions · Answer 5

peter.vanbennekom

SSC Veteran

Points: 226

June 18, 2015 at 5:56 am

#1806327

Clustered index is not equal to PK

mhynds SSC-Addicted Points: 452 More actions · Answer 6

Okay I didn't pay close enough attention to the question. The command still checks the database but doesn't check the specific question. Okay - time to pay more attention to what's going on here.

Xavon SSCrazy Points: 2280 More actions · Answer 7

I thought it was number three, but when I ran dbcc checkdb on SalesArchive (from the last two questions about disabled Clustered Indexes), I got back an error saying it could not find the database. So I answered #4 and got it wrong.

Is this because I am running it in 2008 R2, or a difference between the schema of OrderLineItem and SalesArchive?

briankwartler Ten Centuries Points: 1332 More actions · Answer 8

I disabled the clustered index (also the primary key) of a table called junk and when I ran DBCC CHECKDB in the database, I got the message:

Index 'PK__junk__3BD019E5787EE5A0' on table 'junk' is marked as disabled. Rebuild the index to bring it online.

As this is the only reference to the table in the report, I chose "DBCC runs, but cannot check the table at all."

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 9

Thnx Steve. I knew the answer so it did not take time to click the right radio button.

But, 1 question, if any of the non cluster indexes in a table are disabled, I am sure that only that NC index will be skipped by CheckDB, right? Or for such case also the same scenario?

Thanks.

briankwartler Ten Centuries Points: 1332 More actions · Answer 10

SQL-DBA-01 (6/18/2015)
Thnx Steve. I knew the answer so it did not take time to click the right radio button.
But, 1 question, if any of the non cluster indexes in a table are disabled, I am sure that only that NC index will be skipped by CheckDB, right? Or for such case also the same scenario?

If only a nonclustered index is disabled, then DBCC CHECKDB will show the information for the table. It's only if the clustered index is disabled (whether it's the primary key or not, by the way) that you get the message "Index 'cix_junk_y' on table 'junk' is marked as disabled. Rebuild the index to bring it online."

I confirmed this with the following T-SQL script:

-- CREATE DATABASE MyDB;

USE MyDB;

-- DROP TABLE junk;

CREATE TABLE junk (x INT NOT NULL);

ALTER TABLE junk ADD CONSTRAINT PK_junk_x PRIMARY KEY CLUSTERED (x);

INSERT junk VALUES (1), (2), (3);

DBCC CHECKDB; -- There are 3 rows in 1 pages for object "junk".

ALTER INDEX PK_junk_x ON junk DISABLE;

DBCC CHECKDB; -- Index 'PK_junk_x' on table 'junk' is marked as disabled. Rebuild the index to bring it online.

ALTER INDEX PK_junk_x ON junk REBUILD;

DBCC CHECKDB; -- There are 3 rows in 1 pages for object "junk".

ALTER TABLE junk ADD y int;

GO

UPDATE junk SET y = x;

-- The following doesn't work as you will get an error message

-- An explicit DROP INDEX is not allowed on index 'junk.PK_junk_x'. It is being used for PRIMARY KEY constraint enforcement.

-- DROP INDEX PK_junk_x ON junk;

-- Instead use the following:

ALTER TABLE junk DROP CONSTRAINT PK_junk_x;

CREATE CLUSTERED INDEX cix_junk_y ON junk (y);

DBCC CHECKDB; -- There are 3 rows in 1 pages for object "junk".

ALTER INDEX cix_junk_y ON junk DISABLE;

DBCC CHECKDB; -- Index 'cix_junk_y' on table 'junk' is marked as disabled. Rebuild the index to bring it online.

ALTER INDEX cix_junk_y ON junk REBUILD;

DBCC CHECKDB; -- There are 3 rows in 1 pages for object "junk".

-- DROP INDEX cix_junk_y ON junk;

CREATE INDEX ncix_junk_y ON junk (y);

DBCC CHECKDB; -- There are 3 rows in 1 pages for object "junk".

ALTER INDEX ncix_junk_y ON junk DISABLE;

DBCC CHECKDB; -- There are 3 rows in 1 pages for object "junk".

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 11

Thnx, I thought to check using a quick demo script, but anyways, you did the part.

Thanks.

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 12

Nice question, thanks.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

SQLRNNR SSC Guru Points: 281334 More actions · Answer 13

While the question is good, I would rather have seen it say CHECKDB in lieu of DBCC on the correct answer (or in the explanation). Better still would have been DBCC CHECKDB. There are just so many DBCC commands.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events