I wanted to test tempdb spill warnings so I run the following script on SQL Server 2014:
USE tempdb
IF OBJECT_ID('tempdb..tblTest') IS NOT NULL DROP TABLE tblTest
CREATE TABLE tblTest
(
c1 INT PRIMARY KEY CLUSTERED,
c2 INT ,
c3 CHAR (1000)
);
GO
SET NOCOUNT ON;
BEGIN TRANSACTION;
DECLARE @i AS INT;
SET @i = 1;
WHILE @i <= 10000
BEGIN
INSERT INTO tblTest (c1, c2, c3)
VALUES (@i, @i, 'a');
SET @i = @i + 1;
END
COMMIT TRANSACTION;
GO
UPDATE STATISTICS dbo.tblTest
GO
SET STATISTICS XML ON;
GO
--no tempdb spill (SQL Server 2014)
--in sql server 2012 always different "Estimated number of rows" if you run the whole script several times
SELECT *
FROM tblTest
WHERE c1 <= 5948
ORDER BY c2
OPTION (MAXDOP 1);
GO
SET STATISTICS XML OFF;
SET STATISTICS XML ON;
GO
--no tempdb spill (SQL Server 2014)
SELECT *
FROM tblTest
WHERE c1 <= 5949
ORDER BY c2
OPTION (MAXDOP 1);
GO
SET STATISTICS XML OFF;
(core of the query was based on one material from MS)
1) The first thing I am curious about is what causes the tempdb spill during sort operation at this specific level of c1 column. All the estimates are correct and count of the read page is the same for both queries. So why is the second query spilled? (In another words why memory grant for the later query is so much higher).
2) I have tested this query on SQL Server 2012 and got very interesting behaviour as well. Firstly I was unable to get to the same thresholds, so I run the script repeatedly and noticed that estimated number of rows is always different than it was in the previous run. My question is why is the estimated number of returned rows always different when I repeatedly run the very same script (creating, inserting a updating it's own statistics via full scan)?
