mssql 优化之索引部分

mssql 优化之索引部分

索引组成、产生原理、解决方法、优化方法

1. 索引组成、产生原理、解决方法：http://wenku.baidu.com/view/a45a827d27284b73f2425071.html
2. 优化方法（国外著名Ola Hallengren数据库专家，微软官方推荐）：http://download.csdn.net/detail/daiyueqiang/5272815

显示指定的表或视图的数据和索引的碎片信息

方法：DBCC SHOWCONTIG

语法：

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

　 

后续说明：

具有较高的 index_advantage 的索引那些 SQL 服务器认为会产生最大的积极影响，减少工作量，基于查询的成本和预期他们会使用索引的次数减少。

查看现有索引的使用情况 

-- =============================================

-- 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 ;

 这是一个有用的查询，为更好地了解数据库的工作负荷。它可以帮助您确定某个特定的索引的波动性和写入数据的读取的比率。这可以帮助您改进和优化您的索引策略。例如，如果您有一个表，是相当静态 （很少写入任何索引），你可能会更有信心有关添加更多的索引在你失踪的索引查询中列中。

如果您使用的是 SQL Server 2008 企业版，此查询可以帮助您决定是否会启用数据压缩 （页或行） 的好主意。具有很少写活动的索引很可能是更合适数据压缩比波动性更大的索引。

查询未使用的索引 

-- =============================================

-- 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 ;