SQLServer 维护脚本分享（10）索引

--可添加索引的字段
SELECT top 100 migs.user_seeks,migs.avg_total_user_cost,migs.avg_user_impact,migs.last_user_seek,mid.statement,mid.equality_columns,mid.included_columns,mid.inequality_columns,migs.unique_compiles 
FROM sys.dm_db_missing_index_group_stats migs (nolock)
inner join sys.dm_db_missing_index_groups mig (nolock) on migs.group_handle=mig.index_group_handle
inner join sys.dm_db_missing_index_details mid (nolock) on mig.index_handle=mid.index_handle
inner join sys.objects so (nolock) on mid.object_id=so.object_id
inner join sys.databases sd (nolock) on mid.database_id=sd.database_id
where so.is_ms_shipped=0
and sd.name = DB_NAME()
order by migs.avg_total_user_cost desc


--查看无用索引
SELECT  ind.index_id,obj.name AS TableName,ind.name AS IndexName,ind.type_desc,indUsage.user_seeks,indUsage.user_scans,indUsage.user_lookups,indUsage.user_updates,indUsage.last_system_seek,indUsage.last_user_scan,'drop index [' + ind.name + '] ON [' + obj.name + ']' AS DropIndexCommand  
FROM    sys.indexes AS ind (nolock) 
        INNER JOIN sys.objects AS obj(nolock) ON ind.object_id = obj.object_id  
        LEFT JOIN sys.dm_db_index_usage_stats indUsage(nolock)
         ON ind.object_id = indUsage.object_id AND ind.index_id = indUsage.index_id  
WHERE   ind.type_desc <> 'HEAP'  
        AND obj.type <> 'S'  
        AND OBJECTPROPERTY(obj.object_id,'isusertable') = 1  
        AND ( ISNULL(indUsage.user_seeks,0) = 0  
              AND ISNULL(indUsage.user_scans,0) = 0  
              AND ISNULL(indUsage.user_lookups,0) = 0  
            )  
ORDER BY obj.name,ind.name  



SELECT 
o.name,indexname=i.name,i.index_id,reads=user_seeks + user_scans + user_lookups,writes =  user_updates,rows = (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id),CASE
	WHEN s.user_updates < 1 THEN 100
	ELSE 1.00 * (s.user_seeks + s.user_scans + s.user_lookups) / s.user_updates
 END AS reads_per_write,'DROP INDEX ' + QUOTENAME(i.name) 
+ ' ON ' + QUOTENAME(c.name) + '.' + QUOTENAME(OBJECT_NAME(s.object_id)) as 'drop statement'
FROM sys.dm_db_index_usage_stats s  
INNER JOIN sys.indexes i ON i.index_id = s.index_id AND s.object_id = i.object_id   
INNER JOIN sys.objects o on s.object_id = o.object_id
INNER JOIN sys.schemas c on o.schema_id = c.schema_id
WHERE OBJECTPROPERTY(s.object_id,'IsUserTable') = 1
AND s.database_id = DB_ID()   
AND i.type_desc = 'nonclustered'
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
AND (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id) > 10000
ORDER BY reads


-- 返回最经常被修改的20个索引
SELECT top 20 * 
FROM sys.dm_db_index_operational_stats(NULL,NULL,NULL)
order by leaf_insert_count+leaf_delete_count+leaf_update_count desc
GO


-- 返回当前数据库所有碎片率大于25%的索引
-- 运行本语句会扫描很多数据页面
-- 避免在系统负载比较高时运行
SELECT 
 DB_NAME() as DB_NAME,OBJECT_NAME(s.object_id) as OBJECT_NAME,i.name index_name,i.type_desc,s.index_type_desc,s.alloc_unit_type_desc,s.page_count,s.fragment_count,s.avg_fragment_size_in_pages,s.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (db_id(),NULL) s,sys.indexes i
WHERE s.object_id=i.object_id and s.index_id=i.index_id
AND avg_fragmentation_in_percent>25
order by avg_fragmentation_in_percent desc


--索引页类型及使用情况
SELECT 
 OBJECT_NAME(OBJECT_ID) AS 表名,OBJECT_ID AS 对象ID,SUM(reserved_page_count) AS 已分配页数,SUM(used_page_count) AS 使用页数,SUM(CASE WHEN index_id < 2 
			THEN in_row_data_page_count+lob_used_page_count+row_overflow_used_page_count
			ELSE lob_used_page_count+row_overflow_used_page_count END) AS 数据页数,SUM(CASE WHEN index_id < 2 THEN row_count ELSE 0 END) AS 行数
FROM sys.dm_db_partition_stats 
WHERE OBJECT_ID IN(SELECT OBJECT_ID FROM sys.objects where type = 'U')
GROUP BY OBJECT_ID


SELECT object_name(object_id) AS 表名,object_id AS 对象ID,partition_number,filegroup_id,type_desc,CASE  WHEN index_id=0 THEN '堆索引(无聚集索引)' 
		WHEN index_id=1 THEN '聚集索引' 
		WHEN index_id BETWEEN 2 AND 250 THEN '非聚集索引' 
		ELSE 'text/image' END AS 存储方式,total_pages AS 已分配页数,used_pages AS 使用页数,data_pages AS 数据页数,rows AS 行数,first_page,root_page,first_iam_page
FROM sys.partitions p
inner join sys.system_internals_allocation_units s on p.hobt_id=s.container_id
WHERE OBJECT_ID IN(SELECT OBJECT_ID FROM sys.objects where type = 'U')
--	AND index_id in (0,1) 



--各表索引页的记录情况（指定表，否则太久）
SELECT o.name,ips.partition_number,ips.index_type_desc,ips.index_level,ips.page_count,ips.record_count,ips.compressed_page_count
FROM sys.dm_db_index_physical_stats ( DB_ID(),'DETAILED') ips
INNER JOIN sys.objects o on o.object_id = ips.object_id
WHERE o.name ='table'
ORDER BY o.name,ips.partition_number DESC;
/*
sys.dm_db_index_physical_stats ( 
    { database_id | NULL | 0 | DEFAULT },{ object_id | NULL | 0 | DEFAULT },{ index_id | NULL | 0 | -1 | DEFAULT },{ partition_number | NULL | 0 | DEFAULT },{ mode | NULL | DEFAULT }
)
*/


--各表索引的页页操作情况
select DB_NAME(database_id) DBName,object_name(s.object_id) tabletName,i.name indexName,leaf_insert_count,leaf_delete_count,leaf_update_count,range_scan_count,singleton_lookup_count,row_lock_count,row_lock_wait_count,page_lock_count,page_lock_wait_count
from sys.dm_db_index_operational_stats (db_id(),NULL) s
inner join sys.indexes i on i.object_id = s.object_id and i.index_id = s.index_id
where objectproperty(s.object_id,'IsUserTable') = 1
order by tabletName,indexName,partition_number
 

--(两次执行结果间)表的访问次数统计
select object_name(i.object_id) tabletName,isnull(sum(range_scan_count+singleton_lookup_count),0) as [read]
from sys.dm_db_index_operational_stats (db_id(),NULL) s
right join sys.indexes i on i.object_id = s.object_id and i.index_id = s.index_id
where objectproperty(i.object_id,'IsUserTable') = 1
and object_name(i.object_id) not like 'conflict%'
and exists(SELECT 1 FROM sys.tables t(nolock) WHERE i.object_id=t.object_id and is_ms_shipped=0)
group by object_name(i.object_id)
order by tabletName

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!