博客园  :: 首页  :: 新随笔  :: 联系 :: 订阅 订阅  :: 管理

查找未使用的非聚集索引和表 for sql server 2005/2008

Posted on 2010-03-30 15:03  nzperfect  阅读(508)  评论(0编辑  收藏  举报


查找未使用的非聚集索引和未使用的表.
DMV:sys.dm_db_index_usage_stats
The counters are initialized to empty whenever the SQL Server (MSSQLSERVER) service is started. In addition, whenever a database is detached or is shut down (for example, because AUTO_CLOSE is set to ON), all rows associated with the database are removed.

代码参考:
http://www.cnblogs.com/nzperfect/archive/2010/03/25/1695449.html



--查找未使用的非聚集索引(排除聚集索引和堆表)
--
This returns all the noclustered indexes that have not been used for any requests by users 
--
or the system from the time SQL Server is started.
select tablename,indexname,'drop index '+tablename+'.'+indexname as dropIndexCommand
from 
    (
        
select object_name(i.object_idas tablename, i.name as indexname
        
from sys.indexes i
        
left outer join sys.dm_db_index_usage_stats s
            
on s.object_id = i.object_id and s.index_id = i.index_id
            
and s.database_id = db_id()
        
where objectproperty(i.object_id'IsUserTable'= 1
            
and objectproperty(i.object_id,'IsMSShipped')=0
            
and i.index_id > 1 -- 0 indicates the heap   1 indicates the clustered index  
            and i.is_primary_key = 0   --  1 indicates the primary key
            
and s.object_id is null
        
union all
        
select object_name(i.object_idas tablename, i.name as indexname
        
from sys.indexes i
        
inner join sys.dm_db_index_usage_stats s
            
on s.object_id = i.object_id and s.index_id = i.index_id
            
and s.database_id = db_id()
        
where objectproperty(i.object_id'IsUserTable'= 1
            
and objectproperty(i.object_id,'IsMSShipped')=0
            
and i.index_id > 1 -- 0 indicates the heap   1 indicates the clustered index 
            and i.is_primary_key = 0 --  1 indicates the primary key
            and (s.user_seeks + s.user_scans + s.user_lookups)=0
    )a



--查找未使用的表
--
This returns all the table that have not been used for any requests by users 
--
or the system from the time SQL Server is started.
    select object_name(i.object_idas tablename, i.name as clusteredindexname,
    
case when i.index_id=0 then 'Heap Table'
        
when i.index_id=1 then 'Clustered Table' end as TableType
    
from sys.indexes i
    
left outer join sys.dm_db_index_usage_stats s
        
on s.object_id = i.object_id and s.index_id = i.index_id
        
and s.database_id = db_id()
    
where objectproperty(i.object_id'IsUserTable'= 1
        
and objectproperty(i.object_id,'IsMSShipped')=0
        
and i.index_id in (0,1-- 0 indicates the heap   1 indicates the clustered index     
        and s.object_id is null



Please tell me if you find bugs.

Thanks for reading,
nzperfect