如何管理遺失索引與維護索引的最佳數量

        在我的前面幾篇中已有介紹到多種進行索引的管理與維護的方法，但本篇再介紹實際上Index的數量與Missing Indexes的相關介紹，基本上相信大家多少對Missing Indexes有相當的了解，所謂Missing Indexes就是你的查詢在執行時，由於沒有增加索引，所以造成查詢必需從頭掃到尾，也就是造成Table Scan。

         在進行分析後，許多的客戶對於Missing Indexes有點難抉擇，因為他們的表格中已有相當多的Index存在，加了之後反而可能會有效能更慢的問題，的確Index會加快Select的速度，減少 Table Scan，但是相對的也會增加Insert與Update的執行時間，所以本篇我們就來介紹如何進行這些相關的作業。

先行參考文章：
索引分析與維護建議
http://caryhsu.blogspot.tw/2012/02/blog-post_14.html
SQL Server 上大型資料表的索引效能調整與維護
http://caryhsu.blogspot.tw/2011/08/sql-server.html
效能調整 - 填滿因數對資料庫的影響
http://caryhsu.blogspot.tw/2012/02/blog-post.html


Missing Indexes查詢：
下列的語法可以查出目前系統 "從開機" 到目前的執行統計，藉以找出查詢中沒有使用到Index也就是使用Table Scan的查詢有那些。

PS:因為下列的語法是透過DMV進行查詢，所以如果你希望一直保持記錄與資訊的話，建議你可以將資訊回寫於Table中。

SELECT Top 10
    statement AS [database.scheme.table],
    column_id , column_name, column_usage,
    migs.user_seeks, migs.user_scans,
    migs.last_user_seek, migs.avg_total_user_cost,
    migs.avg_user_impact
FROM sys.dm_db_missing_index_details AS mid
    CROSS APPLY sys.dm_db_missing_index_columns (mid.index_handle)
    INNER JOIN sys.dm_db_missing_index_groups AS mig
    ON mig.index_handle = mid.index_handle
    INNER JOIN sys.dm_db_missing_index_group_stats AS migs
    ON mig.index_group_handle=migs.group_handle
ORDER BY migs.avg_user_impact DESC


基本上述的查詢在Column_usage有三種類型，EQUALITY、INEQUALITY、INCLUDE，在建立Index也會不同，詳細的資訊可以參考下列的第一個網址，將會介紹如何透過此語法並實際的建立Index。

現在另外一個問題來了，就是如果我的表格中已有多個Index時，是否該如何選擇合適的Index，所以透過上一個查詢中找出Missing Indexes的部份，當然也可以找出較不常用的Index，所以就可以藉此將沒有使用到或不常使用的索引給刪除，藉以提高索引的最大效能。


找出未使用的索引(Index)：
SELECT OBJECT_SCHEMA_NAME(I.OBJECT_ID) AS SchemaName,
       OBJECT_NAME(I.OBJECT_ID) AS ObjectName,
       I.NAME AS IndexName
FROM sys.indexes I
WHERE
-- only get indexes for user created tables
OBJECTPROPERTY(I.OBJECT_ID, 'IsUserTable') = 1
-- find all indexes that exists but are NOT used
AND NOT EXISTS (
SELECT index_id
FROM sys.dm_db_index_usage_stats
WHERE OBJECT_ID = I.OBJECT_ID
               AND I.index_id = index_id
               -- limit our query only for the current db
               AND database_id = DB_ID())
ORDER BY SchemaName, ObjectName, IndexName
找出較不常使用的索引(Indexes)：
declare @dbid int
select @dbid = db_id()

select objectname=object_name(s.object_id), s.object_id,
          indexname=i.name, i.index_id,
          user_seeks, user_scans, user_lookups, user_updates
from sys.dm_db_index_usage_stats s, sys.indexes i
where database_id = @dbid and
      objectproperty(s.object_id,'IsUserTable') = 1
      and i.object_id = s.object_id
      and i.index_id = s.index_id
order by (user_seeks + user_scans + user_lookups + user_updates) asc


參考網址：

• Using Missing Index Information to Write CREATE INDEX Statements
• http://msdn.microsoft.com/en-us/library/ms345405.aspx
• SQL Server Indexes Best Practices
• http://msdn.microsoft.com/library/cc917672
• Indexes IN SQL 2005/2008
• http://www.sqlskills.com/BLOGS/KIMBERLY/post/Indexes-in-SQL-Server-20052008-Best-Practices-Part-1.aspx
• Statistics Used by Query Optimizer
• http://technet.microsoft.com/en-us/library/cc966419.aspx
• How can SQL Server 2005 help me evaluate and manage indexes?
• http://blogs.msdn.com/b/sqlcat/archive/2006/02/13/531339.aspx
• A performance profile of OLTP applications
• http://blogs.msdn.com/b/sqlcat/archive/2006/06/23/tom-davidson-sqlcat-best-practices.aspx

關鍵字：SQL Server、Index、Miss Indexes、how to evaluate and manage indexes、索引維護