SQL Server Warning in execution plan about using tempdb in a Sort node

azpvetkf 于 2023-03-17 发布在其他

关注(0)|答案(1)|浏览(143)

Checking my execution plan it shows a warning in a Top N Sort step
Operator used tempdb to spill data during execution with spill level 1 and 4 spilled thread(s), Sort wrote 4609 pages to and read 4609 pages from tempdb with granted memory 107360KB and used memory 107360KB

Is this a bad thing and how should I tackle it?

The original query was

select a.* from TableA a where id in (
    select a.id from TableA a join TableAB ab on a.id = ab.aid join TableB b on ab.bid = b.id
    order by a.datetime desc offset 1000000 rows fetch next 10 rows only
)

In case you wonder, the above was meant to be a faster version of

select a.* from TableA a join TableAB ab on a.id = ab.aid join TableB b on ab.bid = b.id
order by a.datetime desc offset 1000000 rows fetch next 10 rows only

but although it ran faster, it gave that warning message above.

sql-server

来源：https://stackoverflow.com/questions/75728868/warning-in-execution-plan-about-using-tempdb-in-a-sort-node

1条答案

按热度按时间

qco9c6ql1#

As a rule, it's preferable using EXISTS over IN clause, this is assuming that you don't want duplicates from TableA as @seanb. a possible rewriting to the query using nested EXISTS would look like

select a.* 
from TableA a 
where exists(
    select 1 
    from TableA a1
    where a1.id = a.Id
        and exists(
            select 1 
            from TableAB ab 
            where a1.id = ab.aid 
                and exists (
                    select 1
                    from TableB b 
                    where ab.bid = b.id
                )
            )
    order by a1.datetime desc offset 1000000 rows fetch next 10 rows only
)

The SORT is inevitable, but it should happen on fewer columns than using the ORDER inside the IN clause. Even if you see the spill warning, you can check the number of pages is reduced, IMO, 4609 pages (~36MB) spill shouldn't make much difference, unless you are running this every few seconds.

赞(0）回复(0）举报 2023-03-17

我来回答

SQL Server Warning in execution plan about using tempdb in a Sort node

1条答案

相关问题

热门标签

最新问答