January 20, 2022 at 12:15 pm
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
January 20, 2022 at 1:16 pm
You have a key lookup, which you want to eliminate.
Please post the definition of the index IDX_ZPLID
January 20, 2022 at 1:19 pm
The execution plan doesn't have runtime metrics (what's called an "Actual Plan" in SSMS), so it's a little hard to know precisely what's happening. You say it's returning two rows. The plan estimates 10 rows. That's pretty darned close, so assuming the data in the tables is the same as the optimizer thinks it is, you've got a good plan for the code and structures.
So, what's the problem?
Well, it could be that your statistics are out of date. The optimizer thinks you have a table cardinality of 4,526 rows. Is that right? If so, even on my laptop, running a container, I'd see performance better than 30 seconds for this query, so other things may be going on. If not, get the statistics updated and see what the execution plan, and performance, are then.
Otherwise, you could be looking at blocking as the primary cause of the problem. Do you have monitoring in place to look for that? If not, do that too.
As to the plan & query, there are several tuning opportunities (do these after checking statistics). First, you've got a key lookup operation. You could modify the nonclustered index idx_zplid to INCLUDE the three columns it needs to satisfy the query: [Z2DataCore].[Parts].[TradeCodeControl].CodeTypeID, [Z2DataCore].[Parts].[TradeCodeControl].RevisionID, [Z2DataCore].[Parts].[TradeCodeControl].Code. That will eliminate the lookup and increase performance.
Another is to look to creating a filtered index to eliminate null values for ZfeatureKey and ZValue within the index. You could modify the existing index, or maybe create a new one (with the INCLUDE columns for both). That will also improve performance.
That's about all I can see based on what we have.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 20, 2022 at 1:28 pm
create nonclustered index IDX_ZPLID on ExtractReports.dbo.TPls(ZPLID)
January 20, 2022 at 1:29 pm
Another possible issue is a cross-DB table creation and insert
INTO ExtractReports.dbo.TPls
Is the query running in the ExtractReports DB?
January 20, 2022 at 1:35 pm
I would try replacing the existing index with one of the following indexes.
Test the performance of each one
CREATE NONCLUSTERED INDEX IDX_ZPLID
ON ExtractReports.dbo.TPls ( ZPLID, ZfeatureKey, ZValue )
INCLUDE ( Code, CodeTypeID, RevisionID );
--------------------------------------------------------
CREATE NONCLUSTERED INDEX IDX_ZPLID
ON ExtractReports.dbo.TPls ( ZPLID )
INCLUDE ( Code, CodeTypeID, RevisionID, ZfeatureKey, ZValue );
--------------------------------------------------------
CREATE NONCLUSTERED INDEX IDX_ZPLID
ON ExtractReports.dbo.TPls ( ZPLID )
INCLUDE ( Code, CodeTypeID, RevisionID )
WHERE ZfeatureKey IS NULL AND ZValue IS NULL;
January 20, 2022 at 4:08 pm
the index you were asked to supply is the one on table parts.tradecodecontrol - index IDZ_ZPLID , not on ExtractReports.dbo.TPls
the indexes that Des mentioned above would not help you as they are not on the table being selected from - but the contents were near enough.
I could tell you to create/change the existing index but as you have failed to supply the full table DDL including ALL of its indexes I will wait and see if you do supply them this time.
And as you have been advised a few times already supply the ACTUAL explain plan, not an estimated one as this may not give us the details required to address your issue.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply