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

lvxfcjf發表於2021-09-09

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

    那麼我們來看一個簡單的例子,如程式碼清單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的資料型別可以在後續避免很多問題。

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/3486/viewspace-2800059/,如需轉載,請註明出處,否則將追究法律責任。

相關文章