mssql 优化之索引部分

DBCC SHOWCONTIG [ (     { table_name | table_id | view_name | view_id }     [ , index_name | index_id ] ) ]     [ WITH         {          [ , [ ALL_INDEXES ] ]          [ , [ TABLERESULTS ] ]          [ , [ FAST ] ]          [ , [ ALL_LEVELS ] ]          [ NO_INFOMSGS ]          }     ]

declare @table_id int   set @table_id=object_id('表名')   dbcc showcontig(@table_id)

-- =============================================   -- Author: daiyueqiang -- Create date: 2012-12-31   -- Description: 查询当前数据库中缺失的索引，知道你进行优化的参考。   -- =============================================   SELECT user_seeks * avg_total_user_cost * ( avg_user_impact * 0.01 ) AS [index_advantage] ,   migs.last_user_seek , --上一次访问时间   mid.[statement] AS [Database.Schema.Table] ,--表   mid.equality_columns , --等式判断列   mid.inequality_columns ,--不等式判断列   mid.included_columns ,--于查询的涵盖列的逗号分隔列表。有关涵盖列或包含列的详细信息   migs.unique_compiles , --将从该缺失索引组受益的编译和重新编译数。许多不同查询的编译和重新编译可影响该列值   migs.user_seeks , --由可能使用了组中建议索引的用户查询所导致的查找次数   migs.avg_total_user_cost ,-- 可通过组中的索引减少的用户查询的平均成本   migs.avg_user_impact --实现此缺失索引组后，用户查询可能获得的平均百分比收益。该值表示如果实现此缺失索引组，则查询成本将按此百分比平均下降。   FROM sys.dm_db_missing_index_group_stats AS migs WITH ( NOLOCK )   INNER JOIN sys.dm_db_missing_index_groups AS mig WITH ( NOLOCK ) ON migs.group_handle = mig.index_group_handle   INNER JOIN sys.dm_db_missing_index_details AS mid WITH ( NOLOCK ) ON mig.index_handle = mid.index_handle   WHERE mid.database_id = DB_ID()--默认当前数据库。如果自己定义的数据库则使用DB_ID ( [ 'database_name' ] )   ORDER BY index_advantage DESC

-- ============================================= -- Author:  daiyueqiang -- Create date: 2012-12-31 -- Description: 查询当前数据库中所有堆表、 聚集的索引和非聚集索引、 读取、 写入和每个索引的填充因子的数量，知道你进行优化的参考。 --Index Read/Write stats (all tables in current DB) -- =============================================   SELECT OBJECT_NAME(s.[object_id]) AS [ObjectName] ,   i.name AS [IndexName] , i.index_id ,   user_seeks + user_scans + user_lookups AS [Reads] ,   user_updates AS [Writes] ,   i.type_desc AS [IndexType] ,   i.fill_factor AS [FillFactor]--填充因子   FROM sys.dm_db_index_usage_stats AS s   INNER JOIN sys.indexes AS i ON s.[object_id] = i.[object_id]   WHERE OBJECTPROPERTY(s.[object_id], 'IsUserTable') = 1   AND i.index_id = s.index_id   AND s.database_id = DB_ID()   ORDER BY OBJECT_NAME(s.[object_id]) ,   writes DESC ,   reads DESC ;

-- ============================================= -- Author:  daiyueqiang -- Create date: 2012-12-31 -- Description: 查询当前数据库中所有未使用的索引，知道你进行优化的参考。本sql的意思是，表的索引在数据库中未被使用，作为你进行下一步删除的依据。其中也可以加入时间判断 --List unused indexes -- ============================================= SELECT OBJECT_NAME(i.[object_id]) AS [Table Name] , i.name FROM sys.indexes AS i     INNER JOIN sys.objects AS o ON i.[object_id] = o.[object_id] WHERE i.index_id NOT IN (  SELECT s.index_id  FROM sys.dm_db_index_usage_stats AS s      WHERE s.[object_id] = i.[object_id]  AND i.index_id = s.index_id  AND database_id = DB_ID()     --下列条件作为时间判断，查看在某个时间之后未使用的索引列表，如果不需要可删除   AND  (  last_user_seek>='@DateTime' or   --用户上次执行搜索时间  last_user_scan>='@DateTime' or   --用户上次执行扫描时间  last_system_seek>='@DateTime' or --系统上次执行搜索的时间  last_system_scan>='@DateTime'    --系统上次执行扫描的时间  ) ) AND o.[type] = 'U' ORDER BY OBJECT_NAME(i.[object_id]) ASC

-- ============================================= -- Author:  daiyueqiang -- Create date: 2012-12-31 -- Description: 查询当前数据库中使用较少的索引或者写入次数大于读取次数的索引， --此查询会寻找有大量的零的读取和写入的任何索引。任何属于此类别的索引是删除 （在充分调查) 的合适选择,指导你进行优化的参考。 --Possible Bad NC Indexes (writes > reads) -- ============================================= SELECT OBJECT_NAME(s.[object_id]) AS [Table Name] , i.name AS [Index Name] , --索引名称 i.index_id , user_updates AS [Total Writes] ,--写入次数 user_seeks + user_scans + user_lookups AS [Total Reads] ,--读取次数 user_updates - ( user_seeks + user_scans + user_lookups ) AS [Difference]--写入与读取只差 FROM sys.dm_db_index_usage_stats AS s WITH ( NOLOCK ) INNER JOIN sys.indexes AS i WITH ( NOLOCK ) ON s.[object_id] = i.[object_id] AND i.index_id = s.index_id WHERE OBJECTPROPERTY(s.[object_id], 'IsUserTable') = 1 AND s.database_id = DB_ID() AND user_updates > (user_seeks + user_scans + user_lookups ) AND i.index_id > 1 --聚集索引和非聚集索引 ORDER BY [Difference] DESC , [Total Writes] DESC , [Total Reads] ASC ;