리소스 잠금 및 리소스 확인

-- 출처 : SQLTAG BOOK2 

CREATE TABLE dbo.TEST_RESOURCE

( COL1 int NOT NULL

, COL2 varchar(100) NULL

, COL3 smallint NOT NULL

, COL4 varchar(10) SPARSE NULL

, COL5 char(10) NOT NULL

, COL6 char(30) NULL

, COL7 varchar(max) NOT NULL

)

GO

INSERT dbo.TEST_RESOURCE

( COL1

, COL2

, COL3

, COL4

, COL5

, COL6

, COL7

)

VALUES

(1,'A',1,'AA','A','A',REPLICATE(CONVERT(varchar(max),'A'),10000)),

(2,'BB',2,'BBB','BB','BB',REPLICATE(CONVERT(varchar(max),'A'),300)),

(3,'CCC',30,'CCCC','CCC','CCC',REPLICATE(CONVERT(varchar(max),'A'),10))

GO

ALTER TABLE dbo.TEST_RESOURCE

ADD CONSTRAINT PK_TEST_RESOURCE

PRIMARY KEY CLUSTERED

( COL3

)

GO

CREATE UNIQUE NONCLUSTERED INDEX UNCIDX_TEST_RESOURCE

ON dbo.TEST_RESOURCE

( COL1

)

GO

-- 1. Partition Id 확인(PK_TEST_RESOURCE)

SELECT %%ROWDUMP%% AS [ROWDUMP]

, sys.fn_GetRowsetIdFromRowDump(%%ROWDUMP%%) AS PARITION_ID

FROM dbo.TEST_RESOURCE

GO

-- 2. Partition Id 확인(UNCIDX_TEST_RESOURCE)

SELECT %%ROWDUMP%% AS [ROWDUMP]

, sys.fn_GetRowsetIdFromRowDump(%%ROWDUMP%%) AS PARITION_ID

FROM dbo.TEST_RESOURCE WITH(INDEX(UNCIDX_TEST_RESOURCE))

GO

-- 3. 가변 길이 칼럼의 행 정보 확인

SELECT %%ROWDUMP%% AS [ROWDUMP]

, RDC.*

FROM dbo.TEST_RESOURCE AS TR

CROSS APPLY

sys.fn_RowDumpCracker(%%ROWDUMP%%) AS RDC

GO

-- 3. File Id, Page Id, Slot Id, KeyHashValue 확인(PK_TEST_RESOURCE)

SELECT %%PHYSLOC%% AS PHYSLOC

, sys.fn_PhysLocFormatter(%%PHYSLOC%%) AS [FILEID:PAGEID:SLOTID]

, PLC.*

, %%LOCKRES%% AS [LOCKRES]

FROM dbo.TEST_RESOURCE AS TR

CROSS APPLY

sys.fn_PhysLocCracker(%%PHYSLOC%%) AS PLC

GO

-- 4. File Id, Page Id, Slot Id, KeyHashValue 확인(UNCIDX_TEST_RESOURCE)

SELECT %%PHYSLOC%% AS PHYSLOC

, sys.fn_PhysLocFormatter(%%PHYSLOC%%) AS [FILEID:PAGEID:SLOTID]

, PLC.*

, %%LOCKRES%% LOCKRES

FROM dbo.TEST_RESOURCE AS TR WITH(INDEX(UNCIDX_TEST_RESOURCE))

CROSS APPLY

sys.fn_PhysLocCracker(%%PHYSLOC%%) AS PLC

GO