when execute query it take 32 second to return 2 rows so how to solve slowly issue ?

Question

I work on sql server 2017 i face issue when run simple query it take 32 second to run two rows

so it is very slow according to number of rows returned and size of row not big

my execution plan is

https://www.brentozar.com/pastetheplan/?id=HyaIx0I6Y

script table

CREATE TABLE [Parts].[TradeCodeControl](
    [TradeCodeControlID] [int] IDENTITY(1,1) NOT NULL,
    [VersionYear] [int] NULL,
    [Version] [float] NULL,
    [CodeTypeID] [int] NULL,
    [RevisionID] [bigint] NULL,
    [Code] [varchar](20) NULL,
    [ZPLID] [int] NULL,
    [ZfeatureKey] [bigint] NULL,
    [ZValue] [nvarchar](2500) NULL,
    [ZfeatureType] [nvarchar](200) NULL,
    [Comment] [nvarchar](3000) NULL,
    [ModifiedDate] [datetime] NULL,
    [CreatedDate] [datetime] NOT NULL,
    [Modifiedby] [int] NULL,
    [CreatedBy] [int] NULL,
    [OrderSequence] [tinyint] NULL,
 CONSTRAINT [PK__TradeCod__49C7EB212E609428] PRIMARY KEY CLUSTERED 
(
    [TradeCodeControlID] 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

SET ANSI_PADDING ON
GO

ALTER TABLE [Parts].[TradeCodeControl] ADD  CONSTRAINT [DF__TradeCode__Creat__2439A564]  DEFAULT (getdate()) FOR [CreatedDate]
GO

Answer 1

Your query is almost certainly blocked, and not a problem with the actual query performance.

However, your query plan indicates you may benefit from changing IDX_ZIPLID to include ZfeatureKey and ZValue. But with the data size this small, it will not make any difference.

Please post the actual query plan, instead of the estimated query plan for a better explanation.

Also, your query plan indicates you are running the SQL 2019 RTM version. You should upgrade to a patch level.
https://support.microsoft.com/en-us/topic/kb4518398-sql-server-2019-build-versions-782ed548-1cd8-b5c3-a566-8b4f9e20293a

Answer 2

You say that you work on SQL 2017, but the execution plan is from SQL 2019. Did you get the plan on the same server as where you had the performance issue? I would have been a lot if you had included the Actual Execution Plan instead, as we would have had more information and would had better possibilities to help you.

And when it comes to this SQL 2019 instance, I echo what Tom says. Go to https://www.microsoft.com/en-us/download/details.aspx?id=100809 to download and install the most recent Cumulative Update for SQL 2019 to get many bug fixes and improvements.