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


索引建立的好與壞,對 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 個資料列受到影響)

 

Skip to main content