Low performance query while using database variables

Question

I using next queries for extracting top 100 and 101 lines from DB and gettings following elapsing times, which completely different (second query ~8 slower than first):

SELECT TOP (100) *
 FROM PhotoLike WHERE photoAccountId=@accountId AND accountId<>@accountId
 ORDER BY createDate DESC
GO

SQL Server Execution Times: CPU time = 187 ms, elapsed time = 202 ms.

SELECT TOP (101) *
 FROM PhotoLike WHERE photoAccountId=@accountId AND accountId<>@accountId
 ORDER BY createDate DESC
GO

SQL Server Execution Times: CPU time = 266 ms, elapsed time = 1644 ms.

Execution plan of first two cases:

But if I get rid of @accoundId variable, I get following results, which approximately equals and faster more than 2 times than first query from this question.

SELECT TOP (100) *
 FROM PhotoLike WHERE photoAccountId=10 AND accountId<>10
 ORDER BY createDate DESC
GO

SQL Server Execution Times: CPU time = 358 ms, elapsed time = 90 ms.

SELECT TOP (101) *
 FROM PhotoLike WHERE photoAccountId=10 AND accountId<>10
 ORDER BY createDate DESC
GO

SQL Server Execution Times: CPU time = 452 ms, elapsed time = 93 ms.

Execution plan of second two cases:

Why is this happen and how can I improve performance with varibales?

UPDATE

Added execution plans.

Answer 1

There are a couple of things going on here.

When you use variables SQL Server doesn't sniff the values at all except if you also add OPTION (RECOMPILE).

The estimate for the number of rows matching photoAccountId=@accountId is much smaller with the guess than is actually the case. (Note the thick line coming out of the index seek in the second plan and the decision to use a parallel plan).

Also TOP 100 / TOP 101 is the cut off point between the TOP N sort using an algorithm that just needs space to sort 100 rows and it doing a full sort.. The inaccurate row count estimate likely means there is insufficient memory allocated for the full sort and it is spilling to tempdb.

Simply adding OPTION (RECOMPILE) to the query with variables will likely improve things somewhat though it looks as though even the "fast" plan is doing many key lookups that could be avoided with different indexing.