uncategorized

找DataBase資料表的Miss Index並建立

一般開發系統人員常常遇到當自己系統跑一段時間後，就會被使用者抱怨說系統怎越跑越慢，當然，系統越跑越慢的因素很多，其中一項就是Table該要有的Index卻沒有建立，在系統初期設計上，不是很容易訂定有效的，不過，當系統越來越大時候，透過SQL Server的統計資訊分析後，去找出較為精準的Index反而會簡單一點，且Index不是建立越多越好，不好的Index反而會讓系統效能變低。

因此，在有一次聽過百敬老師的DB效能調教課程後，原來，可以透過T-SQL去找出目前DB中有哪些資料表是缺乏Index的然後，先針對這些Miss Index資訊建立Index，可算是一個比較安全建立Index方式，其語法如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
SELECT  DB_NAME(database_id) AS DB_NAME ,
        OBJECT_NAME(object_id, database_id) AS Table_Name ,
        MisDetail.equality_columns [相等欄位] ,
        MisDetail.inequality_columns [不等欄位] ,
        MisDetail.included_columns [覆蓋欄位] ,
        ( user_seeks + user_scans ) * avg_total_user_cost * ( avg_user_impact
                                                              * 0.01 ) [建索引可降低的成本] ,
        MisStatus.unique_compiles [可用此索引的已編譯執行計畫之數量] ,
        MisStatus.user_seeks [使用者搜尋次數] ,
        MisStatus.user_scans [使用者掃描次數] ,
        MisStatus.avg_total_user_cost [平均使用者可以降低的成本] ,
        MisStatus.avg_user_impact [使用後成本佔原成本的百分率] ,
        'use ' + DB_NAME(database_id) + ' create index idx'
        + OBJECT_NAME(object_id, database_id) + '_missing_'
        + CONVERT(VARCHAR(10), MisDetail.index_handle) + ' on ['
        + OBJECT_NAME(object_id, database_id) + ']('
        + ISNULL(MisDetail.equality_columns, '')
        + CASE WHEN MisDetail.equality_columns IS NOT NULL
                    AND MisDetail.inequality_columns IS NOT NULL THEN ','
               ELSE ''
          END + ISNULL(MisDetail.inequality_columns, '') + ')
' + ISNULL('include (' + included_columns + ')
', '') AS SQL_Statment
FROM    sys.dm_db_missing_index_group_stats MisStatus WITH ( NOLOCK )
        JOIN sys.dm_db_missing_index_groups MisGroup WITH ( NOLOCK ) ON MisStatus.group_handle = MisGroup.index_group_handle
        JOIN sys.dm_db_missing_index_details MisDetail WITH ( NOLOCK ) ON MisGroup.index_handle = MisDetail.index_handle
ORDER BY 1 ,
        2 ASC

在SQL_Statment欄位中，是顯示要建立Index語法，只要把內容Copy出來就可以建立Index了，省去還要去撰寫建立Index的SQL語法。

1
2
3
USE msdb
CREATE INDEX idxsysjobhistory_missing_97620 ON [sysjobhistory]([job_id])
INCLUDE ([instance_id], [step_id], [step_name], [message], [run_status], [run_date], [run_time], [run_duration], [operator_id_emailed], [operator_id_netsent], [operator_id_paged])

個人認為因該先從使用者搜尋次數高的優先找尋要建立Index的資訊，畢竟使用者次數少的，可能只是IT人員自行下條件搜尋，並非是系統再使用的，因此，建立Index後可能效益也不大。然後再找建索引可降低的成本最高和平均使用者可以降低的成本最高的為優先考量，透過這樣簡單分析，去建立比較可靠的Index，比胡亂建立Index有效益多了

Share