אוג17
Written by:
ronen ariely
17/08/2014 19:11 
רקע
הפעלת הוראות 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
והנה תוצאת הרצת שאילתת בחירה מטבלת הצפייה שלנו :-)
.