[SQL]SQL Server 動態管理檢視和函數

2011-06-02

8453
0
SQL
2012-07-06

SQL Dynamic Management Views簡稱DMVs，可以幫助管理者診斷SQL系統問題及效能。它是SQL 2005之後才有的，所以SQL 2000並沒有這些View(SQL 2000用的是實體Table)。

DMVs包含2種類別:

a.Server-scoped DMV:存在master資料庫之中

b.Database-scoped DMV:在每個資料庫之中

要可以Query DMVs，使用者必須取得 VIEW SERVER STATE 權限或隱含 VIEW SERVER STATE 權限的任何權限。

GRANT VIEW SERVER STATE to <User>
GRANT VIEW DATABASE STATE to <User>

所有的DMVs都在sys的Schema之下，並以dm_開頭。以下為查出所有的DMVs

SELECT name, type_desc FROM sys.system_objects 
WHERE name LIKE 'dm_%' ORDER BY name

sys.dm_db_missing_index_group_stats:傳回有關遺漏索引群組的摘要資訊。

A. 尋找改善使用者查詢之預期效果最高的 10 個遺漏索引

SELECT TOP 10 *
FROM sys.dm_db_missing_index_group_stats
ORDER BY avg_total_user_cost * avg_user_impact * (user_seeks + user_scans)DESC;

B. 尋找個別遺漏索引及其特定遺漏索引群組的資料行詳細資料

SELECT migs.group_handle, mid.*
FROM sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig
    ON (migs.group_handle = mig.index_group_handle)
INNER JOIN sys.dm_db_missing_index_details AS mid
    ON (mig.index_handle = mid.index_handle)
WHERE migs.group_handle in (SELECT TOP 10 group_handle
FROM sys.dm_db_missing_index_group_stats
ORDER BY avg_total_user_cost * avg_user_impact * (user_seeks + user_scans)DESC)

結果指出索引鍵資料行是相等、不相等或內含資料行，所以我們就可以依這些資訊來建立Index，如下的方式

CREATE NONCLUSTERED INDEX IX_YOUR_INDEX_NAME
ON YOUR_TABLENAME(相等, 不相等)
INCLUDE (內含資料行)

或是使用 SET STATISTICS XML ON 選項開啟 XML 顯示計劃功能

sys.dm_exec_requests:針對在 SQL Server 內執行的每個要求，各傳回一個資料。

透過dm_exec_requests來查看目前在執行的SQL，如下的SQL，並排除掉目前執行的SQL及系統的SQL，而要取得Run的SQL就透過dm_exec_sql_text來取得，如下的SQL。

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT er.session_Id AS [Spid]
, sp.ecid
, DB_NAME(sp.dbid) AS [Database]
, sp.nt_username
, er.status
, er.wait_type
, SUBSTRING (qt.text, (er.statement_start_offset/2) + 1,
((CASE WHEN er.statement_end_offset = -1
    THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
    ELSE er.statement_end_offset
END - er.statement_start_offset)/2) + 1) AS [Individual Query]
, qt.text AS [Parent Query]
, sp.program_name
, sp.Hostname
, sp.nt_domain
, er.start_time
FROM sys.dm_exec_requests er
INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt
WHERE session_Id > 50
AND session_Id NOT IN (@@SPID)
ORDER BY session_Id, ecid

以下SQL為取得執行時間及次數 (參考自SQL Server DMV Views: Getting slow queries within stored procedure)

SELECT

	CAST(total_elapsed_time / 1000000.0 AS DECIMAL(28, 2)) AS [Total Duration (s)]
	, CAST(total_worker_time * 100.0 / total_elapsed_time AS DECIMAL(28, 2)) AS [% CPU]
	, CAST((total_elapsed_time - total_worker_time)* 100.0 /
	total_elapsed_time AS DECIMAL(28, 2)) AS [% Waiting]
	, execution_count
	, CAST(total_elapsed_time / 1000000.0 / execution_count AS DECIMAL(28, 2)) AS [Average Duration (s)]
	, SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,
	 ((CASE WHEN qs.statement_end_offset = -1
	 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
	 ELSE qs.statement_end_offset
	 END - qs.statement_start_offset)/2) + 1) AS [Individual Query]
	, SUBSTRING(qt.text,1,100) AS [Parent Query]
	, DB_NAME(qt.dbid) AS DatabaseName

FROM sys.dm_exec_query_stats qs
	CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
	WHERE total_elapsed_time > 0
	ORDER BY total_elapsed_time DESC

Hi,

亂馬客Blog已移到了「亂馬客 : Re:從零開始的軟體開發生活」

請大家繼續支持 ^_^

回首頁

亂馬客

https://rainmakerho.github.io/