笔记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)
