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 DBType 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 DBType 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 DBType 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](
[sessionid] [decimal](18, 0) NOT NULL,
[sessionseqnum] [smallint] NOT NULL,
[nodeid] [smallint] NOT NULL,
[profileid] [int] NOT NULL,
[resourceid] [int] NOT NULL,
[startdatetime] [datetime2](7) NOT NULL,
[enddatetime] [datetime2](7) NOT NULL,
[qindex] [smallint] NOT NULL,
[gmtoffset] [smallint] NOT NULL,
[ringtime] [smallint] NULL,
[talktime] [smallint] NULL,
[holdtime] [smallint] NULL,
[worktime] [smallint] NULL,
[callwrapupdata] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[callresult] [smallint] NULL,
[dialinglistid] [int] NULL,
[convertedStartDatetimelocal] [datetime2](7) NULL,
[convertedEndDatetimelocal] [datetime2](7) NULL,
CONSTRAINT [PK_agentconnectiondetail] PRIMARY KEY CLUSTERED
(
[sessionid] ASC,
[sessionseqnum] ASC,
[nodeid] ASC,
[profileid] ASC,
[resourceid] ASC,
[startdatetime] ASC,
[qindex] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[contactcalldetail] Script Date: 1/10/2019 9:10:04 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[contactcalldetail](
[sessionid] [decimal](18, 0) NOT NULL,
[sessionseqnum] [smallint] NOT NULL,
[nodeid] [smallint] NOT NULL,
[profileid] [int] NOT NULL,
[contacttype] [smallint] NOT NULL,
[contactTypeDescription] [varchar](20) COLLATE Latin1_General_CI_AS NULL,
[contactdisposition] [smallint] NOT NULL,
[contactdispositionDescription] [varchar](20) COLLATE Latin1_General_CI_AS NULL,
[dispositionreason] [varchar](100) COLLATE Latin1_General_CI_AS NULL,
[originatortype] [smallint] NOT NULL,
[originatorTypeDescription] [varchar](20) COLLATE Latin1_General_CI_AS NULL,
[originatorid] [int] NULL,
[originatordn] [varchar](30) COLLATE Latin1_General_CI_AS NULL,
[destinationtype] [smallint] NULL,
[destinationTypeDescription] [varchar](20) COLLATE Latin1_General_CI_AS NULL,
[destinationid] [int] NULL,
[destinationdn] [varchar](30) COLLATE Latin1_General_CI_AS NULL,
[startdatetimeUTC] [datetime2](7) NOT NULL,
[enddatetimeUTC] [datetime2](7) NOT NULL,
[gmtoffset] [smallint] NOT NULL,
[callednumber] [varchar](30) COLLATE Latin1_General_CI_AS NULL,
[origcallednumber] [varchar](30) COLLATE Latin1_General_CI_AS NULL,
[applicationtaskid] [decimal](18, 0) NULL,
[applicationid] [int] NULL,
[applicationname] [varchar](30) COLLATE Latin1_General_CI_AS NULL,
[connecttime] [smallint] NULL,
[customvariable1] [varchar](40) COLLATE Latin1_General_CI_AS NULL,
[customvariable2] [varchar](40) COLLATE Latin1_General_CI_AS NULL,
[customvariable3] [varchar](40) COLLATE Latin1_General_CI_AS NULL,
[customvariable4] [varchar](40) COLLATE Latin1_General_CI_AS NULL,
[customvariable5] [varchar](40) COLLATE Latin1_General_CI_AS NULL,
[customvariable6] [varchar](40) COLLATE Latin1_General_CI_AS NULL,
[customvariable7] [varchar](40) COLLATE Latin1_General_CI_AS NULL,
[customvariable8] [varchar](40) COLLATE Latin1_General_CI_AS NULL,
[customvariable9] [varchar](40) COLLATE Latin1_General_CI_AS NULL,
[customvariable10] [varchar](40) COLLATE Latin1_General_CI_AS NULL,
[accountnumber] [varchar](40) COLLATE Latin1_General_CI_AS NULL,
[callerentereddigits] [varchar](40) COLLATE Latin1_General_CI_AS NULL,
[badcalltag] [char](1) COLLATE Latin1_General_CI_AS NULL,
[transfer] [bit] NULL,
[NextSeqNum] [smallint] NULL,
[redirect] [bit] NULL,
[conference] [bit] NULL,
[flowout] [bit] NULL,
[metservicelevel] [bit] NULL,
[campaignid] [int] NULL,
[origprotocolcallref] [varchar](32) COLLATE Latin1_General_CI_AS NULL,
[destprotocolcallref] [varchar](32) COLLATE Latin1_General_CI_AS NULL,
[convertedStartDatetimelocal] [datetime2](7) NULL,
[convertedEndDatetimelocal] [datetime2](7) NULL,
[AltKey] AS (concat([sessionid],[sessionseqnum],[nodeid],[profileid]) collate database_default) PERSISTED NOT NULL,
[PrvSeqNum] [smallint] NULL,
CONSTRAINT [PK_contactcalldetail] PRIMARY KEY CLUSTERED
(
[sessionid] ASC,
[sessionseqnum] ASC,
[nodeid] ASC,
[profileid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[contactqueuedetail] Script Date: 1/10/2019 9:10:04 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[contactqueuedetail](
[sessionid] [decimal](18, 0) NOT NULL,
[sessionseqnum] [smallint] NOT NULL,
[profileid] [int] NOT NULL,
[nodeid] [smallint] NOT NULL,
[targetid] [int] NOT NULL,
[targettype] [smallint] NOT NULL,
[targetTypeDescription] [varchar](10) COLLATE Latin1_General_CI_AS NULL,
[qindex] [smallint] NOT NULL,
[queueorder] [smallint] NOT NULL,
[disposition] [smallint] NULL,
[dispositionDescription] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[metservicelevel] [bit] NULL,
[queuetime] [smallint] NULL,
CONSTRAINT [PK_contactqueuedetail] PRIMARY KEY CLUSTERED
(
[sessionid] ASC,
[sessionseqnum] ASC,
[profileid] ASC,
[nodeid] ASC,
[targetid] ASC,
[targettype] ASC,
[qindex] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Index [<Name of Missing Index, sysname,>] Script Date: 1/10/2019 9:10:04 AM ******/
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] ON [dbo].[contactcalldetail]
(
[convertedStartDatetimelocal] ASC
)
INCLUDE ( [sessionid],
[sessionseqnum],
[nodeid],
[profileid]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [idx_CCD_ContactType_DestType_StDtLocal] Script Date: 1/10/2019 9:10:04 AM ******/
CREATE NONCLUSTERED INDEX [idx_CCD_ContactType_DestType_StDtLocal] ON [dbo].[contactcalldetail]
(
[destinationtype] ASC,
[contacttype] ASC,
[convertedStartDatetimelocal] ASC
)
INCLUDE ( [sessionid],
[sessionseqnum],
[nodeid],
[profileid],
[convertedEndDatetimelocal]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [idx_CQD_Profile_Traget_TargetType] Script Date: 1/10/2019 9:10:04 AM ******/
CREATE NONCLUSTERED INDEX [idx_CQD_Profile_Traget_TargetType] ON [dbo].[contactqueuedetail]
(
[profileid] ASC,
[targetid] ASC,
[targettype] ASC
)
INCLUDE ( [targetTypeDescription],
[queueorder],
[disposition],
[dispositionDescription],
[queuetime]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Best Answer
The implicit conversions are caused by the computed column
AltKey
:Given the above simplified table, the simple statement below generates the same implicit conversion warnings given in the question:
From the documentation (emphasis added):
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:
See my article Properly Persisted Computed Columns for more details.