快速取得單一 Table 運行相關資訊 ( 空間,索引,索引遺漏....)

2013-07-31

1631
0
SQL
2021-09-01

摘要:取得單一表單 , 摘要資訊.

在效能調校時，有時候需要快速瞭解一個 Table 的資料使用量、索引使用狀況、索引遺漏狀況等等資訊，雖然 SQL工具有 UI報表可以用，但散布在不同功能中，有時在線上環境打火救災時，更可能因為系統已經處於一個忙碌到不行的狀態，透過UI工具開完相關功能可能又要耗費好長時間的等待，這時如果直接用 T-SQL 去撈取這些資訊的話速度上會快很多。

declare @TableName nvarchar(255);
set @TableName = N'某資料表';

/* 取得空間使用資料 */
exec sp_spaceused @TableName;

/* 取得索引資訊 */
exec sp_helpindex @TableName;

/* 取得 DMV 索引使用狀況 */
select 
	tab.name [TableName], inx.name [IndexName] , inx.type_desc ,
	s.user_seeks , s.user_scans , s.user_updates 
from sys.tables tab
join sys.indexes inx on
	tab.object_id = inx.object_id and inx.type > 0
left join sys.dm_db_index_usage_stats s on
	s.database_id = db_id() and
	s.object_id = inx.object_id and
	s.index_id = inx.index_id 
where tab.name = @TableName;

/* 取得 DMV 中索引遺漏資訊 */
select 
	tab.Name [TableName] ,
	MD.equality_columns ,
	MD.inequality_columns ,
	MD.included_columns ,
	MS.unique_compiles ,
	MS.user_seeks ,
	MS.user_scans ,
	MS.avg_user_impact ,
	MS.avg_total_user_cost ,
	avg_total_user_cost * avg_user_impact * (user_seeks + user_scans) [預期效益]
from sys.tables tab
join sys.dm_db_missing_index_details MD on
	MD.database_id = db_id() and
	MD.object_id = tab.object_id
join sys.dm_db_missing_index_groups MG on
	MD.index_handle = MG.index_handle
join sys.dm_db_missing_index_group_stats MS on
	MG.index_group_handle = MS.group_handle 
where tab.name = @TableName
order by [預期效益] desc;


/* 取得 上次資料庫服務啟動時間 SQL 2008 以後適用 */
select sqlserver_start_time
from sys.dm_os_sys_info;

/* 取得 上次資料庫服務啟動時間 SQL 2005 以後適用 */
select create_date 
from sys.databases
where name = 'tempdb';

/* 取得 上次資料庫服務啟動時間 SQL 2000 後適用 */
select crdate
from master.dbo.sysdatabases
where name = 'tempdb';

/* 取得使用效率不彰的索引資訊:
    這邊使用定義是,更新超過 500 次,但 scan seek 次數卻都低於 10次.
    另外因為 dmv 資訊是每次服務重啟就會重新計算的東西，所以在看這些
    資訊時要考量一下服務啟動多久了，如果服務啟動運行的時間太短，累積
    的數據不足，產生的判定可能就會有偏頗。
============================================== */
select 
 tab.name [TableName], inx.name [IndexName] , inx.type_desc ,
 s.user_seeks , s.user_scans , s.user_updates 
from sys.tables tab
join sys.indexes inx on
 tab.object_id = inx.object_id and inx.type > 0
left join sys.dm_db_index_usage_stats s on
 s.database_id = db_id() and
 s.object_id = inx.object_id and
 s.index_id = inx.index_id 
WHERE 
 s.user_updates > 1000 and 
 s.user_seeks < 10 and 
 s.user_scans < 10
order by s.user_updates desc

參考資料:

avg_user_impact 實作此遺漏索引群組時，使用者查詢可能獲得的平均效益百分比。這個值表示如果實作此遺漏索引群組，平均查詢成本將會依此百分比降低。

avg_total_user_cost 可依據群組中的索引降低之使用者查詢的平均成本。

avg_system_impact 實作此遺漏索引群組時，系統查詢可能獲得的平均效益百分比。這個值表示如果實作此遺漏索引群組，平均查詢成本將會依此百分比降低。

http://msdn.microsoft.com/zh-tw/library/ms345421.aspx

回首頁