Share via

Transact-SQL: Return DBCC Output in a View

  • Article

Background

This is a very short article about a small trick, to use DBCC result in a View element. This article based on an answer to a question in MSDN forum, which will be our basic case study for the article.

Our  case study is the Original Forum question

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/750a9d9a-5d71-44bd-a578-3d8a233d5e42/objectid-from-pageid?forum=transactsql#3c4ecddc-50ae-4ee5-b111-e441a8adae63

"I'm returning Data from the Blocked Process Report and want to show which resource is being blocked. For certain locks means I need to find the ObjectID from the PageID. Ideally I would like to do this using a function or view in order to access it within the query. And of course DBCC Page cannot be used in a view or Function."

Is this correct?

The solution

Since we cannot directly use DBCC in a view, we are going to use a simple workaround based on several ideas: (1) We can catch the output of a DBCC-Statement in a temporary table or table variable. (2) We can create a system stored procedure which uses and returns temporary table content. (3) We can execute a stored procedure in a view using openquery. Let's start

Step 1: create a system stored procedure that returns DBCC

System stored procedures are created and stored in the master database and have the sp_ prefix.

USE MASTER
GO
  
-- we need this value for the PROCEDURE if we want to use "EXECUTE AS" hint
select SUSER_NAME()
GO
  
-- We can use table variable or temporary table in SP
;CREATE PROCEDURE  sp_Ari_DbccPage
    (@_DatabaseName nvarchar(50),
    @_FileID int,
    @_PageID int,
    @_DescriptionLevel int)
-- WITH EXECUTE AS 'user from previous query'
-- WITH EXECUTE AS OWNER
-- WITHOUT LOGIN
AS
    SET NOCOUNT ON
    -- We can insert DBCC result to table variable (this i first step of the trick)
    -- We could use Temporary table as well, then if need we can add index
    DECLARE @MyPageTbl as TABLE(ParentObject VARCHAR(128), [Object] VARCHAR(512), Field VARCHAR(128), [Value] VARCHAR(8000) )
    Declare @Query NVARCHAR(MAX) =
        'DBCC PAGE(
            ''' + convert(nvarchar(max),@_DatabaseName) + ''','
            + convert(nvarchar(max),@_FileID) + ','
            + convert(nvarchar(max),@_PageID) + ','
            + convert(nvarchar(max),@_DescriptionLevel)
        + ') WITH TABLERESULTS'
    Insert @MyPageTbl EXEC (@Query)
    select * from @MyPageTbl
GO
  
-- Let's check our SP:
exec sp_Ari_DbccPage
    @_DatabaseName = 'MSSQLTIPS',
    @_FileID = 1,
    @_PageID = 89,
    @_DescriptionLevel = 3
GO
  
-- Mark our stored procedure as system object
EXEC sp_ms_marksystemobject 'sp_Ari_DbccPage' 
GO

  

Step 2: Preparation

In this step we are going to create a new database. In that database we will create a table and then examine the pages used by the table using the DBCC IDN. We will use this information next step, in order to create the view that returns the result of DBCC page.

 ImportantI

Notice: We can use the same trick and build a SP that returns the DBCC IDN result or even combine SP which will check DBCC IDN and then execute DBCC PAGE according to the result.

  

------------------------------------------------------------ DDL
USE MASTER
GO
  
CREATE DATABASE  Ari_DbccPage_DB
GO
  
USE Ari_DbccPage_DB
GO
  
CREATE TABLE  Ari_DbccPage_Tbl (
    UsersID INT  IDENTITY(1,1),
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Registertime DATETIME NULL,
    CONSTRAINT PK_User PRIMARY KEY  (UsersID),                       -- This wil be a CLUSTERED index
    CONSTRAINT UQ_FirstName_LastName UNIQUE (FirstName, LastName)   -- This wil be a NONCLUSTERED index
)
GO
  
------------------------------------------------------------ DML
INSERT INTO  Ari_DbccPage_Tbl (FirstName,LastName,Registertime)
values
('A','I','2014-02-27'),
('S','U','2014-02-27'),
('D','Y','2014-02-27'),
('F','H','2014-02-27'),
('G','B','2014-02-27'),
('H','G',NULL),
('Z','V',NULL),
('X','C','2014-02-27')
GO
  
select * from Ari_DbccPage_Tbl
GO
  
-- find where the pages for this table and its data live
-- DBCC IND use to list all of a table's data and index pages
-- DBCC IND (database_name, table_name, index_id)
DBCC IND('Ari_DbccPage_DB',Ari_DbccPage_Tbl,-1) -- index_id -1: get all index_id
GO
/*
PageFID PagePID     IAMFID IAMPID      ObjectID    IndexID     PartitionNumber PartitionID          iam_chain_type       PageType IndexLevel NextPageFID NextPagePID PrevPageFID PrevPagePID
------- ----------- ------ ----------- ----------- ----------- --------------- -------------------- -------------------- -------- ---------- ----------- ----------- ----------- -----------
1       121         NULL   NULL        293576084   1           1               72057594040680448    In-row data          10       NULL       0           0           0           0
1       120         1      121         293576084   1           1               72057594040680448    In-row data          1        0          0           0           0           0
1       127         NULL   NULL        293576084   2           1               72057594040745984    In-row data          10       NULL       0           0           0           0
1       126         1      127         293576084   2           1               72057594040745984    In-row data          2        0          0           0           0           0
*/
  
-- What does all this data mean?
---------------------------------
-- PagePID represent a file number
-- ndexID is the index_id as found in sys.indexes
    select * from sys.indexes where object_id = OBJECT_ID('Ari_DbccPage_DB.DBO.Ari_DbccPage_Tbl')
-- PageType
    -- PageType = 1 is a data page,
    -- PageType = 2 is an index page
    -- PageType = 10 is the IAM page that maintains the index itself.
    -- We can notice in our case that we have two IndexID 1,2 and each index have one PageType = 10 and one which is 1,2
-- IndexLevel is the level within the IAM structure the page falls.
    -- IndexLevel = 0, then this is a leaf level page for the index.
    -- Using the value of MAX(IndexLevel) we can estimate the IO use when we need to get a specific row
  
-- DBCC PAGE command allows to examine the contents of data and index pages.
-- DBCC PAGE (database_name,file_number,page_number,information_level)
-- Note: Before we can run DBCC PAGE, it's required that trace flag 3604 be set  to instruct the engine to send output to  the console;
-- otherwise you won't see anything!
DBCC TRACEON(3604)
DBCC PAGE('Ari_DbccPage_DB',1,120,3) WITH  TABLERESULTS
GO

Step 3: Return stored procedure result in view, using openquery

Now we can build a new view in our database and get the DBCC information from the SP using openquery statement.

-- Move to any local database
Use Ari_DbccPage_DB
GO
  
-- get server name
select name  from sys.servers
GO
  
-- check that we can use openquery to execute our stored procedure
select * from openquery(
    [use our server name  from previous query],
    'sp_Ari_DbccPage
        @_DatabaseName = ''Ari_DbccPage_DB'',
        @_FileID = 1,
        @_PageID = 120,
        @_DescriptionLevel = 3'
)
GO
  
-- That is all :-) Now we can use our DBCC in a view
create view  Ari_DbccPage_View as
    select * from openquery(
        [use our server name  from previous query],
        'master.dbo.sp_Ari_DbccPage
            @_DatabaseName = ''MSSQLTIPS'',
            @_FileID = 1,
            @_PageID = 89,
            @_DescriptionLevel = 3'
    )
go
  
-- Check our view
select * from Ari_DbccPage_View
  
-- clean local database (we do not clean the system SP for future use)
drop view  Ari_DbccPage_View

  

 Note!

Conflicts might be well avoided by following good naming convention. For example (1) names might start with "Microsoft SQL_" or something similar. It is not likely users will use something like this. (2) Using "Stored_Procedure" as part of the name will probably implies that this is a stored procedure element, and it is not likely that it will used for different element type. (3) Name should describe the element (what it does). It is likely that element with the name "Split_String" is doing some type of splitting.
>> This article demonstrate how to return the result of "DBCC page" statement. Therefore we use "DbccPage" as basis for the elements names.
>> Name must be unique (sometimes using company's short name like MS for Microsoft). In this article we use "Ari" for uniqness.

  

Conclusions

As we have seen there is no problem to display in VIEW,  the results of executing any DBCC statement, using stored procedure and openquery.

Resources and More information

Code

Resource

More information

See Also

http://c.statcounter.com/10028325/0/954ef191/1/