# 如何找出 SQL Server 2008 資料庫裡是否建立了多餘的索引

講到 SQL Server 2008 效能調校，這可是一門大大的學問，但是若能掌握一些基本的 SQL Server 資料庫運作原理，要能成為 SQL Server 效能調校的專家其實並不困難。我們都知道學習這些原理並不難，看書就會了，難的是如何才能在實務管理工作上真正的融會貫通、靈活運用這些已知的知識，再搭配一些方便的工具幫你快速取得所要的重要資訊，進而做出效能調校的判斷與改善現有資料庫的效能問題。今天我打算來分享一個分析索引使用量統計的技巧，來改善資料庫「寫入」效率的問題。

一般來說，我們在做資料庫效能調校時，都會想調整「讀取」資料的效能，也就是建立「非叢集索引」來縮短篩選與取得資料的時間。不過，建立過多的索引卻會導致資料寫入到資料庫時的效能變差 (註: 寫入資料包括 INSERT、UPDATE、DELETE 等操作)，因為寫入資料表時，如果該資料表有建立非叢集索引的話，就會導致要多寫好幾份資料到資料庫中，因此越多的索引就會寫入越多的資料，相對的也會拉長寫入完成的時間，當應用程式或網站的流量一大，慢慢就會感受到效能變差的情況。

備註：由於改善「讀取」效能的技巧非常多，日後如果有機會我可以多分享一些關於這方面的調校技巧。

首先，我們先開啟 Management Studio，然後選取你要分析的資料庫，並且參考下圖打開該資料庫的【索引使用量統計資料】報表： ( 點圖可放大顯示 )

[![](https://stwillblogassets.blob.core.windows.net/files/images/external/stwillblogassets.blob.core.windows.net/10cdbc3b3e32a8eb2663-SNAGHTML29a5934_thumb.png)](https://stwillblogassets.blob.core.windows.net/files/images/external/stwillblogassets.blob.core.windows.net/77de415df76531872bac-SNAGHTML29a5934.png)

開啟報表後我們進一步展開「索引使用量統計資料」的資料，這時就能看到所有索引使用量的統計資料：

![](https://stwillblogassets.blob.core.windows.net/files/images/external/stwillblogassets.blob.core.windows.net/4d5c8ba46b28b3497be5-image_1006.png)

在這份報表裡，我挑選了一個資料表來分析該資料表所建立的所有索引的使用狀況，在這裡有四個重要的欄位可以進行分析，分別是 **索引類型**、**使用者搜尋次數**、**使用者掃瞄次數**、**使用者更新次數**，這幾個欄位分別說明如下：

-   **索引類型：CLUSTERED 代表「叢集索引」，NONCLUSTERED 代表「非叢集索引」**
-   以下欄位所代表的數字都是從上次資料庫執行個體 (DB Instance) 開始執行後到現在為止的次數。
-   **使用者搜尋次數：**該索引被真正使用到的次數，而且該索引回傳的資料沒有讀取整份索引的資料
-   **使用者掃瞄次數：**該索引被完整掃瞄過的次數，也就是所謂的 索引掃瞄 (Index Scan) 的狀況。如果該索引屬於 **叢集索引** 的話，那就代表該表格被 **表格掃瞄** (**Table Scan**) 的次數。通常這裡的數字越高也意味著這不是一個好的索引，應該可以進一步重新規劃索引。
-   **使用者更新次數：**該索引被更新寫入的總次數，也就是當執行到 INSERT, UPDATE, DELETE 指令並且異動到該索引的次數。

![](https://stwillblogassets.blob.core.windows.net/files/images/external/stwillblogassets.blob.core.windows.net/8c023d5ff41efc6de024-image_1007.png)

接著我依據上圖的標號順序依序來解說如何進一步分析這些數字背後代表的意義：

1.  這個索引的「**使用者更新次數**」為 166,903 次，代表從上次資料庫執行個體 (DB Instance) 開始執行後到現在為止該索引被更新寫入的總次數。而「**使用者搜尋次數**」有高達 1,331,587 次，這代表著這個索引有被充分利用到，這樣的索引才真正對應用程式查詢資料時有幫助。
2.  這個索引的「**使用者更新次數**」為 166,903 次，但是「**使用者搜尋次數**」只有 29 次而已，這麼大的寫入量，相對於僅有 29 次的查詢有用到這個索引，這時就可以考慮是否要保留這個索引，因為這樣的索引會導致 SQL Server 在寫入資料時會多寫一份到這個索引來，而查詢資料時又不常會用到他，這樣就顯的有點多餘。
3.  這個索引的「**使用者更新次數**」為 334,564 次，但是「**使用者搜尋次數**」竟然是 0，而且「**使用者掃瞄次數**」有 354 次，這代表寫入該索引的次數非常多，但是有效率的使用該索引為 0，而無效率的索引使用次數為 354 次，很明顯的這個索引可以重新設計過才對。
4.  這個索引的「**使用者更新次數**」為 166,903 次，但是「**使用者搜尋次數**」與「**使用者掃瞄次數**」都是 0，這也意味著這個索引完全沒用到，建立他根本就是用來消耗 Disk I/O 資源的雞肋，應該于以立即刪除該索引才對！
5.  這一條是「**叢集索引**」，更新次數與使用者搜尋次數都差不多，這點到還好，但是「**使用者掃瞄次數**」有 18 次，這代表著從上次資料庫執行個體 (DB Instance) 開始執行後到現在為止總共有 18 次的機會執行到 Table Scan 的狀況，如果該資料表資料非常多，將會導致這 18 次的查詢效能低落，遇到這種情況也能進一步分析是否有更好的索引可以來建立（建立遺失索引）。

你可以仔細思考上述分析的過程，相信對你分析資料表中是否有多餘的索引的情況將很有幫助。

**後記**

我們的客戶剛好就是有這麼一個資料表可以讓我用來解釋不同的索引使用情境，真的還蠻巧的，也正因為如此剛好可以讓我花些時間寫出這篇文章來，所以寫文章不只要有靈感，還要有緣份才行。 ^\_^

**相關連結**

-   [SQL Server 2005 - 索引的評選與分析 - Part 1](https://dotblogs.com.tw/limingstudio/2009/11/12/11587)
-   [SQL Server 2005 - 索引的評選與分析 - Part 2](https://dotblogs.com.tw/limingstudio/2009/11/12/11588)
-   [Database Engine Tuning Advisor 概觀](http://msdn.microsoft.com/zh-tw/library/ms173494.aspx)
-   [How To: Optimize SQL Indexes](http://msdn.microsoft.com/en-us/library/ff650692.aspx)
-   [SQL Server Best Practices](http://msdn.microsoft.com/en-us/sqlserver/bb671432)
-   [Tips, Tricks, and Advice from the SQL Server Query Processing Team](https://learn.microsoft.com/en-us/archive/blogs/sqlqueryprocessing)
-   [DROP INDEX (Transact-SQL)](http://msdn.microsoft.com/zh-tw/library/ms176118.aspx)
-   I'm a DBA - 微軟年度 SQL Server DBA 大會 - [簡報下載](http://www.microsoft.com/taiwan/events/slides/download_1103.aspx) ( DBA Performance Turning )