Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Does DBCC Page show included data? Expand / Collapse
Author
Message
Posted Monday, February 18, 2013 3:43 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 1:56 PM
Points: 93, Visits: 463
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
Posted Monday, February 18, 2013 3:52 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 11:47 AM
Points: 41,525, Visits: 34,442
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
Posted Monday, February 18, 2013 4:43 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 1:56 PM
Points: 93, Visits: 463
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
Posted Tuesday, February 19, 2013 1:43 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 11:47 AM
Points: 41,525, Visits: 34,442
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 »

Add to briefcase

Permissions Expand / Collapse