snippetsqlModerate
Unexplained sort operation in execution plan
Viewed 0 times
operationunexplainedplansortexecution
Problem
I am running the following query on a database with 7.5 million records:
For some reason the execution plan includes a sort operation on the primary key.
I wouldn't expect it to have to sort the primary key given that there is already a clustered index on that column.
Can anyone shed some light on why this is happening and what I might do to avoid the sort operation?
Edit:
Table creation script
UPDATE TestTable
SET TestCol='1234', TestCol2='1234', TestCol3='1234', Anonymised=1
WHERE [Date] >= '25 June 2016'
AND [Date] <= '25 August 2016'For some reason the execution plan includes a sort operation on the primary key.
I wouldn't expect it to have to sort the primary key given that there is already a clustered index on that column.
Can anyone shed some light on why this is happening and what I might do to avoid the sort operation?
Edit:
Table creation script
CREATE TABLE [dbo].[TestTable](
[Id] [int] IDENTITY(1,1) NOT NULL,
[TestCol] [nvarchar](50) NOT NULL,
[TestCol2] [nvarchar](50) NOT NULL,
[TestCol3] [nvarchar](50) NOT NULL,
[Anonymised] [tinyint] NOT NULL,
[Date] [datetime] NOT NULL,
CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]Solution
Your plan is using a nonclustered index keyed first on, presumably,
In order to optimize IO access of the clustered index update, data coming out of that index access operation is sorted to match key order of the PK/CX.
As an aside, you shouldn't be updating that many rows in one go.
Date to find values that qualify for your WHERE clause.In order to optimize IO access of the clustered index update, data coming out of that index access operation is sorted to match key order of the PK/CX.
As an aside, you shouldn't be updating that many rows in one go.
Context
StackExchange Database Administrators Q#210529, answer score: 10
Revisions (0)
No revisions yet.