Top n percent queries have bonus spool to disk
During a performance test of Effektor, my colleague Thomas discovered something odd. Top n percent queries were slow, much slower than top n queries. Looking at the execution plans, we saw a table spool in the plan for the top n percent query, which explained why.
I guess the spool is there because the top n percent operator needs to be blocking, we need to know the total number of rows coming in, before we know how many rows n percent means.
Here is a test script to replicate the behaviour:
create table numbers( n int ) ; insert into numbers( n ) values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10) ;
select top 1 * from numbers
select top 10 percent * from numbers
You can see the two execution plans in the image above.
The test was performed on SQL Server 2014.