Home Dashboard Directory Help
Search

Relatively simple sort cause spill to disk by Dave_Ballantyne


Status: 

Active


1
0
Sign in
to vote
Type: Bug
ID: 812070
Opened: 12/20/2013 2:36:13 AM
Access Restriction: Public
0
Workaround(s)
view
0
User(s) can reproduce this bug

Description

Hi,

I understand that the are many unknowns, assumptions and assertions that go into making the estimate for a memory grant for operations, however in this scenario is would seem fairly straightforward.

Inserting 1,192,992 integers from a non nullable integer column on a heap into a clustered index ( with the integer being the clustered PK) works within the memory grant.
1,192,993 integers cause a spill !!

Using 1,193,015 rows which cause an over estimation in the number of rows (1,193,020) causes a spill.

Tested on SQL2012 sp2 and SQL2014 CTP2

--------------------------------------

drop table srcheap
go
Create Table SrcHeap
(
ApplicationSK integer not null
)
go

with ctepop
as
(
    Select 1 as g from sys.columns
)
insert into SrcHeap
Select top(1200000) ROW_NUMBER() over(order by (select null))
from ctepop
cross join ctepop a
cross join ctepop b
cross join ctepop c
go

Drop TABLE [DestTable]
go
CREATE TABLE [DestTable](
    [ApplicationSK] [int] NOT NULL
CONSTRAINT [PK_ApplicationWithPK] PRIMARY KEY CLUSTERED
(
    [ApplicationSK] ASC
)
)
go
insert into [DestTable] with (tablock) (
    ApplicationSK
)
Select top(1192992) ApplicationSK
from SrcHeap
option(maxdop 1,recompile,querytraceon 610)
go


Drop TABLE [DestTable]
go
CREATE TABLE [DestTable](
    [ApplicationSK] [int] NOT NULL
CONSTRAINT [PK_ApplicationWithPK] PRIMARY KEY CLUSTERED
(
    [ApplicationSK] ASC
)
)
go
insert into [DestTable] with (tablock) (
    ApplicationSK
)
Select top(1192993) ApplicationSK
from SrcHeap
option(maxdop 1,recompile, querytraceon 610)
go


Details
Sign in to post a comment.
Sign in to post a workaround.