SQLServer中的索引碎片处理

SQLServer数据库随着使用时间的增长,会让人觉得越来越慢,这个和你平时没有合理的维护计划有关系,定期处理索引碎片是一个必不可少的工作内容之一。 具体信息参考msdn

http://msdn.microsoft.com/zh-cn/library/ms189858.aspx 我工作中碰到一张表,有320万记录,数据表占用空间800多兆,所有索引碎片大于80%,甚至有100%,索引占用空间500兆,重新生成索引后占用空间减小到200多兆。 一个可以在SQL2005中测试的脚本

--drop database db_index_test --建立测试环境

create database db_index_test

go

use db_index_test

go

create table tbTest(rownum int identity(1,1),id varchar(100),date datetime)

go

create index index_id on tbTest(id) go

--插入测试数据,并适当删除一部分数据

declare @i int

set @i=1

while @i<10

begin

insert into tbTest(id,date)

select newid(),getdate() from syscolumns

delete from tbTest where rownum%2=0

set @i=@i+1

end

go

--检查索引

SELECT avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'tbTest'), NULL, NULL, NULL) AS a JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id where name='index_id'

go --重建索引

alter index index_id on tbTest rebuild go

--检查索引

SELECT avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'tbTest'), NULL, NULL, NULL) AS a JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id where name='index_id' --删除测试环境 go use master go drop database db_index_test

go

在sql的客户端工具SQL Server Management Studio中也可以手动检查并重建索引

索引碎片处理

01-08

[code=SQL]MSDN: http://msdn.microsoft.com/zh-cn/library/ms189858.aspxrn--索引的维护 提示(online的作用非常的大,聚集的重键会使表不能使用)rn昨天经过kg牛牛的提示,想了点索引碎片的知识。随想随写,有点乱,也许有错的地方,csdner可以灰铁提示rnSELECT OBJECT_NAME(dt.object_id),rnsi.name,rndt.avg_fragmentation_in_percent,rndt.avg_page_space_used_in_percentrnFROMrn(SELECT object_id,rnindex_id,rnavg_fragmentation_in_percent,rnavg_page_space_used_in_percentrnFROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'DETAILED')rnWHERE index_id <> 0rn) AS dt --does not return information about heapsrnINNER JOIN sys.indexes sirnON si.object_id = dt.object_idrnAND si.index_id = dt.index_idrnrnrn碎片的分类rn1内部碎片(avg_page_space_used_in_percent) -是说索引的大小,超过了索引的实际大小,也就是填充度 rn2外部碎片(avg_fragmentation_in_percent) -是说索引页面的排序和物理不一致,index page的newxtpage+prevpageid的是最小相临的(REORGANIZE就是解决这个问题)rn3物理连续性(fragment_count 和 avg_fragment_size_in_pages[>65 and <256]) -是说单一的分配单元的8个页面的连续性质,简单可以说是nextpage是不是当前page的+1至+7rn4内部的逻辑碎片(有聚集索引的情况) 也就是槽号的物理顺序和逻辑顺序的不同(就是很多人说的聚集索引的顺序,和物理的存储顺序是不同的)rnrnALTER INDEX [index_id] ON [dbo].[tbTest] REORGANIZErn重新组织索引rnREORGANIZE的过程:rn 1压缩过程 查找临近的page,查找可以移动的记录转移到小pageid的页面,增加填充度(不会分配新的index page,有些记录会因为移动,而不在先前的page里存放)rn 2使逻辑和物理的顺序保持一致(这和上面的不冲突),使用中间页来互换页面内容,使页面的pageid从小到大排列,删除最后的空pagernALTER INDEX [index_id] ON [dbo].[tbTest] REBUILDrn重键索引rn同时维护2个索引,新的创建完,会删除旧索引(因为同时维护2个版本的索引,只能分配新的索引页面,删除旧的索引页面)rn所以rebuild后的avg_fragmentation_in_percent逻辑碎片,应该是在0-3左右,avg_page_space_used_in_percent会在95以上(大型行也许会小一点)rnrnrn--页拆分引起的碎片rnif object_id('tbTest') is not nullrndrop table tbTestrngorncreate table tbTest(a int primary key,b varchar(1600))rngorninsert into tbtest(a,b) select 5,replicate('a',1600)rninsert into tbtest(a,b) select 10,replicate('b',1600)rninsert into tbtest(a,b) select 15,replicate('c',1600)rninsert into tbtest(a,b) select 20,replicate('d',1600)rninsert into tbtest(a,b) select 25,replicate('e',1600)rnrndbcc ind([index],tbTest,1)rnrndbcc traceon(3604)rnrndbcc page([index],1,45,1) rn rninsert into tbtest(a,b) select 22,replicate('f',1600)rn用上面的查询看碎片的结果rn(无列名) name avg_fragmentation_in_percent avg_page_space_used_in_percentrntbTest PK__tbTest__164452B1 50 59.9085742525327rnrndbcc page([index],1,45,1) rn--内部碎片avg_page_space_used_in_percentrnselect (8096-3245/* m_freeCnt*/)*1.0/8096=0.599184 基本可以对上rn--逻辑碎片rnavg_fragmentation_in_percent 这个的50,也许是[物理连续性]一类的碎片,因为外部的大小顺序倒是一样的,不深究了rnrndbcc page([index],1,80,1) 。。。rnrn--判断REORGANIZE和REBUILD,重新组织和重建索引(注意,text,varchar(max)等要特殊处理,下次再说...:))rnrnSELECT t.name as tablename,'ALTER INDEX [' + ix.name + '] ON [' + s.name + '].[' + t.name + '] ' +rn CASErn WHEN ps.avg_fragmentation_in_percent > 15rn THEN 'REBUILD'rn ELSE 'REORGANIZE'rn END +rn CASErn WHEN pc.partition_count > 1rn THEN ' PARTITION = ' + CAST(ps.partition_number AS nvarchar(MAX))rn ELSE ''rn END,rn avg_fragmentation_in_percentrnFROM sys.indexes AS ixrn INNER JOIN sys.tables trn ON t.object_id = ix.object_idrn INNER JOIN sys.schemas srn ON t.schema_id = s.schema_idrn INNER JOINrn (SELECT object_id ,rn index_id ,rn avg_fragmentation_in_percent,rn partition_numberrn FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL)rn ) psrn ON t.object_id = ps.object_idrn AND ix.index_id = ps.index_idrn INNER JOINrn (SELECT object_id,rn index_id ,rn COUNT(DISTINCT partition_number) AS partition_countrn FROM sys.partitionsrn GROUP BY object_id,rn index_idrn ) pcrn ON t.object_id = pc.object_idrn AND ix.index_id = pc.index_idrnWHERE ps.avg_fragmentation_in_percent > 10rn AND ix.name IS NOT NULLrn--上面语句是网上的,加了行数判断,也可以用页数量判断rnand exists(rnselect * from sys.partitions where object_id=t.object_id and rows>10000 rn/*控制一下记录行数,因为小表的rebuild等会根据很多参数标准来判断是否要去重建。rn网上搜索的判断因子:rn生成查询计划的阀值rn缓存机制,缓存的筛选,LRU算法rn预读机制rncheckpoint减少回滚距离rn智能join判断rn重编译rn*/rn)rn[/code]rn

没有更多推荐了,返回首页

私密
私密原因:
请选择设置私密原因
  • 广告
  • 抄袭
  • 版权
  • 政治
  • 色情
  • 无意义
  • 其他
其他原因:
120
出错啦
系统繁忙,请稍后再试