HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlModerate

Unexplained sort operation in execution plan

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
operationunexplainedplansortexecution

Problem

I am running the following query on a database with 7.5 million records:

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, 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.