sql server 碎片整理——DBCC SHOWCONTIG

转自：

1.http://blog.sina.com.cn/s/blog_6d2675450101ks6i.html

2.http://www.cnblogs.com/CareySon/archive/2012/01/06/2313897.html#!comments

 

关键字：查看索引碎片，重建索引

 

 

一、概述

SQLServer提供了一个数据库命令——DBCC SHOWCONTIG——来确定一个指定的表或索引是否有碎片。 

示例：

显示数据库里所有索引的碎片信息

DBCC SHOWCONTIG WITH ALL_INDEXES

 

显示指定表的所有索引的碎片信息

DBCC SHOWCONTIG (authors) WITH ALL_INDEXES

 

显示指定索引的碎片信息

DBCC SHOWCONTIG (authors,aunmind)

 

DBCC 执行结果：

 

扫描页数：如果你知道行的近似尺寸和表或索引里的行数，那么你可以估计出索引里的页数。看看扫描页数，如果明显比你估计的页数要高，说明存在内部碎片。 

 

扫描扩展盘区数：用扫描页数除以8,四舍五入到下一个最高值。该值应该和DBCC SHOWCONTIG返回的扫描扩展盘区数一致。如果DBCC SHOWCONTIG返回的数高，说明存在外部碎片。碎片的严重程度依赖于刚才显示的值比估计值高多少。 

 

扩展盘区开关数：该数应该等于扫描扩展盘区数减1。高了则说明有外部碎片。 

 

每个扩展盘区上的平均页数：该数是扫描页数除以扫描扩展盘区数，一般是8。小于8说明有外部碎片。 

 

扫描密度［最佳值:实际值］：DBCC SHOWCONTIG返回最有用的一个百分比。这是扩展盘区的最佳值和实际值的比率。该百分比应该尽可能靠近100％。低了则说明有外部碎片。 

 

逻辑扫描碎片：无序页的百分比。该百分比应该在0％到10％之间，高了则说明有外部碎片。 

 

扩展盘区扫描碎片：无序扩展盘区在扫描索引叶级页中所占的百分比。该百分比应该是0％，高了则说明有外部碎片。 

 

每页上的平均可用字节数：所扫描的页上的平均可用字节数。越高说明有内部碎片，不过在你用这个数字决定是否有内部碎片之前，应该考虑fill factor（填充因子）。 

 

平均页密度（完整）：每页上的平均可用字节数的百分比的相反数。低的百分比说明有内部碎片。 

 

查看索引碎片（常规）

--查看索引碎片
select 
db_name(database_id) as '数据库名',
object_name(t.object_id) as '表名',
t.index_id as '索引id',
t1.index_name as '索引名称',
t1.type_desc as '索引类型',
t1.column_name as '索引列名',
t.partition_number as '当前索引所在分区',
t.page_count as '页统计',
t.avg_page_space_used_in_percent as '页使用率/填充因子' ,
t.record_count as '页行记录数',
t.avg_record_size_in_bytes as '平均每条记录大小(B)',
t.avg_fragmentation_in_percent as '索引碎片比率',
t.fragment_count as '索引中的碎片数量',
t.avg_fragment_size_in_pages as '一个索引中碎片的平均页数'
from sys.dm_db_index_physical_stats(db_id('db_tank'),NULL,NULL,NULL,NULL) t
join (select distinct t3.object_id,t3.index_id,object_name(t3.object_id) as 'table_name',t3.name as 'index_name',t3.type_desc, t1.name as 'column_name'
from sys.columns t1 join sys.index_columns t2 on t1.column_id = t2.column_id AND t1.object_id = t2.object_id
join sys.indexes t3 on t2.index_id = t3.index_id AND t2.object_id = t3.object_id) t1
on t1.object_id = t.object_id AND t1.index_id = t.index_id
where object_name(t.object_id) = 'sys_users_goods'

--查看所有表中对应的索引名与索引列
select t3.object_id,object_name(t3.object_id) as 'table_name',t3.name as 'index_name',t3.type_desc, t1.name as 'column_name'
from sys.columns t1 join sys.index_columns t2 on t1.column_id = t2.column_id AND t1.object_id = t2.object_id
join sys.indexes t3 on t2.index_id = t3.index_id AND t2.object_id = t3.object_id
where object_name(t3.object_id) = 'sys_users_goods'

--查看表中所有索引
SELECT name,type_desc FROM sys.indexes WHERE object_id=object_id('O_Orders')

--根据索引名称查看对应的列
DBCC SHOW_STATISTICS(O_Orders,idx_order_status_2)
DBCC SHOW_STATISTICS(O_Orders,IX_O_OrdersUID)


--查找碎片率大于40%的
SELECT object_name(object_id) ,index_type_desc,alloc_unit_type_desc,avg_fragmentation_in_percent,
  fragment_count,avg_fragment_size_in_pages,page_count,record_count,
  avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID('DBNAME'), 
OBJECT_ID(''),NULL,NULL,'Sampled')  
WHERE avg_fragmentation_in_percent>40

 最有效的索引碎片查看方式（必看）

use gameabcAction;

--查看索引碎片
select 
db_name(database_id) as '数据库名',
object_name(t1.object_id) as obj_name,
t.index_id as '索引id',
t1.index_name as '索引名称',
t1.type_desc as '索引类型',
t1.column_name as '索引列名',
t.partition_number as '所在分区',
t.page_count as '页统计',
cast(t.page_count * 8.0/1024 as decimal(10,2)) as 'indexMB',
t.avg_fragmentation_in_percent as '索引碎片比率',
t2.rows as '行记录数',t.fragment_count as '索引中的碎片数量',
t.avg_fragment_size_in_pages as '一个索引中碎片的平均页数',
t.avg_page_space_used_in_percent as '页使用率/填充因子' ,
t.record_count as '页行记录数',
t.avg_record_size_in_bytes as '平均每条记录大小(B)'
from sys.dm_db_index_physical_stats(db_id('gameabcAction'),NULL,NULL,NULL,NULL) t
join 
(
    select distinct t3.object_id,t3.index_id,t3.name as 'index_name',t3.type_desc, t1.name as 'column_name' --t4.name,t4.xtype,
    from 
    (
        select t1.object_id,t2.index_id,
        stuff((
            select ','+name 
            from sys.index_columns  q2 join  sys.columns q1 
            on q1.column_id = q2.column_id AND q1.object_id = q2.object_id
            where q1.object_id = t1.object_id and q2.index_id=t2.index_id
            for xml path('')
        )
        ,1,1,'') as name 
        from sys.index_columns  t2 join  sys.columns t1 
        on t1.column_id = t2.column_id AND t1.object_id = t2.object_id
        group by t1.object_id,t2.index_id
        
    ) t1
    join sys.indexes t3 on t1.index_id = t3.index_id AND t1.object_id = t3.object_id
    --join  sys.sysobjects t4 on t3.object_id = t4.id
    --where t3.object_id >100
            
) t1
on t1.object_id = t.object_id AND t1.index_id = t.index_id
left join sys.sysindexes t2 on t1.object_id = t2.id and t.index_id=t2.indid
where t.avg_fragmentation_in_percent>0
order by [索引碎片比率] desc,[indexMB] desc

 

 

解决碎片问题 

1. 删除并重建索引 
2. 使用DROP_EXISTING子句重建索引 
3. 执行DBCC DBREINDEX 
4. 执行DBCC INDEXDEFRAG 
dbcc showcontig('Log_FairBattlePrestige')
dbcc dbreindex('Log_FairBattlePrestige')

alter table index_name on table_name rebuild with(online=off);

ALTER INDEX ALL ON Employee REBUILD 
WITH (FILLFACTOR = 60, SORT_IN_TEMPDB = ON,STATISTICS_NORECOMPUTE = ON);

-------------------------
STATISTICS_NORECOMPUTE = {ON | OFF} 
指定是否重新计算分布统计信息。默认为OFF。

SORT_IN_TEMPDB = {ON | 关闭 }

适用于：SQL Server（从SQL Server 2008开始）和SQL数据库。

指定是否将排序结果存储在tempdb中。默认为OFF。

ON 
用于构建索引的中间排序结果存储在tempdb中。如果tempdb与用户数据库位于不同的磁盘集上，则可能会减少创建索引所需的时间。但是，这会增加索引构建期间使用的磁盘空间量。

OFF 
中间排序结果与索引存储在同一数据库中。

如果不需要排序操作，或者可以在内存中执行排序，则忽略SORT_IN_TEMPDB选项。

有关更多信息，请参阅索引的SORT_IN_TEMPDB选项。

参考：联机丛书alter index :https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-index-transact-sql?view=sql-server-2017

----------------------------------------------------------------

 

 

删除并重建索引 

 

　　用DROP INDEX和CREATE INDEX或ALTER TABLE来删除并重建索引有些缺陷包括在删除重建期间索引会消失。在索引删除重建时，对于查询它不在可用，查询性能也许会受到明显的影响，直到重建索引为止。另一个潜在的缺陷是当都请求索引的时候会引起阻塞，直到重建索引为止。通过其他的处理也能解决阻塞，就是索引被使用的时候不删除索引。另一个主要的缺陷是在用DROP INDEX和CREATE INDEX重建聚集索引时会引起非聚集索引重建两次。删除聚集索引时非聚集索引的行指针会指向数据堆，聚集索引重建时非聚集索引的行指针又会指回聚集索引的行位置。 

 

删除并重建索引的确有一个好处就是通过重新排序索引页，使索引页紧凑并删除不需要的索引页来完全重建索引。你也许需要考虑那些内部和外部碎片都很高的情况下才使用，以使那些索引回到它们应该在的位置。

 

使用DROP_EXISTING子句重建索引 ：

 

　　为了避免在重建聚集索引时表上的非聚集索引重建两次，可以使用带DROP_EXISTING子句的CREATE INDEX语句。这个子句会保留聚集索引键值，以避免非聚集索引重建两次。和删除并重建索引一样，该方法也可能会引起阻塞和索引消失的问题。该方法的另一个缺陷是也强迫你去分别发现和修复表上的每一个索引。 

 

除了和上一个方法一样的好处之外，该方法的好处是不必重建非聚集索引两次。这样可以对那些带约束的索引提供正确的索引定义以符合约束的要求。 

 

执行DBCC DBREINDEX ：

 

DBCC DBREINDEX类似于第二种方法，但它物理地重建索引，允许SQLServer给索引分配新页来减少内部和外部碎片。DBCC DBREINDEX也能动态的重建带约束的索引，不象第二种方法。 

 

DBCC DBREINDEX的缺陷是会遇到或引起阻塞问题。DBCC DBREINDEX是作为一个事务来运行的，所以如果在完成之前中断了，那么你会丢失所有已经执行过的碎片。 

 

执行DBCC INDEXDEFRAG ：

 

　　DBCC INDEXDEFRAG（在SQLServer2000中可用）按照索引键的逻辑顺序，通过重新整理索引里存在的叶页来减少外部碎片，通过压缩索引页里的行然后删除那些由此产生的不需要的页来减少内部碎片。它不会遇到阻塞问题但它的结果没有其他几个方法彻底。这是因为DBCC INDEXDEFRAG跳过了锁定的页且不使用任何新页来重新排序索引。如果索引的碎片数量大的话你也许会发现DBCC INDEXDEFRAG比重建索引花费的时间更长。DBCC INDEXDEFRAG比其他方法的确有好处的是在其他过程访问索引时也能进行碎片整理，不会引起其他方法的阻塞问题。

 

二、查看方法及测试理解

 

1.查询索引碎片的方法

核心：sys.dm_db_index_physical_stats (db_id(‘数据库名’),null,null,null,’DETAILED’ )

SELECT object_name(dt.object_id) Tablename,si.name IndexName,
dt.avg_fragmentation_in_percent AS ExternalFragmentation,
dt.avg_page_space_used_in_percent AS InternalFragmentation FROM
( SELECT object_id,index_id,avg_fragmentation_in_percent,avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats (db_id(‘数据库名’),null,null,null,’DETAILED’ )
WHERE index_id <> 0) AS dt INNER JOIN sys.indexes si ON si.object_id=dt.object_id
AND si.index_id=dt.index_id
AND dt.avg_fragmentation_in_percent>10 AND
dt.avg_page_space_used_in_percent<75 ORDER BY avg_fragmentation_in_percent DESC

 

（1）avg_fragmentation_in_percent 

此列中所返回的值可确定索引的逻辑碎片（堆的区碎片）。逻辑碎片是在索引的叶级别中次序混乱页面的百分比，而区碎片是在索引的叶级别中次序混乱区的百分比。由于磁头只有左右跳动才能按照顺序读取页面，因此，逻辑碎片和区碎片会因为其需要额外的 I/O 和磁头运动而影响索引的性能。尽量保证逻辑碎片和区碎片均接近于零。

（2）avg_page_space_used_in_percent 

此列可以确定索引页的填充度。为了正确配置该数字以使其尽量接近 100%，请在调整索引填充因子的同时观察所出现的页面分割数量。在某一刻，页面分割的数量会开始急剧增加，这表明您设置的索引填充因子数值高于其应有的数值。调整索引的填充因子需要花费一定的时间并需要进行测试，而且必须在事先进行合理规划。（如果未向索引中随意插入，则可以将索引填充因子设置为 100，且不必担心增加的页面分割数。）

2、内部碎片和外部碎片对于查询性能的影响

---

    外部碎片对于性能的影响上面说过，主要是在于需要进行更多的跨区扫描，从而造成更多的IO操作.

    而内部碎片会造成数据行分布在更多的页中，从而加重了扫描的页树，也会降低查询性能.

    下面通过一个例子看一下,我们人为的为刚才那个表插入一些数据造成内部碎片:

    

    通过查看碎片，我们发现这时碎片已经达到了一个比较高的程度:

    

 

    通过查看对碎片整理之前和之后的IO，我们可以看出，IO大大下降了:

    

 3、对于碎片的解决办法

---

    基本上所有解决办法都是基于对索引的重建和整理，只是方式不同

    1.删除索引并重建

       这种方式并不好.在删除索引期间，索引不可用.会导致阻塞发生。而对于删除聚集索引，则会导致对应的非聚集索引重建两次(删除时重建，建立时再重建).虽然这种方法并不好，但是对于索引的整理最为有效

    2.使用DROP_EXISTING语句重建索引

       为了避免重建两次索引，使用DROP_EXISTING语句重建索引，因为这个语句是原子性的，不会导致非聚集索引重建两次，但同样的，这种方式也会造成阻塞

    3.如前面文章所示，使用ALTER INDEX REBUILD语句重建索引

       使用这个语句同样也是重建索引，但是通过动态重建索引而不需要卸载并重建索引.是优于前两种方法的，但依旧会造成阻塞。可以通过ONLINE（rebuild index会复制旧索引来新建索引--基于快照，此时旧的索引依然可以被读取和修改，但是所以在旧索引上的修改都会同步更新到新索引下。中间会有一些冲突解决机制，在新旧索引借还的时候，索引不可用）关键字减少锁，但会造成重建时间加长.

    4.使用ALTER INDEX REORGANIZE

       这种方式不会重建索引，也不会生成新的页，仅仅是整理，当遇到加锁的页时跳过，所以不会造成阻塞。但同时，整理效果会差于前三种.

 4、理解填充因子

---

      重建索引固然可以解决碎片的问题.但是重建索引的代价不仅仅是麻烦，还会造成阻塞。影响使用.而对于数据比较少的情况下，重建索引代价并不大。而当索引本身超过百兆的时候。重建索引的时间将会很让人蛋疼.

      填充因子的作用正是如此。对于默认值来说，填充因子为0（0和100表示的是一个概念）,则表示页面可以100%使用。所以会遇到前面update或insert时，空间不足导致分页.通过设置填充因子，可以设置页面的使用程度:

     

 

   如何设置填充因子的值并没有一个公式或者理念可以准确的设置。使用填充因子虽然可以减少更新或者插入时的分页，但同时因为需要更多的页，所以降低了查询的性能和占用更多的磁盘空间.如何设置这个值进行trade-off需要根据具体的情况来看.

    具体情况要根据对于表的读写比例来看,我这里给出我认为比较合适的值:

    1.当读写比例大于100：1时，不要设置填充因子，100%填充

    2.当写的次数大于读的次数时，设置50%-70%填充

    3.当读写比例位于两者之间时80%-90%填充

    上面的数据仅仅是我的看法，具体设置的数据还要根据具体情况进行测试才能找到最优.

 

三、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()  读取原始日志信息