Contents

1 sp_AllocationMetadata
2 Null Bitmap Size


1 sp_AllocationMetadata #

USE master;
GO

IF OBJECT_ID ('sp_AllocationMetadata') IS NOT NULL
   DROP PROCEDURE sp_AllocationMetadata;
GO

CREATE PROCEDURE sp_AllocationMetadata
(
   @object VARCHAR (128) = NULL
)
AS
SELECT
   OBJECT_NAME (sp.object_id) AS [Object Name],
   sp.index_id AS [Index ID],
   sa.allocation_unit_id AS [Alloc Unit ID],
   sa.type_desc AS [Alloc Unit Type],
   '(' + CONVERT (VARCHAR (6),
      CONVERT (INT, SUBSTRING (sa.first_page, 6, 1) +
         SUBSTRING (sa.first_page, 5, 1))) +
   ':' + CONVERT (VARCHAR (20),
      CONVERT (INT, SUBSTRING (sa.first_page, 4, 1) +
         SUBSTRING (sa.first_page, 3, 1) +
         SUBSTRING (sa.first_page, 2, 1) +
         SUBSTRING (sa.first_page, 1, 1))) +
   ')' AS [First Page],
   '(' + CONVERT (VARCHAR (6),
      CONVERT (INT,
         SUBSTRING (sa.root_page, 6, 1) +
         SUBSTRING (sa.root_page, 5, 1))) +
   ':' + CONVERT (VARCHAR (20),
      CONVERT (INT,
         SUBSTRING (sa.root_page, 4, 1) +
         SUBSTRING (sa.root_page, 3, 1) +
         SUBSTRING (sa.root_page, 2, 1) +
         SUBSTRING (sa.root_page, 1, 1))) +
   ')' AS [Root Page],
   '(' + CONVERT (VARCHAR (6),
      CONVERT (INT,
         SUBSTRING (sa.first_iam_page, 6, 1) +
         SUBSTRING (sa.first_iam_page, 5, 1))) +
   ':' + CONVERT (VARCHAR (20),
      CONVERT (INT,
         SUBSTRING (sa.first_iam_page, 4, 1) +
         SUBSTRING (sa.first_iam_page, 3, 1) +
         SUBSTRING (sa.first_iam_page, 2, 1) +
         SUBSTRING (sa.first_iam_page, 1, 1))) +
   ')' AS [First IAM Page] 
FROM
   sys.system_internals_allocation_units AS sa,
   sys.partitions AS sp
WHERE
   sa.container_id = sp.partition_id
   AND sp.object_id =
      (CASE WHEN (@object IS NULL)
         THEN sp.object_id
         ELSE OBJECT_ID (@object)
      END);
GO

EXEC sys.sp_MS_marksystemobject sp_AllocationMetadata;
GO

2 Null Bitmap Size #

use tempdb
go

CREATE TABLE t1 (
    c1 INT, c2 INT, c3 INT, c4 INT, c5 INT,
    c6 INT, c7 INT, c8 INT, c9 INT, c10 INT);
GO 

CREATE TABLE t2 (
    c1 INT NOT NULL, c2 INT NOT NULL, c3 INT NOT NULL,
    c4 INT NOT NULL, c5 INT NOT NULL, c6 INT NOT NULL,
    c7 INT NOT NULL, c8 INT NOT NULL, c9 INT NOT NULL,
    c10 INT);
GO 

INSERT INTO t1 VALUES (1, 2, 3, 4, 5, 6, 7, 8, 9, 10);
GO 

INSERT INTO t2 VALUES ( 1, 2, 3, 4, 5, 6, 7, 8, 9, NULL);
GO 

EXEC sp_AllocationMetadata 't1';
EXEC sp_AllocationMetadata 't2';

蟆郁骸
Object Name      Index ID        Alloc Unit ID Alloc Unit Type First Page  Root Page First IAM Page
------------- ----------- -------------------- --------------- ----------- --------- --------------
t1                      0    72057649233985536 IN_ROW_DATA     (4:82)      (0:0)     (3:278)


Object Name      Index ID        Alloc Unit ID Alloc Unit Type First Page  Root Page First IAM Page
------------- ----------- -------------------- --------------- ----------- --------- --------------
t2                      0    72057649234051072 IN_ROW_DATA     (1:4461784) (0:0)     (5:520217)

DBCC PAGE ({dbid|dbname}, filenum, pagenum, print option)
  • dbid|dbname - 伎襯 螻 db id 企
  • filenum - 伎襯 螻 殊 覯
  • pagenum - 朱伎 伎 覯
  • print option - 豢レ旧
    • 0 - 覯 る 伎 る襯 豢(吴)
    • 1 - 覯 る, 伎 る, 螳 , ろ 企れ 豢
    • 2 - 覯 覦 伎 る, 伎 豌, ろ 企れ 豢
    • 3 - 覯 る, 伎 る, 螳, ろ 企れ 豢


dbcc traceon(3604)
dbcc page (tempdb, 4, 82, 3)

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x6106C060

00000000: 10002c00 01000000 02000000 03000000 ..,.............
00000010: 04000000 05000000 06000000 07000000 ................
00000020: 08000000 09000000 0a000000 0a0000fc ................

dbcc page (tempdb, 1, 4461784, 3)

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x5F06C060

00000000: 10002c00 01000000 02000000 03000000 ..,.............
00000010: 04000000 05000000 06000000 07000000 ................
00000020: 08000000 09000000 0a000000 0a0000fe ................

dbcc traceoff(3604)

蟆郁骸伎
  • 0a0000fc -> 朱 0xfc 0x00 0x00 0x0a
  • 0xfc 0x00 --> 1111110000000000 --> 1 ~ 10 貉殊 Null 螻, 6bit 讌 .
  • 0xfe 0x00 --> 1111111000000000 --> t2.c10 貉殊 Null企襦 10覯讌 bit 1企.


蠏碁? 覘.. 伎手..
願碓 覃 覘 ?
讌伎 企..