mssql sqlserver 使用sql系统视图sys.dm_db_index_usage_stats 获取索引使用情况


摘要:
下文讲述使用系统视图sys.dm_db_index_usage_stats 查询数据库中索引的运行次数和最后使用时间,如下所示:
实验环境:sql server 2008 R2


例:
1.新建一个表test
2.插入大量数据
3.对查询列建立相应索引
4.清空查询缓存
5.模拟查询
6.执行索引运行情况统计,返回相应信息
7.清空测试数据

/*
第一步:测试数据及索引创建
*/

create table test
(keyId int identity,
info varchar(300))
go


declare @I int 
set @i=0
while @i<100
begin
   insert into test (info)
   select  name  from sysobjects  
   set @i =@i +1 
end
go

/*第二步:创建索引*/
create index ix_test on test (info)


/*第三步: 
  清空查询缓存
  数据查询6次*/
  
DBCC freeproccache
go
select count(1)  as a from test where info like 'test%'
select count(1)  as b from test where info like 'test%'
select count(1)  as c from test where info like 'test%'
select count(1)  as d from test where info like 'test%'
select count(1)  as e from test where info like 'test%'
select count(1)  as f from test where info like 'test%'

go
/*第四步: 
  查看索引的运行情况*/
select 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()   ---当前数据库
 and object_name(a.object_id)   like 'test%' ---待检索表名
 order by user_seeks,user_scans,object_name(a.object_id)

go
drop index ix_test on test 
go
truncate table test 
drop table test