Today, I discovered the command DBCC PAGE(). I understand that it displays information about the data stored at the page level.
(This question is about SQL Server 2017 - v14.0)
So this is what I do :
- I want to get data from my table named
user - I access page number and file number of this table by using
DBCC IND()
DBCC IND('gescom', 'user', -1)
- The result of the previous command is :
PageFID PagePID IAMFID IAMPID ObjectID IndexID PartitionNumber PartitionID iam_chain_type PageType IndexLevel NextPageFID NextPagePID PrevPageFID PrevPagePID
------- ----------- ------ ----------- ----------- ----------- --------------- -------------------- -------------------- -------- ---------- ----------- ----------- ----------- -----------
4 8 NULL NULL 917578307 0 1 72057594043105280 In-row data 10 NULL 0 0 0 0
5 8 4 8 917578307 0 1 72057594043105280 In-row data 1 0 0 0 0 0
- I'm looking for the data in the second line, as it is the line with PageType = 1 (data page)
- So I use the
DBCC PAGEcommand with the following parameters : database = 'gescom', file num = 5, page num = 8, display option = 3
DBCC TRACEON(3604) -- Activate display trace
DBCC PAGE('gescom', 5, 8, 3)
- Result of the previous command is (truncated result...) :
Page @0x0000018E2D3D6000
[...]
Memory Dump @0x0000005DD33FA000
0000005DD33FA000: 01010000 08020001 00000000 00001400 00000000 ....................
0000005DD33FA014: 00000400 b1000000 8a1e3f03 08000000 05000000 ....±....?.........
0000005DD33FA028: 26000000 b00f0000 6b000000 2a100000 00000000 &...°...k...*.......
0000005DD33FA03C: 70954bd5 00000000 00000000 00000000 00000000 pKÕ................
0000005DD33FA050: 00000000 00000000 00000000 00000000 30001400 ................0...
0000005DD33FA064: b6ec768b aadc3c49 9ef64911 a2bb0766 04000003 ¶ìvªÜ<IöI.¢».f....
0000005DD33FA078: 00290031 0041004a 65616e4d 69636865 6c427570 .).1.A.JeanMichelBup
0000005DD33FA08C: 7550554f 494a6561 6e4d6940 7961686f 6f2e636f [email protected]
0000005DD33FA0A0: 6d300014 0076cf59 d2c044d1 49866422 8f4afddf m0...vÏYÒÀDÑId".Jýß
Then I finally try to update this data:
BEGIN TRAN T1
UPDATE [USER]
SET pseudo = 'newPseudo'
WHERE email LIKE '%Mi%'
Then, I want to see on which address the lock have been set so I run the following query while I don't end the previous transaction :
SELECT * FROM sys.dm_tran_locks
Here it's the result (look at the column lock_owner_address) :
resource_type resource_subtype resource_database_id resource_description resource_associated_entity_id resource_lock_partition request_mode request_type request_status request_reference_count request_lifetime request_session_id request_exec_context_id request_request_id request_owner_type request_owner_id request_owner_guid request_owner_lockspace_id lock_owner_address
------------------------------------------------------------ ------------------------------------------------------------ -------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------- ----------------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ----------------------- ---------------- ------------------ ----------------------- ------------------ ------------------------------------------------------------ -------------------- ------------------------------------ -------------------------------- ------------------
DATABASE 5 0 0 S LOCK GRANT 1 0 57 0 0 SHARED_TRANSACTION_WORKSPACE 0 00000000-0000-0000-0000-000000000000 0x0000018E3B366130:0:0 0x0000018E36E1AE80
PAGE 5 5:8 72057594043105280 0 IX LOCK GRANT 0 33554432 57 0 0 TRANSACTION 218160 00000000-0000-0000-0000-000000000000 0x0000018E3B366130:1:1 0x0000018E2D61E380
OBJECT 5 917578307 0 IX LOCK GRANT 1 33554432 57 0 0 TRANSACTION 218160 00000000-0000-0000-0000-000000000000 0x0000018E3B366130:1:1 0x0000018E2D61D9C0
Here I can't understand why the address of the object lock is different (=0x0000018E2D61D9C0) of the address previously seen with the DBCC PAGE command (= 0000005DD33FA078) ?
I see that the page number match : @0x0000018E2D3D6000 but I can't get why the object lock address don't match any address in the DBCC PAGE result...
Please tell me where I'm doing something wrong :|
Up :)