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

ahmed salah 3,216 Reputation points
2022-01-20T12:12:44.977+00:00

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
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,854 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,559 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Tom Phillips 17,716 Reputation points
    2022-01-20T17:14:16.623+00:00

    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

    0 comments No comments

  2. Erland Sommarskog 102.2K Reputation points MVP
    2022-01-20T22:41:01.13+00:00

    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.

    0 comments No comments