--可添加索引的字段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_handleinner join sys.dm_db_missing_index_details mid (nolock) on mig.index_handle=mid.index_handleinner join sys.objects so (nolock) on mid.object_id=so.object_idinner join sys.databases sd (nolock) on mid.database_id=sd.database_idwhere so.is_ms_shipped=0and 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_idINNER JOIN sys.schemas c on o.schema_id = c.schema_idWHERE OBJECTPROPERTY(s.object_id,'IsUserTable') = 1AND s.database_id = DB_ID()   AND i.type_desc = 'nonclustered'AND i.is_primary_key = 0AND i.is_unique_constraint = 0AND (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id) > 10000ORDER 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 descGO-- 返回当前数据库所有碎片率大于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_percentFROM sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, NULL) s,sys.indexes iWHERE s.object_id=i.object_id and s.index_id=i.index_idAND avg_fragmentation_in_percent>25order 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_IDSELECT 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_pageFROM sys.partitions pinner join sys.system_internals_allocation_units s on p.hobt_id=s.container_idWHERE 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_countFROM sys.dm_db_index_physical_stats ( DB_ID(), NULL, NULL, NULL, 'DETAILED') ipsINNER JOIN sys.objects o on o.object_id = ips.object_idWHERE 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_countfrom sys.dm_db_index_operational_stats (db_id(), NULL, NULL, NULL) sinner join sys.indexes i on i.object_id = s.object_id and i.index_id = s.index_idwhere objectproperty(s.object_id,'IsUserTable') = 1order 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) sright join sys.indexes i on i.object_id = s.object_id and i.index_id = s.index_idwhere objectproperty(i.object_id,'IsUserTable') = 1and 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