SQL SEVER碎片化及压缩整理

对于有聚集索引的表，如果存在碎片。
（1）索引重新组织
（2）索引重新生成

对于堆，如果存在碎片。
（1）将数据插入临时表，Truancate，再insert 
（2）在表中创建聚集索引后，再删除聚集索引，因为创建聚集索引会重新分布数据，这种分布一般是最优的。
如果表中存在非聚集索引，需要在drop 聚集索引后重新生成所有非聚集索引。

对于索引的碎片处理。
（1）索引重新组织
（2）索引重新生成

在碎片清理的基础上，还可以通过压缩（表压缩、索引压缩、分区压缩），提高I/O。
但是压缩是牺牲CPU来换取I/O。

表压缩（聚集索引会继承表压缩，非聚集索引不会继承），分为行压缩和页压缩
（1）需要频繁更新的对象应该使用行压缩。
（2）只是执行读取操作的应该使用页压缩。
  
  行压缩：对字段类型进行压缩，如INT，默认4个字节，如填写的1，会压缩成1个字节。
如Char（500），默认分配500个字符，没有写满会用空字符填充，压缩会将这些空字符去掉。
  
 页压缩主要是 前缀压缩和字典压缩
 前缀压缩：对于一页中的每一行，提取前缀，将数据值有前缀代替。 如提取的前缀为AABB，数据值为AABBCC，替换后4CC。 数据值为CDBE，替换后为0CDBE 。不要求每行的每一列都包括了前缀。
 字典压缩：在前缀压缩的基础之上，是对一页中重复值，抽取出来，替换。

压缩适合场景：
1.CPU充足，I/O瓶颈或存储空间步骤
2.表字段存在大量定长字段
3.表中存在大量空数据
4.表中存在大量重复数据

表压缩是不能减少碎片，只是在原有数据将数据进行压缩，腾出一定程度的空间，减少存储的页和区。但是先前的碎片仍然存在。

分析碎片常使用脚本 ：

-------------------查看表的分区信息-------------------------------
CREATE PROCEDURE SP_ExtentInfo  
AS  
        DBCC ExtentInfo(0)  
GO  

  --创建保存分区信息的临时表  
        Create Table #ExtentInfo  
        (  
                fileid smallint,  
                pageid int,  
                pg_alloc int,  
                ext_size int,  
                obj_id int,  
                index_id int,  
                partition_number int,  
                partition_id bigint,  
                iam_chain_type varchar(50),  
                pfs_bytes varbinary(10)  
        )  
        insert into #ExtentInfo exec SP_ExtentInfo  

   --显示当前分区信息  
        select fileid,obj_id,index_id,partition_id,ext_size,  
                object_name(obj_id) as '对象名',  
                count(*) as '实际区数', sum(pg_alloc) as '实际页数',  
                ceiling(sum(pg_alloc)*1.00/ext_size)*ext_size as '最大可用页数',  
                ceiling(sum(pg_alloc)*1.00/ext_size*100.00/count(*)) as '表空间使用比率'  
        from ExtentInfo  
        group by fileid,obj_id,index_id,partition_id,ext_size  
        order by partition_id,obj_id,index_id,fileid  
          

----------------------------查看表碎片------------------------
   DBCC SHOWCONTIG  ('GPSMonitorLog')   
   
   SET STATISTICS IO on
   SET STATISTICS time  on
   select * from GPSMonitorLog

----------------------------查看索引碎片--------------------------
--查看索引碎片使用sys.dm_db_index_physical_stats(DB_ID(),@objectid,@indexid,NULL,'limited')，以下为查看整个数据库索引碎片。
--考虑到效率问题，没有直接用JOIN，而是用的游标，

IF EXISTS  (select  OBJECT_ID('#temp1') )   drop  table #temp1 
Create table  #temp1
(
 objectID   INT , 
 indexid  INT,
 partition_number  INT,
 index_type_desc varchar(50) ,
 alloc_unit_type_desc varchar(50),
 index_depth  INT  ,
 index_level INT ,
 avg_fragmentation_in_percent Decimal(10,2) ,
 fragment_count INT  ,
  avg_fragment_size_in_pages Decimal(10,2)
)
declare @objectid  int 
declare @indexid int , @n int =1 

declare   obj_cur  cursor for 
select a.object_id ,b.index_id    from  sys.objects a
join   sys.indexes  b on  a.object_id=b.object_id 
where a.type  = 'U'
and b.type  >0 --排除堆 为1表示聚集索引，为2表示非聚集索引

 open obj_cur  
 fetch next from obj_cur into @objectid  ,@indexid
 while (@@FETCH_STATUS =0)
 begin 
 
 INSERT INTO #temp1
 select  object_ID   , 
 index_id ,
 partition_number ,
 index_type_desc ,
 alloc_unit_type_desc ,
 index_depth ,
 index_level,
 avg_fragmentation_in_percent ,
 fragment_count,
 avg_fragment_size_in_pages 
 from   sys.dm_db_index_physical_stats(DB_ID(),@objectid,@indexid,NULL,'limited')
 fetch next from obj_cur into @objectid  ,@indexid
 print (@n ) 
 set @n = @n +1  
 end 
close obj_cur
deallocate obj_cur

select b.name , c.name ,a.*  from #temp1 a
join   sys.objects b on  a.objectID=b.object_id 
join   sys.indexes  c on  a.indexid=c.index_id  and b.object_id =c.object_id
where  avg_fragmentation_in_percent >30  --碎片比大于30%