仓酷云

标题: 使用sys.dm_db_index_physical_stats检察索引碎片等数据 [打印本页]

作者: 精灵巫婆 时间: 2015-1-16 14:09
标题: 使用sys.dm_db_index_physical_stats检察索引碎片等数据
BDB源自BerkeleyDB，事务型数据库的另一种选择，支持COMMIT和ROLLBACK等其他事务特性我们都晓得，进步sqlserver的数据查询速率，最无效的办法，就是为表创立索引，而索引在对数据举行新增，删除，修正的时分，会发生索引碎片，索引碎片多了，就必要从头构造或从头天生索引，以到达索引的最年夜效力。

那末我们要怎样晓得索引的碎片巨细呢？在sqlserver中，碎片的巨细是利用碎片比来表现的，按msdn下面的说法，假如碎片比小于30%,我们能够从头构造索引，假如碎片比年夜于即是30%，我们能够选择从头天生索引。

那末我们在那边能够检察到碎片比呢？最复杂的就是在microsoftsqlservermanagementstudio当选择:要检察的索引地点的表->"索引"，选择要检察的索引，选择"从头天生"大概"从头构造"，在弹出框的"碎片总计"栏中，显现的就是该索引的碎片比了。

固然，在本文中，我们次要是要解说经由过程sys.dm_db_index_physical_stats静态函数来检察索引的碎片比率。

先看看sys.dm_db_index_physical_stats的界说：
sys.dm_db_index_physical_stats(
{database_id|NULL}
,{object_id|NULL}
,{index_id|NULL|0}
,{partition_number|NULL}
,{mode|NULL|DEFAULT}
)

注释一下：
database_id--要检察索引地点数据库，以后数据库ID我们能够用db_id()函数来取到
object_id--要查索引地点表的id,好比我们要检察表T1，能够用object_id(T1)来取到该表的ID
index_id--要检察索引的索引号,该索引也一样能够用object_id(索引名)来猎取
partition_number--工具中的分区号。partition_number为int范例。无效的输出包含索引或堆的partion_number或NULL
mode--在msdn中的注释是如许的：
函数的实行形式将断定为了猎取此函数所利用的统计信息数据而实行的扫描级别。mode被指定为LIMITED、SAMPLED或DETAILED。此函数将遍历组成表或索引的指定分区的分派单位页链。
LIMITED：形式运转最快，扫描的页数起码。关于堆，它将扫描一切页，但关于索引，则只扫描叶级下面的父级别页。

SAMPLED：形式将前往基于索引或堆中一切页的1％样本的统计信息。假如索引或堆少于10,000页，则利用DETAILED形式取代SAMPLED。

DETAILED：形式将扫描一切页并前往一切统计信息。

从LIMITED到DETAILED形式，速率将愈来愈慢，由于在每一个形式中实行的义务愈来愈多。若要疾速丈量表或索引的巨细或碎片级别，请利用LIMITED形式。它的速率最快，而且关于索引的IN_ROW_DATA分派单位中的每一个非叶级别，不前往与其对应的一行。

上面经由过程示例来检察：
好比我要检察以后数据库的Assets_BackUp_Assets表的索引情形，能够用以下的语句查询:
select*fromsys.dm_db_index_physical_stats(db_id(),object_id(Assets_BackUp_Assets),null,null,null)

注释一下几个对照有效前往列：
database_id：以后数据库ID，也就是db_id()
object_id列：以后表的ID,也就是object_id(Assets_BackUp_Assets)
index_id列:以后索引的ID，假设Assets_BackUp_Assets中有两个索引，index_id分离为1和2,假如我们只想检察索引1的情形，能够用
select*fromsys.dm_db_index_physical_stats(db_id(),object_id(Assets_BackUp_Assets),1,null,null)
partition_number列：以后索引地点分区号，同sys.dm_db_index_physical_stats界说中的partition_number参数
index_type_desc列：索引范例-会萃索引大概非会萃索引等。
alloc_unit_type_desc列：分派单位范例--这个鄙人面注释
avg_fragmentation_in_percent列：最主要的列，以后索引碎片比率。

分派单位范例解说：
IN_ROW_DATA：包括除年夜型工具(LOB)数据之外的一切数据的数据行或索引行。页的范例为Data或Index。

LOB_DATA：以以下一种或多种数据范例存储的年夜型工具数据：text、ntext、image、xml、varchar(max)、nvarchar(max)、varbinary(max)或CLR用户界说范例(CLRUDT)。页的范例为Text/Image。

ROW_OVERFLOW_DATA：存储在凌驾8,060字节行巨细限定的varchar、nvarchar、varbinary或sql_variant列中的可变长度数据。页的范例为Data。

注重：偶然候，我们只为表创立了一个索引，但经由过程sys.dm_db_index_physical_stats却查出了两笔记录，这就是由于在表中利用了text、ntext、image、varchar(max)、nvarchar(max)、varbinary(max)和xml列，也就是LOB_DATA存储单位.利用了这些列后，在创立表但未拔出数据前只要一笔记录，但有拔出过数据后，就会有两笔记录了，两笔记的区分就是alloc_unit_type_desc中的值的区分，大概的情形就是一条的值是N_ROW_DATA，另外一条的值是LOB_DATA。

读者能够本人做测试：
CreatetableT(Colnchar(200),Col2nvarchar(max))
CreateclusteredindexIX_TonT(Col)
select*fromsys.dm_db_index_physical_stats(db_id(),object_id(T),null,null,null)
insertintoTselectREPLICATE(Roy,50),REPLICATE(Roy_88,2000)
select*fromsys.dm_db_index_physical_stats(db_id(),object_id(T),null,null,null)
droptableT

增补一点：sys.dm_db_index_physical_stats函数中五个参数都能够为null。
select*fromsys.dm_db_index_physical_stats(db_id(),null,null,null,null)

查询以后库的一切表的索引情形
select*fromsys.dm_db_index_physical_stats(null,null,null,null,null)

查询一切数据库中一切索引情形，固然了，条件是你必需有查询别的数据库索引的权限，假如权限不敷，会前往毛病：
用户没有实行此操纵的权限。

以上为本章的全体内容，报告的对照庞杂，但愿人人包涵。
在本站前面的文章中会持续报告怎样使用SQL从头天生与从头构造索引，请存眷！由MySQL用来存储数据的文件格式以已经被广泛地测试过，但是总是有外部情况可以导致数据库表被破坏：

作者: 活着的死人 时间: 2015-1-18 12:10
再开发调试阶段和OLAP环境中，外键是可以建立的。新版本中加入了SETNULL和SETDEFAULT属性，能够提供能好的级联设置。

作者: 海妖 时间: 2015-1-26 15:32
可能有的朋友会抱怨集成的orderby，其实如果使用ranking函数,Orderby是少不了的。如果担心Orderby会影响效率，可以为orderby的字段建立聚集索引，查询计划会忽略orderby操作（因为本来就是排序的嘛）。

作者: 小妖女 时间: 2015-2-4 20:37
但是随着数据量的增大，这种成本差距会逐渐减小，趋于相等。（500万数量级只相差10%左右）

作者: 莫相离 时间: 2015-2-10 09:17
原来的计算字段其实和虚拟字段很像。只是管理方面好了而已，性能方面提高不多。但是SQL2005提供了计算字段的持久化，这就提高了查询的性能，但是会加重insert和update的负担。OLTP慎用。OLAP可以大规模使用。

作者: 爱飞 时间: 2015-3-1 08:58
多加的系统视图和实时系统信息这些东西对DBA挑优非常有帮助，但是感觉粒度还是不太细。

作者: 老尸 时间: 2015-3-10 16:09
外键的级联更能扩展可能大部分的同行在设计OLTP系统的时候都不愿意建立外键，都是通过程序来控制父子数据的完整性。

作者: 飘灵儿 时间: 2015-3-17 09:08
其中最有名的应该是row_number了。这个终于解决了用临时表生成序列号的历史，而且SQLServer2005的row_number比Oracle的更先进。因为它把Orderby集成到了一起，不用像Oracle那样还要用子查询进行封装。

作者: 蒙在股里 时间: 2015-3-24 05:02
原理很简单，对要求长时间计算某一时间点的报表生成和防用户操作错误很有帮助。但是比起Oracle10g的闪回技术还是细粒度不够。可惜！

欢迎光临仓酷云 (http://ckuyun.com/)