現象

收到SQL專家雲阻塞告警郵件，登入SQL專家雲，進入實時視覺化頁面，看到從20點開始出現大量的阻塞。

進入活動會話原始資料頁面，看到產生阻塞的都是同一類語句。

SQL語句是很簡單的一個MERGE語句，基於表的主鍵 PK_NAME進行匹配，匹配到做更新，匹配不到做插入。



DECLARE @PK_NAME 
nvarchar(
4000), @PK_VALUE 
nvarchar(
4000)






MERGE 
INTO 

  COIND_DS t


USING

(

  
SELECT @PK_NAME 
AS PK_NAME, @PK_VALUE 
AS PK_VALUE

) b 


ON

(

  t.PK_NAME = b.PK_NAME

)


WHEN 
MATCHED 
THEN

  
UPDATE 
SET PK_VALUE = @PK_VALUE


WHEN 
NOT 
MATCHED 
THEN 

  
INSERT (PK_NAME, PK_VALUE) 
VALUES(@PK_NAME, @PK_VALUE);

分析

透過現象發現有兩點不正常，第一：基於主鍵的更新或者插入一條資料，而且各個併發會話的引數值都不一樣，不應該產生阻塞；第二：該語句不應該有如此高的CPU消耗時間和讀次數。

第一反應就是可能存在隱式轉換，檢視錶的 PK_NAME欄位，是varchar（4000）的資料型別，而引數@PK_NAME是nvarchar(4000)，在進行比較前，透過隱式轉換把 PK_ NA ME欄位的值轉換成 n varch ar，導致查詢次數增多，讀次數和CPU時間增加，最主要的是導致了併發的下降，因此才產生了阻塞。

SQL Server在進行比較、組合、賦值等操作時，要進行資料型別的轉換，資料型別轉換分為隱式轉換和顯示轉換。隱式轉換對使用者不可見， SQL Server根據資料型別優先順序規則將優先順序較低的資料型別自動轉換為優先順序較高的資料型別，例如，smallint 會被轉換為 int，char會被轉換為nchar。如果兩種資料型別不支援隱式轉換，則返回錯誤；顯式轉換使用 CAST 或 CONVERT 函式， CAST 和 CONVERT 函式可將值（區域性變數、列或其他表示式）從一種資料型別轉換為另一種資料型別，例如， CAST ( $157.27 AS VARCHAR(10) )。

SQL Server官方文件中提供了一張資料型別轉換表，詳細的描述了各資料型別之間的轉換關係，詳細請參考：。

DEMO

首先建立一張表，其中欄位id的資料型別varchar，並且為主鍵、聚集索引。最後寫入100萬行資料。

對欄位id做一個簡單的LIKE查詢，引數@id的資料型別為varchar。透過執行計劃看使用聚集索引查詢。邏輯讀取次數是7次。

把引數@id的資料型別改成nvarchar後，透過執行計劃可以看到，在比較時要把欄位id 每行的值從varchar轉換成nvarchar，然後再進行比較，從而產生了隱式轉換，導致索引查詢變成索引掃描，邏輯讀取次數從7次增加到1006108次。這僅是一個100萬行資料的表，表的行數越多造成的效能影響越大。

說明1：這裡用LIKE來做演示是因為執行計劃簡單，容易理解。現實中大多數情況還是等值比較，因為SQL Server做了相關的最佳化，把nchar型別的值計算為char型別的最小值和最大值，然後進行範圍的查詢，再對查詢出來的結果進行隱式轉換後比較，因此是不會導致索引掃描的，效能下降的不是特別厲害，但是由於變成了範圍查詢，加鎖的行數增加了，還是會導致併發下降。

說明2：如果字段是nchar\nvarchar，引數是char\varchar型別，隱式轉換的是引數的值，是不影響效能的。

解決

在設計欄位型別和宣告參數時不要隨意，最典型的錯誤就是在S QL語句中聲明字符串引數時不管什麼用途一律使用n varchar型別。

在程式中指明資料型別，例如在C#中不明確指定資料型別的字串引數，就會使用nvarchar資料型別。

想要生成varchar資料型別，一定要明確指定。

資料型別隱式轉換導致的阻塞

現象

分析

DEMO

解決

相關文章