如何尋找MS SQL效能不好的索引

如何尋找MS SQL效能不好的索引

簡訊發送平台上線一段時間後,就會有大量發送資料儲存在上面,這時候會遇到一個考驗是資料庫的索引設定的是否適當,是否有SQL語法開發時期效能測試適中,但遇到正式環境裡有大量資料時反而變成效能瓶頸。故定期檢查與調整索引是必要的工作。這篇文章就是來介紹調整索引的技巧。

Step 1.找出效能不好的索引:

這邊效能不好的索引指的是在SQL執行計畫裡是用掃描(Index Scan)而不是搜尋(Index Seek)的方式使用索引,這表示該索引是從第一筆索引逐筆讀到最後一筆索引,而沒有利用到索引的B-Tree結構快速找到資料。

 

找尋效能不好索引的方式有兩種,第一種是透過Microsoft SQL Server Management Studio點選資料庫的右鍵→報表→索引使用量統計報表

如何尋找MS SQL效能不好的索引

 

該報表會顯示所有Table的索引使用數據(此數據是從資料庫服務啟動或索引重建後開始計算),請注意下圖的使用者掃描次數欄位,數字越大者的索引就是接下來要鎖定調整的目標。

如何尋找MS SQL效能不好的索引

 

第一種方法的缺點是要人工介入來檢查報表,如果想要將這種檢查工作自動化,則可以使用第二種方法:

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_UPDATES
FROM SYS.DM_DB_INDEX_USAGE_STATS AS S
INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = S.[OBJECT_ID] AND I.INDEX_ID = S.INDEX_ID
WHERE OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1
AND 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欄位,數字越大者的索引就是接下來要鎖定調整的目標。

如何尋找MS SQL效能不好的索引

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_plan
FROM sys.dm_exec_cached_plans decp
CROSS APPLY sys.dm_exec_query_plan(decp.plan_handle) deqp
WHERE (
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執行計畫,可點開來看。

如何尋找MS SQL效能不好的索引

 

下圖為執行計畫點開後的畫面,小紅框分別顯示了使用的索引名稱,以及該索引使用了索引掃描還是索引搜尋;上方黑字為Step 2所要找的目標SQL語法。

如何尋找MS SQL效能不好的索引

 

可點選下圖兩處紅框將目標SQL語法蒐集起來。

如何尋找MS SQL效能不好的索引

 

Step 3.找出常被執行,且會造成索引掃描的語法後,幫其建立適當的索引:

若是影響效能很大的SQL語法,點開執行計畫時,會看到上方有綠色字顯示遺漏索引,MS SQL會主動建議要建立該索引以調整SQL語法的效能,點選綠色字→右鍵→遺漏索引詳細資料

如何尋找MS SQL效能不好的索引

 

遺漏索引詳細資料:

如何尋找MS SQL效能不好的索引

 

有時上方不會有綠色字顯示,就要依照一般建立索引的建議去建立Step 2所找的SQL語法的索引。當然並不是說一定要建立索引,索引太多時也會影響Insert、Update、Delete的效能。

 

Step 4.檢查索引的使用狀況是否已改善:

重新使用Step 1的語法或報表檢查資料表裡的各索引使用狀況,確認索引掃描的狀況有改善,就可以休工了。

 

Reference:

  1. https://www.sqlservercentral.com/forums/topic/find-all-queries-which-use-a-perticular-index
  2. https://t.codebug.vip/questions-483018.htm

發表迴響

這個網站採用 Akismet 服務減少垃圾留言。進一步瞭解 Akismet 如何處理網站訪客的留言資料