SQL Server 2019 新增函数

 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