内部碎片:
指 当索引页没有用到最大量时就产生了内部碎片。虽然在一个有频繁数据插入的应用程序里这也许有帮助,然而设置一个fill factor(填充因子)会在索引页上留下空间,服务器内部碎片会导致索引尺寸增加,从而在返回需要的数据时要执行额外的读操作。这些额外的读操作会降低查询的性能
外部碎片;
指 表中的数据被修改会产生碎片。当插入或更新表中数据时,表的对应聚簇索引和受影响的聚簇索引被修改。如果对索引的修改不能容纳到同一个页面中,就可能导致索引叶子页面分割。这样就会有一个新的叶子页面被添加,该页面包含原来页面中的部分信息(通常是一半),并且维持索引键中的逻辑顺序。但是该页面通常不是与原来页面相邻的。这就产生了逻辑关键字顺序和文件中的物理顺序不一致。
---外部碎片导致磁盘上的索引页面不连续,新的叶子页面和原始叶子页面离的很远,物理顺序和逻辑顺序不同.
select b.name,c.name as indexname, a.index_type_desc,a.index_depth,a.avg_fragmentation_in_percent,
a.avg_page_space_used_in_percent,a.fragment_count ,a.avg_fragment_size_in_pages,a.page_count
from sys.dm_db_index_physical_stats(DB_ID(N'MYTEST'),NULL,NULL,NULL,'DETAILED') as a
inner join sys.objects as b
on a.object_id =b.object_id
inner join sys.indexes as c on a.object_id=c.object_id and a.index_id=c.index_id
order by a.avg_fragmentation_in_percent desc,a.object_id asc
--如果 avg_fragmentation_in_percent 的%比大于30(可以自定义%比值) 则
ALTER INDEX INDEX_NAME ON TABLE_NAME REBUILD WITH(ONLINE=ON) ---联机不锁定表来重新创建索引,以减小索引碎片
--如果小于30 则
ALTER INDEX INDEX_NAME ON TABLE_NAME REORGANIZE --脱机重新创建索引,以减小索引碎片
--避免重新创建聚簇索引时表上的非聚簇索引重建两次
CREATE CLUSTERED INDEX INDEX_NAME ON TABLE_NAME(COLUMN1) WITH(DROP_EXISTING=ON) --该方法有可能引起阻塞和索引的消失。
--显示指定的表或视图的数据和索引的碎片信息
DBCC SHOWCONTIG
DBCC SHOWCONTIG
[ (
{ table_name | table_id | view_name | view_id }
[ , index_name | index_id ]
) ]
[ WITH
{
[ , [ ALL_INDEXES ] ]
[ , [ TABLERESULTS ] ]
[ , [ FAST ] ]
[ , [ ALL_LEVELS ] ]
[ NO_INFOMSGS ]
}
]
--table_name | table_id | view_name | view_id
要检查碎片信息的表或视图。如果未指定,则检查当前数据库中的所有表和索引视图。若要获得表或视图 ID,请使用 OBJECT_ID 函数。
index_name | index_id
要检查碎片信息的索引。如果未指定,则该语句将处理指定表或视图的基本索引。若要获取索引 ID,请使用 sys.indexes 目录视图。
WITH
指定有关 DBCC 语句返回的信息类型的选项。
FAST
指定是否要对索引执行快速扫描和输出最少信息。快速扫描不读取索引的叶级或数据级页。
ALL_INDEXES
显示指定表和视图的所有索引的结果,即使指定了特定索引也是如此。
TABLERESULTS
将结果显示为含附加信息的行集。
ALL_LEVELS
仅为保持向后兼容性而保留。即使指定了 ALL_LEVELS,也只对索引叶级或表数据级进行处理。
NO_INFOMSGS
取消严重级别从 0 到 10 的所有信息性消息。
--分析:
扫描页数:
如果知道行的近似尺寸 和索引里的行数,即可估算出索引里的页数。如果扫描的页数明显大于估算的页数则存在内部碎片。
扫描的扩展盘区数:
扫描页数除以8(8K为一页)得到一个最高值。如果DBCC 扫描返回的数 高,则说明存在外部碎片。
扩展盘区开关数:
该数应该等于扫描扩展盘区数 -1 。如果高则存在外部碎片。
每个扩展盘区上的平均页数;
扫描页数 除以 被扫描扩展盘区数.一般为8 .如果小于 8则存在外部碎片。
扫描密度:
即 最佳值 :实际值 的比率.如果这个比率值过低说明存在外部碎片.这个值最好接近100%.越低越有问题.
逻辑扫描碎片:
即 无序页的百分比.最好将该值控制在10%以内.高了说明有外部碎片.
扩展盘区扫描碎片:
即 无序扩展盘区在扫描索引叶级别页中所占的百分比.如果百分比 高说明存在外部碎片。
平均页密度:
每页上的平均可用字节数的百分比的相反数。低的百分比说明 有内部碎片。
每页上的平均可用字节数:
指所扫描的页上的平均可用字节数。越高说明存在 内部碎片。
---///
----将其放到作业中定时执行即可(最好在生产服务器空闲的时候执行)。仅供参考。
create procedure pr_auto_indexdefrag
as
set nocount on
begin
declare @Db_name nvarchar(256)
,@SchemaName nvarchar(256)
,@TableName Nvarchar(256)
,@IndexName Nvarchar(512)
,@PctFrag decimal
,@Defrag nvarchar(max)
if exists(select 1 from sys.objects where object_id =object_id(N'#tmp')) Drop table #tmp;
create table #tmp(dbname nvarchar(256),tablename nvarchar(256),indexname nvarchar(256),schemaname nvarchar(256),avgfragment decimal)
exec sp_MSforeachdb 'insert into #tmp(dbname,tablename,indexname,schemaname,avgfragment)
select ''?'' dbname,c.name,b.name,e.name,a.avg_fragmentation_in_percent
from ?.sys.dm_db_index_physical_stats(DB_ID(''?''),NULL,NULL,NULL,''SAMPLED'') as a
join ?.sys.indexes as b on a.object_id=b.object_id and a.index_id=b.index_id
join ?.sys.tables as c on a.object_id=c.object_id
join sys.databases as d on a.database_id=d.database_id
join ?.sys.schemas as e on c.schema_id=e.schema_id
where a.avg_fragmentation_in_percent >20
and c.type=''U'' and a.page_count>8
and d.name like ''XXXX%'''
declare frg_cur cursor for
select * from #tmp
open frg_cur
fetch next from frg_cur into @Db_name,@TableName,@IndexName,@SchemaName,@PctFrag
while @@FETCH_STATUS=0
begin
if @PctFrag between 20.0 and 40.0
begin
set @Defrag=N' ALTER INDEX '+@IndexName+' ON'+@Db_name+'.'+@SchemaName+'.'+ @TableName +' REORGANIZE'--重新组织索引页不删除索引
EXEC SP_EXECUTESQL @Defrag
end
else if @PctFrag>40.0
begin
SET @Defrag=N' ALTER INDEX '+@IndexName+' ON'+@Db_name+'.'+@SchemaName+'.'+ @TableName +' REBUILD WITH (ONLINE = ON )'--联机重建索引。即不锁定表重新创建索引
EXEC SP_EXECUTESQL @Defrag
end
fetch next from frg_cur into @Db_name,@TableName,@IndexName,@SchemaName,@PctFrag
end
close frg_cur
deallocate frg_curend
end
set nocount off
---由于表中含有 text、ntext、image、varchar(max)、nvarchar(max)、varbinary(max)、xml 或大型 CLR 类型的列 必须脱机处理。
--可以对pr_auto_indexdefrag 进行改造。则可以这样:
CREATE procedure [dbo].[pr_auto_indexdefrag_offline]
as
begin
set nocount on
declare @Db_name nvarchar(256)
,@SchemaName nvarchar(256)
,@TableName Nvarchar(256)
,@IndexName Nvarchar(512)
,@PctFrag decimal
,@Defrag nvarchar(max)
if exists(select 1 from sys.objects where object_id =object_id(N'#tmp')) Drop table #tmp;
if exists(select 1 from sys.objects where object_id =object_id(N'#tmp_sub')) Drop table #tmp_sub;
create table #tmp_sub(database_id int,dbname nvarchar(32),tablename nvarchar(128),index_type_desc nvarchar(128))
create table #tmp(database_id int,dbname nvarchar(256),tablename nvarchar(256),indexname nvarchar(256),type_desc nvarchar(128),schemaname nvarchar(256),avgfragment decimal)
------找出 text、ntext、image、varchar(max)、nvarchar(max)、varbinary(max)、xml 或大型 CLR 类型的列
exec sp_MSforeachdb 'insert into #tmp_sub(database_id,dbname,tablename,index_type_desc)
select distinct c.database_id,''?'' dbname,b.name,''CLUSTERED''
from
?.sys.dm_db_index_physical_stats(DB_ID(''?''),NULL,NULL,NULL,''SAMPLED'') as a
join ?.sys.tables as b on a.object_id=b.object_id
join sys.databases as c on a.database_id=c.database_id
join ?.sys.all_columns d on d.object_id =a.object_id
join ?.sys.sysobjects e on d.object_id=e.id and e.xtype=''U''
join ?.sys.types f on d.user_type_id=f.user_type_id
where b.type_desc=''USER_TABLE'' and b.is_ms_shipped=0 and (d.max_length =-1
OR (f.name in (''image'',''text'',''ntext'',''xml'',''varbinary'',''binary'')))'
----找出 所有库中的索引
exec sp_MSforeachdb 'insert into #tmp(database_id,dbname,tablename,indexname,type_desc,schemaname,avgfragment)
select distinct d.database_id,''?'' dbname,c.name,b.name,b.type_desc,e.name,a.avg_fragmentation_in_percent
from ?.sys.dm_db_index_physical_stats(DB_ID(''?''),NULL,NULL,NULL,''SAMPLED'') as a
join ?.sys.indexes as b on a.object_id=b.object_id and a.index_id=b.index_id
join ?.sys.tables as c on a.object_id=c.object_id
join sys.databases as d on a.database_id=d.database_id
join ?.sys.schemas as e on c.schema_id=e.schema_id
join ?.sys.sysobjects f on c.object_id=f.id
join ?.sys.all_columns g on f.id=g.object_id
join ?.sys.types h on g.user_type_id=h.user_type_id
where a.avg_fragmentation_in_percent >20
and c.type=''U'' and f.xtype=''U''
and c.is_ms_shipped=0 and d.name like ''%XXXX%'' '
if exists ( select 1 from #tmp where exists (select 1 from #tmp_sub b where database_id=b.database_id and tablename=b.tablename and type_desc=b.index_type_desc))
begin
declare frg_cur cursor for
select dbname,tablename,indexname,
schemaname,avgfragment
from #tmp
where exists (select 1 from #tmp_sub b where database_id=b.database_id and tablename=b.tablename and type_desc=b.index_type_desc)
open frg_cur
fetch next from frg_cur into @Db_name,@TableName,@IndexName,@SchemaName,@PctFrag
while @@FETCH_STATUS=0
begin
if @PctFrag between 20.0 and 40.0
begin
set @Defrag=N' ALTER INDEX '+@IndexName+' ON '+@Db_name+'.'+@SchemaName+'.'+ @TableName +' REORGANIZE'--重新组织索引页不删除索引
EXEC SP_EXECUTESQL @Defrag
end
else if @PctFrag>40.0
begin
SET @Defrag=N' ALTER INDEX '+@IndexName+' ON '+@Db_name+'.'+@SchemaName+'.'+ @TableName +' REBUILD WITH (ONLINE = OFF )'--脱机重建索引。
EXEC SP_EXECUTESQL @Defrag
end
fetch next from frg_cur into @Db_name,@TableName,@IndexName,@SchemaName,@PctFrag
end
close frg_cur
deallocate frg_cur
end
end
set nocount off
--2、
CREATE procedure [dbo].[pr_auto_indexdefrag_online]
as
begin
set nocount on
declare @Db_name nvarchar(256)
,@SchemaName nvarchar(256)
,@TableName Nvarchar(256)
,@IndexName Nvarchar(512)
,@PctFrag decimal
,@Defrag nvarchar(max)
if exists(select 1 from sys.objects where object_id =object_id(N'#tmp')) Drop table #tmp;
if exists(select 1 from sys.objects where object_id =object_id(N'#tmp_sub')) Drop table #tmp_sub;
create table #tmp_sub(database_id int,dbname nvarchar(32),tablename nvarchar(128),index_type_desc nvarchar(128))
create table #tmp(database_id int,dbname nvarchar(256),tablename nvarchar(256),indexname nvarchar(256),type_desc nvarchar(128),schemaname nvarchar(256),avgfragment decimal)
------找出 text、ntext、image、varchar(max)、nvarchar(max)、varbinary(max)、xml 或大型 CLR 类型的列
exec sp_MSforeachdb 'insert into #tmp_sub(database_id,dbname,tablename,index_type_desc)
select distinct c.database_id,''?'' dbname,b.name,''CLUSTERED''
from
?.sys.dm_db_index_physical_stats(DB_ID(''?''),NULL,NULL,NULL,''SAMPLED'') as a
join ?.sys.tables as b on a.object_id=b.object_id
join sys.databases as c on a.database_id=c.database_id
join ?.sys.all_columns d on d.object_id =a.object_id
join ?.sys.sysobjects e on d.object_id=e.id and e.xtype=''U''
join ?.sys.types f on d.user_type_id=f.user_type_id
where b.type_desc=''USER_TABLE'' and b.is_ms_shipped=0 and (d.max_length =-1
OR (f.name in (''image'',''text'',''ntext'',''xml'',''varbinary'',''binary'')))'
----找出 所有库中的索引
exec sp_MSforeachdb 'insert into #tmp(database_id,dbname,tablename,indexname,type_desc,schemaname,avgfragment)
select distinct d.database_id,''?'' dbname,c.name,b.name,b.type_desc,e.name,a.avg_fragmentation_in_percent
from ?.sys.dm_db_index_physical_stats(DB_ID(''?''),NULL,NULL,NULL,''SAMPLED'') as a
join ?.sys.indexes as b on a.object_id=b.object_id and a.index_id=b.index_id
join ?.sys.tables as c on a.object_id=c.object_id
join sys.databases as d on a.database_id=d.database_id
join ?.sys.schemas as e on c.schema_id=e.schema_id
join ?.sys.sysobjects f on c.object_id=f.id
join ?.sys.all_columns g on f.id=g.object_id
join ?.sys.types h on g.user_type_id=h.user_type_id
where a.avg_fragmentation_in_percent >20
and c.type=''U'' and f.xtype=''U''
and c.is_ms_shipped=0 and d.name like ''%XXXX%'' '
declare frg_cur cursor for
select dbname,tablename,indexname,
schemaname,avgfragment
from #tmp
where not exists (select 1 from #tmp_sub b where database_id=b.database_id and tablename=b.tablename and type_desc=b.index_type_desc)
open frg_cur
fetch next from frg_cur into @Db_name,@TableName,@IndexName,@SchemaName,@PctFrag
while @@FETCH_STATUS=0
begin
if @PctFrag between 20.0 and 40.0
begin
set @Defrag=N' ALTER INDEX '+@IndexName+' ON '+@Db_name+'.'+@SchemaName+'.'+ @TableName +' REORGANIZE'--重新组织索引页不删除索引
EXEC SP_EXECUTESQL @Defrag
end
else if @PctFrag>40.0
begin
SET @Defrag=N' ALTER INDEX '+@IndexName+' ON '+@Db_name+'.'+@SchemaName+'.'+ @TableName +' REBUILD WITH (ONLINE = ON )'--联机重建索引。即不锁定表重新创建索引
EXEC SP_EXECUTESQL @Defrag
end
fetch next from frg_cur into @Db_name,@TableName,@IndexName,@SchemaName,@PctFrag
end
close frg_cur
deallocate frg_cur
end
set nocount off
-------------索引使用率
select distinct db_name(database_id) as N'数据库名称',
object_name(a.object_id) as N'表名',
b.name N'索引名称',
user_seeks N'用户索引查找次数',
user_scans N'用户索引扫描次数',
last_user_seek N'最后查找时间',
last_user_scan N'最后扫描时间',
rows as N'表中的行数'
from sys.dm_db_index_usage_stats a join
sys.indexes b
on a.index_id = b.index_id
and a.object_id = b.object_id
join sysindexes c
on c.id = b.object_id
where database_id=db_id('DBName') ---改成要查看的数据库
and object_name(a.object_id) not like 'sys%'
order by user_seeks,user_scans,object_name(a.object_id)
-----判断是否需要更新索引统计信息
select
table_Name=sysobjects.Name,
index_Name=sysindexes.Name,
Type=sysobjects.type,
分配索引页=sysindexes.reserved,
使用索引页=sysindexes.used,
叶子层页=sysindexes.Dpages,
非叶子层页=sysindexes.used-sysindexes.Dpages,
rows=sysindexes.rowcnt
from sysindexes left outer join sysobjects on sysindexes.id=sysobjects.id
where sysindexes.indid>0 and sysindexes.indid<255 and sysindexes.status & 64=0
--注意:若发现非叶子层的页数为负数,最好是运行DBCC UPDATEUSAGE ('dbname','tbname','ixname')来更新一下sysindexes的信息
dbcc updateusage('test','t_goods','idx_baseGoodsID_packetSize')