sys.dm_db_page_info 某个数据页的头部信息
USE master
GO
CREATE DATABASE Test;
GO
USE Test;
GO
SELECT [object_id] as objectid,name,type_desc,create_date,modify_date INTO myobjects FROM sys.objects
GO
--------------------------------------------------------------------------------------------------
-- sys.dm_db_page_info 某个数据页的头部信息
--------------------------------------------------------------------------------------------------
-- 表所有数据页信息
DBCC IND('Test','myobjects',-1)
GO
-- 表所有数据页信息 (SQL Server 2012 函数)
-- select * from sys.dm_db_database_page_allocations(@DatabaseId , @TableId , @IndexId , @PartionID , @Mode)
SELECT * FROM sys.dm_db_database_page_allocations(DB_ID(),OBJECT_ID('myobjects'),null,null,'Detailed') WHERE is_allocated=1
GO
-- 根据上面的结果，查看某个数据页的具体信息
DBCC TRACEON(3604)
GO
DBCC PAGE(TEST,1,280,3)
GO
-- 某个数据页的头部信息 (SQL Server 2019 函数)
-- select * from sys.dm_db_page_info ( @DatabaseId, @FileId, @PageId, @Mode )
SELECT * FROM sys.dm_db_page_info ( DB_ID(), 1, 280, 'Detailed' )
GO
-- 两个系统视图也新增了字段 [page_resource]，用于处理页等待情况。
SELECT page_resource FROM sys.sysprocesses
SELECT page_resource FROM sys.dm_exec_requests
SELECT page_info.*
FROM sys.dm_exec_requests AS d
CROSS APPLY sys.fn_PageResCracker(d.page_resource) AS r
CROSS APPLY sys.dm_db_page_info(r.db_id, r.file_id, r.page_id,'DETAILED') AS page_info;
GO
sys.dm_db_database_page_allocations 与 DBCC IND 结果字段差异：
APPROX_COUNT_DISTINCT 快速统计非空字段的非重复近似总数
--------------------------------------------------------------------------------------------------
-- APPROX_COUNT_DISTINCT 快速统计非空字段的非重复近似总数
--------------------------------------------------------------------------------------------------
-- Oracle 12c 已经有了该函数 ,对大量数据统计很有用（不是很准确）
SELECT APPROX_COUNT_DISTINCT(name),COUNT(name) FROM myobjects