en-UShe-IL
You are here:   Blog
Register   |  Login

Blog Archive:

Maximize
* Can be used in order to search for older blogs Entries

Search in blogs


Blog Categories:

Maximize
* Can be used in order to search for blogs Entries by Categories

Blog Tags:

Maximize
* Can be used in order to search for blogs by keywords

TNWikiSummit


Awared MVP 


 


Microsoft® Community Contributor 


Microsoft® Community Contributor


 Read first, before you you use the blog! Maximize

Recent Entries

Minimize
אוג17

Written by: ronen ariely
17/08/2014 19:11 RssIcon

רקע

הפעלת הוראות DBCC כחלק משאילתת טבלת צפייה (View) לא מותרת בצורה ישירה. בבלוג זה נראה כיצד ניתן לעקוף את המגבלהבמספר צעדים מהירים.

הבעיה (כפי שהוצגה מתוך פורום MSDN)

אני מחזיר נתונים על תהליכים חסומים ואני רוצה לחקור את המאשבים החסומים כגון הדפים (Pages) במסד הנתונים. לצורך כך אני צריך לחקור את ה ObjectID המתקבל מה PageID החסום. באופן אידאלי אני רוצה לבצע את זה באמצעות טבלת צפייה או פונקציה, אבל כמובן שזה לא אפשרי.

האם זה באמת לא אפשרי?!?

הפתרון

היות ואנחנו לא יכולים להפעיל DBCC באופן ישיר כחלק משאילתת טבלת המפייה, אנחנו נבסס את הפתרון שלנו על עקיפת המגבלות תוך היתבססות על מספר רעיונות: (1) אנחנו יכולים להעביר את תוצאות הוראת BDCC אל טבלה זמנית או למשתנה טבלאי. (2) אחנו יכולים ליצור פרוצדורה שמורה כאלמנט מערכת, אשר תחיר לנו את הנתנים בטבלה הזמנית או המשתנה הטבלאי. (3) אנחנו יכולים לגשת לתוצאות פרודורה שמורה בשאילתת טבלת צפייה על ידי שימוש ב openquery. בואו נראה את הפתרון בפועל.

צעד ראשון: יצירת פרוצדורה שמורה כאלמנט מערכת, שמחזירה את תוצאות הוראת DBCC

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_Ariely_DBCC_Page_SP
    (@_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_Ariely_DBCC_Page_SP
    @_DatabaseName = 'MSSQLTIPS',
    @_FileID = 1,
    @_PageID = 89,
    @_DescriptionLevel = 3
GO
 
-- Mark our stored procedure as system object
EXEC sp_ms_marksystemobject 'sp_Ariely_DBCC_Page_SP' 
GO


צעד שני: יצירת מסד הנתונים והטבלה להדגמה

בשלב זה אנחנו הולכים ליצור מסד נתונים חדש. במסד הנתונים החדש, ניצור טבלה חדשה ונכניס אליה כמה שורות. לאחר מכן נבחן את הדפים שמשמשים את הטבלה לאחסון המידע, באמצעות הוראת DBCC IDN. בצעד הבא נשתמש במידע שקיבלנו בכאן , כדי ליצור את טבלת התצוגה אשר תחזיר את התוצאה של הפעלת הוראת DBCC PAGE על הדף שנרצה לחקור. 

שים לב! אנו יכולים להשתמש באותו הטריק ולבנות SP שיחזיר את תוצאת DBCC IDN או אפילו לשלב SP שיבדוק DBCC IDN ולאחר מכן יבצע חקירה של העמודים המתאימים באמצעות DBCC PAGE.


------------------------------------------------------------ DDL
USE MASTER
GO
 
CREATE DATABASE Ariely_DBCC_Page_DB
GO
 
USE Ariely_DBCC_Page_DB
GO
 
CREATE TABLE Ariely_DBCC_Page_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 Ariely_DBCC_Page_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 Ariely_DBCC_Page_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('Ariely_DBCC_Page_DB',Ariely_DBCC_Page_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('Ariely_DBCC_Page_DB.DBO.Ariely_DBCC_Page_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('Ariely_DBCC_Page_DB',1,120,3) WITH TABLERESULTS
GO


צעד שלישי: שימוש בפרוצדורה השמורה בטבלת צפייה על מנת להציג את תוצאות הוראת DBCC.

עתה אנחנו יכולים ליצור טבלת צפייה חדשה בכל אחד ממסדי הנתונים שלנו (נ\דגים את הנושא במסד הנתונים החדש כמובן) ולהציג את תוצאות הוראת DBCC על ידי שימוש בפרוצדורה שהכנו בצעד הראשון.

-- Move to any local database
Use Ariely_DBCC_Page_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_Ariely_DBCC_Page_SP
        @_DatabaseName = ''Ariely_DBCC_Page_DB'',
        @_FileID = 1,
        @_PageID = 120,
        @_DescriptionLevel = 3'
)
GO
 
-- That is all :-) Now we can use our DBCC in a view
create view Ariely_DBCC_Page_View as
    select * from openquery(
        [use our server name from previous query],
        'master.dbo.sp_Ariely_DBCC_Page_SP
            @_DatabaseName = ''MSSQLTIPS'',
            @_FileID = 1,
            @_PageID = 89,
            @_DescriptionLevel = 3'
    )
go
 
-- Check our view
select * from Ariely_DBCC_Page_View
 
-- clean local database (we do not clean the system SP for future use)
drop view Ariely_DBCC_Page_View


והנה תוצאת הרצת שאילתת בחירה מטבלת הצפייה שלנו :-)

.