1

I have the below query and I am trying to understand the sort estimate when I use TOP with a variable.

PLAN

create table justint ( c1 int )

insert into justint
select top 1000000 row_number() over(order by t1.number) as N
from   master..spt_values t1 
cross join master..spt_values t2

declare  @MaxRowsToReturn    INT     = 500
SELECT  top (@MaxRowsToReturn) c1
FROM justint WITH (NOLOCK)
where  c1 >= 600000
ORDER BY c1 ASC

So how does an estimate of 404986 going into the sort operator comes out as an estimate of 100? Is it just an arbitrary number since it cant sniff the variable in the TOP operator?

1

This would seem to be an issue with parameter sniffing (or lack thereof in this case).

The optimizer has to choose the best plan for the batch, in this case without knowing how many rows the user is going to need from the TOP operator. The key here is that in your case the @Maxrowstoreturn is set during the batch in which it is used, meaning the engine needs to create the entire plan before that variable is initialized or used. As for why the number 100 is used, it's likely an arbitrary decision by the developers as there is no way to provide a solid estimate in this situation anyway.

If we run the following code, this corrects the estimate, as SQL is able to use the input parameter to create a more accurate execution plan:

create table justint ( c1 int )
go

insert into justint
select top 1000000 row_number() over(order by t1.number) as N
from   master..spt_values t1 
cross join master..spt_values t2
go

update statistics justint with fullscan;
go


create procedure #tmpproc

(@Maxrowstoreturn int)

 AS
begin


SELECT  top (@MaxRowsToReturn) c1
FROM justint WITH (NOLOCK)
--where  c1 >= 600000
ORDER BY c1 ASC
end
go

exec #tmpproc 10000


drop table justint

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.