SqlServer異常處理常用步驟_MsSql

SQL Server常見的問題主要是SQL問題造成，常見的主要是CPU過高和阻塞。

一、CPU過高的問題

1、查詢系統動態視圖查詢執行時間長的sql語句

WITH ProcessCTE(blocked) AS(  SELECT spid FROM sys.sysprocesses WHERE cpu>500)SELECT distinct a.* FROM (   SELECT TEXT,AA.* FROM sys.sysprocesses AA    CROSS APPLY sys.dm_exec_sql_text(AA.sql_handle)    ) a JOIN ProcessCTE bucte WITH(NOLOCK)  ON bucte.blocked=a.spid --where loginame = 'TCScenery' ORDER BY a.CPU

二、阻塞問題

1、查詢系統動態視圖查詢阻塞的sql語句

WITH ProcessCTE(blocked) AS(  SELECT blocked FROM sys.sysprocesses WHERE blocked>0  union  SELECT blocked FROM sys.sysprocesses WHERE blocked>0)SELECT distinct a.* FROM (    SELECT TEXT,AA.* FROM sys.sysprocesses AA    CROSS APPLY sys.dm_exec_sql_text(AA.sql_handle)    ) a JOIN ProcessCTE bucte WITH(NOLOCK)  ON bucte.blocked=a.spid ORDER BY a.blocked

2、使用系統內建的預存程序

Sp_who2和sp_lock以及使用dbcc inputbuffer(spid) 也可以用來分析阻塞

sp_who可以返回如下資訊： (選擇性參數LoginName, 或active代表活動會話數)
Spid         (系統進程ID)
status      (進程狀態)
loginame  (使用者登入名稱)
hostname(使用者主機名稱)
blk           (阻塞進程的SPID)
dbname   (進程正在使用的資料庫名)
Cmd        (當前正在執行的命令類型)

sp_who2除了顯示上面sp_who的輸出資訊外，還顯示下面的資訊：  (選擇性參數LoginName, 或active代表活動會話數)
CPUTime           (進程佔用的總CPU時間)
DiskIO              (進程對磁碟讀的總次數)
LastBatch         (客戶最後一次調用預存程序或者執行查詢的時間)
ProgramName  (用來初始化連線應用程式程式名稱，或者主機名稱)

下面是sp_who的用法，sp_who2與此類似

A.列出全部當前進程

以下樣本使用沒有參數的 sp_who 來報告所有目前使用者。

USE master;GOEXEC sp_who;GO

B.列出特定使用者的進程

以下樣本顯示如何通過登入名稱查看有關單個目前使用者的資訊。

USE master;GOEXEC sp_who 'janetl';GO

C.顯示所有活動進程

USE master;GOEXEC sp_who 'active';GO

D.顯示會話 ID 標識的特定進程

USE master;GOEXEC sp_who '10' --specifies the process_id;GO

sp_lock用法說明

sp_lock [ [ @spid1 = ] 'session ID1' ] [ , [@spid2 = ] 'session ID2' ][ ; ]
[ @spid1 = ] 'session ID1'

來自使用者想要鎖定其資訊的 sys.dm_exec_sessions 的資料庫引擎會話 識別碼。 session ID1 的資料類型為 int，預設值為 NULL。 執行 sp_who 可擷取有關該會話的進程資訊。 如果未指定會話 ID1，則顯示有關所有鎖的資訊。

[ @spid2 = ] 'session ID2'

來自 sys.dm_exec_sessions 的另一個資料庫引擎會話 識別碼，該會話 識別碼可能與 session ID1 同時具有鎖，並且使用者也需要其有關資訊。 session ID2 的資料類型為 int，預設值為 NULL。

在 sp_lock 結果集中，由 @spid1 和 @spid2 參數指定的會話所持有的每個鎖都對應一行。 如果既未指定 @spid1 又未指定 @spid2，則結果集將報告當前在資料庫引擎執行個體中處於活動狀態的所有會話的鎖。

列名	資料類型	說明
spid	smallint	請求鎖的進程的資料庫引擎會話 識別碼。
dbid	smallint	保留鎖的資料庫的標識號。 可以使用 DB_NAME() 函數來標識資料庫。
ObjId	int	持有鎖的對象的標識號。 可以在相關資料庫中使用 OBJECT_NAME() 函數來標識對象。 值為 99 時是一種特殊情況，表示用於記錄資料庫中頁分配的其中一個系統頁的鎖。
IndId	smallint	持有鎖的索引的標識號。
類型	nchar(4)	鎖的類型：
		RID = 表中單個行的鎖，由行標識符 (RID) 標識。
		KEY = 索引內保護可串列事務中一系列鍵的鎖。
		PAG = 資料頁或索引頁的鎖。
		EXT = 對某區的鎖。
		TAB = 整個表（包括所有資料和索引）的鎖。
		DB = 資料庫的鎖。
		FIL = 資料庫檔案的鎖。
		APP = 指定的應用程式資源的鎖。
		MD = 中繼資料或目錄資訊的鎖。
		HBT = 堆或 B 樹索引的鎖。 在 SQL Server 中此資訊不完整。
		AU = 配置單位的鎖。 在 SQL Server 中此資訊不完整。
Resource	nchar(32)	標識被鎖定資源的值。 值的格式取決於 Type 列標識的資源類型：
		Type 值：Resource 值
		RID：格式為 fileid:pagenumber:rid 的標識符，其中 fileid 標識包含頁的檔案，pagenumber 標識包含行的頁，rid 標識頁上的特定行。 fileid 與sys.database_files 目錄檢視中的 file_id 列相匹配。
		KEY：資料庫引擎內部使用的十六進位數。
		PAG：格式為 fileid:pagenumber 的數字，其中 fileid 標識包含頁的檔案，pagenumber 標識頁。
		EXT：標識區中的第一頁的數字。 該數位格式為 fileid:pagenumber。
		TAB：沒有提供資訊，因為已在 ObjId 列中標識了表。
		DB：沒有提供資訊，因為已在 dbid 列中標識了資料庫。
		FIL：檔案的標識符，與 sys.database_files 目錄檢視中的 file_id 列相匹配。
		APP：被鎖定的應用程式資源的唯一識別碼。 格式為 DbPrincipleId:<資源字串的前 2 個到 16 個字元><雜湊運算值>。
		MD：隨資源類型而變化。 有關詳細資料，請參閱 sys.dm_tran_locks (Transact-SQL) 中 resource_description 列的說明。
		HBT：沒有提供任何資訊。 請改用 sys.dm_tran_locks 動態管理檢視。
		AU：沒有提供任何資訊。 請改用 sys.dm_tran_locks 動態管理檢視。
模式	nvarchar(8)	所請求的鎖模式。 可以是：
		NULL = 不授予對資源的存取權限。 用作預留位置。
		Sch-S = 架構穩定性。 確保在任何會話持有對架構元素（例如表或索引）的架構穩定性鎖時，不刪除該架構元素。
		Sch-M = 架構修改。 必須由要更改指定資源架構的任何會話持有。 確保沒有其他會話正在引用所指示的對象。
		S = 共用。 授予持有鎖的會話對資源的共用存取權限。
		U = 更新。 指示對最終可能更新的資源擷取的更新鎖定。 用於防止一種常見的死結，這種死結在多個會話鎖定資源以便稍後對資源進行更新時發生。
		X = 排他。 授予持有鎖的會話對資源的獨佔存取權限。
		IS = 意圖共用。 指示有意將 S 鎖放置在鎖階層中的某個從屬資源上。
		IU = 意向更新。 指示有意將 U 鎖放置在鎖階層中的某個從屬資源上。
		IX = 意向排他。 指示有意將 X 鎖放置在鎖階層中的某個從屬資源上。
		SIU = 共用意向更新。 指示對有意在鎖階層中的從屬資源上擷取更新鎖定的資源進行共用訪問。
		SIX = 共用意向排他。 指示對有意在鎖階層中的從屬資源上擷取獨佔鎖定的資源進行共用訪問。
		UIX = 更新意向排他。 指示對有意在鎖階層中的從屬資源上擷取獨佔鎖定的資源持有的更新鎖定。
		BU = 大容量更新。 用於大容量操作。
		RangeS_S = 共用鍵範圍和共用資源鎖。 指示可串列範圍掃描。
		RangeS_U = 共用鍵範圍和更新資源鎖。 指示可串列更新掃描。
		RangeI_N = 插入鍵範圍和 Null 資源鎖。 用於在將新鍵插入索引前測試範圍。
		RangeI_S = 鍵範圍轉換鎖。 由 RangeI_N 和 S 鎖的重疊建立。
		RangeI_U = 由 RangeI_N 和 U 鎖的重疊建立的鍵範圍轉換鎖。
		RangeI_X = 由 RangeI_N 和 X 鎖的重疊建立的鍵範圍轉換鎖。
		RangeX_S = 由 RangeI_N 和 RangeS_S 鎖的重疊建立的鍵範圍轉換鎖 。
		RangeX_U = 由 RangeI_N 和 RangeS_U 鎖的重疊建立的鍵範圍轉換鎖。
		RangeX_X = 排他鍵範圍和排他資源鎖。 這是在修改範圍中的鍵時使用的轉換鎖。
狀態	nvarchar(5)	鎖的請求狀態：
		CNVRT：鎖正在從另一種模式進行轉換，但是轉換被另一個持有鎖（模式相衝突）的進程阻塞。
		GRANT：已擷取鎖。
		WAIT：鎖被另一個持有鎖（模式相衝突）的進程阻塞。

DBCC INPUTBUFFER

顯示從用戶端發送到 Microsoft® SQL Server™ 的最後一個語句。

文法

DBCC INPUTBUFFER (spid)

參數

spid

是 sp_who 系統預存程序的輸出中所顯示的使用者串連系統進程 ID (SPID)。

結果集

DBCC INPUTBUFFER 返回包含如下列的行集。

列名	資料類型	描述
EventType	nvarchar(30)	事件類型，例如：RPC、語言或無事件。
Parameters	Int	0 = 文本 1- n = 參數
EventInfo	nvarchar(255)	對於 RPC 的 EventType，EventInfo 僅包含過程名。對於語言或無事件的 EventType，僅顯示事件的頭 255 個字元。

例如，當緩衝區中的最後事件是 DBCC INPUTBUFFER(11) 時，DBCC INPUTBUFFER 將返回以下結果集。

EventType      Parameters EventInfo           
-------------- ---------- ---------------------
Language Event 0          DBCC INPUTBUFFER (11)
(1 row(s) affected)