公司数据库服务器的空间越来越紧张、最大的数据库达到400个G,100G 以上的库就有四五个。当然我们应该感到欣慰,数据高速增长说明我们的业务发展较好,但不可否认,我们的应用设计也存在着某些问题。诸如:滥建索引、过度冗余或者是系统在设计时没有考虑对超过价值期的历史数据进行清理。
下面这个脚本用来获取数据库每张表/索引的空间使用情况。
![ContractedBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
![ExpandedBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
with pa as
(
SELECT p.object_id,p.index_id,a.type_desc as pagetype_desc,a.total_pages,a.used_pages,a.data_pages
FROM sys.partitions p JOIN sys.allocation_units a
ON p.partition_id = a.container_id
),
indexes as
(
select object_id,index_id,object_name(object_id) as tbname , name as indexname,type_desc as tbtype_desc
from sys.indexes
where object_id > =100
),
result as
(
select i.*,p.pagetype_desc,p.total_pages,p.used_pages,p.data_pages
from pa p inner join indexes i
on p.object_id=i.object_id and p.index_id=i.index_id
)
select * from result order by total_pages desc
下面这个脚本用以统计索引的使用率
![ContractedBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
![ExpandedBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
declare @dbid int
select @dbid = db_id()
select objectname=object_name(s.object_id), s.object_id, indexname=i.name, i.index_id
, user_seeks, user_scans, user_lookups, user_updates
from sys.dm_db_index_usage_stats s,
sys.indexes i
where database_id = @dbid and objectproperty(s.object_id,'IsUserTable') = 1
and i.object_id = s.object_id
and i.index_id = s.index_id
order by (user_seeks + user_scans + user_lookups + user_updates) asc
CREATE VIEW sys.dm_db_index_usage_stats AS
SELECT database_id, object_id, index_id,
user_seeks, user_scans, user_lookups, user_updates,
last_user_seek, last_user_scan, last_user_lookup, last_user_update,
system_seeks, system_scans, system_lookups, system_updates,
last_system_seek, last_system_scan, last_system_lookup, last_system_update
FROM OpenRowSet(TABLE LOGINDEXSTATS)
WHERE status = 0
这个是sys.dm_db_index_usage_stats的定义
回复 引用 查看
SELECT database_id, object_id, index_id,
user_seeks, user_scans, user_lookups, user_updates,
last_user_seek, last_user_scan, last_user_lookup, last_user_update,
system_seeks, system_scans, system_lookups, system_updates,
last_system_seek, last_system_scan, last_system_lookup, last_system_update
FROM OpenRowSet(TABLE LOGINDEXSTATS)
WHERE status = 0
这个是sys.dm_db_index_usage_stats的定义
回复 引用 查看
#6楼
2009-01-16 19:41 |
Keep Walking
select b.name,a.* from sys.dm_db_index_usage_stats a inner join sysindexes b
on (a.object_id = b.id) order by a.user_seeks desc
这个还附带了name,供楼主参考吧,删除那些既不扫描也不查找的索引即是
on (a.object_id = b.id) order by a.user_seeks desc
这个还附带了name,供楼主参考吧,删除那些既不扫描也不查找的索引即是