Sql Server : DBCC IND some details on the undocumented command

Quick one today:

Though rarely used, there are some hidden DBCC commands that come in handy in unexpected situations. Today, we’ll look at one such undocumented command called DBCC IND. Output from this is used as input for DBCC PAGE command.

DBCC IND

It allows us to find the PageID of a given object. In Sql Server, data is stored in pages and each page has an ID. This command allows us to uncover the list of PageIDs that are used by an object to store data; Either table data or index data.

Let’s look at an example:

--
-- Create a dummy table to test DBCC IND
--
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'Uniquifier_Test' AND type = 'U')
     DROP TABLE dbo.Uniquifier_Test
GO

CREATE TABLE dbo.Uniquifier_Test (
      ID    INT         NOT NULL    DEFAULT (1)
    , Name  VARCHAR(5)  NOT NULL    DEFAULT('aa')
)
GO

--
-- Create Clustered and NonClustered indexes
--
CREATE CLUSTERED INDEX CI_Uniquifier_Test_ID
    ON dbo.Uniquifier_Test(ID ASC)
GO

CREATE NONCLUSTERED INDEX nCI_Uniquifier_Test_Name
    ON dbo.Uniquifier_Test(Name ASC)
GO

--
-- Let's insert some dummy records
--
INSERT INTO dbo.Uniquifier_Test
DEFAULT VALUES
GO 2

SELECT *
FROM dbo.Uniquifier_Test
GO

The results look something like this (nothing unusual):

DBCC IND

Now, lets figure out what pages are used to store the Clustered Index and NonClustered Index. This command takes 3 parameters:

• Database Name
• Object Name
• Index ID

DBCC IND (‘Database Name’, ‘Object Name’, ‘Index ID’)

Clustered Index:

For clustered index, use 1 for the IndexID.

--
-- List of pages used by Clustered Index
--
DBCC IND(test, Uniquifier_Test, 1)
GO

DBCC IND results for ClusterIndex

NonClustered Index:

For nonclustered indexes, specify the IndexId to return pertinent results.

--
-- List of pages used by Non-Clustered Index
--
DBCC IND(test, Uniquifier_Test, 2)
GO

DBCC IND Nonclustered Index

All Indexes at Once:

With -1 as the parameter value for IndexID parameter, we could return PageID list for all indexes on this table.

--
-- All in one
--
DBCC IND(test, Uniquifier_Test, -1)
GO

DBCC IND For All

Most of the column names are self-explanatory.

• PageFID is the File ID of the page
• PageID is for PageID
• ….
• ….
• PageType displays the type of page
  • 1 – data page
  • 2 – index page
  • 3 and 4 – text pages
  • 8 – GAM page
  • 9 – SGAM page
  • 10 – IAM page
  • 11 – PFS page
• …

Hope this helps,
_Sqltimes

Read Full Post »