22,199
社区成员
发帖
与我相关
我的任务
分享
use tempdb
go
CREATE TABLE tmp (id int ,c1 char(500),c2 char(500))
CREATE CLUSTERED INDEX CI_id ON tmp(id)
DECLARE @i INT
SET @i=0
WHILE @i<20129
BEGIN
INSERT INTO tmp(id,c1,c2)
SELECT @i,'a','z'
SET @i=@i+1
END
set statistics io on
select COUNT(*) from tmp
DBCC IND(tempdb,tmp,-1)
dbcc traceon(3604)
dbcc page(tempdb,1,1666,3)
dbcc page(tempdb,1,196,3)
dbcc page(tempdb,1,193,3)
DBCC DROPCLEANBUFFERS --无法清除tempdb的缓存数据.(所以不要使用tempdb库做测试)
SELECT bd.*
FROM sys.dm_os_buffer_descriptors AS bd
INNER JOIN
(
SELECT object_name(object_id) AS name
,index_id ,allocation_unit_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.hobt_id
AND (au.type = 1 OR au.type = 3)
UNION ALL
SELECT object_name(object_id) AS name
,index_id, allocation_unit_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.partition_id
AND au.type = 2
) AS obj
ON bd.allocation_unit_id = obj.allocation_unit_id
WHERE database_id = db_id() and name='tbname'
ORDER BY allocation_unit_id,page_type