–需要维护但是从未被用过的索引

作者: admin 分类: MYSQL, 其他技术 发布时间: 2015-09-29 15:03 ė 6没有评论

–需要维护但是从未被用过的索引

select ‘[' + DB_NAME () + '].[' + su.[name] + ‘].[' + o.[name] +’]’ as [statement] ,
i.[name] as [index_name] ,
ddius.[user_seeks] + ddius.[user_scans] + ddius.[user_lookups] as [user_reads],
ddius.[user_updates] as [user_writes] ,
sum(SP.rows) as [total_rows]
from sys.dm_db_index_usage_stats ddius
inner join sys.indexes i on ddius.[object_id] = i.[object_id]
and i.[index_id] = ddius.[index_id]
inner join sys.partitions SP ON ddius.[object_id] = SP.[object_id]
and SP.[index_id] = ddius.[index_id]
inner join sys.objects o on ddius.[object_id] = o.[object_id]
inner join sys.sysusers su on o.[schema_id] = su.[UID]
where ddius.[database_id] = DB_ID() — 只是当前的数据库
and OBJECTPROPERTY(ddius.[object_id],’IsUserTable’) = 1
and ddius.[index_id] > 0
group by su.[name],
o.[name],
i.[name],
ddius.[user_seeks] + ddius.[user_scans] + ddius.[user_lookups],
ddius.[user_updates]
having ddius.[user_seeks] + ddius.[user_scans] + ddius.[user_lookups] = 0
order by ddius.[user_updates] DESC ,
su.[name],
o.[name],
i.[name]

本文出自 高度PHP,转载时请注明出处及相应链接。

本文永久链接: http://ypweb.net/3715.html

0

发表评论

电子邮件地址不会被公开。 必填项已用 * 标注

您可以使用这些 HTML 标签和属性: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Ɣ回顶部