Does DBCC Page show included data?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 Administration » Does DBCC Page show included data?

Does DBCC Page show included data?

Rate Topic

Display Mode

Topic Options

Author

Message

UncleBoris

Posted Monday, February 18, 2013 3:43 PM

Valued Member

Group: General Forum Members
Last Login: 2 days ago @ 4:39 PM
Points: 70, Visits: 316

For my own interest I have been looking at DBCC IND and DBCC Page.

Regarding a non clustered index page I can see the NC key data and the PK key data but not the INCLUDED column data.

No issues, I was just curious as to where this data is stored and if I should be seeing this data from the DBCC Page command.

I am using this so far.

DBCC IND ('test_db', 'test_table',10); -- 10 is the index id of the non clustered index with included columns

DBCC TRACEON(3604)

DBCC PAGE('test_db',1,1022685,3)

thanks

Post #1421359

GilaMonster

Posted Monday, February 18, 2013 3:52 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:49 PM
Points: 37,722, Visits: 29,977

Include columns are stored in the pages at the leaf level of the nonclustered index. Hence if you are looking at a leaf level page of an index that has an include column, DBCC Page will show the included data.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1421361

UncleBoris

Posted Monday, February 18, 2013 4:43 PM

Valued Member

Group: General Forum Members
Last Login: 2 days ago @ 4:39 PM
Points: 70, Visits: 316

Thanks for the reply.

For some reason SP_HELPINDEX2 was showing me the index had included fields when in fact the index did not, hence the reason I could not see the data with DBCC Page.
I re-tested on an index that definitely has included fields and as you say I can see the data no problem.

I dropped and re-created the index and the issue went away so maybe I need to see if I have an older version of SP_HELPINDEX2!

Out of interest, is it possible to see what data page (PageID) holds a particular bit of data?
For example I am not sure if I can find out that the NC key OrderID 1234 resides on PageID 4567 so I can view it with DBCC Page?

No issues just having a play.

Post #1421375

GilaMonster

Posted Tuesday, February 19, 2013 1:43 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:49 PM
Points: 37,722, Visits: 29,977

Not directly. You could navigate the index levels like SQL does when looking for data.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1421473

« Prev Topic | Next Topic »

Permissions