אוג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
והנה תוצאת הרצת שאילתת בחירה מטבלת הצפייה שלנו :-)

.