MS SQL 日常維護管理常用指令碼(下)

監控資料庫執行

下面是整理、收集監控資料庫執行的一些常用指令碼，也是MS SQL 日常維護管理常用指令碼(上)的續集，歡迎大家補充、提意見。

檢視資料庫登入名資訊

Code Snippet

檢視資料庫使用者資訊

SELECT * FROM sysusers;

檢視使用者擁有的伺服器角色

方法1： 用SSMS管理工具檢視
方法2： 指令碼查詢

檢視使用者角色

檢視最大工作執行緒數

Code Snippet

檢視當前使用者程式的會話ID

SELECT @@SPID

查詢當前會話使用哪種協議

Code Snippet

檢視當前連線的會話資訊

–程式號1–50是SQL Server系統內部用的

SELECT * FROM sys.dm_exec_sessions WHERE session_id >=51

–檢視某臺機器的連線會話資訊

SELECT * FROM sys.dm_exec_sessions WHERE session_id >=51 AND host_name=’PO130018801′

–檢視某個登入名的連線會話資訊

SELECT * FROM sys.dm_exec_sessions WHERE session_id >=51 AND login_name=’username’

–檢視活動的連線會話資訊

SELECT * FROM sys.dm_exec_sessions WITH(NOWAIT) WHERE session_id >=51 AND status =’running’

–查詢連線到伺服器的使用者並返回每個使用者的會話數

SELECT login_name ,
COUNT(session_id) AS session_count
FROM sys.dm_exec_sessions
GROUP BY login_name ;

檢視正在執行的SQL語句

方法1： 選擇資料庫例項，單擊右鍵，選擇”活動監視器“，監控/檢視正在執行的SQL
方法2： profile去跟蹤，比較耗費資源。

方法3：
Code Snippet

方法4：

Code Snippet

–檢視某個會話ID正在執行的SQL

Code Snippet

檢視SQL SERVER程式執行的語句

Code Snippet

查詢TOP N語句

按平均 CPU 時間返回排名前十個的查詢的相關資訊。此示例將根據查詢的查詢雜湊對查詢進行聚合，以便按照查詢的累積資源消耗來分組在邏輯上等效的查詢。
–注意：SQL 2005 某些版本，沒有sys.dm_exec_query_stats系統動態檢視沒有query_hash檢視。

Code Snippet

檢視會話阻塞/死鎖資訊

方法1：檢視那個引起阻塞，檢視blk不為0的記錄，如果存在阻塞程式，則是該阻塞程式的會話 ID。否則該列為零。

EXEC sp_who active

方法2：檢視那個引起阻塞，檢視欄位BlkBy，這個能夠得到比sp_who更多的資訊。

EXEC sp_who2 active

方法3：sp_lock 系統儲存過程，報告有關鎖的資訊，但是不方便定位問題
方法4：sp_who_lock儲存過程

Code Snippet

方法5：右鍵伺服器-選擇“活動和監視器”，檢視程式選項。注意“任務狀態”欄位。

方法6：右鍵服務名稱-選擇報表-標準報表-活動-所有正在阻塞的事務。

小結：總結之後，才發現居然有這麼多方法，MGD，系統的整理、梳理知識點是非常有必要的，你能更全面、深入的瞭解。

檢視記憶體狀態

dbcc memorystatus
具體如何分析，請檢視官方文件http://support.microsoft.com/kb/907877/zh-cn

檢視指令碼執行時間

方法1： 檢視SSMS管理器，查詢視窗右下角
方法2:

Code Snippet

方法3：

SET STATISTICS TIME ON

–SQL 語句

檢視程式正在執行的SQL語句

dbcc inputbuffer ()

檢視那些表缺少索引

下面語句功能強大，執行結果受統計資訊的影響

Code Snippet

檢視應該被移除的索引

檢視那些多餘的、應該被移除的索引

SQL 1：

Code Snippet

SQL 2: 分析下面語句，移除那些沒有必要的索引

Code Snippet