April 24, 2013 at 5:21 pm
I periodically run Jonathan Kehayias' script to find implicit conversions in the plan cache for the SQL server that supports our line of business databases. I'm usually able to find these in code and get the data types lined up so that implicit conversions don't have to occur.
However, there's multiple instances where an implicit conversion appears to occur when a table which has a full text index is updated. That particular table has an int-type primary key on the account number, yet the full text index is processing it as a bigint data type as far as I can tell. One of the expressions in our plan cache shows the following:
Scalar Operator(CASE WHEN CASE WHEN [Expr1068] IS NOT NULL THEN (1) ELSE (4) END=(4) THEN CONVERT_IMPLICIT(bigint,[S**********].[dbo].[tblMainAcc********].[AccountKey],0) ELSE [S******].[sys].[fulltext_index_docidstatus_415508384].[docid] END)
If anybody has any ideas on what might cause this, I'd very much appreciate the feedback. It's as much a matter of intellectual curiosity as anything else right now.
Sincerely,
Andre Ranieri
PS: Here's Jonathan's implicit conversion query code:
/*****************************************************************************
* Presentation: Performance Tuning with the Plan Cache
* FileName: 3.2 - Implicit Column Side Conversions.sql
*
* Summary: Demonstrates how to find column side implicit conversions and the
* statements that generated it by parsing XML Plans stored in the
* plan cache.
*
* Date: October 16, 2010
*
* SQL Server Versions:
* 2005, 2008, 2008 R2
*
******************************************************************************
* Copyright (C) 2010 Jonathan M. Kehayias
* All rights reserved.
*
* For more scripts and sample code, check out
* http://sqlblog.com/blogs/jonathan_kehayias
*
* You may alter this code for your own *non-commercial* purposes. You may
* republish altered code as long as you include this copyright and give
*due credit.
*
*
* THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF
* ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED
* TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
* PARTICULAR PURPOSE.
*
******************************************************************************/
/*
-- This demo requires that the appropriate problem be available for it to work.
-- If you run this demo and it returns no results, the following code will
-- create an example of the problem.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @dbname SYSNAME
SET @dbname = QUOTENAME(DB_NAME());
WITH XMLNAMESPACES
(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT top 50
stmt.value('(@StatementText)[1]', 'varchar(max)'),
t.value('(ScalarOperator/Identifier/ColumnReference/@Schema)[1]', 'varchar(128)'),
t.value('(ScalarOperator/Identifier/ColumnReference/@Table)[1]', 'varchar(128)'),
t.value('(ScalarOperator/Identifier/ColumnReference/@Column)[1]', 'varchar(128)'),
ic.DATA_TYPE AS ConvertFrom,
ic.CHARACTER_MAXIMUM_LENGTH AS ConvertFromLength,
t.value('(@DataType)[1]', 'varchar(128)') AS ConvertTo,
t.value('(@Length)[1]', 'int') AS ConvertToLength,
query_plan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS batch(stmt)
CROSS APPLY stmt.nodes('.//Convert[@Implicit="1"]') AS n(t)
JOIN INFORMATION_SCHEMA.COLUMNS AS ic
ON QUOTENAME(ic.TABLE_SCHEMA) = t.value('(ScalarOperator/Identifier/ColumnReference/@Schema)[1]', 'varchar(128)')
AND QUOTENAME(ic.TABLE_NAME) = t.value('(ScalarOperator/Identifier/ColumnReference/@Table)[1]', 'varchar(128)')
AND ic.COLUMN_NAME = t.value('(ScalarOperator/Identifier/ColumnReference/@Column)[1]', 'varchar(128)')
WHERE t.exist('ScalarOperator/Identifier/ColumnReference[@Database=sql:variable("@dbname")][@Schema!="[sys]"]') = 1
Viewing post 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply