SQL Server中提前找到隱式轉換提升效能的辦法

    之前談論了由於資料隱式轉換造成執行計劃不準確，從而造成了死鎖。那如果在事情出現之前發現了這類潛在的風險豈不是更好？

    那麼我們來看一個簡單的例子，如程式碼清單1所示。

   1: SELECT    *
   2: FROM      HumanResources.Employee
   3: WHERE     NationalIDNumber = 243322160
   4:  
   5: SELECT    *
   6: FROM      HumanResources.Employee
   7: WHERE     NationalIDNumber = '243322160'

    NationalIDNumber列定義是Nvarchar，而引數第一個為INT型別，第二個為Varchar型別。那麼就存在隱式轉換，由高繼偉提到的資料型別轉換優先順序可以看到，第一列Nvarchar和INT屬性型別，INT資料型別優先順序高，需要把列NationalIDNumber轉換為INT型別，因此涉及到需要把所有該列值轉換為INT，因此只能透過掃描操作，從而影響效能。

    而程式碼清單1中第二個查詢，NationalIDNumber列為Nvarchar型別，而引數為varchar型別，根據資料型別優先順序，需要將Varchar轉換為Navrchar，因此僅僅需要對引數進行隱式轉換，因此不影響效能。

 

如何在出現問題之前找到出問題的查詢？

    在SQL Server中，執行計劃會被快取起來，以便後續進行復用。SQL Server提供了一系列DMV可以檢視這些執行計劃。由於執行計劃的本質是XML，因此透過XQUERY查詢特定的執行計劃變為可能。

    在執行計劃中，存在隱式轉換的節點會存在類似如程式碼清單2所示的欄位：

   1: 
   2:                                   
   3:                                     
   4:                                       
   5:                                     
   6:                                   
   7:                                 

    前面提到，只有對列而不是引數進行隱式轉換時，才會影響效能。而在程式碼清單2中對列進行隱式轉換的執行計劃會引用具體的資料庫名稱、架構名稱、表名稱、列名稱。而對引數進行隱式轉換的僅僅是引用引數，如程式碼清單3所示。

   1: 
   2:                                     
   3:                                       
   4:                                         
   5:                                       
   6:                                     
   7:                                   

 

    既然我們已經知道產生問題的執行計劃特徵，那麼我們就可以利用DMV和Xquery找出這些執行計劃，程式碼如程式碼清單4所示：

   1: SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
   2:  DECLARE @dbname SYSNAME
   3:  SET @dbname = QUOTENAME(DB_NAME());
   4:  WITH XMLNAMESPACES
   5:  (DEFAULT '')
   6:  SELECT stmt.value('(@StatementText)[1]', 'varchar(max)') AS SQL_Text ,
   7:         t.value('(ScalarOperator/Identifier/ColumnReference/@Schema)[1]',
   8:                 'varchar(128)') AS SchemaName ,
   9:         t.value('(ScalarOperator/Identifier/ColumnReference/@Table)[1]',
  10:                 'varchar(128)') AS TableName ,
  11:         t.value('(ScalarOperator/Identifier/ColumnReference/@Column)[1]',
  12:                 'varchar(128)') AS ColumnName ,
  13:         ic.DATA_TYPE AS ConvertFrom ,
  14:         ic.CHARACTER_MAXIMUM_LENGTH AS ConvertFromLength ,
  15:         t.value('(@DataType)[1]', 'varchar(128)') AS ConvertTo ,
  16:         t.value('(@Length)[1]', 'int') AS ConvertToLength ,
  17:         query_plan
  18:  FROM sys.dm_exec_cached_plans AS cp
  19:         CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
  20:         CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple')
  21:         AS batch ( stmt )
  22:         CROSS APPLY stmt.nodes('.//Convert[@Implicit="1"]') AS n ( t )
  23:         JOIN INFORMATION_SCHEMA.COLUMNS AS ic ON QUOTENAME(ic.TABLE_SCHEMA) = t.value('(ScalarOperator/Identifier/ColumnReference/@Schema)[1]',
  24:                                                               'varchar(128)')
  25:                                                  AND QUOTENAME(ic.TABLE_NAME) = t.value('(ScalarOperator/Identifier/ColumnReference/@Table)[1]',
  26:                                                               'varchar(128)')
  27:                                                  AND ic.COLUMN_NAME = t.value('(ScalarOperator/Identifier/ColumnReference/@Column)[1]',
  28:                                                               'varchar(128)')
  29:  WHERE t.exist('ScalarOperator/Identifier/ColumnReference[@Database=sql:variable("@dbname")][@Schema!="[sys]"]') = 1

 

小結

    本篇文章提供了透過執行計劃快取找出對效能影響的隱式轉換，在出現問題之前進行調優。對於開發人員來講，注意書寫T-SQL的資料型別可以在後續避免很多問題。