SQLSERVER資料庫死鎖與優化雜談

死鎖雜談

當資料庫死鎖時，SqlServer會釋放一個優先順序較低的鎖，讓另一個事務運行；所以，即時去捕捉資料庫死鎖，是挺不容易的。

如果，資料庫死鎖比較長時間，那麼死鎖是可以被捕捉的。

可以用SqlServer活動監視器來查看，哪些進程鎖了資料庫。

首先打開SqlServer活動監視器，然後可以看到，界面里有進程，查看資源，數據文件I/O，最近消耗大量資源的查詢四項。

四項顯示內容如下：

進程：在進程里可以看到哪些進程被阻塞，查看屬性【阻塞者】可以看到，【阻塞者】的會話ID。

等待資源：等待資源里有一些鎖，可以看看那些鎖累計等待時間較多。

數據文件I/O：數據文件I/O記錄一些資料庫MDF，LDF的讀寫速度。

最近消耗大量資源的查詢：記錄一些消耗資源較大的SQL查詢。

查詢進程里被死鎖的會話ID，然後執行下麵的SQL，進行解鎖。

declare @spid int Set @spid = 518 --鎖表進程會話ID 
declare @sql varchar(1000) 
set @sql='kill '+cast(@spid as varchar) 
exec(@sql)

也可以用下麵SQL語句查詢死鎖進程，這樣查詢死鎖進程，定位比較快。

select  request_session_id spid,  OBJECT_NAME(resource_associated_entity_id) tableName    
from  sys.dm_tran_locks where  resource_type='OBJECT' 

優化雜談

最近消耗大量資源的查詢也可以用SQL查詢。

下麵SQL是查詢最耗時的前10條SQL語句。

SELECT TOP 10 total_worker_time / 1000 AS [自編譯以來執行所用的CPU時間總量(ms-毫秒)],
total_elapsed_time/1000 as [完成執行此計劃所用的總時間],
total_elapsed_time / execution_count/1000 as [平均完成執行此計劃所用時間],
execution_count  as [上次編譯以來所執行的次數],    
creation_time as [編譯計劃的時間],
deqs.total_worker_time / deqs.execution_count / 1000 AS [平均使用CPU時間(ms)],
last_execution_time AS [上次開始執行計劃的時間],
total_physical_reads [編譯後在執行期間所執行的物理讀取總次數],
total_logical_reads/execution_count [平均邏輯讀次數],
min_worker_time /1000 AS [單次執行期間所用的最小CPU時間(ms)],
max_worker_time / 1000 AS [單次執行期間所用的最大 CPU 時間(ms)],
SUBSTRING(dest.text, deqs.statement_start_offset / 2 + 1,          
(CASE WHEN deqs.statement_end_offset = -1 THEN DATALENGTH(dest.text) ELSE deqs.statement_end_offset END - deqs.statement_start_offset) / 2 + 1) AS [執行SQL], 
dest.text as [完整SQL],
db_name(dest.dbid) as [資料庫名稱],
object_name(dest.objectid, dest.dbid) as [對象名稱]
,deqs.plan_handle [查詢所屬的已編譯計劃]
FROM sys.dm_exec_query_stats deqs WITH(NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest --平均使用CPU時間降序 
ORDER BY (deqs.total_worker_time / deqs.execution_count / 1000) DESC

在SqlServer活動監視器里，查看資源等待。

通常可以看到等待類別是Latch的排在最上面，如下圖：

Latch 【閂鎖】雖然是一種輕量級的鎖，但等待的鎖越多，肯定越影響資料庫性能。

執行下麵SQL，查看下哪些Latch比較耗資源。

SELECT * FROM sys.dm_os_latch_stats

查詢結果如下圖所示：

從結果中可以看到各種鎖類型的請求的次數，等待時間，最大等待時間（毫秒）。

但這些鎖類型都是英文簡寫，需要使用下麵表格查詢它們的真實意義。

通過對比表格，我們發現了最消耗資源的ACCESS_METHODS_DATASET_PARENT鎖的意義是併發操作時資源訪問的鎖。那麼想降低併發操作，就可以減少ACCESS_METHODS_DATASET_PARENT鎖的資源消耗了。

Latch參考網址：https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-latch-stats-transact-sql?view=sql-server-2017

DBCC雜談

DBCC 語句是SQL Server 的資料庫控制台命令，共有以下四種類型。

維護：對資料庫、索引或文件組進行維護的任務。

雜項：雜項任務，如啟用跟蹤標誌或從記憶體中刪除 DLL。

信息：收集並顯示各種類型信息的任務。

驗證：對資料庫、表、索引、目錄、文件組或資料庫頁的分配進行的驗證操作。

DBCC shrinkdatabase

DBCC shrinkdatabase用於收縮資料庫，SQL語句如下：

DBCC shrinkdatabase (N'庫名' , 1) 

 執行結果如下：

各欄位含義如下：

DbId：資料庫引擎試圖收縮的文件的資料庫標識號。

FileId：資料庫引擎嘗試收縮的文件的文件標識號。

CurrentSize：文件當前占用的 8 KB 頁數。

MinimumSize：文件最低可以占用的 8 KB 頁數。 這與文件的最小大小或最初創建時的大小相對應。

UsedPages：文件當前使用的 8 KB 頁數。

EstimatedPages：資料庫引擎估計文件能夠收縮到的 8 KB 頁數。

如果收縮不成功，可以查看下資料庫是否有可以收縮的空間。

SQL如下：

SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;

如果有空間還收縮不成功，則可能是別原因。

DBCC參考網址：https://docs.microsoft.com/zh-cn/sql/t-sql/database-console-commands/dbcc-shrinkdatabase-transact-sql?view=sql-server-2017

資料庫日誌雜談

SqlServer資料庫日誌對執行的SQL語句進行了加密，所以，在日誌里，我們看不到真正執行的SQL語句。

如果想查看SQL語句，需要藉助一些工具，如ApexSQLLog。

不過，雖然看不到SQL語句，也可以通過日誌看出一些資料庫問題，比如，可以查看資料庫執行了多少次插入，更新等操作。

查看資料庫日誌的SQL如下：

SELECT * FROM [sys].[fn_dblog](NULL,NULL)

查詢結果如下：

 查詢結果各欄位含義如下：

----------------------------------------------------------------------------------------------------

註：此文章為原創，歡迎轉載，請在文章頁面明顯位置給出此文鏈接！
若您覺得這篇文章還不錯，請點擊下右下角的【推薦】，非常感謝！