首页 > 数据库 > SQL Server > 正文
SQLSEVER碎片化及压缩整理
2017-05-19       个评论    来源:qq_23968505的博客  
收藏    我要投稿

SQLSEVER碎片化及压缩整理。

对于有聚集索引的表,如果存在碎片。

(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%

点击复制链接 与好友分享!回本站首页
上一篇:SQL增加列、修改列、删除列
下一篇:最后一页
相关文章
图文推荐
文章
推荐
点击排行

关于我们 | 联系我们 | 广告服务 | 投资合作 | 版权申明 | 在线帮助 | 网站地图 | 作品发布 | Vip技术培训
版权所有: 红黑联盟--致力于做实用的IT技术学习网站