Mixing
Sort spills to tempdb but estimated rows equals to actual rows
On a SQL Server 2016 SP2 with max memory set to 25GB we have a query that executes about 80 times in a minute. The query spills about 4000 pages to tempdb. This causes for a lot of IO on the disk of tempdb.
When you take a look at the query plan (simplied query) you'll see that the number of estimated rows is equal to the number of actual rows but still spills occur. So out-of-date statistics can't be the cause of the problem.
I did some testing and following query spills to Tempdb:
select id --uniqueidentifier
from SortProblem
where [status] ='A'
order by SequenceNumber asc
option (maxdop 1)
But if I select a different column no spills occur:
select startdate --datetime
from SortProblem
where [status] ='A'
order by SequenceNumber asc
option (maxdop 1)
So I tried to 'enlarge' the size of the id column:
select CONVERT(nvarchar(512),id)
from SortProblem
where [status] ='A'
order by SequenceNumber asc
option (maxdop 1)
Then also no spilling occurs.
Why is the uniqueidentifier spilling to tempdb and a datatime column not?
When I delete about 20000 records then also no spilling happens when I select the id column.
With following script you can reproduce the problem:
CREATE TABLE SortProblem
(
id UNIQUEIDENTIFIER,
startdate DATETIME,
sequencenumber BIGINT,
status VARCHAR(50),
PRIMARY KEY CLUSTERED(id)
)
SET nocount ON;
WITH nums(num)
AS (SELECT TOP 103000 ROW_NUMBER()
OVER (
ORDER BY 1/0)
FROM sys.all_objects o1,
sys.all_objects o2)
INSERT INTO SortProblem
SELECT newid(),
DATEADD(millisecond, num, GETDATE()),
num,
CASE
WHEN num <= 100000 THEN 'A'
WHEN num <= 101000 THEN 'B'
WHEN num <= 102000 THEN 'C'
WHEN num <= 103000 THEN 'D'
END
FROM nums
CREATE NONCLUSTERED INDEX [IX_Status]
ON [dbo].[SortProblem]([status] ASC)
INCLUDE ([sequencenumber])
sql-server sql-server-2016 tempdb sorting cardinality-estimates
edited Jan 25 at 14:12
Martin Smith
63.9k10172256
asked Jan 25 at 13:41
Frederik VanderhaegenFrederik Vanderhaegen
8501518
add a comment |
On a SQL Server 2016 SP2 with max memory set to 25GB we have a query that executes about 80 times in a minute. The query spills about 4000 pages to tempdb. This causes for a lot of IO on the disk of tempdb.
When you take a look at the query plan (simplied query) you'll see that the number of estimated rows is equal to the number of actual rows but still spills occur. So out-of-date statistics can't be the cause of the problem.
I did some testing and following query spills to Tempdb:
select id --uniqueidentifier
from SortProblem
where [status] ='A'
order by SequenceNumber asc
option (maxdop 1)
But if I select a different column no spills occur:
select startdate --datetime
from SortProblem
where [status] ='A'
order by SequenceNumber asc
option (maxdop 1)
So I tried to 'enlarge' the size of the id column:
select CONVERT(nvarchar(512),id)
from SortProblem
where [status] ='A'
order by SequenceNumber asc
option (maxdop 1)
Then also no spilling occurs.
Why is the uniqueidentifier spilling to tempdb and a datatime column not?
When I delete about 20000 records then also no spilling happens when I select the id column.
With following script you can reproduce the problem:
CREATE TABLE SortProblem
(
id UNIQUEIDENTIFIER,
startdate DATETIME,
sequencenumber BIGINT,
status VARCHAR(50),
PRIMARY KEY CLUSTERED(id)
)
SET nocount ON;
WITH nums(num)
AS (SELECT TOP 103000 ROW_NUMBER()
OVER (
ORDER BY 1/0)
FROM sys.all_objects o1,
sys.all_objects o2)
INSERT INTO SortProblem
SELECT newid(),
DATEADD(millisecond, num, GETDATE()),
num,
CASE
WHEN num <= 100000 THEN 'A'
WHEN num <= 101000 THEN 'B'
WHEN num <= 102000 THEN 'C'
WHEN num <= 103000 THEN 'D'
END
FROM nums
CREATE NONCLUSTERED INDEX [IX_Status]
ON [dbo].[SortProblem]([status] ASC)
INCLUDE ([sequencenumber])
sql-server sql-server-2016 tempdb sorting cardinality-estimates
edited Jan 25 at 14:12
Martin Smith
63.9k10172256
asked Jan 25 at 13:41
Frederik VanderhaegenFrederik Vanderhaegen
8501518
add a comment |
On a SQL Server 2016 SP2 with max memory set to 25GB we have a query that executes about 80 times in a minute. The query spills about 4000 pages to tempdb. This causes for a lot of IO on the disk of tempdb.
When you take a look at the query plan (simplied query) you'll see that the number of estimated rows is equal to the number of actual rows but still spills occur. So out-of-date statistics can't be the cause of the problem.
I did some testing and following query spills to Tempdb:
select id --uniqueidentifier
from SortProblem
where [status] ='A'
order by SequenceNumber asc
option (maxdop 1)
But if I select a different column no spills occur:
select startdate --datetime
from SortProblem
where [status] ='A'
order by SequenceNumber asc
option (maxdop 1)
So I tried to 'enlarge' the size of the id column:
select CONVERT(nvarchar(512),id)
from SortProblem
where [status] ='A'
order by SequenceNumber asc
option (maxdop 1)
Then also no spilling occurs.
Why is the uniqueidentifier spilling to tempdb and a datatime column not?
When I delete about 20000 records then also no spilling happens when I select the id column.
With following script you can reproduce the problem:
CREATE TABLE SortProblem
(
id UNIQUEIDENTIFIER,
startdate DATETIME,
sequencenumber BIGINT,
status VARCHAR(50),
PRIMARY KEY CLUSTERED(id)
)
SET nocount ON;
WITH nums(num)
AS (SELECT TOP 103000 ROW_NUMBER()
OVER (
ORDER BY 1/0)
FROM sys.all_objects o1,
sys.all_objects o2)
INSERT INTO SortProblem
SELECT newid(),
DATEADD(millisecond, num, GETDATE()),
num,
CASE
WHEN num <= 100000 THEN 'A'
WHEN num <= 101000 THEN 'B'
WHEN num <= 102000 THEN 'C'
WHEN num <= 103000 THEN 'D'
END
FROM nums
CREATE NONCLUSTERED INDEX [IX_Status]
ON [dbo].[SortProblem]([status] ASC)
INCLUDE ([sequencenumber])
sql-server sql-server-2016 tempdb sorting cardinality-estimates
edited Jan 25 at 14:12
Martin Smith
63.9k10172256
asked Jan 25 at 13:41
Frederik VanderhaegenFrederik Vanderhaegen
8501518
On a SQL Server 2016 SP2 with max memory set to 25GB we have a query that executes about 80 times in a minute. The query spills about 4000 pages to tempdb. This causes for a lot of IO on the disk of tempdb.
When you take a look at the query plan (simplied query) you'll see that the number of estimated rows is equal to the number of actual rows but still spills occur. So out-of-date statistics can't be the cause of the problem.
I did some testing and following query spills to Tempdb:
select id --uniqueidentifier
from SortProblem
where [status] ='A'
order by SequenceNumber asc
option (maxdop 1)
But if I select a different column no spills occur:
select startdate --datetime
from SortProblem
where [status] ='A'
order by SequenceNumber asc
option (maxdop 1)
So I tried to 'enlarge' the size of the id column:
select CONVERT(nvarchar(512),id)
from SortProblem
where [status] ='A'
order by SequenceNumber asc
option (maxdop 1)
Then also no spilling occurs.
Why is the uniqueidentifier spilling to tempdb and a datatime column not?
When I delete about 20000 records then also no spilling happens when I select the id column.
With following script you can reproduce the problem:
CREATE TABLE SortProblem
(
id UNIQUEIDENTIFIER,
startdate DATETIME,
sequencenumber BIGINT,
status VARCHAR(50),
PRIMARY KEY CLUSTERED(id)
)
SET nocount ON;
WITH nums(num)
AS (SELECT TOP 103000 ROW_NUMBER()
OVER (
ORDER BY 1/0)
FROM sys.all_objects o1,
sys.all_objects o2)
INSERT INTO SortProblem
SELECT newid(),
DATEADD(millisecond, num, GETDATE()),
num,
CASE
WHEN num <= 100000 THEN 'A'
WHEN num <= 101000 THEN 'B'
WHEN num <= 102000 THEN 'C'
WHEN num <= 103000 THEN 'D'
END
FROM nums
CREATE NONCLUSTERED INDEX [IX_Status]
ON [dbo].[SortProblem]([status] ASC)
INCLUDE ([sequencenumber])
sql-server sql-server-2016 tempdb sorting cardinality-estimates
sql-server sql-server-2016 tempdb sorting cardinality-estimates
edited Jan 25 at 14:12
Martin Smith
63.9k10172256
asked Jan 25 at 13:41
Frederik VanderhaegenFrederik Vanderhaegen
8501518
edited Jan 25 at 14:12
Martin Smith
63.9k10172256
asked Jan 25 at 13:41
Frederik VanderhaegenFrederik Vanderhaegen
8501518
edited Jan 25 at 14:12
Martin Smith
63.9k10172256
edited Jan 25 at 14:12
Martin Smith
63.9k10172256
edited Jan 25 at 14:12
Martin Smith
63.9k10172256
63.9k10172256
asked Jan 25 at 13:41
Frederik VanderhaegenFrederik Vanderhaegen
8501518
asked Jan 25 at 13:41
Frederik VanderhaegenFrederik Vanderhaegen
8501518
asked Jan 25 at 13:41
Frederik VanderhaegenFrederik Vanderhaegen
8501518
8501518
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Enable trace flag 7470.
FIX: Sort operator spills to tempdb in SQL Server 2012 or SQL Server 2014 when estimated number of rows and row size are correct
As I wrote in answer to Query Plan question:
This trace flag corrects an oversight in the calculation. It is quite safe to use, and in my opinion ought to be on by default. The change is protected by a trace flag simply to avoid unexpected plan changes.
answered Jan 25 at 14:04
Paul White♦Paul White
53.2k14284457
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f228089%2fsort-spills-to-tempdb-but-estimated-rows-equals-to-actual-rows%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Enable trace flag 7470.
FIX: Sort operator spills to tempdb in SQL Server 2012 or SQL Server 2014 when estimated number of rows and row size are correct
As I wrote in answer to Query Plan question:
This trace flag corrects an oversight in the calculation. It is quite safe to use, and in my opinion ought to be on by default. The change is protected by a trace flag simply to avoid unexpected plan changes.
answered Jan 25 at 14:04
Paul White♦Paul White
53.2k14284457
add a comment |
Enable trace flag 7470.
FIX: Sort operator spills to tempdb in SQL Server 2012 or SQL Server 2014 when estimated number of rows and row size are correct
As I wrote in answer to Query Plan question:
This trace flag corrects an oversight in the calculation. It is quite safe to use, and in my opinion ought to be on by default. The change is protected by a trace flag simply to avoid unexpected plan changes.
answered Jan 25 at 14:04
Paul White♦Paul White
53.2k14284457
add a comment |
Enable trace flag 7470.
FIX: Sort operator spills to tempdb in SQL Server 2012 or SQL Server 2014 when estimated number of rows and row size are correct
As I wrote in answer to Query Plan question:
This trace flag corrects an oversight in the calculation. It is quite safe to use, and in my opinion ought to be on by default. The change is protected by a trace flag simply to avoid unexpected plan changes.
answered Jan 25 at 14:04
Paul White♦Paul White
53.2k14284457
Enable trace flag 7470.
FIX: Sort operator spills to tempdb in SQL Server 2012 or SQL Server 2014 when estimated number of rows and row size are correct
As I wrote in answer to Query Plan question:
This trace flag corrects an oversight in the calculation. It is quite safe to use, and in my opinion ought to be on by default. The change is protected by a trace flag simply to avoid unexpected plan changes.
answered Jan 25 at 14:04
Paul White♦Paul White
53.2k14284457
answered Jan 25 at 14:04
Paul White♦Paul White
53.2k14284457
answered Jan 25 at 14:04
Paul White♦Paul White
53.2k14284457
answered Jan 25 at 14:04
Paul White♦Paul White
53.2k14284457
53.2k14284457
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f228089%2fsort-spills-to-tempdb-but-estimated-rows-equals-to-actual-rows%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
