SQL 2005 之 如何使用 DMV 傳回有關遺漏索引的詳細資訊

  • Article

索引建立的好與壞，對 SQL Server 執行的效能影響很大，SQL Server 2005 提供的 DMV 可以協助提供資料表是否有遺漏索引。

詳細的使用範例如下：

1. 使用 tempdb 建立範例資料

use tempdb
go
if exists (select * from dbo.sysobjects where id = object_id(N'[tbl1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [tbl1]
GO

create table tbl1
(學號 int,學生姓名 varchar(20),性別 char(2),年齡 int,入學時間 datetime,備註 char(3000))
go

declare @i int
set @i=0
declare @j int
set @j=0

while @i<50000
begin
if (rand()*10>3) set @j=1 else set @j=0
insert into tbl1 values(@i,
char(rand()*10+100)+char(rand()*5+50)+char(rand()*3+100)+char(rand()*6+80),
@j, 20+rand()*10,convert(varchar(20), getdate()-rand()*3000,112),
char(rand()*9+100)+char(rand()*4+50)+char(rand()*2+130)+char(rand()*5+70))
set @i=@i+1
end
go

-- 執行查詢動作

Select count(學號) from tbl1

Select 學生姓名,入學時間 from tbl1 where 學號=972

2. 使用 sys.dm_db_missing_index_details 查詢相關資訊

/* sys.dm_db_missing_index_details 傳回的資訊會在查詢最佳化工具進行最佳化查詢時更新，而不會一直保存。
遺漏索引資訊只會保留到 SQL Server 重新啟動為止。如果資料庫管理員想要在伺服器回收之後保留遺漏索引資訊，
應該定期製作該項資訊的備份副本。 */

/* 若要判斷特定遺漏索引屬於哪些遺漏索引群組，您可以依據 index_handle 資料行，
將該遺漏索引與 sys.dm_db_missing_index_details 等聯結，以便查詢 sys.dm_db_missing_index_groups 動態管理檢視。
*/

Use Tempdb
Go

--- suggested index columns and usage
declare @handle int

select @handle = d.index_handle
from sys.dm_db_missing_index_group_stats s
,sys.dm_db_missing_index_groups g
,sys.dm_db_missing_index_details d
where s.group_handle = g.index_group_handle
and d.index_handle = g.index_handle

select *
from sys.dm_db_missing_index_columns(@handle)
order by column_id

回傳結果 - 顯示可能在『學號』欄位少了索引

column_id column_name column_usage
----------- ------------------- ------------------
1 學號 EQUALITY

(1 個資料列受到影響)