SQL Server中提前找到隐式转换提升性能的办法

作者：网络转载发布时间：[ 2015/2/16 11:55:53 ] 推荐标签：数据库软件开发隐式转换

　　代码清单3.对参数进行转换的执行计划片段
　　既然我们已经知道产生问题的执行计划特征，那么我们可以利用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 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
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
　　代码清单4.找出隐式转换的执行计划
　　对于本例的结果如图1所示。