Why is this simple query granted so much memory?
-- Demo table
CREATE TABLE dbo.Test
(
TID integer IDENTITY NOT NULL,
FilterMe integer NOT NULL,
SortMe integer NOT NULL,
Unused nvarchar(max) NULL,
CONSTRAINT PK_dbo_Test_TID
PRIMARY KEY CLUSTERED (TID)
);
GO
-- 100,000 example rows
INSERT dbo.Test WITH (TABLOCKX)
(FilterMe, SortMe)
SELECT TOP (100 * 1000)
CHECKSUM(NEWID()) % 1000,
CHECKSUM(NEWID())
FROM sys.all_columns AS AC1
CROSS JOIN sys.all_columns AS AC2;
GO
-- Query
SELECT
T.TID,
T.FilterMe,
T.SortMe,
T.Unused
FROM dbo.Test AS T
WHERE
T.FilterMe = 567
ORDER BY
T.SortMe;
For an estimated 50 rows, the optimizer reserves almost 500 MB for the sort:
Aucun commentaire:
Enregistrer un commentaire