为什么dm_os_buffer_descriptors中的一些allocation_unit_id在sys.allocation_units中看不到？

Question

环境信息：

OS版本：Windows Server 2008 R2 Standard (64-bit)

DB版本：SQL Server 2008 R2 Enterprise (64-bit)

问题描述：

最近想写一个分析buffer pool中的page属于哪些表或索引的SQL。发现sys.dm_os_buffer_descriptors中一些buffer的allocation_unit_id在sys.allocation_units（或者sys.system_internals_allocation_units）中查不到。请看如下查询结果：

SELECT * FROM sys.dm_os_buffer_descriptors
WHERE database_id = DB_ID()
AND   allocation_unit_id NOT IN
(
    SELECT allocation_unit_id FROM sys.allocation_units
)

我想知道这是为什么？

谢谢！

Answer 1

That is master db, you can forget it. Also note from books online:

sys.dm_os_buffer_descriptors might show nonexistent values in allocation_unit_id for clustered indexes that are created in versions of SQL Server earlier than SQL Server 2005.

Answer 2

不是所有都能够匹配的

SELECT  *
FROM    sys.[dm_os_buffer_descriptors]

SELECT  *
FROM    sys.[allocation_units]


SELECT  *
FROM    sys.[dm_os_buffer_descriptors] AS a
        INNER JOIN sys.[allocation_units] AS b ON [b].[allocation_unit_id] = [a].[allocation_unit_id]

---

Love SQL

Answer 3

That is master db, you can forget it. Also note from books online:

sys.dm_os_buffer_descriptors might show nonexistent values in allocation_unit_id for clustered indexes that are created in versions of SQL Server earlier than SQL Server 2005.

是的，我的截图是在master库中执行的结果。

但是，这个现象对于其它数据库也是存在的。请看如下截图，这是一个非系统库的结果：

Answer 4

不是所有都能够匹配的

SELECT  *
FROM    sys.[dm_os_buffer_descriptors]

SELECT  *
FROM    sys.[allocation_units]


SELECT  *
FROM    sys.[dm_os_buffer_descriptors] AS a
        INNER JOIN sys.[allocation_units] AS b ON [b].[allocation_unit_id] = [a].[allocation_unit_id]

---

Love SQL

嗯，其实我是想写一个脚本，统计一下buffer pool中缓存了哪些表或索引的页面。

结果发现dm_os_buffer_descriptors内连接allocation_units后，少了很多记录。

所以想知道到底这些未包含在allocation_units中的allocation_unit_id到底属于什么情况？

Answer 5

Is it new db you created on that sql2k8r2 server from scratch?

Answer 6

Is it new db you created on that sql2k8r2 server from scratch?

是的，没有做过版本升级，就是在2008 R2上建的库。你们的环境中都没有这个现象么？

Answer 7

Is it new db you created on that sql2k8r2 server from scratch?

是的，没有做过版本升级，就是在2008 R2上建的库。你们的环境中都没有这个现象么？

我的就是在R2上测的

---

Love SQL

Answer 8

Is it new db you created on that sql2k8r2 server from scratch?

是的，没有做过版本升级，就是在2008 R2上建的库。你们的环境中都没有这个现象么？

Couple of notes:
- sys.allocation_units excludes internal index pages and allocation-management pages.

- the values returned by sys.allocation_units immediately after dropping or truncating a large object may not reflect the actual disk space available.