--可添加索引的字段 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, 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, NULL, NULL, 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(), NULL, NULL, NULL, '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 ,partition_number ,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, NULL, 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, NULL, 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