运行缓慢的查询语句（监测索引的使用）

　　另外一个影响查询性能的因素跟DML查询已经删除，插入以及更改数据的查询有关。在指定表上创建的索引越多，更改数据时需要占用的资源也就越多。当事务之间存在锁的组合时，较长的更改操作会破坏并发性。结果，所以，了解哪个索引被应用程序超时使用就显得很重要。然后你就可以指出是否在数据库中结构中存在着从未被使用过的索引。

　　SQL Server2008提供了sys.dm_db_usage_stats DMV，它显示出了哪些索引被使用以及它们是在被用户查询所使用还是仅仅共系统操作使用。伴随着查询的每一次执行，这个动态管理试图返回的列会随着查询语句中使用的执行计划的增加而增加。数据是在SQL Server运行的时候被收集的。这个dmv中数据仅被存储在内存中，并且不会被永久保存。所以当SQL Server实例停掉以后，数据也会丢失。你可以间歇性地获取并保存这些信息，便于以后对它们进行分析。

　　在索引上的操作被划分为用户类型及系统类型两种。用户类型倾向于SELECT，INSERT，DELETE以及UPDATE操作，或者更新统计信息。两种类别的语句在列中对比显示如下：

• 在索引上的查找(seek)操作 (user_seeks or system_seeks).
• 在索引上的查阅(Lookup)操作 (user_lookups or system_lookups).
• 在索引上的遍历操作(Scan) (user_scans or system_scans).
• 在索引上的更新(Update)操作 (user_updates or system_updates).

对于这些在索引上的每一种操作，最后的访问都会被标记上一个时间戳。索引本身通过三个列被识别：database_id,object_id,index_id。index_id=0表示一个堆类型的表，index_id=1表示一个群集索引，index_id>1表示一个非聚集索引。

应用程序在数据库上持续应用，sys.dm_db_index_usage_stats可访问的索引列表也会随之增加。

SQL Server上scan，seek，以及lookup工作的定义及规则如下：

• Seek：指出访问数据的过程中B树结构被使用了多少次。无论B-树结构是被用来从各层级上读取几个页面进而只获取一个数据行，还是近乎一半的索引页被读取进而从基础表中读取几G的数据。所以你可以期望所有对应索引的查找都落在这个类别里。
• Scan：指出在数据层不使用B-树结构去获取数据时对表的访问次数。对于未定义任何索引的表，就可能是这种scan的情况。对于表上已经定义了索引的情况，如果查询未使用创建在表上的任何索引，scan也会发生。
• Lookup：它表示因非聚集索引指针指向聚集索引而引起的聚集索引对数据的查找。这个场景在SQL Server2000及更在版本中被称书签查找(Bookmark lookup)。它呈现了这样一种场景：一个非聚集索引被用来去访问某个表，并且这个非聚集索引并没有涵盖select查询中的所有列，并且这个列被定义在了where谓词中。SQL Server会为非聚集索引增加user_seeks这个列中的值并加上作为聚集索引入口的user_lookups列中的值。如果表上定义了很多的非聚集索引，这个和值会变得很高。如果用户在表的某一个索引上seek的次数很高，那么用户lookups的次数也会很高，并且如果用户对某一个非聚集索引的访问次数也很高，你就应该考虑让非聚集索引帮助解决聚集索引高数量使用的问题。

下面的DMV查询可以被用来去获取所有数据库上的所有对象的索引使用信息：

select object_id, index_id, user_seeks, user_scans, user_lookups 
from sys.dm_db_index_usage_stats 
order by object_id, index_id

你可以参考下面的输出

object_id       index_id    user_seeks    user_scans    user_lookups 
--------------------------------------------------------------------
521690298        1          0             251           123
521690298        2          123           0             0

　　当前情况下有251个对应于某一查询的执行直接访问数据层中的表，而没有使用任意一个索引。有123个对应于某一查询的执行通过第一个非聚集索引访问表，它既没有涵盖Select中的列也没有涵盖where谓词中指定的列，因为我们看到有123个lookup存在于聚集索引之上。

　　sys.dm_db_index_usage_stats中最有意思的列是用来查看用户类型的列，包括user_seeks和user_scans。系统使用列，比如system_seeks可以被认为是已经存在的索引的结果。如果索引不存在，它就可以不必强制更新统计信息并且也可以不需要被检查一致性。所以，分析工作需要关注那四个列以指出即时查询及用户应用程序对索引的使用情况。

　　为了获取指定表在SQL Server最后一次启动后一直未被使用的索引的信息，可以在拥有这个对象的数据库中运行下面查询：

select i.name
from sys.indexes i 
where i.object_id=object_id('<table_name>') and
    i.index_id NOT IN  (select s.index_id 
                        from sys.dm_db_index_usage_stats s 
                        where s.object_id=i.object_id and     
                        i.index_id=s.index_id and
                        database_id = <dbid> )

　　所有未被使用过的索引可以通过下面的语句获取到：

select object_name(object_id), i.name 
from sys.indexes i 
where  i.index_id NOT IN (select s.index_id 
                          from sys.dm_db_index_usage_stats s 
                          where s.object_id=i.object_id and 
                          i.index_id=s.index_id and 
                          database_id = <dbid> )
order by object_name(object_id) asc

　　当前的情况，表名及索引名称按照表名被存储。

　　这个DMV的真实目的是观察长时间运行的索引的使用情况。可能最好是去创建一个针对视图的快照或者针对结果的快照并且存储起来，按照这种方式每天执行一次并且去对比彼此的变化。如果你可以明确识别出哪个索引已经好几个月或者有段时间都没有被使用过了，你可以最终从数据库中删除掉这些索引。

 

 

 

 

　　

转载于:https://www.cnblogs.com/sharpwang/archive/2012/06/10/2541886.html