笔记210 查询数据文件的页面信息

 1 --查询数据文件的页面信息
 2 USE AdventureWorks
 3 EXEC sys.sp_helpdb  @dbname = 'AdventureWorks' 
 4 
 5 DBCC TRACEON(3604)
 6 
 7 DBCC PAGE(11,1,3230,3) --结果显示3230这个页面是person.contact表
 8 
 9 SELECT s.name ,o.name  From sys.sysobjects o INNER JOIN sys.schemas s ON o.uid =s.schema_id
10 WHERE o.id=309576141
11 GO
12 SELECT id,indid,name   from sys.sysindexes WHERE id=309576141 AND indid =3
13 GO
14 --EXEC sys.sp_helpindex  @objname = N'' -- nvarchar(776)
15 
16 DBCC SHOWCONTIG
17 
18 USE partionTest
19 SELECT * FROM sys.allocation_units
20 
21 SELECT * FROM sys.partitions
22 
23 EXEC sys.sp_spaceused  @objname = N'dbo.testPartionTable', -- nvarchar(776)
24     @updateusage = 'true' -- varchar(5)
25 
26 --使用系统管理视图sys.dm_db_partition_stats比sp_spaceused更准确
27 SELECT
28 o.name ,
29 SUM(p.reserved_page_count ) AS reserved_page_count,
30 SUM(p.used_page_count) AS used_page_count,
31 SUM(
32 CASE
33      WHEN (p.index_id <2) THEN (p.in_row_data_page_count +p.lob_used_page_count +p.row_overflow_used_page_count )
34      ELSE p.lob_used_page_count +p.row_overflow_used_page_count
35 END
36 )  AS datapages,
37 SUM(
38 CASE
39      WHEN (p.index_id <2) THEN row_count
40      ELSE 0
41 END
42 ) AS rowcounts
43 from sys.dm_db_partition_stats p INNER JOIN sys.objects o ON o.object_id =p.object_id
44 GROUP BY o.name
45 
46 --DBCC SHOWCONTIG  最准确  每个页 每个区的使用情况、碎片程度
47 --查看GPOSDB每个表的使用情况
48 USE GPOSDB
49 DBCC SHOWCONTIG
50 
51 
52 --dm_db_index_physical_stats的三种模式
53 --limited:运行最快，扫描页数最少。对于堆，它将扫描所有页，但对于索引，只扫描叶级别上面的父级别页
54 --sampled:返回基于索引或堆中所有页的1%样本的统计信息。如果索引或堆少于10000页，则使用detailed模式代替sampled
55 --detailed:虽然精确，但是在数据库处于高峰时应避免使用
56 USE GPOSDB
57 SELECT f.avg_fragmentation_in_percent ,f.avg_fragment_size_in_pages ,f.fragment_count ,i.name  FROM sys.dm_db_index_physical_stats(17,NULL ,NULL,NULL,NULL ) f INNER   JOIN sys.indexes i ON  f.index_id =i.index_id
58 WHERE i.name IS NOT NULL  AND f.fragment_count >0
59 ORDER BY f.index_id
60 
61 --显示日志大小，使用比率
62 DBCC SQLPERF(LOGSPACE)