DBCC PAGE、DBCC LOG、DBCC EXTENTINFO、DBCC IND、DBCC SHOW_STATISTICS、DBCC SHOWCONTIG、DBCC SQLPERF

最新推荐文章于 2020-07-18 22:36:14 发布

阳泉酒家小当家

最新推荐文章于 2020-07-18 22:36:14 发布

阅读量2.1k

点赞数

分类专栏：【SQL Server管理】 SQL Server探索

本文链接：https://blog.csdn.net/sqlserverdiscovery/article/details/19236313

版权

【SQL Server管理】同时被 2 个专栏收录

58 篇文章 6 订阅

订阅专栏

SQL Server探索

9 篇文章 3 订阅

订阅专栏

下面的代码，产生的结果可能和你测试时的结果有所不同，所以需要以实际的值为准。

1、DBCC PAGE

语法：

dbcc page ( {'dbname' | dbid}, filenum, pagenum [, printopt={0|1|2|3} ])

The printopt parameter has the following meanings:

0 - print just the page header
1 - page header plus per-row hex dumps and a dump of the page slot array (unless its a page that doesn't have one, like allocation bitmaps)
2 - page header plus whole page hex dump
3 - page header plus detailed per-row interpretation

实例：

--1.先建表
if OBJECT_ID('test') is not null
   drop table test

CREATE TABLE test(idd INT NOT NULL,name VARCHAR(10) NULL)

INSERT INTO TEST
SELECT 1,'abcdefg'
UNION ALL
SELECT 2,'hijklmn'


--SELECT * FROM TEST

SELECT *
FROM sys.tables 
WHERE name = 'test'



--2.查询元数据
--hobt_id : 72057594043236352
SELECT hobt_id
FROM sys.partitions
WHERE object_id = object_id('test')


/*
first_page ：0x790500000100

拆分成2部分:0100和79050000

这2部分要翻转,也就是0001 和 00000579

前面表示fileId,后面是pageId,都是16机制的表示方法,

通过calc计算器的转换,就是10进制就是1和1401

*/
SELECT first_page    --转换值的顺序
FROM sys.system_internals_allocation_units
WHERE container_id = 72057594043236352



--3.这里创建一个表，用来存放dbcc page的结果
if exists(select * from sys.tables where name = 'dbcc_page')
   drop table dbcc_page
go
create table dbcc_page
(
ParentObject varchar(500),
Object       varchar(2000),
Field        varchar(1000),
Value        nvarchar(max)
)
go


--4.参数分别是数据库名,fileid,pageid,显示格式
--注意:这里是在自己的电脑上实验,用的master数据库,大家不要在正式的服务器上尝试
/*
--这样会报错，只能采用下面的，建一个存储过程
insert into dbcc_page(ParentObject,Object,Field,Value)
DBCC page(master,1,1401,3) with tableresults

*/
if exists(select * from sys.procedures where name = 'proc_dbcc_page')
   drop procedure proc_dbcc_page
go

create procedure proc_dbcc_page
as

DBCC page(master,1,1401,3) with tableresults

go


--5.把dbcc page的结果插入到表中
/*
另一种更简单的方法:

insert into dbcc_page(ParentObject,Object,Field,Value)
exec('DBCC page(master,1,1401,3) with tableresults')

*/
insert into dbcc_page(ParentObject,Object,Field,Value)
exec proc_dbcc_page


--6.查看数据
select *
from dbcc_page

2、DBCC EXTENTINFO

分配的区的信息

语法：

DBCC EXTENTINFO(<dbname|dbid>,<tabelname|tableid>[,{indexname|indexid|-1}] [,partitionnumber])

DBCC EXTENTINFO
(
<dbname|dbid>,              --数据库名称,数据库id
<tabelname|tableid>         --表名称,表id
[,{indexname|indexid|-1}]   --可选:  索引名称,索引id
[,partitionnumber]          --可选:  分区号
)

实例：

--1.先建表
if OBJECT_ID('test') is not null
   drop table test

CREATE TABLE test(idd INT NOT NULL,name VARCHAR(10) NULL)

INSERT INTO TEST
SELECT 1,'abcdefg'
UNION ALL
SELECT 2,'hijklmn'


--SELECT * FROM TEST

SELECT *
FROM sys.tables 
WHERE name = 'test'


--2.索引id
select name,
       index_id  -- 0
from sys.indexes
where object_id = object_id('test')


--3.显示对象分配的区的信息
DBCC EXTENTINFO(wc,test,0)

DBCC EXTENTINFO(wc)

3、DBCC IND

分配的数据页信息

语法：

02.DBCC ind：  
03.  
04.DBCC IND(  
05.    ['database name'|database id],   
06.    tablename,  
07.    indexid,   
08.    -1 shows all indexes and IAMs, -2 just show IAMs  
09.)  
10.  
11.PageFID： 页所在的文件号  
12.PagePID： 数据所在文件内的页号  
13.IndexID： 在sys.indexes中的index_id,  -1表示所有索引页和IAM页,-2只显示IAM页  
14.PageType：表示页的类型，1是数据页,2是索引页，10是保存页本身的IAM页。  
15.IndexLevel: 是IAM结构的级别。如果0，那么这是索引的叶级别页

实例：

--1.先建表
if OBJECT_ID('test') is not null
   drop table test

CREATE TABLE test(idd INT NOT NULL,name VARCHAR(10) NULL)

INSERT INTO TEST
SELECT 1,'abcdefg'
UNION ALL
SELECT 2,'hijklmn'


--SELECT * FROM TEST

SELECT *
FROM sys.tables 
WHERE name = 'test'


--2.索引id
select name,
       index_id  -- 0
from sys.indexes
where object_id = object_id('test')



--3.创建表
if exists(select * from sys.tables where name = 'dbcc_ind')
   drop table dbcc_ind
go

create table dbcc_ind
(
	PageFID numeric(20),
	PagePID numeric(20),
	
	IAMFID numeric(20),
	IAMPID numeric(20),
	
	ObjectID numeric(20),
	IndexID numeric(20),
	PartitionNumber numeric(20),
	PartitionID numeric(20),
	
	iam_chain_type nvarchar(100),
	
	PageType numeric(20),
	IndexLevel numeric(20),
	
	NextPageFID numeric(20),
	NextPagePID numeric(20),
	PrevPageFID numeric(20),
	PrevPagePID numeric(20)
)
go


--4.建存储过程
if exists(select * from sys.procedures)
   drop procedure proc_dbcc_ind
go

create procedure proc_dbcc_ind
as

dbcc ind(master,test,0) 

go


insert into dbcc_ind
exec proc_dbcc_ind


--5.一共2页，分别是IAM页1402，数据页1401
select PageFID,
       PagePID,
       OBJECTID,
       IndexID,
       PartitionNumber,
       PartitionID,
       iam_chain_type,
       PageType,
       IndexLevel,
       
       NextPageFID,  --下一页的文件id
       NextPagePID,  --下一页的页id
       PrevPageFID,  
       PrevPagePID
from dbcc_ind

4、DBCC SHOW_STATISTICS

DBCC SHOW_STATISTICS 显示表或索引视图的当前查询优化统计信息。查询优化器使用统计信息估计查询结果中的基数或行数，这样，查询优化器可以创建高质量的查询计划。例如，查询优化器可以使用基数估计在查询计划中选择索引查找运算符而不是索引扫描运算符，从而通过避免消耗大量资源的索引扫描来提高查询性能。

查询优化器将表或索引视图的统计信息存储在统计信息对象中。对于表，统计信息对象是根据索引或表列的列表创建的。统计信息对象包含一个带有统计信息的相关元数据的标题、一个带有统计信息对象第一个键列中的值的分布的直方图，以及一个用于度量各列之间的相关性的密度向量。数据库引擎可以使用统计信息对象中的任何数据计算基数估计。

语法：

DBCC SHOW_STATISTICS ( table_or_indexed_view_name , target ) 
[ WITH [ NO_INFOMSGS ] < option > [ , n ] ]
< option > :: =
    STAT_HEADER | DENSITY_VECTOR | HISTOGRAM | STATS_STREAM

table_or_indexed_view_name
要显示其统计信息的表或索引视图的名称。

target
要显示其统计信息的索引、统计信息或列的名称。 
如果 target 是表或索引视图的现有索引或统计信息的名称，则返回有关此目标的统计信息。 
如果 target 是现有列的名称，且此列中存在自动创建的统计信息，则返回有关该自动创建的统计信息的信息。 
如果列目标中不存在自动创建的统计信息，则返回错误消息 2767。

NO_INFOMSGS
取消严重级别从 0 到 10 的所有信息性消息。

STAT_HEADER | DENSITY_VECTOR | HISTOGRAM | STATS_STREAM [ ,n ]
如果指定以上一个或多个选项，将根据指定的选项限制该语句返回的结果集。 如果没有指定任何选项，则返回所有统计信息。

STATS_STREAM 为 标识为仅供参考。不提供支持。不保证以后的兼容性。

实例：

dbcc show_statistics
(
'test',   --表的名称
'idd'     --列的名称
)


dbcc show_statistics
(
'test',   --表的名称
'_WA_Sys_00000001_117F9D94'     --统计信息的名称
)

5、DBCC SHOWCONTIG

显示指定的表或视图的数据和索引的碎片信息。

后续版本的 Microsoft SQL Server 将删除该功能。请不要在新的开发工作中使用该功能，并尽快修改当前还在使用该功能的应用程序。

语法：

DBCC SHOWCONTIG 
[ ( 
    { table_name | table_id | view_name | view_id } 
    [ , index_name | index_id ] 
) ] 
    [ WITH 
        { 
         [ , [ ALL_INDEXES ] ] 
         [ , [ TABLERESULTS ] ] 
         [ , [ FAST ] ]
         [ , [ ALL_LEVELS ] ] 
         [ NO_INFOMSGS ]
         }
    ]

able_name | table_id | view_name | view_id
要检查碎片信息的表或视图。 如果未指定，则检查当前数据库中的所有表和索引视图。 
若要获得表或视图 ID，请使用 OBJECT_ID 函数。

index_name |index_id
要检查碎片信息的索引。 如果未指定，则该语句将处理指定表或视图的基本索引。 
若要获取索引 ID，请使用 sys.indexes 目录视图。

WITH
指定有关 DBCC 语句返回的信息类型的选项。

FAST
指定是否要对索引执行快速扫描和输出最少信息。 快速扫描不读取索引的叶级或数据级页。

ALL_INDEXES
显示指定表和视图的所有索引的结果，即使指定了特定索引也是如此。

TABLERESULTS
将结果显示为含附加信息的行集。

ALL_LEVELS
仅为保持向后兼容性而保留。 即使指定了 ALL_LEVELS，也只对索引叶级或表数据级进行处理。

NO_INFOMSGS
取消严重级别从 0 到 10 的所有信息性消息。

实例：

dbcc showcontig('test',0)
/*
DBCC SHOWCONTIG 正在扫描 'test' 表...
表: 'test' (293576084)；索引 ID: 0，数据库 ID: 7
已执行 TABLE 级别的扫描。
- 扫描页数................................: 1
- 扫描区数..............................: 1
- 区切换次数..............................: 0
- 每个区的平均页数........................: 1.0
- 扫描密度 [最佳计数:实际计数].......: 100.00% [1:1]
- 区扫描碎片 ..................: 0.00%
- 每页的平均可用字节数.....................: 8048.0
- 平均页密度(满).....................: 0.57%
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
*/

6、DBCC SQLPERF

提供所有数据库的事务日志空间使用情况统计信息。也可以用于重置等待和闩锁的统计信息。

语法：

DBCC SQLPERF 
(
     [ LOGSPACE ]
     |
          [ "sys.dm_os_latch_stats" , CLEAR ]
     |
     [ "sys.dm_os_wait_stats" , CLEAR ]
) 
     [WITH NO_INFOMSGS ]
     
LOGSPACE
返回事务日志的当前大小和用于每个数据库的日志空间的百分比。 
可以使用此信息来监视事务日志中使用的空间量。

" sys.dm_os_latch_stats" , CLEAR
重置闩锁统计信息。 有关详细信息，请参阅 sys.dm_os_latch_stats (Transact-SQL)。

"sys.dm_os_wait_stats" , CLEAR
重置等待统计信息。 有关详细信息，请参阅 sys.dm_os_wait_stats (Transact-SQL)。

WITH NO_INFOMSGS
取消严重级别从 0 到 10 的所有信息性消息。

实例：

dbcc sqlperf(logspace)
/*
Database Name	Log Size (MB)	Log Space Used (%)	Status
master	10.17188	16.82028	0
tempdb	0.4921875	64.78175	0
model	0.7421875	36.31579	0
msdb	5.054688	20.40186	0
ReportServer	6.242188	18.77347	0
ReportServerTempDB	0.8046875	45.20631	0
wc	0.5546875	93.39789	0
*/

7、DBCC LOG

语法：

dbcc log(dbname,4)       --(n=0,1,2,3,4)

0 - 默认选项
1 - 更多信息plus flags, tags, row length
2 - 非常详细的信息plus object name, index name,page id, slot id
3 - 每种操作的全部信息
4 - 每种操作的全部信息加上该事务的16进制信息

实例：

dbcc log(wc,1)

--系统函数
select * from fn_dblog(null,null)

阳泉酒家小当家

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
DBCC PAGE、DBCC LOG、DBCC EXTENTINFO、DBCC IND、DBCC SHOW_STATISTICS、DBCC SHOWCONTIG、DBCC SQLPERF

下面的代码，产生的结果可能和你测试时的结果有所不同，所以需要以实际的值为准。1、DBCC PAGE语法：dbcc page ( {'dbname' | dbid}, filenum, pagenum [, printopt={0|1|2|3} ])The printopt parameter has the following meanings:0 - print
复制链接

扫一扫