Sql-Server

為什麼我將 Int / Smallint 隱式轉換為 Varchar，它真的會影響基數估計嗎？

  • October 18, 2020

我正在嘗試在實際執行計劃上使用顯示計劃分析 (SSMS) 解決執行緩慢的查詢。分析工具指出，在計劃中的幾個地方，對行數的估計與返回的結果不一致，並進一步給了我一些隱式轉換警告。

我不明白這些從 int 到 Varchar 的隱式轉換 - 引用的欄位不是查詢中任何參數/過濾器的一部分，並且在所有涉及的表中，列數據類型都是相同的：

我收到以下 CardinalityEstimate 警告：

表達式中的類型轉換 (CONVERT_IMPLICIT(varchar(12),

$$ ccd $$.$$ profileid $$,0)) 可能會影響查詢計劃選擇中的“CardinalityEstimate”——該欄位在我的數據庫中的任何地方都是一個整數 表達式中的類型轉換 (CONVERT_IMPLICIT(varchar(6),

$$ ccd $$.$$ nodeid $$,0)) 可能會影響查詢計劃選擇中的“CardinalityEstimate”——該欄位在我的數據庫中的任何地方都是一個 smallint 表達式中的類型轉換 (CONVERT_IMPLICIT(varchar(6),

$$ ccd $$.$$ sessionseqnum $$,0)) 可能會影響查詢計劃選擇中的“CardinalityEstimate”——該欄位在我的數據庫中的任何地方都是一個 smallint 表達式中的類型轉換 (CONVERT_IMPLICIT(varchar(41),

$$ ccd $$.$$ sessionid $$,0)) 可能會影響查詢計劃選擇中的“CardinalityEstimate”——該欄位在我的數據庫中的任何地方都是小數

$$ EDIT $$這是查詢和實際執行計劃以供參考 https://www.brentozar.com/pastetheplan/?id=SysYt0NzN 和表定義..

/****** 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

隱式轉換是由計算列引起的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,
);

鑑於上面的簡化表，下面的簡單語句會生成與問題中給出的相同的隱式轉換警告：

SELECT T.*
FROM dbo.Test AS T;

有警告的計劃

從文件（強調添加）：

CONCAT 在連接之前將所有參數隱式轉換為字元串類型。

當 SQL Server 考慮不使用持久值但顯式計算值的計劃替代方案時，將添加警告。如果最終計劃使用持久值，則不會刪除警告。

在這種情況下，可以安全地忽略警告。據我所知，這也適用於您的執行計劃——其中涉及的隱式轉換CONCAT不會對計劃選擇產生不利影響。

使用記錄和支持的跟踪標誌 176 可防止持久計算列擴展並刪除警告：

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

帶 TF 176

有關詳細資訊，請參閱我的文章Properly Persisted Computed Columns。

引用自：https://dba.stackexchange.com/questions/226610

相關問答

Sql-Server

我怎樣才能使這個執行計劃更有效率？

  • July 3, 2019
檢視問答
Sql-Server

查詢計劃“基數估計”中的 警告

  • February 8, 2019
檢視問答
Sql-Server

為什麼 DELETE 查詢以一種格式執行的時間比另一種格式長得多？

  • February 6, 2019
檢視問答
Sql-Server

執行計劃中的點陣圖創建導致聚集索引掃描的錯誤估計

  • January 28, 2022
檢視問答
Sql-Server

實際行和估計行差異很大

  • December 22, 2021
檢視問答
Sql-Server

由於連接了 2 個良好估計的結果，如何更正行估計

  • October 30, 2021
檢視問答