DBCC page parsing row data

DBCC page parsing row data

 

The DBCC page usage is detailed:

DBCC page ({' dbname ' | dbid}, FileNum, Pagenum [, printopt={0|1|2|3}])

The printopt parameter has the following meanings:

· 0-print just the page header

· 1-page header plus Per-row hex dumps anda dump of the page slot array (unless its a page that doesn ' t have one, Likeallo cation bitmaps)

· 2-page Header plus Whole page hex dump

· 3-page Header plus detailed per-rowinterpretation

1, the construction test data.

Create a table first

[SQL]

--Build the table

If object_id (' T1 ') is not null

drop table T1

Go

CREATE TABLE T1

(

ID int PRIMARY KEY,

V varchar (20)

)

INSERT INTO T1

Select 1, ' AA ' UNION ALL

Select 2, ' BB ' UNION ALL

Select 3, ' CC ' UNION ALL

Select 4, ' DD ' UNION ALL

Select 5, ' EE ' UNION ALL

Select 6, ' FF '

Open session x, and enter the following code in it:

[SQL]

--Execution order. 1

BEGIN Tran

Update T1

Set v = ' xx '

WHERE id = 3

--Execution order. 1

--Execution order. 3

Update T1

Set v = ' yy '

WHERE id = 6

--Execution order. 3

Open session Y again and enter the code inside:

[SQL]

--Execution order. 2

BEGIN Tran

Update T1

Set v = ' mm '

WHERE id = 6

--Execution order. 2

--Execution order. 4

Update T1

Set v = ' nn '

WHERE id = 3

--Execution order. 4

Then, follow the execution order 1 in session x, then execute sequence 2 in session y, execute sequence 3 in session x, execute sequence 4 in session y, and step through.

After performing these 4 steps, in session X, a deadlock is displayed and session X's transactions are rolled back:

At the same time, you can see the deadlock that is monitored in SQL Profiler:

From this diagram, we can see the detailed deadlock information, the forked representation of the conversation being rolled back, and the mouse over the ellipse, which shows the SQL statement that caused the deadlock, the session is running.

In a rectangular box, you can see two sessions to get an X lock, the session on the left has the key lock in the box below, and the session on the right has the key lock on it, and when the left session wants to get the key lock on it, it is blocked, and when the right session wants to acquire the following key lock, it is blocked, So the whole image forms a loop, which leads to a deadlock.

2, to obtain more detailed blocking information.

By sys.dm_tran_locks, you can get more detailed blocking information.

[SQL]

Select Resource_type,

RESOURCE_DATABASE_ID,--Database ID

Resource_description,--Resource description

RESOURCE_ASSOCIATED_ENTITY_ID,--Resource association Entity ID

Request_mode,--Request mode

Request_type,--Request type

Request_status,

request_session_id,--Request session ID

Request_owner_type

From sys.dm_tran_locks

where request_session_id = 58

Parse resource_associated_entity_id value:

[SQL]

--1. The meaning of inquiring resource_associated_entity_id

SELECT *

From Sys.tables

where object_id = 837578022

/*

This ID is T1 table

Name object_id

T1 837578022

*/

--2. The meaning of inquiring resource_associated_entity_id

SELECT *

From Sys.partitions P

where p.hobt_id = 72057594041466880

/*

This is a B-tree in the T1 of the table.

partition_id object_id index_id partition_number hobt_id

72057594041466880 837578022 1 1 72057594041466880

*/

4, through the DBCC page to resolve the value of the Resource_description column in sys.dm_tran_locks:

[SQL]

Resource_type resource_database_id resource_description

PAGE 10 1:188

KEY (B9B173BBE8D5)

KEY (98ec012aa510)

In order to parse resource_description values, a table and a stored procedure need to be established, noting that the 1:188 referenced in the stored procedure is the result of the sys.dm_tran_locks above:

[SQL]

--3. Create a table here to store the results of the DBCC page

if exists (SELECT * from sys.tables where name = ' Dbcc_page ')

drop table Dbcc_page

Go

CREATE TABLE Dbcc_page

(

ParentObject varchar (500),

Object varchar (2000),

Field varchar (1000),

Value nvarchar (max)

)

Go

--Create a stored procedure

if exists (SELECT * from sys.procedures where name = ' Proc_dbcc_page ')

drop procedure Proc_dbcc_page

Go

CREATE PROCEDURE Proc_dbcc_page

As

DBCC page (10,--Database Id:10

1,--File Id:1

188,--page id:188

3) with Tableresults

Go

INSERT INTO Dbcc_page

EXEC proc_dbcc_page

Go

Finally, let's look at what line of data (B9B173BBE8D5) and (98ec012aa510)

[SQL]

--Queries (B9B173BBE8D5) and (98ec012aa510), exactly which line of data

; with T

As

(

Select OBJECT,

Field,

Value

Case when charindex (' Column ', object) > 0

Then charindex (' Column ', object)

else charindex (' Offset ', object)

End as Substring_len

From Dbcc_page DP

where Object like ' slot%column% '

Or

Field = ' Keyhashvalue '

),

Tt

As

(

Select Object,

Field

Value

Cast (substring (Object,len (' Slot ') +1,substring_len-len (' Slot ')-1) as int) as row

From T

),

Ttt

As

(

Select Object,

Field

Value

Row,--the first few lines

Max (case field = ' Keyhashvalue '

Then value

Else ' "

End) over (partition by row) as Keyhashvalue

From TT

)

SELECT *

From TTT

where keyhashvalue in  (' (b9b173bbe8d5) ',  ' (98ec012aa510) ')