SQL索引使用效率
SQL SERVER往往有一堆索引，但是如何了解這些索引有正常運作? 可以透過語法了解
主要Table
sys.object
查詢各Table的Object_Id
sys.indexes
查詢每個Index的名稱、方式(HEAP、CLUSTERD、NONCLUSTERED)
sys.dm_db_index_usage_stats
每個Index的使用率
SELECT
OBJECT_NAME(I.object_id) AS table_name,
I.name AS index_name,
S.user_seeks,
S.user_scans,
S.user_lookups,
S.user_updates,
I.type_desc,
CASE
WHEN last_user_seek is not null then last_user_seek
WHEN last_user_scan is not null then last_user_scan
else last_user_update
END as user_time
FROM
sys.objects AS O
LEFT JOIN sys.indexes AS I ON O.object_id = I.object_id
LEFT JOIN sys.dm_db_index_usage_stats AS S ON S.object_id = I.object_id AND S.index_id=I.index_id
WHERE O.name = 'NineYiSalePage'
ORDER BY user_time desc;
查詢範例
| table_name | index_name | user_seeks | user_scans | user_lookups | type_desc | user_time |
|---|---|---|---|---|---|---|
| YFSStockTransaction | IndexWarehouseName | 682 | 184 | 0 | NONCLUSTERED | 38:03.2 |
| YFSPickingOrder_Log | INDEX_PickingNo | 0 | 1 | 0 | NONCLUSTERED | 29:33.8 |
| YFSPickingOrder_Log | IX_TransferDate | 0 | 1 | 0 | NONCLUSTERED | 49:56.0 |
| YFSPickingOrder | IX_OrderDate | 0 | 7 | 0 | NONCLUSTERED | 58:49.2 |
| YFSPickingOrder | IX_StockData | 3 | 68 | 0 | NONCLUSTERED | 39:23.4 |
| YFS_SerialTemp | IX_SerialNo | 0 | 0 | 0 | NONCLUSTERED | 13:35.8 |
| YFS_OrderTemp | IX_EShopNo | 6 | 3 | 0 | NONCLUSTERED | 15:05.4 |
| YFS_OrderStatus | IX_YFS_OrderStatus | 6006 | 111828 | 0 | NONCLUSTERED | 03:59.9 |
| YFS_OrderMaster | missing_index_1 | 18175 | 21105 | 0 | NONCLUSTERED | 14:25.4 |
| YFS_OrderList | YFS_OrderListSynchronousOrder2 | 234 | 4303 | 0 | NONCLUSTERED | 14:08.5 |
| YFS_OrderList | <Name of Missing Index, sysname,> | 1 | 10707 | 0 | NONCLUSTERED | 40:56.4 |
| YFS_OrderDetail | IX_index_YFS_OrderDetail | 0 | 5638 | 0 | NONCLUSTERED | 14:30.5 |
欄位說明：
- user_seeks：這個索引被使用在 index seek 次數
- user_scans：這個索引被使用在 index scan 次數
- user_lookups：這個索引被使用在 index lookup 次數
- user_updates：索引更新次數
- last_user_seek：上一次執行 index seek 的時間
- last_user_scan：上一次執行 index scan 的時間
- last_user_lookup：上一次執行 index lookup 的時間
在查詢結果中，你應該關注的是：
- 若 user_seeks 或 user_scans 值都很低，表示這個索引很少在使用。
- 若 user_seeks 很低， user_scans 很高，表示這個索引不是一個好的索引，應該可以進一步重新規劃。
- 若 user_updates 很高，可以檢查看看，是否有不必要加入索引的欄位。
找到MSSQL 索引破碎的問題
SELECT OBJECT_NAME(dt.object_id),si.name,dt.avg_fragmentation_in_percent,dt.avg_page_space_used_in_percent
FROM
(SELECT object_id,index_id,avg_fragmentation_in_percent,avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'DETAILED')
WHERE index_id <> 0
) AS dt
INNER JOIN sys.indexes si
ON si.object_id = dt.object_id
AND si.index_id = dt.index_id