簡訊發送平台上線一段時間後，就會有大量發送資料儲存在上面，這時候會遇到一個考驗是資料庫的索引設定的是否適當，是否有SQL語法開發時期效能測試適中，但遇到正式環境裡有大量資料時反而變成效能瓶頸。故定期檢查與調整索引是必要的工作。這篇文章就是來介紹調整索引的技巧。
Step 1.找出效能不好的索引：
這邊效能不好的索引指的是在SQL執行計畫裡是用掃描(Index Scan)而不是搜尋(Index Seek)的方式使用索引，這表示該索引是從第一筆索引逐筆讀到最後一筆索引，而沒有利用到索引的B-Tree結構快速找到資料。
找尋效能不好索引的方式有兩種，第一種是透過Microsoft SQL Server Management Studio點選資料庫的右鍵→報表→索引使用量統計報表
該報表會顯示所有Table的索引使用數據(此數據是從資料庫服務啟動或索引重建後開始計算)，請注意下圖的使用者掃描次數欄位，數字越大者的索引就是接下來要鎖定調整的目標。
第一種方法的缺點是要人工介入來檢查報表，如果想要將這種檢查工作自動化，則可以使用第二種方法：
SELECT DB_Name(S.database_id) as [DB_NAME],OBJECT_NAME(S.[OBJECT_ID]) AS [TABLE_NAME],I.[NAME] AS [INDEX_NAME],USER_SEEKS,USER_SCANS,USER_LOOKUPS,USER_UPDATESFROM SYS.DM_DB_INDEX_USAGE_STATS AS SINNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = S.[OBJECT_ID] AND I.INDEX_ID = S.INDEX_IDWHERE OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1AND S.database_id = DB_ID()--AND S.database_id = DB_ID('資料庫名稱')--AND S.object_id = OBJECT_ID('資料表名稱')--AND i.name = 'Index名稱'ORDER BY [TABLE_NAME],[INDEX_NAME]
註解的語法可以視需要填加上去過濾條件，請注意下圖的USER_SCANS欄位，數字越大者的索引就是接下來要鎖定調整的目標。
Step 2. 找出使用索引效能不佳的SQL語法：
從資料庫cache所留存的SQL語法執行計畫，找出有哪些SQL語法使用了待調整的目標索引。
(註1：須注意久久執行一次的語法可能不會留在cache裡，所以此方法不保證可以找到所有有用到此索引的語法)
(註2：當資料庫的交易量大時，此查詢會花費一些時間)
直接執行的結果是列出該SQL Server上所有有掃描行為的SQL語法；移除註解並將Step 1找到的索引名稱填入，則是列出指定索引有掃描行為的SQL語法。
;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS qp)SELECT decp.usecounts, QueryPlan = deqp.query_planFROM sys.dm_exec_cached_plans decpCROSS APPLY sys.dm_exec_query_plan(decp.plan_handle) deqpWHERE ( deqp.query_plan.exist('/qp:ShowPlanXML//qp:TableScan') = 1 --AND deqp.query_plan.exist('//*[@Index=''[索引名稱]'']') = 1 AND EXISTS ( SELECT 1 FROM deqp.query_plan.nodes('/qp:ShowPlanXML//qp:TableScan/qp:Object') ts(x) WHERE ts.x.exist('@Table[contains(., "@")]') = 0 --exclude scans of table variables AND ts.x.exist('@Table[contains(., "#")]') = 0 --exclude scans of temp tables AND ts.x.query('..').exist('//qp:SeekPredicates') = 0 --exlude seeks ))OR ( deqp.query_plan.exist('/qp:ShowPlanXML//qp:IndexScan') = 1 --AND deqp.query_plan.exist('//*[@Index=''[索引名稱]'']') = 1 AND EXISTS ( SELECT 1 FROM deqp.query_plan.nodes('/qp:ShowPlanXML//qp:IndexScan/qp:Object') ts(x) WHERE ts.x.exist('@Table[contains(., "@")]') = 0 --exclude scans of table variables AND ts.x.exist('@Table[contains(., "#")]') = 0 --exclude scans of temp tables AND ts.x.query('..').exist('//qp:SeekPredicates') = 0 --exlude seeks ))ORDER BY decp.usecounts DESC;
下圖為不指定目標索引時的執行結果，第一欄為SQL語法的執行次數，第二欄為SQL執行計畫，可點開來看。
下圖為執行計畫點開後的畫面，小紅框分別顯示了使用的索引名稱，以及該索引使用了索引掃描還是索引搜尋；上方黑字為Step 2所要找的目標SQL語法。
可點選下圖兩處紅框將目標SQL語法蒐集起來。
Step 3.找出常被執行，且會造成索引掃描的語法後，幫其建立適當的索引：
若是影響效能很大的SQL語法，點開執行計畫時，會看到上方有綠色字顯示遺漏索引，MS SQL會主動建議要建立該索引以調整SQL語法的效能，點選綠色字→右鍵→遺漏索引詳細資料
遺漏索引詳細資料：
有時上方不會有綠色字顯示，就要依照一般建立索引的建議去建立Step 2所找的SQL語法的索引。當然並不是說一定要建立索引，索引太多時也會影響Insert、Update、Delete的效能。
Step 4.檢查索引的使用狀況是否已改善：
重新使用Step 1的語法或報表檢查資料表裡的各索引使用狀況，確認索引掃描的狀況有改善，就可以休工了。
Reference: