Relatively simple sort cause spill to disk - by Dave_Ballantyne

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


1
0
Sign in
to vote
ID 812070 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 12/20/2013 2:36:13 AM
Access Restriction Public

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


Sign in to post a comment.