01.データ取得-00.環境準備.sql

IF DB_ID('DemoDB') IS NULL
BEGIN
    CREATE DATABASE DemODB
END
GO

USE DemoDB
GO

DROP TABLE IF EXISTS DemoTBL
CREATE TABLE DemoTBL(
    C1 int identity,
    C2 float DEFAULT RAND() * 10,
    C3 varchar(36) DEFAULT NEWID(),
    C4 char(1000) DEFAULT NEWID(),
    CONSTRAINT PK_DemoTBL PRIMARY KEY CLUSTERED (C1)
)
CREATE INDEX NCIX_DeomTBL ON DemoTBL(C2) INCLUDE(C3)

SET NOCOUNT ON
GO

INSERT INTO DemoTBL DEFAULT VALUES
GO 1000

CHECKPOINT
GO

SET NOCOUNT ON
GO

DROP TABLE IF EXISTS FragmentTBL
CREATE TABLE FragmentTBL(
    C1 int identity,
    C2 float DEFAULT RAND() * 10,
    C3 varchar(300) DEFAULT NEWID(),
    C4 char(1000) DEFAULT NEWID(),
    CONSTRAINT PK_FragmentTBL PRIMARY KEY CLUSTERED (C1)
)
GO

INSERT INTO FragmentTBL DEFAULT VALUES
GO 1000
ALTER TABLE FragmentTBL REBUILD

CHECKPOINT

01.データ取得-01.SELECT の動作確認.sql

DBCC DROPCLEANBUFFERS
GO

-- クラスター化インデックスを使用した検索 (実行プランを取得する)
SET STATISTICS IO, TIME ON
SELECT TOP 10 * FROM DemoTBL WHERE C1 = 125
GO

-- DemoTBL のページ割り当ての確認
SELECT 
    OBJECT_NAME(object_id) AS object_name,
    index_id,
    allocated_page_iam_page_id,
    is_iam_page,
    allocated_page_file_id,
    extent_page_id,
    allocated_page_page_id,
    is_allocated,
    page_type_desc,
    page_level,
    next_page_page_id,
    previous_page_page_id
FROM 
    sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID('DemoTBL'), NULL, NULL, 'DETAILED')
GO

-- インデックスの確認
SELECT 
    OBJECT_NAME(object_id) AS object_name,
    index_id,
    allocated_page_iam_page_id,
    is_iam_page,
    allocated_page_file_id,
    extent_page_id,
    allocated_page_page_id,
    is_allocated,
    page_type_desc,
    page_level,
    next_page_page_id,
    previous_page_page_id

FROM 
    sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID('DemoTBL'), NULL, NULL, 'DETAILED')
WHERE 
    page_type_desc = 'INDEX_PAGE'
GO

-- クラスター化インデックスの確認
DBCC TRACEON(3604)

-- インデックスページの確認
DBCC PAGE('DemoDB', 1, 353, 3)


-- インデックス内の子ページの確認
DBCC PAGE('DemoDB', 1, 496, 3)


-- 非クラスター化インデックスを使用した検索
DECLARE @C2 float = (SELECT C2 FROM DemoTBL WHERE C1 = 125)
SELECT @C2
SELECT * FROM DemoTBL WHERE C2 = @C2

-- DemoTBL のページ割り当ての確認
SELECT 
    OBJECT_NAME(object_id) AS object_name,
    index_id,
    allocated_page_iam_page_id,
    is_iam_page,
    allocated_page_file_id,
    extent_page_id,
    allocated_page_page_id,
    is_allocated,
    page_type_desc,
    page_level,
    next_page_page_id,
    previous_page_page_id

FROM 
    sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID('DemoTBL'), NULL, NULL, 'DETAILED')
WHERE 
    page_type_desc = 'INDEX_PAGE'
GO

-- インデックスページの確認
DBCC PAGE('DemoDB', 1, 353, 3)

-- インデックス内の子ページの確認
DBCC PAGE('DemoDB', 1, 355, 3) WITH TABLERESULTS


-- 非クラスター化インデックスを使用した検索
DECLARE @C2 float = (SELECT C2 FROM DemoTBL WHERE C1 = 125)
SELECT @C2
SELECT *,%%lockres%%
FROM DemoTBL 
OUTER APPLY sys.fn_PhysLocCracker(%%physloc%%)
WHERE C2 = @C2

01.データ取得-02.ロックの確認.sql

-- 拡張イベントの有効化とライブデータの表示を有効化
/*
CREATE EVENT SESSION [Lock Info] ON SERVER 
ADD EVENT sqlserver.lock_acquired(SET collect_database_name=(1),collect_resource_description=(1)
    ACTION(sqlserver.client_app_name,sqlserver.session_id,sqlserver.sql_text)
    WHERE ([package0].[equal_uint64]([sqlserver].[session_id],(59)))),
ADD EVENT sqlserver.lock_released(SET collect_database_name=(1),collect_resource_description=(1)
    ACTION(sqlserver.client_app_name,sqlserver.session_id,sqlserver.sql_text)
    WHERE ([package0].[equal_uint64]([sqlserver].[session_id],(59))))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=NO_EVENT_LOSS,MAX_DISPATCH_LATENCY=5 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO
*/
-- Session ID を Current Session に変更しておく


-- 2 回目の実行の情報で確認 (コンパイルなしで比較)
-- (想定したロックになっていない場合は SELECT の C1 を変更して別ページを読む or CheckPoint)
DBCC FREEPROCCACHE
CHECKPOINT
DBCC DROPCLEANBUFFERS
SELECT OBJECT_ID('DemoTBL')

SELECT * FROM DemoTBL WHERE C1 = 200
SELECT * FROM DemoTBL WITH(NOLOCK) WHERE C1 = 200
SELECT * FROM DemoTBL WHERE C1 = 200 OPTION(RECOMPILE)
GO

-- 他のセッションで次のクエリを実行し、SELECT を実行し、取得されるロックを確認
BEGIN TRAN
UPDATE DemoTBL SET C3 = NEWID()WHERE C1 = 201
ROLLBACK TRAN

SELECT * FROM DemoTBL CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%) WHERE C1 = 200

SELECT * FROM sys.dm_os_buffer_descriptors 
WHERE database_id = DB_ID() AND is_modified = 1
ORDER BY page_id ASC

01.データ取得-03.ロックの取得の別ケース.sql

USE DemoDB
-- Repeatable Read で SELECT した状態でトランザクションを継続し、別セッションでALTER を実行
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
SELECT * FROM LockTEST01
SELECT request_mode,resource_lock_partition FROM sys.dm_tran_locks 
WHERE request_session_id = @@spid AND resource_lock_partition IS NOT NULL
ROLLBACK TRAN

-- 別セッションで実行
ALTER INDEX NCIX_LockTEST02 ON LockTEST01
REBUILD WITH(ONLINE=ON)
/*
REBUILD WITH(ONLINE=ON 
    (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 10 MINUTES, ABORT_AFTER_WAIT  = SELF))
)
*/

-- ブロッキング情報の取得
select * from sys.dm_os_waiting_tasks where blocking_session_id IS NOT NULL


-- ブロッキングチェーンの取得

-- SQL Query Stress 実行後に情報を取得
SELECT * FROM LockTEST01

select * from sys.dm_tran_locks
where request_session_id = @@SPID OR request_status = 'WAIT' 
OR request_mode IN('SCH-M', 'IS', 'SCH-S')
ORDER BY request_session_id ASC, resource_lock_partition ASC


ORDER BY request_session_id, resource_lock_partition

02.データ追加-01 INSERT の基本動作.sql

-- トランザクションログの解放
CHECKPOINT
SELECT * FROM sys.fn_dblog(NULL, NULL)

-- データの追加
INSERT INTO DemoTBL DEFAULT VALUES

--　トランザクションログの確認
SELECT * FROM sys.fn_dblog(NULL, NULL)

-- INSERT のログの確認
SELECT
    *
FROM 
    sys.fn_dblog(NULL, NULL)
WHERE
    [Transaction ID] =
    (SELECT  [Transaction ID] FROM sys.fn_dblog(NULL, NULL) WHERE [Transaction Name] = 'INSERT')


-- ページの確認
SELECT
    Operation,
    [Page ID],
    CONVERT(int, CONVERT(varbinary(20), '0x' +RIGHT([Page ID], LEN([Page ID]) - CHARINDEX(':', [Page ID])), 1), 1) AS [Page No],
    [Slot ID],
    [Lock Information],
    AllocUnitName
FROM 
    sys.fn_dblog(NULL, NULL)
WHERE
    [Transaction ID] IN (SELECT  [Transaction ID] FROM sys.fn_dblog(NULL, NULL) WHERE [Transaction Name] = 'INSERT')
    AND
    [Page ID] IS NOT NULL
GO

-- メモリの確認
SELECT * FROM sys.dm_os_buffer_descriptors  
WHERE database_id = DB_ID() AND
page_id IN(
    SELECT
        CONVERT(int, CONVERT(varbinary(20), '0x' +RIGHT([Page ID], LEN([Page ID]) - CHARINDEX(':', [Page ID])), 1), 1) AS [Page No]
    FROM 
        sys.fn_dblog(NULL, NULL)
    WHERE
        [Transaction ID] IN (SELECT  [Transaction ID] FROM sys.fn_dblog(NULL, NULL) WHERE [Transaction Name] = 'INSERT')
        AND
        [Page ID] IS NOT NULL
)

-- チェックポイント実行前にページ No を控えておく
/*
Page No
543
356
*/

-- チェックポイント後の状態の確認
CHECKPOINT
GO

-- メモリの確認
SELECT 
    * 
FROM sys.dm_os_buffer_descriptors
WHERE 
    database_id = DB_ID() AND page_id IN(544, 356)

03.データ削除-01.DELETE の基本動作.sql

CHECKPOINT

-- ページ情報を取得し、以下の DBCC コマンドに指定しておく
SELECT * FROM DemoTBL 
OUTER APPLY sys.fn_PhysLocCracker(%%physloc%%)
WHERE C1 = 150

DBCC TRACEON(3604)
DBCC PAGE('DemODB', 1, 382, 3)


-- データの削除
DELETE FROM DemoTBL WHERE C1 = 150
DBCC PAGE('DemODB', 1, 382, 3)

-- ゴーストレコードとしてのマーク
SELECT * FROM sys.fn_dblog(NULL, NULL)
WHERE [Transaction ID] IN(
    SELECT [Transaction ID] FROM sys.fn_dblog(NULL, NULL) WHERE [Transaction Name] = 'DELETE'
)


-- ゴーストクリーンアップ
SELECT * FROM sys.dm_os_wait_stats where wait_type LIKE 'GHOST%'

04.データ更新-01.UPDATE の基本動作.sql

CHECKPOINT

-- 更新を実行し、トランザクションログを確認
UPDATE DemoTBL SET C3 = NEWID() WHERE C1 = 12
SELECT * FROM sys.fn_dblog(NULL, NULL)
WHERE [Transaction ID] IN (SELECT [Transaction ID] FROM  sys.fn_dblog(NULL, NULL) WHERE [Transaction Name] = 'UPDATE')


-- 同一の ID で 2 回更新
CHECKPOINT

DECLARE @id uniqueidentifier = NEWID()
SELECT @id
UPDATE DemoTBL SET C3 = @id WHERE C1 = 12
UPDATE DemoTBL SET C3 = @id WHERE C1 = 12
GO


SELECT * FROM sys.fn_dblog(NULL, NULL)
WHERE [Transaction ID] IN (SELECT [Transaction ID] FROM  sys.fn_dblog(NULL, NULL) WHERE [Transaction Name] = 'UPDATE')

UPDATE DemoTBL SET C3 = 'D' WHERE C1 = 12

SELECT * FROM sys.fn_dblog(NULL, NULL)
WHERE [Transaction ID] IN (SELECT [Transaction ID] FROM  sys.fn_dblog(NULL, NULL) WHERE [Transaction Name] = 'UPDATE')

05.断片化-01.断片化の基本動作.sql

-- FragmentTBL のページ割り当ての確認 (ページのチェーンを確認)
SELECT 
    OBJECT_NAME(object_id) AS object_name,
    index_id,
    allocated_page_iam_page_id,
    is_iam_page,
    allocated_page_file_id,
    extent_page_id,
    allocated_page_page_id,
    is_allocated,
    page_type_desc,
    page_level,
    next_page_page_id,
    previous_page_page_id
FROM 
    sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID('FragmentTBL'), NULL, NULL, 'DETAILED')
GO

SELECT * FROM FragmentTBL
OUTER APPLY sys.fn_PhysLocCracker(%%physloc%%)

BEGIN TRAN PAGE_SPLIT
UPDATE FragmentTBL SET C3 =REPLICATE('A', 300) WHERE C1 BETWEEN 1 AND 3
ROLLBACK TRAN

SELECT * FROM FragmentTBL
OUTER APPLY sys.fn_PhysLocCracker(%%physloc%%)

-- FragmentTBL のページ割り当ての確認 (ページのチェーンが変わる)
SELECT 
    OBJECT_NAME(object_id) AS object_name,
    index_id,
    allocated_page_iam_page_id,
    is_iam_page,
    allocated_page_file_id,
    extent_page_id,
    allocated_page_page_id,
    is_allocated,
    page_type_desc,
    page_level,
    next_page_page_id,
    previous_page_page_id
FROM 
    sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID('FragmentTBL'), NULL, NULL, 'DETAILED')
GO

-- 変更のトランザクションの取得
SELECT * FROM sys.fn_dblog(NULL, NULL) WHERE [Transaction ID] IN(
SELECT [Transaction ID] FROM sys.fn_dblog(NULL, NULL) WHERE [Transaction Name] = 'PAGE_SPLIT'
)

-- ページ分割の実際の動作
SELECT * FROM sys.fn_dblog(NULL, NULL) WHERE [Current LSN] >=
(SELECT [Current LSN] FROM sys.fn_dblog(NULL, NULL) WHERE [Transaction Name] = 'PAGE_SPLIT')


-- 断片化の影響
DBCC TRACEON(3604)
DBCC PAGE('DemoDB', 1, 704, 3)

-- インデックスの再構築
ALTER TABLE FragmentTBL REBUILD
GO

-- 再構築後のページの状態
SELECT 
    OBJECT_NAME(object_id) AS object_name,
    index_id,
    allocated_page_iam_page_id,
    is_iam_page,
    allocated_page_file_id,
    extent_page_id,
    allocated_page_page_id,
    is_allocated,
    page_type_desc,
    page_level,
    next_page_page_id,
    previous_page_page_id

FROM 
    sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID('FragmentTBL'), NULL, NULL, 'DETAILED')

SELECT * FROM FragmentTBL
OUTER APPLY sys.fn_PhysLocCracker(%%physloc%%)

GO

06.待機事象-01.待機事象.sql

-- 実行プランから、待機事象を取得
USE [master]
GO
ALTER DATABASE [tpch] SET COMPATIBILITY_LEVEL = 110
GO


DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
EXEC Q2
GO

ALTER DATABASE [tpch] SET COMPATIBILITY_LEVEL = 150
GO

-- クエリストアから待機事象を取得
SELECT
    si.start_time,
    sp.query_id,
    st.query_sql_text,
    ws.wait_category_desc,
    ws.execution_type_desc,
    ws.total_query_wait_time_ms,
    ws.avg_query_wait_time_ms,
    ws.max_query_wait_time_ms
FROM 
    sys.query_store_wait_stats AS ws
    INNER JOIN sys.query_store_runtime_stats_interval AS si
        ON si.runtime_stats_interval_id = ws.runtime_stats_interval_id
        AND si.start_time >= DATEADD(MINUTE, -60, GETUTCDATE())
    INNER JOIN sys.query_store_plan AS sp
        ON sp.plan_id = ws.plan_id
    INNER JOIN sys.query_store_query AS sq
        ON sq.query_id = sp.query_id
    INNER JOIN sys.query_store_query_text AS st
        ON st.query_text_id = sq.query_text_id
GO

-- sqlcmd で接続してタイムアウトを発生させてから確認


-- 待機事象の可視化
SELECT
    start_time,
    query_id,
    execution_type_desc,
    query_sql_text,
    ISNULL([Unknown], 0) AS [Unknown],
    ISNULL([CPU], 0) AS [CPU],
    ISNULL([Worker Thread], 0) AS [Worker Thread],
    ISNULL([Lock], 0) AS [Lock],
    ISNULL([Latch], 0) AS [Latch],
    ISNULL([Buffer Latch], 0) AS [Buffer Latch],
    ISNULL([Buffer IO], 0) AS [Buffer IO],
    ISNULL([Compilation], 0) AS [Compilation],
    ISNULL([SQL CLR], 0) AS [SQL CLR],
    ISNULL([Mirroring], 0) AS [Mirroring],
    ISNULL([Transaction], 0) AS [Transaction],
    ISNULL([Idle], 0) AS [Idle],
    ISNULL([Preemptive], 0) AS [Preemptive],
    ISNULL([Service Broker], 0) AS [Service Broker],
    ISNULL([Tran Log IO], 0) AS [Tran Log IO],
    ISNULL([Network IO], 0) AS [Network IO],
    ISNULL([Parallelism], 0) AS [Parallelism],
    ISNULL([Memory], 0) AS [Memory],
    ISNULL([User Wait], 0) AS [User Wait],
    ISNULL([Tracing], 0) AS [Tracing],
    ISNULL([Full Text Search], 0) AS [Full Text Search],
    ISNULL([Other Disk IO], 0) AS [Other Disk IO],
    ISNULL([Replication], 0) AS [Replication],
    ISNULL([Log Rate Governor], 0) AS [Log Rate Governor]
FROM
(
    (
    SELECT
        si.start_time,
        sp.query_id,
        st.query_sql_text,
        ws.execution_type_desc,
        ws.wait_category_desc,
        ws.avg_query_wait_time_ms
    FROM 
        sys.query_store_wait_stats AS ws
        INNER JOIN sys.query_store_runtime_stats_interval AS si
            ON si.runtime_stats_interval_id = ws.runtime_stats_interval_id
            AND si.start_time >= DATEADD(MINUTE, -60, GETUTCDATE())
        INNER JOIN sys.query_store_plan AS sp
            ON sp.plan_id = ws.plan_id
        INNER JOIN sys.query_store_query AS sq
            ON sq.query_id = sp.query_id
        INNER JOIN sys.query_store_query_text AS st
            ON st.query_text_id = sq.query_text_id
    )
) AS T
PIVOT(
    MAX(avg_query_wait_time_ms)
    FOR wait_category_desc IN([Unknown],[CPU],[Worker Thread],[Lock],[Latch],[Buffer Latch],[Buffer IO],[Compilation],[SQL CLR],[Mirroring],[Transaction],[Idle],[Preemptive],[Service Broker],[Tran Log IO],[Network IO],[Parallelism],[Memory],[User Wait],[Tracing],[Full Text Search],[Other Disk IO],[Replication],[Log Rate Governor])
) AS PVT
GO

06.待機事象-02.並列クエリ.sql

-- 並列クエリ
use tpch
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
EXEC sp_configure 'show advanced options', 1
RECONFIGURE

EXEC sp_configure 'max degree of parallelism', 1
RECONFIGURE

EXEC  Q1

EXEC sp_configure 'max degree of parallelism', 0
RECONFIGURE


-- 実行中に別のセッションで情報を取得
DECLARE @session_id int = 54

SELECT
    session_id,
    blocking_session_id,
    status,
    command,
    wait_type,
    wait_time,
    last_wait_type,
    cpu_time,
    total_elapsed_time,
    reads,
    writes,
    logical_reads
FROM
    sys.dm_exec_requests
WHERE
    session_id = @session_id
GO

DECLARE @session_id int = 54
select
    er.session_id, 
    oth.os_thread_id,
    ot.exec_context_id,
    er.status, er.command, er.wait_type, er.wait_time, er.last_wait_type,
    er.cpu_time,oth.status,oth.kernel_time, oth.usermode_time,
    er.total_elapsed_time,er.granted_query_memory,
    er.reads,er.logical_reads, er.writes,
    ot.task_state,
    ow.context_switch_count,ow.last_wait_type,ow.state,
    owt.resource_description
from 
    sys.dm_exec_sessions  AS es
    INNER JOIN sys.dm_exec_requests AS er
        on er.session_id = es.session_id
    LEFT JOIN sys.dm_os_waiting_tasks AS owt
        ON owt.session_id = es.session_id
    LEFT JOIN sys.dm_os_tasks AS ot
        ON ot.session_id = es.session_id
    LEFT JOIN sys.dm_os_workers AS ow
        ON ow.worker_address = ot.worker_address
    LEFT JOIN sys.dm_os_threads AS oth
        ON oth.thread_address = ow.thread_address
where 
    es.session_id = @session_id
order by
    ot.exec_context_id asc
GO

DECLARE @session_id int = 54
SELECT * FROM sys.dm_exec_session_wait_stats
WHERE session_id = @session_id
GO


DECLARE @session_id int = 54
DECLARE @pvt varchar(max) = (
    SELECT STUFF(C1,1, 2,'') + ']'
    FROM
    (
    SELECT
        DISTINCT
        '],[' + wait_type
    from
        sys.dm_exec_session_wait_stats
    where
        session_id = @session_id
    FOR XML PATH('')
    ) AS T(C1)
)
DECLARE @sql nvarchar(max) = N'
SELECT
    *
FROM
(
    select 
        session_id,
        getdate() as collect_date,
        wait_type,
        wait_time_ms
    from 
        sys.dm_exec_session_wait_stats
    where session_id = ' + cast(@session_id as varchar(10))+ '
) AS T
PIVOT(
    MAX(wait_time_ms)
    FOR wait_type IN(' + @pvt + ')
) AS PVT
'
 
EXECUTE (@sql)
GO

DECLARE @session_id int = 54
SELECT * FROM sys.dm_exec_query_profiles WHERE session_id = @session_id

インデックスアクセス

-- インデックス / 列の情報を取得
DECLARE @object_name varchar(128) = 'Sales.Orders'

SELECT 
    OBJECT_SCHEMA_NAME(ic.object_id) AS schema_name,
    OBJECT_NAME(ic.object_id) AS object_name,
    ic.index_id,
    i.name,
    ic.index_column_id,
    c.name
FROM 
    sys.index_columns AS ic
    INNER JOIN sys.columns AS c
        ON c.object_id = ic.object_id AND c.column_id = ic.column_id
    INNER JOIN sys.indexes AS i
        ON i.object_id = ic.object_id AND i.index_id = ic.index_id
WHERE 
    ic.object_id = OBJECT_ID(@object_name)
ORDER BY
    ic.index_id, ic.index_column_id
GO

-- 統計情報の取得
DECLARE @object_name varchar(128) = 'Sales.Orders'
DECLARE @stats_id int = 2

SELECT * FROM sys.stats WHERE object_id = OBJECT_ID(@object_name)
SELECT TOP 20 * FROM sys.dm_db_stats_histogram(OBJECT_ID(@object_name), @stats_id)
GO


-- インデックスページの割り当ての確認
DECLARE @object_name varchar(128) = 'Sales.Orders'
DECLARE @index_id int = 2

SELECT * FROM sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID(@object_name), @index_id, NULL, 'DETAILED')
ORDER BY page_level DESC
GO

-- インデックスページの内部アロケーションの確認
DECLARE @object_name varchar(128) = 'Sales.Orders'

SELECT 
    OBJECT_NAME(p.object_id) AS object_name,
    p.index_id,
    iau.type_desc,
    CONVERT(int, SUBSTRING(iau.first_page, 5,1)) AS first_page_file_id,
    CONVERT(int, SUBSTRING(iau.first_page, 4,1) + SUBSTRING(iau.first_page, 3,1) + SUBSTRING(iau.first_page, 2,1) + SUBSTRING(iau.first_page, 1,1)) first_page_page_id,
    CONVERT(int, SUBSTRING(iau.root_page, 5,1)) AS root_page_file_id,
    CONVERT(int, SUBSTRING(iau.root_page, 4,1) + SUBSTRING(iau.root_page, 3,1) + SUBSTRING(iau.root_page, 2,1) + SUBSTRING(iau.root_page, 1,1)) root_page_page_id,
    CONVERT(int, SUBSTRING(iau.first_iam_page, 5,1)) AS first_iam_page_file_id,
    CONVERT(int, SUBSTRING(iau.first_iam_page, 4,1) + SUBSTRING(iau.first_iam_page, 3,1) + SUBSTRING(iau.first_iam_page, 2,1) + SUBSTRING(iau.first_iam_page, 1,1)) first_iam_page_page_id
FROM 
    sys.system_internals_allocation_units AS iau
    INNER JOIN sys.partitions AS p
        ON p.partition_id = iau.container_id
        AND p.object_id = OBJECT_ID(@object_name)
GO

-- ページの確認
DBCC TRACEON(3604)

-- IAM
DECLARE @iam_page int = 1517

DBCC PAGE('WideWorldImporters', 3, @iam_page, 3)

-- Root
DROP TABLE IF EXISTS #t1
CREATE TABLE #t1(
    FileId int,
    PageId int,
    Row int,
    Level int,
    ChildFileId int,
    ChildPageId int,
    CustomerId int,
    OrderId int,
    KeyHashValue varchar(50),
    RowSize int
)
INSERT INTO #t1 EXECUTE ('DBCC PAGE(''WideWorldImporters'', 3, 1553, 3)')
GO


SELECT * FROM #t1 WHERE CustomerId <= 10


DBCC PAGE('WideWorldImporters', 3, 10978, 3)


-- Data Page
-- http://jongurgul.com/blog/sql-servers-virtual-columns-row-cracking/
SELECT 
    TOP 10 
    %%physloc%%, 
    %%lockres%% ,
    %%rowdump%%,
    sys.fn_PhysLocFormatter(%%physloc%%),
    sys.fn_GetRowsetIdFromRowDump(%%rowdump%%),
    plc.*,
    rdc.*,
    so.* 
FROM 
    Sales.Orders AS so
    OUTER APPLY sys.fn_PhysLocCracker(%%physloc%%) AS plc
    OUTER APPLY sys.fn_RowDumpCracker(%%rowdump%%) AS rdc
WHERE 
    OrderID = '14756'
GO
    
DBCC PAGE('WideWorldImporters', 3, 9402, 3) WITH TABLERESULTS

参考情報.txt

SQL Server ガイド
https://docs.microsoft.com/ja-jp/sql/relational-databases/sql-server-guides?view=sql-server-ver15