November 11, 2014 at 9:50 am
I'm new to using SQL Server. I've been asked to optimize a series of scripts that queries over 4 millions records. I've managed to add indexes and remove a cursor, which helped performance. Now when I run the execution plan, the only query that cost is a DELETE statement from the main table. It shows a SORT which cost 71%. The table has 2 columns and a unique index. Here is the current index:
ALTER TABLE [dbo].[Qry] ADD CONSTRAINT [Qry_PK] PRIMARY KEY NONCLUSTERED
(
[QryNum] ASC,
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = ON, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Question: Will the SORT affect the overall performance? If so, is there anything I should change within the index that would speed up my query?
Thank you
Liza
November 11, 2014 at 10:01 am
lizamungro (11/11/2014)
I'm new to using SQL Server. I've been asked to optimize a series of scripts that queries over 4 millions records. I've managed to add indexes and remove a cursor, which helped performance. Now when I run the execution plan, the only query that cost is a DELETE statement from the main table. It shows a SORT which cost 71%. The table has 2 columns and a unique index. Here is the current index:ALTER TABLE [dbo].[Qry] ADD CONSTRAINT [Qry_PK] PRIMARY KEY NONCLUSTERED
(
[QryNum] ASC,
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = ON, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Question: Will the SORT affect the overall performance? If so, is there anything I should change within the index that would speed up my query?
Thank you
Liza
Quick thought, sort can heavily affect the performance but without further information, any suggestions would be a shot in the dark. Best thing moving forward would be to post the actual execution plan, the DDL for the table and preferably some sample data although the last isn't strictly mandatory.
😎
November 11, 2014 at 10:42 am
At the least we'd need to see the query to determine why there is a sort at all. Without that I have not clue why there is a sort, so I can't even guess if it is a problem or if it can be removed.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 11, 2014 at 10:52 am
Thanks for quick response.
Here is the DDL:
CREATE TABLE [dbo].[Qry(
[QryNum] [int] NOT NULL,
[ID] [int] NOT NULL,
CONSTRAINT [Qry_PK] PRIMARY KEY NONCLUSTERED
(
[QryNum] ASC,
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = ON, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
The execution plan is rather large. But the section where Query cost shows: DELETE cost 0%, Index Delete cost 14%, Sort 71%, table delete cost 13%, index seek cost 2%.
As you mentioned, I bet SORT is the source of my performance issue. Is there a way to remove SORT from my index? Or possibly a workaround to prevent SQL Server from sorting when deleting?
Thanks
November 11, 2014 at 11:53 am
Are you sure the sort is a problem?
There are two ways SQL can do a delete from an index. One is deleting row by row. This is incredibly slow on larger row counts. The other requires a sort.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 12, 2014 at 4:44 am
Just no way to say without the query and the execution plan to understand what's happening and why.
"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
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply