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: 7:4: 6:5:
前面提到,只有對列而不是引數進行隱式轉換時,才會影響效能。而在程式碼清單2中對列進行隱式轉換的執行計劃會引用具體的資料庫名稱、架構名稱、表名稱、列名稱。而對引數進行隱式轉換的僅僅是引用引數,如程式碼清單3所示。
1:2: 3: 7:4: 6:5:
既然我們已經知道產生問題的執行計劃特徵,那麼我們就可以利用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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- sql隱式轉換SQL
- SQL Server資料庫中的資料型別隱式轉換問題SQLServer資料庫資料型別
- 【隱式轉換】注意隱式轉換將導致索引無法使用索引
- [轉帖]見識一下SQL Server隱式轉換處理的不同SQLServer
- scala中隱式轉換之隱式轉換呼叫類中本不存在的方法
- Spark中的三種隱式轉換Spark
- MySQL和Oracle中的隱式轉換MySqlOracle
- 高效的SQL(隱式轉換導致不走索引)SQL索引
- js顯式轉換和隱式轉換JS
- sql server 資料型別轉換函式SQLServer資料型別函式
- SQL Server 內建轉換函式介紹SQLServer函式
- Sql Server 資料型別 轉換 函式SQLServer資料型別函式
- Sql Server資料型別轉換函式SQLServer資料型別函式
- javascript 隱式轉換JavaScript
- Oracle 隱式轉換Oracle
- java隱式轉換Java
- sql server 行列轉換SQLServer
- Scala - 隱式轉換和隱式引數
- Scala隱式轉換與隱式引數
- Sql Server系列:資料型別轉換函式SQLServer資料型別函式
- ORACLE中的隱式資料型別轉換(一)Oracle資料型別
- Scala Essentials: 隱式轉換
- sql server行列轉換案例SQLServer
- sql server型別轉換SQLServer型別
- SQL SERVER效能優化(轉)SQLServer優化
- 從Java的型別轉換看MySQL和Oracle中的隱式轉換(二)Java型別MySqlOracle
- [] == ![],走進==隱式轉換的世界
- 【原創】由隱式轉換引起的資料庫效能問題資料庫
- Java資料型別的顯式轉換和隱式轉換Java資料型別
- sql server 資料庫中null 轉換為 0SQLServer資料庫Null
- SQL Server的安全隱患:不要以為黑掉的只是SQL SERVER而已。 (轉)SQLServer
- SQL Server中如何找到歷史增長資訊SQLServer
- 給SQL Server傳送陣列引數的變通辦法(轉)SQLServer陣列
- JavaScript隱式型別轉換JavaScript型別
- mysql隱式轉換問題MySql
- MySQL 隱式型別轉換MySql型別
- 【C++】禁止隱式轉換C++
- SQL Server效能分析引數 (轉)SQLServer