Why am I getting an implicit conversion of Int / Smallint to Varchar, and is it really impacting Cardinality Estimates?

I'm trying to trouble shoot a slow performing query using Show Plan Analysis (SSMS) on the actual execution plan. The Analysis tool points out that estimates for number of rows are off from returned results in a few places in the plan and further gives me some implicit conversion warnings.

I don't understand these implicit conversions of int over to Varchar- The fields referenced are not part of any parameter/filter on the query and in all tables involved the column data types are the same:

I get the below CardinalityEstimate Warnings:


Type conversion in expression
(CONVERT_IMPLICIT(varchar(12),[ccd].[profileid],0)) may affect
"CardinalityEstimate" in query plan choice
--This field is an integer everywhere in my DB


Type conversion in expression
(CONVERT_IMPLICIT(varchar(6),[ccd].[nodeid],0)) may affect
"CardinalityEstimate" in query plan choice
--This field is an smallint everywhere in my DB


Type conversion in expression
(CONVERT_IMPLICIT(varchar(6),[ccd].[sessionseqnum],0)) may affect
"CardinalityEstimate" in query plan choice
--This field is an smallint everywhere in my DB


Type conversion in expression
(CONVERT_IMPLICIT(varchar(41),[ccd].[sessionid],0)) may affect
"CardinalityEstimate" in query plan choice
--This field is an decimal everywhere in my DB

[EDIT] Here is the query and actual execution plan for reference
https://www.brentozar.com/pastetheplan/?id=SysYt0NzN

And table definitions..

```
/ Object: Table [dbo].[agentconnectiondetail] Script Date: 1/10/2019 9:10:04 AM /
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].agentconnectiondetail NOT NULL,
[sessionseqnum] [smallint] NOT NULL,
[nodeid] [smallint] NOT NULL,
[profileid] [int] NOT NULL,
[resourceid] [int] NOT NULL,
[startdatetime] datetime2 NOT NULL,
[enddatetime] datetime2 NOT NULL,
[qindex] [smallint] NOT NULL,
[gmtoffset] [smallint] NO

The implicit conversions are caused by the computed column AltKey:

CREATE TABLE dbo.Test
(
    [sessionid] [decimal](18, 0) NOT NULL,
    [sessionseqnum] [smallint] NOT NULL,
    [nodeid] [smallint] NOT NULL,
    [profileid] [int] NOT NULL,
    [AltKey] AS 
        CONCAT
        (
            [sessionid],
            [sessionseqnum],
            [nodeid],
            [profileid]
        ) PERSISTED NOT NULL,
);

Given the above simplified table, the simple statement below generates the same implicit conversion warnings given in the question:

SELECT T.*
FROM dbo.Test AS T;

From the documentation (emphasis added):

CONCAT implicitly converts all arguments to string types before concatenation.

The warning is added when SQL Server considers a plan alternative that does not use the persisted value, but computes the value explicitly. The warning is not removed if the final plan uses the persisted value.

The warnings may be safely ignored in this case. This also applies to your execution plan, as far as I can tell - the implicit conversions involved in the CONCAT are not adversely affecting plan choice.

Using documented and supported trace flag 176 prevents persisted computed column expansion and removes the warnings:

SELECT * 
FROM dbo.Test AS T
OPTION (QUERYTRACEON 176);

See my article Properly Persisted Computed Columns for more details.

StackExchange Database Administrators Q#226610, answer score: 11