SQL Server 中行的物理位置

Wendelius

4.85/5 (18投票s)

2011年2月20日

CPOL

6分钟阅读

99324

243

本文介绍用于标识单个行唯一物理位置的伪列。

下载脚本 - 1.28 KB

引言

首先，一个警告：这些是 SQL Server 中未记录且不受支持的功能，因此请自行承担风险！

在 Oracle 中，每行都有一个名为 ROWID 的伪列，它会显示行在数据库文件中的物理地址。此列可用于标识单个行，即使该行没有键。根据文档，SQL Server 似乎缺少此类功能，但这并不完全正确，因为 SQL Server 也有一个标识行物理地址的机制。在 SQL Server 2008 中，这称为 %%physloc%%，在 SQL Server 2005 中，称为 %%lockres%%。本文将尝试描述此伪列在 SQL Server 2008 中的基本用法。

如何使用

首先，我们创建一个表来测试此功能。为了测试不同的用法，我们需要一个包含多行的表。

----------------------------------
-- Create test objects
----------------------------------
-- Schema
CREATE SCHEMA PhysLocTest;
GO

-- SeveralRows -table
IF OBJECT_ID ( 'PhysLocTest.SeveralRows', 'U' ) IS NOT NULL 
DROP TABLE PhysLocTest.SeveralRows;
GO

-- Create the table
CREATE TABLE PhysLocTest.SeveralRows (
   Id         int        NOT NULL IDENTITY(1,1)PRIMARY KEY,
   InsertTime date       NOT NULL DEFAULT (GETDATE()),
   Category   varchar(2) NOT NULL
);
GO

-- Fill the table with test data. Contains 100'000 rows in 11 categories
SET NOCOUNT ON
DECLARE @counter int;
BEGIN
   SET @counter = 0;
   WHILE @counter < 100000 BEGIN
      INSERT INTO PhysLocTest.SeveralRows (Category) 
      VALUES (CONVERT(varchar, ROUND( RAND(), 1) * 10 ));

      SET @counter = @counter + 1;
   END;
END;

在表创建并填充后，您可以尝试使用 %%physloc%% 伪列

-----------------------------------------
-- Find physical address of first 5 rows
-----------------------------------------
SELECT TOP(5)
       a.%%physloc%% AS Address,
       a.*
FROM   PhysLocTest.SeveralRows a
ORDER BY a.Id;

-- Results (physical locations, Id's and categories vary):
 
Address             Id  InsertTime  Category
------------------  --  ----------  --------
0xFB0D000001000000  1   2011-02-19  3
0xFB0D000001000100  2   2011-02-19  2
0xFB0D000001000200  3   2011-02-19  4
0xFB0D000001000300  4   2011-02-19  2
0xFB0D000001000400  5   2011-02-19  1

此时，您会看到每行都有一个唯一的地址。该地址实际上包含有关文件、页和行所在的槽的信息。但是，十六进制值不容易解释，因此 SQL Server 有一个名为 sys.fn_PhysLocFormatter 的函数，可以更好地可视化行的位置。

使用 sys.fn_PhysLocFormatter

此函数以物理地址作为参数，并将地址格式化为文本，以显示行的位置。

-----------------------------------------
-- Find physical address of first 5 rows
-----------------------------------------
SELECT TOP(5)
       a.%%physloc%%                          AS Address,
       sys.fn_PhysLocFormatter(a.%%physloc%%) AS AddressText,
       a.*
FROM   PhysLocTest.SeveralRows a
ORDER BY a.Id;
 

-- Results (physical locations and categories vary):

Address             AddressText  Id  InsertTime  Category
------------------  -----------  --  ----------  --------
0xFB0D000001000000  (1:3579:0)   1   2011-02-19  3
0xFB0D000001000100  (1:3579:1)   2   2011-02-19  2
0xFB0D000001000200  (1:3579:2)   3   2011-02-19  4
0xFB0D000001000300  (1:3579:3)   4   2011-02-19  2
0xFB0D000001000400  (1:3579:4)   5   2011-02-19  1

现在您有了清晰格式的物理地址。根据输出，ID 为 3 的行位于文件 1 的页面 3579 和槽 2 中。现在，您可以使用系统视图 sys.database_files 来识别行所在的实际数据文件。

-----------------------------------------
-- Find the actual database file
-----------------------------------------
SELECT df.type_desc,
       df.name,
       df.physical_name
FROM   sys.database_files df
WHERE  df.file_id = 1;
 
-- Results:
type_desc  name    physical_name
---------  ------  -------------
ROWS       Test02  C:\Program Files\Microsoft SQL Server\Inst1\MSSQL\DATA\test02.ndf

如果您想更进一步，可以使用 DBCC PAGE 来转储块的内容。为了查看 DBCC PAGE 的信息，必须打开跟踪标志 3604。DBCC PAGE 命令接受以下参数

数据库名称或数据库 ID
文件编号
页码
输出详细信息级别

0 = 标头
1 = 标头和行的十六进制转储
2 = 标头和页面转储
3 = 标头和详细行信息

-----------------------------------------
-- Get the page dump for rows
-----------------------------------------
DBCC TRACEON(3604)
DBCC PAGE (Test, 1, 3579, 1)
DBCC TRACEOFF(3604)
 
-- Results:
PAGE: (1:3579)
 
BUFFER:
BUF @0x0000000084FD4200
bpage = 0x00000000848B0000 bhash = 0x0000000000000000 bpageno = (1:3579)
bdbid = 15 breferences = 0 bcputicks = 0
bsampleCount = 0 bUse1 = 5565 bstat = 0xc0010b
blog = 0xbbbbbbbb bnext = 0x0000000000000000 
 
PAGE HEADER:
Page @0x00000000848B0000
m_pageId = (1:3579) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x0
m_objId (AllocUnitId.idObj) = 365 m_indexId (AllocUnitId.idInd) = 256 
Metadata: AllocUnitId = 72057594061848576 
Metadata: PartitionId = 72057594056081408 Metadata: IndexId = 1
Metadata: ObjectId = 1746821285 m_prevPage = (0:0) m_nextPage = (1:3582)
pminlen = 11 m_slotCnt = 384 m_freeCnt = 10
m_freeData = 7414 m_reservedCnt = 0 m_lsn = (78:1213:16)
m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0
m_tornBits = 0 
 
Allocation Status
GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED 
PFS (1:1) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED 
 
DATA:
Slot 0, Offset 0x60, Length 19, DumpStyle BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 19 
Memory Dump @0x000000001613A060
0000000000000000: 30000b00 01000000 ea330b03 00000100 † 0.......ê3...... 
0000000000000010: 130033† † † † † † † † † † † † † † † † † † † † † † † † † † † † † † † ..3 
...

这可以用来加快检索速度吗？

在 Oracle 中，ROWID 有时用于加快检索速度（尽管不推荐）。由于 ROWID 是行的物理位置，因此如果已知道 ROWID，则无需使用索引来定位行。SQL Server 是否也适用？答案很简单：不。%%physloc%% 的作用更像一个函数，因此如果它用于 SQL 语句的 WHERE 子句中，SQL Server 需要扫描行位置，然后选取匹配的位置。为了测试这一点，让我们尝试使用 ID（主键）和位置从测试表中选择单个行。

-----------------------------------------
-- Fetch the row based on primary key
-----------------------------------------
SELECT a.%%physloc%% AS Address,
       a.*
FROM   PhysLocTest.SeveralRows a
WHERE  a.Id = 4321
 

-- Results:
Address             Id    InsertTime  Category
------------------  ----  ----------  --------
0x641E000001006100  4321  2011-02-19  8
 
-- Statistics:
Table 'SeveralRows'. Scan count 0, logical reads 2, physical reads 0, 
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

以及可视化执行计划（总成本 0.0032831，索引查找估计行数 = 1）

这非常高效。当在条件中使用 ID 列时，在执行计划和统计信息中都可以清楚地看到 SQL Server 使用主键索引来获取行。那么，如果改用物理位置会发生什么？

-------------------------------------------
-- Fetch the row based on physical location
-------------------------------------------
SELECT a.%%physloc%% AS Address,
       a.*
FROM   PhysLocTest.SeveralRows a
WHERE  a.%%physloc%% = 0x641E000001006100
 
-- Results:
Address             Id    InsertTime  Category
------------------  ----  ----------  --------
0x641E000001006100  4321  2011-02-19  8
 
-- Statistics:
Table 'SeveralRows'. Scan count 1, logical reads 29, physical reads 0, 
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

以及可视化执行计划（总成本 0.323875，索引扫描估计行数 = 100'000）

现在 SQL Server 必须扫描聚集主键索引才能找到正确的物理地址。扫描次数为 1，读取了 29 页。从性能角度来看，这不是一个很好的选择。因此，我们可以安全地说，使用物理位置并不会提供任何性能改进。

标识没有键的行

物理位置的一个用例是标识表中的单个行，即使该表上没有定义键。一个相当常见的问题是：当我没有键可用时，如何删除重复的行？有几种可能性。一种是向表中添加一个键并使用它进行删除，或者创建一个临时表，使用 DISTINCT 关键字将唯一行加载到临时表中，截断原始表，然后将不同的数据重新加载回来。这两种方法都可以，但有一些问题需要考虑。

在第一种解决方案（添加键）中，表结构被更改了，实际上行被加宽了。更改结构应该是自然要避免的，当使用简单的 DML 操作（SELECT、INSERT、UPDATE）时。在第二种解决方案（使用临时表重新加载）中，所有数据都被删除，然后重新添加到原始表中。如果原始表上有触发器，这可能会导致问题和数据不一致。

在我们的测试表中，有一个主键，但让我们暂时忽略它。如果我想删除所有重复的行，以便每个类别只剩下一行怎么办？如果随机数生成器工作正常，表中应该有 11 个类别，值为 0-10。所以基本上，我必须创建一个删除语句，根据物理位置排除每个类别的一行。在下面的示例中，我决定为每个类别保留最小的位置，并删除其余的。注意：最小位置不一定是首先添加的行，这可能是一个容易误解的地方。

-------------------------------------------
-- Delete duplicates based on Category
-------------------------------------------
DELETE
FROM  PhysLocTest.SeveralRows 
WHERE PhysLocTest.SeveralRows.%%physloc%% 
NOT IN (SELECT MIN(b.%%physloc%%)
        FROM   PhysLocTest.SeveralRows b
        GROUP BY b.Category);
 
-------------------------------------------
-- Check the data
-------------------------------------------
SELECT * 
FROM   PhysLocTest.SeveralRows a
ORDER BY a.Category;
 
-- Results
Id     InsertTime  Category
-----  ----------  --------
9      2011-02-19  0
5      2011-02-19  1
6      2011-02-19  10
2      2011-02-19  2
1      2011-02-19  3
3      2011-02-19  4
21     2011-02-19  5
14     2011-02-19  6
10     2011-02-19  7
17     2011-02-19  8
16     2011-02-19  9
 
(11 row(s) affected)

任务完成，重复项已被删除。

结论

%%physloc%% 伪列有助于定位物理行并转储页的内容。这在您想调查块结构或想找出实际存储数据的行时非常有用。此伪列还有助于在您必须标识单个行，即使表上没有定义键的情况下。在常规的 SQL Server 使用中，此列不会提供任何可用于编程的改进。因此，切勿将物理位置的值存储在变量中并尝试以后使用该值。不会获得性能提升，反而会适得其反。

在将应用程序从 Oracle 迁移时，%%physloc%% 可用于替换 ROWID，但应尽快实现其他方法，最可能是使用键，以获得可观的响应时间。

另外请记住，行的物理位置可能会发生变化，例如，当聚集索引重建时。当然，由于这是一个未记录且不受支持的功能，它可能会在下一个 SQL Server 版本中消失。

历史

2011 年 2 月 20 日：创建。