（4.11）DBCC 常用命令

关键字：跟踪标记，跟踪

【0】DBCC状态、验证、其他语句

状态语句

DBCC INPUTBUFFER 显示从客户端发送到MS SQL Server 的最后一个语句
DBCC OPENTRAN 如果在指定数据库内存在最旧的活动事务和最旧的分布和非分布式复制事务，
则显示与之相关的信息。只有当存在活动事务或数据库包含复制信息时，
才显示结果。如果没有活动事务，就显示信息性消息
DBCC OUTPUTBUFFER 以十六进制或 ASCII 格式返回指定系统进程 ID (SPID) 的当前输出缓冲区
DBCC PROCCACHE 以报表形式显示有关过程高速缓存的信息
DBCC SHOWCONTIG 显示指定的表的数据和索引的碎片信息
DBCC SHOW_STATISTICS 显示指定表上的指定目标的当前分布统计信息
DBCC SQLPERF 提供有关所有数据库中的事务日志空间使用情况的统计信息
DBCC TRACESTATUS 显示跟踪标记的状态
DBCC USEROPTIONS 返回当前连接的活动（设置）的 SET 选项

验证语句

DBCC CHECKALLOC 检查指定数据库的磁盘空间分配结构的一致性
DBCC CHECKCATALOG 检查指定数据库中的系统表内及系统表间的一致性
DBCC CHECKCONSTRAINTS 检查指定表上的指定约束或所有约束的完整性
DBCC CHECKDB 检查指定数据库中的所有对象的分配和结构完整性
DBCC CHECKFILEGROUP 检查指定文件组中的所有表（在当前数据库中）的分配和结构完整性
DBCC CHECKIDENT 检查指定表的当前标识值，如有必要，还对标识值进行更正DBCC CHECKTABLE
检查指定表或索引视图的数据、索引及 text、ntext 和 image 页的完整性
DBCC NEWALLOC 检查数据库的扩展结构内的每个表的数据和索引页的分配

其他语句

DBCC dllname (FREE) 从内存中卸载指定的扩展存储过程动态链接库 (DLL)
DBCC HELP 返回指定的 DBCC 语句的语法信息
DBCC PINTABLE 将表标记为驻留，这表示MS SQL Server不从内存中刷新表页
DBCC ROWLOCK 在MS SQL Server 6.5 版中使用，对表启用插入行锁定 (IRL) 操作
DBCC TRACEOFF 禁用指定的跟踪标记
DBCC TRACEON 打开（启用）指定的跟踪标记
DBCC UNPINTABLE 将表标记为不在内存驻留。将表标记为不在内存驻留后，
可以清空高速缓存中的表页

日志语句

DBCC LOGINFO 查看事务日志信息

sys.fn_dblog() 读取原始日志信息

【1】常规dbcc命令

dbcc help('?')  --查看dbcc 所有命令，常规下只有32个常用的
dbcc TRACEON(2588) --指定了2588标记的话，你就可以看到未公开的dbcc命令了，同时你也可以看到各种命令参数的提示了。很多dbcc命令需要开启它才能使用，比如ind，page等
dbcc help('checkdb') --帮助命令，使用其来查看命令用法，开启了上面的2588就可以用ind（查看堆表/索引组织表的数据也信息）了
dbcc TRACEON(3604) -- 指定标记，使得dbcc page的结果可以在客户端上显示出来

3064 将DBCC的结果可以在客户端上显示出来
3605 将DBCC的结果输出到错误日志。
1204 返回参与死锁的锁的资源和类型，以及受影响的当前命令。
1222 返回参与死锁的锁的资源和类型，以及使用了不符合任何 XSD 架构的 XML 格式的受影响的当前命令(比1204更进一步,SQL 2005及以上可用)。


--实例：查看表的数据页分布和数据页具体内容：DBCC的概念与用法(DBCC TRACEON、DBCC IND、DBCC PAGE)

常用：

DBCC HELP --返回指定的 DBCC 命令的语法信息。 当不知道哪个DBCC命令语法情况，这个命令很好用
DBCC   HELP ('checkdb') 

DBCC SHOWFILESTATS  --显示文件使用情况的，需要通过换算所得
DBCC SHOWCONTIG   --返回整个数据库的空间使用情况
DBCC SHOWCONTIG   ('HumanResources.Employee' ) WITH TABLERESULTS --以表格的形式返回结果
DBCC SHOWCONTIG   ('HumanResources.Employee' ) WITH ALL_INDEXES --默认只扫描基表，不扫描索引
DBCC SHOWCONTIG   ('HumanResources.Employee' ) WITH FAST  --只统计表本身数据，不包括其他非聚集索引
DBCC SHOWCONTIG   ('HumanResources.Employee' ) WITH NO_INFOMSGS 

DBCC SQLPERF( LOGSPACE)  --为所有数据库提供事务日志空间用法的统计信息。也可以用于重置等待和闩锁的统计信息。
DBCC SQLPERF ("sys.dm_os_latch_stats" , CLEAR)
DBCC SQLPERF ("sys.dm_os_wait_stats" , CLEAR) WITH NO_INFOMSGS

DBCC CLEANTABLE('AdventureWorks2008R2','HumanResources.Employee') --回收表或索引视图中已删除的可变长度列的空间。 
DBCC INDEXDEFRAG('AdventureWorks2008R2','HumanResources.Employee') --指定表或视图的索引碎片整理。
DBCC DBREINDEX ('AdventureWorks2008R2','HumanResources.Employee') --对指定数据库中的表重新生成一个或多个索引。 

DBCC SHRINKDATABASE ('AdventureWorks2008R2')   --收缩指定数据库中的数据文件和日志文件的大小。 
DBCC SHRINKFILE --收缩当前数据库的指定数据或日志文件的大小

DBCC CHECKALLOC --检查指定数据库的磁盘空间分配结构的一致性。
DBCC CHECKFILEGROUP  --检查当前数据库中指定文件组中的所有表和索引视图的分配和结构完整性。
DBCC CHECKCATALOG  --检查指定数据库内的目录一致性。
DBCC CHECKCONSTRAINTS --检查当前数据库中指定表上的指定约束或所有约束的完整性。
DBCC CHECKTABLE --检查组成表或索引视图的所有页和结构的完整性。
DBCC CHECKDB   --通过执行下列操作检查指定数据库中所有对象的逻辑和物理完整性： 
/*
对数据库运行 DBCC CHECKALLOC。
对数据库中的每个表和视图运行 DBCC CHECKTABLE。
对数据库运行 DBCC CHECKCATALOG。
验证数据库中每个索引视图的内容。
验证数据库中的 Service Broker 数据。

维护语句 
*/
DBCC UPDATEUSAGE   --报告目录视图中的页数和行数错误并进行更正。这些错误可能导致 sp_spaceused 系统存储过程返回不正确的空间使用报告。

DBCC OPENTRAN (AdventureWorks2008R2) WITH TABLERESULTS 
DBCC USEROPTIONS --返回当前连接的活动（设置）的 SET 选项。
DBCC SHOW_STATISTICS --显示指定表上的指定目标的当前分发统计信息。 

DBCC INPUTBUFFER (57)--显示从客户端发送到 Microsoft SQL Server实例的最后一个语句。
DBCC OUTPUTBUFFER(57)--以十六进制和 ASCII 格式返回指定 session_id 的当前输出缓冲区。
DBCC TRACEON --启用指定的跟踪标记。
DBCC TRACEOFF  --禁用指定的跟踪标记。
DBCC TRACESTATUS(1244) --显示跟踪标志的状态
DBCC FREEPROCCACHE   --从过程缓存中删除所有元素
DBCC FREESYSTEMCACHE --从所有缓存中释放所有未使用的缓存条目。SQL Server 2005 数据库引擎会事先在后台清理未使用的缓存条目，以使内存可用于当前条目。但是，可以使用此命令从所有缓存中手动删除未使用的条目。 
DBCC PROCCACHE   --以表格格式显示有关过程缓存的信息。
DBCC FREESESSIONCACHE --刷新针对 Microsoft SQL Server 实例执行的分布式查询所使用的分布式查询连接缓存。

DBCC CHECKIDENT('HumanResources.Employee' )  --检查指定表的当前标识值，如有必要，则更改标识值。

【2】常用优化跟踪标识

【2.1】如何控制、启用、查看跟踪标识？


--（1）使用dbcc 启用跟踪标识，注意，所有跟踪在重启引擎服务后会失效，需要重新开启

DBCC TRACEON(2203,-1) --启用，-1表示全局global scope
DBCC TRACEOFF(2203,1) --禁用，1表示会话session scope,默认为1，即会话范围生效
DBCC TRACESTATUS    --查看当前会话开启跟踪的信息及状态

--解决重启会失效问题，即使用实例启动存储过程，把相关标识在过程中写好即可

EXEC sp_procoption @ProcName = '<procedure name>' 
    , @OptionName = ] 'startup' 
    , @OptionValue = 'on'; 

--（2）通过在SQL Server配置管理器中指定
　　这种方式是通过在数据库引擎启动项里加启动参数设置，只有Global Scope。格式为-T#跟踪标记1;T跟踪标记2;T跟踪标记3。
--（3）通过注册表启动
　　与（1）（2）大同小异

--~~注意，某些跟踪不能使用dbcc ，只能使用-T加在sql server配置管理器中指定

--（4）建议使用的跟踪

Trace Flag  610 --OLTP不建议开启，减少日志产生量。如果你对于日志用了很多基础的best practice，比如说只有一个日志文件、VLF数量适当、单独存储，如果还是不能缓解日志过大的话，考虑使用该跟踪标记。

Trace Flag  834 --使用 Microsoft Windows 大页面缓冲池分配。如果服务器是SQL Server专用服务器的话，值得开启该跟踪标记。

Trace Flag  835 --允许SQL Server 2005和2008标准版使用＂锁定内存页＂，和在组策略中设置的结果大同小异，但是允许在标准版中使用．

Trace Flag  1118 --tempdb分配整个区，而不是混合区，减少SGAM页争抢。

Trace Flag  1204和1222 --这两个跟踪标记都是将死锁写到错误日志中，不过1204是以文本格式进行，而1222是以XML格式保存。可以通过 sp_readerrorlog来查看错误日志 最佳实践：dbcc traceon (1204,1222,3605,-1)

Trace Flag  1211和1224 --两种方式都是禁用锁升级。但行为有所差别1211是无论何时都不会锁升级，而1224在内存压力大的时候会启用锁升级，从而避免了out-of-locks错误。当两个跟踪标记都启用是，1211的优先级更高。

Trace Flag  2528 --不建议开启，禁用并行执行DBCC CHECKDB, DBCC CHECKFILEGROUP,DBCC CHECKTABLE。这意味着这几个命令只能单线程执行，这可能会需要更多的时间，但是在某些特定情况下还是有些用处。

Trace Flag  3226 --防止日志记录成功的备份。如果日志备份过于频繁的话，会产生大量错误日志，启用该跟踪标记可以使得日志备份不再被记录到错误日志。

Trace Flag  2371 --允许增加数据库内统计信息的更新频率（统计信息参考：https://www.cnblogs.com/gered/p/9087055.html）



--（5）不建议使用的跟踪

Trace Flag  806 --在读取过程中对页检查逻辑一致性，在错误日志中就可以看到类似下面的信息：

　　该跟踪标记会极大的降低性能！！！

　　2004-06-25 11:29:04.11 spid51 错误： 823，严重性： 24 日状态： 2
　　2004-06-25 11:29:04.11 spid51 I/O 错误 （审核失败） 在读取过程中检测到的偏移量主题 SQL Server\MSSQL\data\pubs.mdf e:\Program 文件中的 0x000000000b0000.

Trace Flag 818  --检查写一致性

踪标志 818 启用了一个内存中的环形缓冲区，用于跟踪由运行 SQL Server 的计算机执行的最后 2,048 个成功写操作（不包括排序和工作文件 I/O）
发生 605、823 或 3448 之类的错误时，将传入缓冲区的日志序列号 (LSN) 值与最新写入列表进行比较。
如果在读操作期间检索到的 LSN 比在写操作期间指定的更旧，就会在 SQL Server 错误日志中记录一条新的错误信息。大部分 SQL Server 写操作以检查点或惰性写入形式出现。
惰性写入是一项使用异步 I/O 操作的后台任务。环形缓冲区的实现是轻量的，因此对系统性能的影响可以忽略。

Trace Flag 1200 --返回加锁信息的整个过程，是学习加锁过程很牛逼的标志。 最佳实践：DBCC TRACEON(1200,3604,-1) ;dbcc tracestatus;

Trace Flag 1806 --禁用即时文件初始化，所有的磁盘空间请求全部使用填０初始化，可能造成在空间增长时产生阻塞。

Trace Flag 3502 --在日志中显示有关checkpoint的相关信息。

Trace Flag 3505 --不允许自动进行checkpoint,checkpoint只能手动进行，是非常危险的一个命令。

--（6）其他跟踪标记

--8602 停止索引提示功能
--8755 停止锁提示功能
--8780 停止最优化超时配置，强制做完整的最优化动作
--3004：数据库还原详细信息记录在errorlog里面
--1117：同一个文件组内的多个文件插入数据的时候会同时增长，而不是只增长第一个数据文件