資料庫維護管理和監視新特性
我的查詢沒有響應 為什麼? 哪一個運作T-SQL的連接配接阻塞了我的查詢? 阻塞查詢的連接配接正在運作哪個T-SQL? 哪個T-SQL在等待什麼資源?
診斷工具: Profiler Performance Monitor(PerfMon) DBCC command Activity Monitor Stored procedures(sp_who,sp_who2,sp_lock...) 其它實用工具(Read80Trace...)
SQL Server 2005維護管理和監視新特性: 動态管理對象DMO-->Dynamic Management Objects DMO(SQL Server早期版本中的分布式管理對象)與SMO(SQL Server 2005中的管理對象)
什麼是DMV & DMF? DMO動态管理對象(Dynamic Management Objects) DMV--動态管理視圖(Dynamic Management Views) DMF--動态管理函數(Dynamic Management Functions) 擷取用于監視伺服器執行個體的運作狀況、診斷故障以及優化性能的伺服器狀态資訊
DMV & DMF的異同-->相同點: 以T-SQL方式查詢資料庫運作狀态的動态資訊 結果都以表的形式傳回 最大的差別: DMF需要提供一些參數來擷取不同的輸出
分類: DMV & DMF分成兩類-->伺服器範圍内 資料庫範圍内 存儲在Master資料庫的SYS架構中 命名規則: 以"dm_"開頭 共有85個,其中76個為DMV,9個為DMF
功能分組-->見下清單:
dm_broker_* - 與Service Broker有關的DMV
dm_clr_* - 與公共語言運作時有關的DMV
dm_db_* - 與資料庫有關的DMV
dm_db_index_* - 與索引有關的DMV & DMF
dm_db_mirroring_* 與資料庫鏡像有關的DMV
dm_exec_* - 與執行有關的DMV & DMF
dm_fts_* - 與全文搜尋有關的DMV
dm_io_* - 與I/O有關的DMV & DMF
dm_os_* - 與SQL作業系統有關的DMV
dm_qn_* - 與查詢通知有關的DMV
dm_repl_* - 與複制有關的DMV
dm_tran_* - 與事務有關的DMV
用法: 使用Select語句來查詢 與性能計數器類似,通過DMV & DMF檢索到的資訊也不是你通路當時的最新資訊。查詢的結果是動态的。使用兩部分、三部分或四部分所組成的名稱來引用 查詢MSDN幫助時需要使用兩部分命名法 任何動态對象在未來的SQLSERVER版本中可能會發生改變,是以任何涉及DMV及DMF的代碼可能需要重寫或棄用,如果Microsoft改變DMV或DMF。
權限: 授權通路-->SELECT VIEW SERVER STATE VIEW DATABASE STATE 禁止通路-->首先在master中建立使用者,然後拒絕該使用者對不希望被通路的動态管理視圖或函數的SELECT權限
如何檢視所有的DMV & DMF、擷取DMV的字段資訊、擷取DMF的參數資訊和授予權限呢?
1.如果你想檢視目前資料庫有那些DMV的話就把檢視所有的DMV & DMF的語句全選--按執行之後就可以看到總共有多少個DMV了 2.全選擷取DMV的字段資訊的語句--按執行之後就可以看到DMV字段的詳細資訊了
全選擷取DMF的參數資訊的語句--按執行之後就可以看到DMF的參數資訊了
展開SQL2005(本地計算機)--安全性--對着登入名右鍵--選擇建立登入名--登入名就叫做user1吧--選擇SQL Server身份驗證,輸入密碼--在預設資料庫裡面選擇AdventureWorks 按确定
展開資料庫--AdventureWorks--安全性--對着使用者右鍵--選擇建立使用者--使用者名就叫做user1吧--按确定
在對象資料總管裡面按連接配接--選擇資料庫引擎--在身份驗證裡面選擇SQL Server身份驗證--輸入登入名(user1)和密碼 按連接配接
展開資料庫--系統資料庫--對着master這個資料庫右鍵--選擇建立查詢--輸入select * from sys.dm_clr_tasks這條語句--按執行之後可以看到有一個提示-->使用者沒有執行此操作的權限 現在怎麼辦呢? 我們知道使用DMV需要有浏覽資料庫和伺服器的權限 此時我們需要增加一些權限
在對象資料總管裡面按連接配接--選擇資料庫引擎--在身份驗證裡面選擇Windows身份驗證--按連接配接
展開安全性--登入名--對着user1右鍵--選擇屬性--按安全對象--在安全對象裡面按添加--在要添加什麼對象裡面選擇特定類型的所有對象 按确定
在對象類型裡面把伺服器溝上 按确定
在SQL2005的顯式權限裡面把View server state的授予這一項權限溝上 按确定
展開資料庫--系統資料庫--master--安全性--對着使用者右鍵--選擇建立使用者--使用者名和登入名都叫做user1吧 按确定
對着user1右鍵--選擇屬性--按安全對象--在安全對象裡面按添加--在要添加什麼對象裡面選擇特定類型的所有對象--按确定
在選擇要查找的對象類型裡面把資料庫溝上 按确定
在master的顯式權限裡面把View database state的授予這一項權限溝上 按确定
按master這個資料庫--在查詢裡面輸入select * from sys.dm_clr_tasks這條語句--按執行之後可以看到現在已經不存在權限的問題了 可以看到目前的内容了 但是現在沒有clr這個任務的内容
DMV & DMF在診斷性能問題上的典型應用: SQL Server有關的性能問題-->資源瓶頸(CPU,memory,disk I/O and network) Tempdb瓶頸 使用者查詢 診斷瓶頸的相關工具-->System Monitor(PerfMon) SQL Server Profiler DBCC commands DMVs & DMFs
應用之一: CPU瓶頸-->System Monitor: Processor: % Processor Time 可運作任務的數量-->見下清單:
Select Scheduler_id,
Current_tasks_count,
Runnable_tasks_count
from sys.dm_os_schedulers
where Scheduler_id<255
哪一組T-SQL或過程占用了最多的CPU時間-->見下列語句:
select top 50 sum(qs.total_worker_time)
as total_cpu_time,
sum(qs.execution_count)as
total_execution_count, count(*)
as number_of_statements,
qs.plan_handle
from sys.dm_exec_query_stats qs
group by qs.plan_handle
order by sum(qs.total_worker_time)desc
過度的編譯和重編譯問題引起的CPU性能問題: 導緻重編譯的原因-->架構的改變 統計資訊的改變 SET選項的改變 臨時表的改變 帶RECOMPILE選項的存儲過程 診斷-->System Monitor(Perfmon): SQL Server: SQL Statistics: Batch Requests/sec SQL Server: SQL Statistics: SQL Compilations/sec SQL Server: SQL Statistics: SQL Recompilations/sec
SQL Server Profiler: 跟蹤事件: SP: Recompile SQL: StmtRecompile 對事件資料的分析-->見下列語句:
select spid, StartTime, Textdata,
EventSubclass, ObjectID,
DatabaseID, SQLHandle
from fn_trace_gettable('e:/recompiletrace.trc',1)
where EventClass in(37,75,166)
使用DMVs-->select * from sys.dm_exec_query_optimizer_info
select top 25 sql_text.text, sql_handle,
plan_generation_num,execution_count,
dbid, objectid
from sys.dm_exec_query_stats a
cross apply sys.dm_exec_sql_text(sql_handle)as
sql_text
where plan_generation_num>1
order by plan_generation_num desc
發現過度重編譯的解決辦法-->避免在存儲過程中修改SET選項 避免在存儲過程中使用recompile,考慮改用語句級recompile選項 使用Database Engine Tuning Advisor(DTA) 關閉相關索引的自動更新統計資訊選項 ...
應用之二: TempDB瓶頸-->Tempdb的用途 與Tempbdb有關的問題: 空間不足 查詢速度緩慢 大量的DDL/DML操作引起系統表的瓶頸問題 Tempbdb空間配置設定: 使用者對象 内部對象 版本存儲區 可用空間
診斷tempdb磁盤空間問題: SQL Server錯誤日志-->見下清單:
錯誤 引發錯誤的情況
1101或1105 任何會話都必須配置設定tempdb中的空間。
3959 版本存儲區已滿。此錯誤在日志中通常出現在錯誤1105或1101之後。
3967 由于tempdb已滿,版本存儲區被強制收縮。
3958或3966 事務在tempdb中找不到所需的版本記錄。
監視tempdb磁盤空間: 擷取Tempdb中的使用者對象,内部對象,版本存儲區使用的空間量以及可用空間量-->見下列語句:
Select SUM(user_object_reserved_page_count)*8
as user_objects_kb,
SUM(internal_object_reserved_page_count)*8
as internal_objects_kb,
SUM(version_store_reserved_page_count)*8
as version_store_kb,
SUM(unallocated_extent_page_count)*8
as freespace_kb
From sys.dm_db_file_space_usage
Where database_id=2
輸出樣例: user_objects_kb internal_objects_kb version_store_kb freespace_kb
------------ -------------- ------------ --------
8736 128 64 448
如果版本存儲區使用了tempdb中的大量空間,則需要确定運作時間最長的事務。-->見下列語句:
SELECT transaction_id
FROM
sys.dm_tran_active_snapshot_database_transactions
ORDER BY elapsed_time_seconds DESC;
使用大量空間的大型查詢-->擷取每個會話中目前運作的所有任務中的内部對象占用的空間-->見下列語句:
CREATE VIEW all_task_usage AS
SELECT session_id,
SUM(internal_objects_alloc_page_count)AS
task_internal_objects_alloc_page_count,
SUM(internal_objects_dealloc_page_count)
AS
task_internal_objects_dealloc_page_count
FROM sys.dm_db_task_space_usage
GROUP BY session_id;
擷取目前會話中正在運作的任務和已完成任務的内部對象占用的空間-->見下列語句:
CREATE VIEW all_session_usage
AS SELECT R1.session_id,
R1.internal_objects_alloc_page_count+
R2.task_internal_objects_alloc_page_count AS
session_internal_objects_alloc_page_count,
R1.internal_objects_dealloc_page_count+
R2.task_internal_objects_dealloc_page_count
AS
session_internal_objects_dealloc_page_count
FROM sys.dm_db_session_space_usage AS R1,
all_task_usage AS R2
WHERE R1.session_id=R2.session_id;
情景構設: Script 1: Create stored procedure
USE AdventureWorks
GO
CREATE PROCEDURE dbo.updater
AS
BEGIN TRAN
UPDATE Person.Contact WITH(HOLDLOCK)
SET FirstName = 'Justin'
WAITFOR DELAY '01:00:00'
ROLLBACK TRAN
GO
Script 2: Execute Sp
[email protected]@spid
EXEC dbo.updater
Script 3: Select the blocked table
Select * from Person.Contact
擷取資訊: Script 4: What's going on
DECLARE @sessionid INT
SET @sessionid=54-to be changed
DECLARE @sqlhandle VARBINARY(64)
DECLARE @planhandle VARBINARY(64)
SELECT @sqlhandle=sql_handle,@planhandle=plan_handle
FROM sys.dm_exec_requests
WHERE [email protected]
SELECT * FROM sys.dm_exec_sql_text(@sqlhandle)
SELECT * FROM sys.dm_exec_query_plan(@planhandle)
擷取答案: Script 5: Who blocked me
SELECT blocked_query.session_id AS blocked_session_id,
blocking_query.session_id AS blocking_session_id,
sql_text.text AS blocking_text,waits.wait_type AS
blocking_resource
FROM sys.dm_exec_requests blocked_query
JOIN sys.dm_exec_requests blocking_query ON
blocked_query.blocking_session_id=blocking_query.session_id
CROSS APPLY
(SELECT*
FROM sys.dm_exec_sql_text(blocking_query.sql_handle)sql_text
JOIN sys.dm_os_waiting_tasks waits ON
waits.session_id=blocking_query.session_id
1.全選第一條語句--按執行來建立存儲過程 2.全選第二條語句--按執行之後就執行我們剛才建立的存儲過程了 如果執行select @@spid這條語句的話就可以看到目前會話的ID是56 3.全選第三條語句--按執行之後會發現查詢沒有響應
4.全選第四條語句--按執行之後可以看到這個存儲過程二條資訊了 按ShowPlanXML.xmlns="http://schemas.microsoft.com...來打開裡面的内容
按檔案--按儲存--我把它存儲在C槽裡面--檔案的擴充名叫做sqlplan--儲存類型為XML檔案(*.xml)--按儲存
打開C槽--把query_plan2.sqlplan.xml的擴充名修改成query_plan2.sqlplan
輕按兩下query_plan2.sqlplan這個檔案來打開它 可以看到不是剛才看到的XML格式的内容了而是圖形的格式了 讓大家了解這個查詢計劃是怎麼樣執行的
如果你想查詢究竟是什麼SQL阻塞了我的查詢的話 可以執行它的語句來檢視 5.全選第五條語句--按執行之後可以看到阻塞查詢的會話ID是55 55這個ID就是我查詢Select * from Person.Contact這條語句的會話ID 誰阻塞了55這個會話ID呢? 是56這個ID執行存儲過程阻塞的 它停留什麼資源呢？它運作了一條語句叫做WAITFOR這樣的等待一個小時的語句 大家可以看到DMV和DMF提供了一個非常詳細的資訊 可以非常友善地解決一個查詢沒有響應的問題
更多資訊...
SQL Server 2005聯機叢書: SQL Server語言參考->Transact_SQL參考->系統視圖(Transact_SQL)->動态管理視圖和函數