SQL Server 查找未使用的非聚集索引和表

概述查找未使用的非聚集索引和未使用的表. 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…

查找未使用的非聚集索引和未使用的表.

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.

--查找未使用的非聚集索引(排除聚集索引和堆表)
--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.
-- nzperfect
select tablename,indexname,'drop index '+tablename+'.'+indexname as dropIndexCommand
from
(
select object_name(i.object_ID) as 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_ID) as 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,'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.
--nzperfect
select object_name(i.object_ID) as 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,'IsMSShipped')=0
and i.index_ID in (0,1) -- 0 indicates the heap 1 indicates the clustered index
and s.object_ID is null

总结

以上是内存溢出为你收集整理的SQL Server 查找未使用的非聚集索引和表全部内容，希望文章能够帮你解决SQL Server 查找未使用的非聚集索引和表所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。