mssql sqlserver 使用sql脚本查询出内存中的脏页的方法分享


摘要:
下文讲述使用sql脚本获取脏页的方法,如下所示:
实验环境:sql server 2008 R2



数据库脏页简介


数据库脏页:
是指内存中数据没有更新至硬盘上(缓存数据没更新至数据库),通常指:
更新 插入 删除等操作产生的脏数据。
——————————————–
注意事项:
当数据库启动时,数据库是没有脏页


数据库脏页获取脚本

SELECT d.name as [数据库名称],
CASE au.TYPE
WHEN 1 THEN o1.name
WHEN 2 THEN o2.name
WHEN 3 THEN o1.name
END as [对象名称],
CASE au.TYPE
WHEN 1 THEN p1.OBJECT_ID
WHEN 2 THEN p2.OBJECT_ID
WHEN 3 THEN p1.OBJECT_ID
END as [对象ID],
CASE au.TYPE
WHEN 1 THEN p1.index_id
WHEN 2 THEN p2.index_id
WHEN 3 THEN p1.index_id
END as [对象索引Id],
bd.FILE_ID as [文件Id],
bd.page_id as [页Id],
bd.page_type as [页类型],
bd.page_level as [页等级]
FROM sys.dm_os_buffer_descriptors bd
INNER JOIN sys.databases d
ON bd.database_id = d.database_id
INNER JOIN sys.allocation_units au
ON bd.allocation_unit_id = au.allocation_unit_id
LEFT JOIN sys.partitions p1
ON au.container_id = p1.hobt_id
LEFT JOIN sys.partitions p2
ON au.container_id = p2.partition_id
LEFT JOIN sys.objects o1
ON p1.OBJECT_ID = o1.OBJECT_ID
LEFT JOIN sys.objects o2
ON p2.OBJECT_ID = o2.OBJECT_ID
WHERE is_modified = 1
mssql_sqlserver_脏页读取_8564

mssql_sqlserver_脏页读取_8564